Sql script to restore .bak file

SQL Server Administration
4

Here is my sample script which covers all the options I normally use:

First get details of the Logical Names within the database and where the original files were backed up to (Path and Filename etc.) in case that is helpful in decided what-goes-where during the restore

RESTORE FILELISTONLY FROM DISK
	= 'x:\Mypath\MyBackupFilename_Full.BAK'

RESTORE HEADERONLY FROM DISK 
	= 'x:\Mypath\MyBackupFilename_Full.BAK'

Then make the actual restore using the script below. Be VERY careful not to unintentionally overwrite an existing database!! Use Find & Replace to change names such as "MyDatabaseName" globally in this script:

USE master	-- (Can't sit in the database whilst its being Restored / Dropped!)
GO

-- ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE MyDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

-- Restore Full Backup
RESTORE DATABASE MyDatabaseName
	FROM DISK = 'x:\Mypath\MyBackupFilename_Full.BAK'
	WITH
		REPLACE,
--		RECOVERY,	-- Use if NO more T/Logs to recover
		NORECOVERY,	-- Use if more T/Logs to recover
		STATS = 10,	-- Show progress (every 10%)
	MOVE 'MyDatabaseName_Data' TO 'x:\MSSQL\DATA\MyDatabaseName.mdf', 
	MOVE 'MyDatabaseName_Log' TO 'x:\MSSQL\DATA\MyDatabaseName.ldf'
GO

-- Optional restore Differential Backup
RESTORE DATABASE MyDatabaseName
	FROM DISK = 'x:\Mypath\MyDatabaseName_Diff.BAK'
	WITH
--		RECOVERY	-- Use if NO more file to recover
		NORECOVERY	-- Use if there are T/Logs to recover
GO

-- Optional restore Transaction Log Backup
RESTORE LOG MyDatabaseName
	FROM DISK = 'x:\Mypath\MyDatabaseName_yyyymmdd_hhmm_Trans.BAK'
	WITH
--		RECOVERY	-- Use if NO more T/Logs to recover
		NORECOVERY	-- Use if more T/Logs to recover
GO
-- REPEAT FOR EACH Transaction Log Backup file - in chronoloical order

-- Now activate the database (NOT required if RECOVERY was used earlier)
RESTORE DATABASE MyDatabaseName WITH RECOVERY
GO

-- Rename logical names (only if restoring from a backup for a Different database)
ALTER DATABASE MyDatabaseName 
	MODIFY FILE (NAME = 'OrigDatabase_Data', NEWNAME = 'MyDatabaseName_data')
GO
ALTER DATABASE MyDatabaseName 
	MODIFY FILE (NAME = 'OrigDatabase_Log', NEWNAME = 'MyDatabaseName_log')
GO

-- Optional - if backup-file was from a database set to SINGLE_USER or READ_ONLY
ALTER DATABASE MyDatabaseName SET MULTI_USER, READ_WRITE
GO