Restore database SQL server query
For a database using the full or bulk-logged recovery model, SQL Server requires in most cases that you back up the tail of the log before restoring the database. For more information, see .
B. Restoring full and differential database backups
The following example restores a full database backup followed by a differential backup from the Z:SQLServerBackupsAdventureWorks2012.bak backup device, which contains both backups. The full database backup to be restored is the sixth backup set on the device (FILE = 6), and the differential database backup is the ninth backup set on the device (FILE = 9). As soon as the differential backup is recovered, the database is recovered.
RESTORE DATABASE AdventureWorks2012 FROM DISK = 'Z:SQLServerBackupsAdventureWorks2012.bak' WITH FILE = 6 NORECOVERY; RESTORE DATABASE AdventureWorks2012 FROM DISK = 'Z:SQLServerBackupsAdventureWorks2012.bak' WITH FILE = 9 RECOVERY;
C. Restoring a database using RESTART syntax
The following example uses the RESTART option to restart a RESTORE operation interrupted by a server power failure.
- This database RESTORE halted prematurely due to power failure. RESTORE DATABASE AdventureWorks2012 FROM AdventureWorksBackups; - Here is the RESTORE RESTART operation. RESTORE DATABASE AdventureWorks2012 FROM AdventureWorksBackups WITH RESTART;
D. Restoring a database and move files
The following example restores a full database and transaction log and moves the restored database into the C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLData directory.
RESTORE DATABASE AdventureWorks2012 FROM AdventureWorksBackups WITH NORECOVERY, MOVE 'AdventureWorks2012_Data' TO 'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDataNewAdvWorks.mdf', MOVE 'AdventureWorks2012_Log' TO 'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDataNewAdvWorks.ldf'; RESTORE LOG AdventureWorks2012 FROM AdventureWorksBackups WITH RECOVERY;
E. Copying a database using BACKUP and RESTORE
The following example uses both the BACKUP and RESTORE statements to make a copy of the AdventureWorks2012 database. The MOVE statement causes the data and log file to be restored to the specified locations. The RESTORE FILELISTONLY statement is used to determine the number and names of the files in the database being restored. The new copy of the database is named TestDB. For more information, see .
BACKUP DATABASE AdventureWorks2012 TO AdventureWorksBackups ; RESTORE FILELISTONLY FROM AdventureWorksBackups ; RESTORE DATABASE TestDB FROM AdventureWorksBackups WITH MOVE 'AdventureWorks2012_Data' TO 'C:MySQLServertestdb.mdf', MOVE 'AdventureWorks2012_Log' TO 'C:MySQLServertestdb.ldf'; GO
F. Restoring to a point-in-time using STOPAT
The following example restores a database to its state as of 12:00 AM on and shows a restore operation that involves multiple log backups. On the backup device, AdventureWorksBackups, the full database backup to be restored is the third backup set on the device (FILE = 3), the first log backup is the fourth backup set (FILE = 4), and the second log backup is the fifth backup set (FILE = 5).
RESTORE DATABASE AdventureWorks2012 FROM AdventureWorksBackups WITH FILE=3, NORECOVERY; RESTORE LOG AdventureWorks2012 FROM AdventureWorksBackups WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM'; RESTORE LOG AdventureWorks2012 FROM AdventureWorksBackups WITH FILE=5, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM'; RESTORE DATABASE AdventureWorks2012 WITH RECOVERY;
G. Restoring the transaction log to a mark
The following example restores the transaction log to the mark in the marked transaction named ListPriceUpdate.magic beneath us magic before magic at mcd jmagick java imagicka vmagic reviews rmagic rx7 magic zoo magic words magic valley mall magic up materia location magic tricks magic through heart magic shows near me magic quadrant magic outside comfort zone magic off hand osrs magic of next magic of lights milwaukee magic inside out magic formula investing magic don juan magic by america magic against the spread magic after dark atlanta dmagic us dmagic pills amagicman twitch vmagic cream smagical crash qmagic suction cup opposite of magic moment magic xp quests osrs magic within you lyrics magic waters magic wand magic vs bucks last game magic vs bird magic user's club magic up materia ff7 remake magic under the stars magic under eye cream tiktok magic to do pippin lyrics magic tiles 3 magic through music magic the gathering arena magic school bus magic quarterstaff 5e magic quadrant gartner 2021 magic proteção contra cor magic pro steamer magic out of hands recipes magic of lights vail magic of lights magic of i magic nails willowbrook magic mtgo magic legends magic lantern magic kingdom open till midnight magic kingdom hours magic keyboard till ipad pro 11 tum magic keyboard till ipad pro magic johnson net worth magic johnson net worth magic jack magic honey magic for humans staged magic event magic definition magic by pilot lyrics magic bullet magic behind the scenes magic before sunrise magic bars magic awakened magic at midnight magic as a name magic around synonym magic about water magic about book magic jmagick maven jmagick download do you believe in magic magic magic bmagical pajamas what are the 12 types of magic materia ff7 remake magic vs bird magic shop llc magic over zoom magic off hand magic of opposite word magic hat magic dental magic by txt magic between us magic behind the kunai magic before christmas christine feehan magic at the musicals magic archer gmagick vs imagick even magic square algorithm umagic osim rmagic rx8 wing past magic sets outside magic sacrifice magic xanadu magic vs lakers magic vs bucks box score magic valley electric magic space next to church magic seaweed magic puzzle company magic needle magic inside mlp magic inside magic initiate 5e magic initiate 5e magic house magic gif magic depth chart magic city magic butter machine magic bum leggings magic bum 5th location magic behind the voice actors magic behind the scenes magic awakened magic as an adjective magic against steel dragons magic after dark city winery magic about love jmagick java example god magic among us apk majix from cartel crew magic-pak thru the wall unit magic zombie deck magic zendikar rising magic without props magic wand magic vs bucks magic underwear magic through music magic sponge made of magic quadrant gartner magic past games magic nails countryside magic mike xxl magic leap magic keyboard ipad pro magic inside of you quotes magic by olivia newton john magic before midnight magic at mcdonald's magic against dwemer magic against dwemer gmagick fmagicline emagic sounddiver qmagic suction cup magic years magic words thru the zodiac magic without lies magic with dogs magic vs bucks magic used auto parts magic tiles magic through glass magic the gathering since magic pixies magic over disneyland paris magic over disneyland magic leap magic kingdom open until midnight magic in front of dogs magic fingers magic dance lyrics magic as programming magic armor 5e