syntaxhelp.com Your online cheat sheet!

Restoring Database from a Disk File

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backup\Adventureworks.bak'

Restoring Database from a Disk File with MOVE

--If the files locations are to be modified for the restored database use MOVE option
--Check the logical file names from the database being restored
RESTORE FILELISTONLY 
FROM DISK = 'C:\Backup\Adventureworks.bak'

/* Output
LogicalName	PhysicalName	
-----------------------------------
AdventureWorks	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorks.mdf	
AdventureWorks_log	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorks_log.ldf	
*/
-- MOVE 'LogicalName' To 'New Physical path'
RESTORE DATABASE AdventureWorks1	
FROM DISK = 'C:\Backup\Adventureworks.bak'
WITH MOVE 'AdventureWorks' To 'C:\AdventureWorks1.mdf',
	 MOVE 'AdventureWorks_log' To 'C:\AdventureWorks1_log.ldf'

Restoring Database from Tape

RESTORE DATABASE AdventureWorks 
FROM TAPE = '\\.\tape0'

Restoring Database from Backup Device

-- Check the backup devices
SELECT * FROM sys.sysdevices
/* output
name			phyname
--------------------------------------
AdventureWorks	c:\AdventureWorks.bak
*/
RESTORE DATABASE AdventureWorks
FROM AdventureWorks

Restoring Database from a Backup Device with MOVE

-- MOVE 'LogicalName' To 'New Physical path'
RESTORE DATABASE AdventureWorks1 
FROM AdventureWorks
WITH 
	MOVE 'AdventureWorks' To 'C:\AdventureWorks1.mdf',
	MOVE 'AdventureWorks_log' To 'C:\AdventureWorks1_log.ldf'

Restoring Database using Filegroups with PARTIAL

RESTORE DATABASE AdventureWorks
FILEGROUP ='Primary'
FROM DISK='C:\Backup\Adventureworks.bak'
WITH 
	MOVE 'AdventureWorks' TO 'C:\AdventureWorks.mdf',
	MOVE 'AdventureWorks_log' TO 'C:\AdventureWorks.ldf',
	PARTIAL

RESTORE DATABASE AdventureWorks 
FILEGROUP ='Secondary'
FROM DISK='C:\Backup\Adventureworks.bak'
WITH MOVE 'File_Secondary_Data_1' TO 'C:\AdventureWorks_Secondary_Data_1.ndf'

Restoring Database using File

RESTORE DATABASE AdventureWorks 
FILE ='File_Secondary_Data_2'
FROM DISK='C:\Backup\Adventureworks.bak'
WITH MOVE 'File_Secondary_Data_2' TO 'C:\AdventureWorks_Secondary_Data_2.ndf'

Restoring Database from a Backup Set

--If a backup set contains more than one backup files,specify the File number
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backup\Adventureworks.bak'
WITH FILE = 2

Restoring Database with RECOVERY and NORECOVERY

--NORECOVERY allows to add other backups such as differential and log backups in the sequence
--Database cannot be accessed with NORECOVERY
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backup\Adventureworks.bak'
WITH NORECOVERY

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backup\diff.bak'
WITH NORECOVERY

--Restore log
--RECOVERY is a Default option 
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backup\log.trn'
WITH RECOVERY

Restoring Database with RESTART

--If database RESTORE halts prematurely due to power failure use RESTART option
RESTORE DATABASE AdventureWorks 
FROM DISK = 'C:\Backup\Adventureworks.bak' 
WITH RESTART

Restoring Database with STOPAT(point in time)

--If the database has to be restored upto a point in time use STOPAT
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backup\Adventureworks.bak'
WITH NORECOVERY

--Restore log
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backup\log.trn'
WITH NORECOVERY

--Specify the time uptill where the log has to be restored
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backup\log1.trn'
WITH 
	RECOVERY, 
	STOPAT = 'Dec 31, 2009 04:15:30 PM'

Restoring Database with STOPATMARK

--If the database has to be restored uptill a marked transation use STOPATMARK
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backup\Adventureworks.bak'
WITH NORECOVERY

--Restore log
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backup\log1.trn'
WITH NORECOVERY

--Transaction named Tran1 is committed after log1 log backup
USE AdventureWorks
GO
BEGIN TRANSACTION Tran1  WITH MARK 'Update Tran'
GO
UPDATE test SET i=100
GO
COMMIT TRANSACTION Tran1
GO
--If the database corrupts after Transaction Tran1, Restore till Tran1
--Specify the Transaction name last committed in STOPATMARK
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backup\log2.trn'
WITH 
	RECOVERY, 
	STOPATMARK = 'Tran1'

Restoring Database with PASSWORD

--If the Database is backed up with Password = 'DIVYA'
BACKUP DATABASE AdventureWorks TO DISK = 'C:\Backup\Adventureworks.bak'
WITH FORMAT,
PASSWORD='DIVYA';
--Database can be Restored with Password only
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backup\Adventureworks.bak'
WITH PASSWORD='DIVYA';

Restoring Database with MEDIA

BACKUP DATABASE AdventureWorks TO DISK = 'C:\Backup\Adventureworks.bak'
WITH	
	FORMAT,
	MEDIANAME = 'AdventureWorks-Media',
	MEDIAPASSWORD = 'DIVYA';

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backup\Adventureworks.bak'
WITH
	MEDIANAME = 'AdventureWorks-Media',
	MEDIAPASSWORD = 'DIVYA';

Restoring Database with CHECKSUM

BACKUP DATABASE AdventureWorks TO DISK = 'C:\Backup\Adventureworks.bak'
WITH FORMAT,CHECKSUM

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backup\Adventureworks.bak'
WITH CHECKSUM

Restoring Database with STATS

--Displays the Restore time in Percentage 
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backup\Adventureworks.bak'
WITH STATS = 10

Reverting Database from Snapshot

--Create Snapshot of AdventureWorks
CREATE DATABASE AdventureWorks_snapshot 
ON (	NAME = N'AdventureWorks',   
		FILENAME = N'c:\snapshot.snap' 
	) 
AS SNAPSHOT OF AdventureWorks;
GO
RESTORE DATABASE AdventureWorks 
FROM DATABASE_SNAPSHOT = 'AdventureWorks_snapshot';

Verifying Backup set

RESTORE VERIFYONLY AdventureWorks 
FROM DISK = 'C:\Backup\Adventureworks.bak'
--output
--The backup set on file 1 is valid.