I created a short run book at work for doing restores, part of it was this quick cheat sheet, reminder on the T-SQL Syntax for restores.

 

RESTORE FILELISTONLY

Returns the logical name of the database files, If you are doing a restore purely in TSQL you need to use this RESTORE for the WITH MOVE.

/*RESTORE FILELISTONLY to get details of logical files*/
RESTORE FILELISTONLY
FROM DISK = 'F:\SQLData\Backup\ELLIS\AdventureWorks2012\FULL\Backup.bak'
GO

Example of the results returned:

RestoreFileListOnlyResults

 

 

RESTORE WITH NORECOVERY

NORECOVERY means that when the restore finishes it leaves the database in a state that can STILL restore more backup files.

If you need to restore a FULL backup followed by a DIFF or LOG backup. The FULL backup HAS TO BE RESTORED WITH NORECOVERY!

USE [master]
RESTORE DATABASE [AdventureWorks2012] FROM DISK = N'F:\SQLData\Backups\ELLIS\AdventureWorks2012\FULL\ELLIS_AdventureWorks2012_FULL_20131019_225709.bak' 
WITH NORECOVERY, STATS = 10

RESTORE DATABASE [AdventureWorks2012] FROM DISK = N'F:\SQLData\Backups\ELLIS\AdventureWorks2012\DIFF\ELLIS_AdventureWorks2012_DIFF_20131020_003919.bak' 
WITH STATS = 10
GO

 

RESTORE WITH FILE

It is possible to have multiple backups to a single physical file. When restoring from the file you will need state which backup to restore using WITH FILE.

USE [master]
RESTORE DATABASE [AdventureWorks2012] FROM DISK = N'F:\SQLData\Backups\ELLIS\AdventureWorks2012\FULL\ELLIS_AdventureWorks2012_FULL_20131019_225709.bak' 
WITH FILE = 1

 

RESTORE WITH MOVE

When restoring a database and needing to MOVE the data files due to either restoring the same database multiple times to the same instance or any other. The syntax is below:

/* Record logical file names */
RESTORE FILELISTONLY FROM DISK = N'F:\SQLData\Backups\ELLIS\AdventureWorks2012\FULL\ELLIS_AdventureWorks2012_FULL_20131019_225709.bak'

/*
For AdventureWorks2012 the logical files
AdventureWorks2012_Data
AdventureWorks2012_Log
*/

/*Now run RESTORE WITH MOVE*/
USE [master]
RESTORE DATABASE [AdventureWorks2012_2] FROM DISK = N'F:\SQLData\Backups\ELLIS\AdventureWorks2012\FULL\ELLIS_AdventureWorks2012_FULL_20131019_225709.bak'
WITH
MOVE 'AdventureWorks2012_Data' TO 'F:\SQLData\AdventureWorks2012_2_Data.mdf',
MOVE 'AdventureWorks2012_Log' TO 'F:\SQLData\AdventureWorks2012_2_Log.ldf',
NORECOVERY, STATS = 10
GO

 

Advertisements