eraberet34

Issue 　　 One of many challenges for database administrators is always to backup and restore the database. The backup is accomplished inside the automatic schedule, but the recovery can take many distinct versions, you might require to restore a product database, restore a improvement package, or test the database, or simply create a copy with the database in one more spot. You will discover some solutions to automate the recovery method and generate scripts, but this technique shows 1 approach to read only the contents of the directory that exists within the backup file.
　　 Professional answers
　　The following can be a easy approach to read the contents in the directory and create a restore command that requirements to be executed to restore the database. This script may be made use of for full backup, differential backup and transaction log backup.
　　Before we start out, the following script assumes the following circumstance:
　　 1. The restored database has precisely the same name because the backup database.
　　 2. recover deleted data from pendrive restored database as well as the backup database are stored in the exact same place.
　　 3. pen drive deleted data recovery software has the following format
　　DbName_YYYYMMDDHHMM.xxx
　　 four. The file extension is as follows
　　All backups-BAK
　　 Differential backup-DIF
　　 Transaction log backup-TRN
　　 5, XP_CMDSHELL is offered
　　 6. There is certainly no missing transaction log that might damage the recovery chain
　　 So, let us follow the methods beneath to create our own backup:
Perform full backups at midnight, begin a differential backup just about every 3 hours starting at 3:15 am, and start off a log backup each and every half hour beginning at 1 am At 9 am, we may get the following backup made on September ten, 2008 File, this backup file follows the above guidelines and is named Buyer.
　　Customer_200809100000.BAK
　　Customer_200809100100.TRN
　　Customer_200809100130.TRN
　　Customer_200809100200.TRN
　　Customer_200809100230.TRN
　　Customer_200809100300.TRN
　　Customer_200809100315.DIF
　　Customer_200809100330.TRN
　　Customer_200809100400.TRN
　　Customer_200809100430.TRN
　　Customer_200809100500.TRN
　　Customer_200809100530.TRN
　　Customer_200809100600.TRN
　　Customer_200809100615.DIF
　　Customer_200809100630.TRN
　　Customer_200809100700.TRN
　　Customer_200809100730.TRN
　　Customer_200809100800.TRN
　　Customer_200809100830.TRN
　　Customer_200809100900.TRN
　　If we would like to restore the most recent all backups, differential backups and transaction log backups at 9 am, then we need to restore the following files:
　　Customer_200809100000.BAK
　　Customer_200809100615.DIF
　　Customer_200809100630.TRN
　　Customer_200809100700.TRN
　　Customer_200809100730.TRN
　　Customer_200809100800.TRN
　　Customer_200809100830.TRN
　　Customer_200809100900.TRN
　　 The following script will study the directory and create a recovery script for us. how to recover data from formatted pen drive that have to be changed are @dbName and @backupPath.
USEMaster;
GO
SETNOCOUNTON
--1-Variabledeclaration
DECLARE@dbNamesysname
DECLARE@backupPathNVARCHAR(500)
DECLARE@cmdNVARCHAR(500)
DECLARE@fileListTABLE(backupFileNVARCHAR(255))
DECLARE@lastFullBackupNVARCHAR(500)
DECLARE@lastDiffBackupNVARCHAR(500)
DECLARE@backupFileNVARCHAR(500)
--2-Initializevariables
SET@dbName='Customer'
SET@backupPath='D:\SQLBackups\'
--3-getlistoffiles
SET@cmd='DIR/b'+@backupPath
INSERTINTO@fileList(backupFile)
EXECmaster.sys.xp_cmdshell@cmd
--4-Findlatestfullbackup
SELECT@lastFullBackup=MAX(backupFile)
FROM@fileList
WHEREbackupFileLIKE '%.BAK'
ANDbackupFileLIKE@dbName+'%'
SET@cmd='RESTOREDATABASE'+@dbName+'FROMDISK='''
+@backupPath+@lastFullBackup+'''WITHNORECOVERY,REPLACE'
PRINT@cmd
--4-Findlatestdiffbackup
SELECT@lastDiffBackup=MAX(backupFile)
FROM@fileList
WHEREbackupFileLIKE '%.DIF'
ANDbackupFileLIKE@dbName+'%'
ANDbackupFile@lastFullBackup
--checktomakesurethereisadiffbackup
IF@lastDiffBackupISNOTNULL
Commence
SET@cmd='RESTOREDATABASE'+@dbName+'FROMDISK='''
+@backupPath+@lastDiffBackup+'''WITHNORECOVERY'
PRINT@cmd
SET@lastFullBackup=@lastDiffBackup
End
--5-checkforlogbackups
DECLAREbackupFilesCURSORFOR
SELECTbackupFile
FROM@fileList
WHEREbackupFileLIKE '%.TRN'
ANDbackupFileLIKE@dbName+'%'
ANDbackupFile@lastFullBackup
OPENbackupFiles
--Loopthroughallthefilesforthedatabase
FETCHNEXTFROMbackupFilesINTO@backupFile
WHILE@@FETCH_STATUS=0
Commence
SET@cmd='RESTORELOG'+@dbName+'FROMDISK='''
+@backupPath+@backupFile+'''WITHNORECOVERY'
PRINT@cmd
FETCHNEXTFROMbackupFilesINTO@backupFile
Finish
CLOSEbackupFiles
DEALLOCATEbackupFiles
--6-putdatabaseinauseablestate
SET@cmd='RESTOREDATABASE'+@dbName+'WITHRECOVERY'
PRINT@cmd
　　 For those who run the above code inside a query window and assume that the files listed above exist, then you definitely will get the following output. At this moment, you'll be able to copy and paste the code into one more query window and execute the query to perform a real recovery.
　　
　　As you can see, it does a full backup, and the most current differential backup is followed by all transaction logs. This script lastly made use of WITHRECOVERY to keep the database out there.