Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 How to Restore Database Syntax

Author  Topic 

Kristen
Test

22859 Posts
Posted - 2012-01-20 : 05:00:18
This is discussed in a number of threads, but I thought as an answer to a FAQ I would consolidate here and show how I restore a Backup file using SQL Server T-SQL commands

To restore a database you need to do three things:

1) Interogate the backup file to find what its logical names are
2a) Restore the Full Backup file into the appropriate database files (including indicating what the path is)
2b) Optionally restore the file(s) for Different backup and/or Transaction Log Backup(s)
3) Optionally "RENAME" the logical names

You can restore:
  • A Full backup followed by, optionally:
  • A Differential backup (based on the Full backup) followed by, optionally:
  • Any number of Log backups, restored in chronological order starting from the Full (or optional Differential) backup

Note that the target databases does not need to already exist, you can use the RESTORE command to create a brand new database

Step (1a) Locate the backup file

You can use Windows Explorer etc, or if you have sufficient permission to use xp_cmdshell from SQL (which can be handy if you have a SQL connection to a remote server and can't easily get access to the file structure by other means)

You can also interrogate the SQL Server to see dates/times, and filenames, of recently made backups (see below for instructions)

Making a Directory Listing in SQL:

EXEC master.dbo.xp_cmdshell 'DIR x:\Mypath\*.* /O-D'

Step (1b) Determine the Logical Device names (within the Backup File)

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

This will give something like:

LogicalName        PhysicalName                 Type FileGroupName Size      MaxSize
------------------ ---------------------------- ---- ------------- --------- --------------
MyOldDatabase_data D:\MSSQL\DATA\MyDatabase.mdf D    PRIMARY       355467264 35184372080640
MyOldDatabase_log  E:\MSSQL\LOG\MyDatabase.ldf  L    NULL          168624128 35184372080640

Step (2): Make sure you are not "sitting" in the database you are trying to restore to

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

Step (3): Disconnect all users

If you are restoring over an existing database make sure all currently connected users are disconnected
(If you are restoring to create a brand new database skip this step)

ALTER DATABASE MyTargetDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
-- Or this more drastic method (which will also prevent other SA users connecting to the DB)
-- ALTER DATABASE MyTargetDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

Step (4a): Restore the Database starting with a file from a Full Backup

(Note that the file paths for the Data and Log files on your server may be the same, or may be different [usually different physical drives])

RESTORE DATABASE MyTargetDatabaseName
	FROM DISK = 'x:\Mypath\MyFullBackupfile.BAK'
	WITH
		  REPLACE	-- Overwrite DB - if one exists
		, NORECOVERY	-- Use if DIFFs / T/Logs to recover
--		, RECOVERY	-- Use if NO more files to recover, database will be set ready to use
		, STATS = 10	-- Show progress (every 10%)
	, MOVE 'MyOldDatabase_data' TO 'x:\MySQLDataPath\MyTargetDatabaseName.mdf'
	, MOVE 'MyOldDatabase_log' TO 'y:\MySQLLogPath\MyTargetDatabaseName.ldf'
GO

Step (4b) Optionally restore a Differential Backup

(The Differential backup MUST have been based on the Full Backup, i.e. there must not have been any other Full Backup made between the one you restored earlier and the Differential Backup file you now use)

-- Optional restore from Differential Backup File
RESTORE DATABASE MyTargetDatabaseName
	FROM DISK = 'x:\Mypath\MyDifferentialBackupfile.BAK'
	WITH
		NORECOVERY	-- Use if there are T/Logs to recover
--		RECOVERY	-- Use if NO more files to recover, database will be set ready to use
		, STATS = 10	-- Show progress (every 10%)
GO

Step (4c) Transaction Log Restore

If you have any Transaction Backup files to restore repeat this for each one in turn:
(To get a list of TLog files to restore see: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60109 )

-- Optional restore from Transaction Log Backup file(s)
RESTORE LOG MyTargetDatabaseName
	FROM DISK = 'x:\Mypath\MyTransactionBackupfile.BAK'	--  e.g. MyDatabase_yyyymmdd_hhmm_Trans.BAK
	WITH
		NORECOVERY	-- Use if more T/Logs to recover
--		RECOVERY	-- Use if NO more T/Logs to recover, database will be set ready to use
		, STATS = 10	-- Show progress (every 10%)
GO

Repeat for each transaction log backup file in chronological order

If you want to restore to a point-in-time then for the LAST transaction log file restored use:

RESTORE LOG MyTargetDatabaseName
	FROM DISK = 'x:\Mypath\MyLastTransactionBackupfile.BAK'
	WITH
		NORECOVERY	-- Use if more T/Logs to recover
--		RECOVERY	-- Use if NO more T/Logs to recover, database will be set ready to use
		, STOPAT = '20061231 10:31'	-- Restore up to 31-Dec-2006 10:31
		, STATS = 10	-- Show progress (every 10%)
GO

Step (5) Set the database ready for use

(You do not need to do this if you used "RECOVERY" on the final file you restored, it is required if you were using "NORECOVERY". If you are not sure run it anyway)

-- Set the database ready for use (after all backups have been restored)
RESTORE DATABASE MyTargetDatabaseName WITH RECOVERY
GO

Step (6) Rename the logical names:

(only needed if restoring from a backup for a Different database, or if you want to change the logical names)

-- Optionally Rename the logical names:
ALTER DATABASE MyTargetDatabaseName
	MODIFY FILE (NAME = 'MyOldDatabase_data', NEWNAME = 'MyNewDatabase_data')
GO
ALTER DATABASE MyTargetDatabaseName
	MODIFY FILE (NAME = 'MyOldDatabase_log', NEWNAME = 'MyNewDatabase_log')
GO

If the backup came from a different server you will now need to synchronise the Database Users and Server logins.

Kristen
Test

22859 Posts
Posted - 2012-01-20 : 05:00:34
To see a history of recent backups:

SELECT TOP 100
--	BS.database_name,
	BS.backup_set_id,
	BS.backup_start_date,
	BS.backup_finish_date,
	BS.type,
	BS.backup_size,
	BS.name,
	BS.user_name,
	BS.first_lsn,
	BS.last_lsn,
	BS.database_backup_lsn,
	BS.description,
	BMF.family_sequence_number,
	BMF.device_type,
	BMF.physical_device_name
--	, * 
FROM	msdb.dbo.backupset AS BS
	LEFT OUTER JOIN msdb.dbo.backupmediafamily AS BMF
		ON BMF.media_set_id = BS.media_set_id
WHERE	1=1
	-- SELECT DB_NAME()	-- Get name of current database (for Cut & Paste)
	AND BS.database_name = N'MyDatabaseName'
	-- type : D=Full, I=Differential, L=Log, G=FileGroup, V=VerifyOnly
-- 	AND BS.type='D'
ORDER BY BS.backup_start_date DESC, BS.database_name, BMF.family_sequence_number
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2012-01-20 : 05:00:44
To see what was recently restored:

SELECT DISTINCT TOP 100
	RH.destination_database_name,
 	RF.file_number,
	RH.restore_date,
	RF.destination_phys_drive,
	RF.destination_phys_name,
	[Backup Set Name]=BS.name,
	BS.description,
	BS.database_creation_date,
	BS.backup_start_date,
	BS.database_name,
	BS.server_name,
	RH.restore_type,
	RH.replace,
	RH.stop_at
FROM	msdb.dbo.restorehistory AS RH
	LEFT OUTER JOIN msdb.dbo.restorefile AS RF
		ON RF.restore_history_id = RH.restore_history_id
	LEFT OUTER JOIN msdb.dbo.restorefilegroup AS RG
		ON RG.restore_history_id = RH.restore_history_id
	LEFT OUTER JOIN msdb.dbo.backupset AS BS
		ON BS.backup_set_id = RH.backup_set_id
WHERE	1=1
	-- SELECT DB_NAME()	-- Get name of current database (for Cut & Paste)
	AND RH.destination_database_name = N'MyDatabaseName'
	-- restore_type : D=Full, I=Differential, L=Log, G=FileGroup, V=VerifyOnly
-- 	AND RH.restore_type = 'D'
ORDER BY RH.restore_date DESC,
	RF.file_number DESC
Go to Top of Page

michal101
Starting Member

1 Post
Posted - 2012-08-16 : 03:37:49
quote:
Originally posted by Kristen

To see what was recently restored:

SELECT DISTINCT TOP 100
	RH.destination_database_name,
 	RF.file_number,
	RH.restore_date,
	RF.destination_phys_drive,
	RF.destination_phys_name,
	[Backup Set Name]=BS.name,
	BS.description,
	BS.database_creation_date,
	BS.backup_start_date,
	BS.database_name,
	BS.server_name,
	RH.restore_type,
	RH.replace,
	RH.stop_at
FROM	msdb.dbo.restorehistory AS RH
	LEFT OUTER JOIN msdb.dbo.restorefile AS RF
		ON RF.restore_history_id = RH.restore_history_id
	LEFT OUTER JOIN msdb.dbo.restorefilegroup AS RG
		ON RG.restore_history_id = RH.restore_history_id
	LEFT OUTER JOIN msdb.dbo.backupset AS BS
		ON BS.backup_set_id = RH.backup_set_id
WHERE	1=1
	-- SELECT DB_NAME()	-- Get name of current database (for Cut & Paste)
	AND RH.destination_database_name = N'MyDatabaseName'
	-- restore_type : D=Full, I=Differential, L=Log, G=FileGroup, V=VerifyOnly
-- 	AND RH.restore_type = 'D'
ORDER BY RH.restore_date DESC,
	RF.file_number DESC



SQL database is very important because it has so many important data. In the case when it has been corrupted then it may be the loss of data. So to get back those data I simply use the third party tool in the absence of technical knowledge. For more help visit:- unspammed

Recover Damage Or Corrupt Database
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts
Posted - 2012-08-16 : 15:25:48
quote:
Originally posted by Kristen

To see a history of recent backups:

SELECT TOP 100
--	BS.database_name,
	BS.backup_set_id,
	BS.backup_start_date,
	BS.backup_finish_date,
	BS.type,
	BS.backup_size,
	BS.name,
	BS.user_name,
	BS.first_lsn,
	BS.last_lsn,
	BS.database_backup_lsn,
	BS.description,
	BMF.family_sequence_number,
	BMF.device_type,
	BMF.physical_device_name
--	, * 
FROM	msdb.dbo.backupset AS BS
	LEFT OUTER JOIN msdb.dbo.backupmediafamily AS BMF
		ON BMF.media_set_id = BS.media_set_id
WHERE	1=1
	-- SELECT DB_NAME()	-- Get name of current database (for Cut & Paste)
	AND BS.database_name = N'MyDatabaseName'
	-- type : D=Full, I=Differential, L=Log, G=FileGroup, V=VerifyOnly
-- 	AND BS.type='D'
ORDER BY BS.backup_start_date DESC, BS.database_name, BMF.family_sequence_number


Nice FAQ. There are also a few other types:
'D' = 'Database'
'F' = 'File'
'G' = 'Filegroup'
'I' = 'Differential'
'L' = 'Log'
'V' = 'Verifyonly'


Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site