SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 How to Restore Database Syntax
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/20/2012 :  05:00:18  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 01/20/2012 :  05:00:34  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 01/20/2012 :  05:00:44  Show Profile  Reply with Quote
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

USA
1 Posts

Posted - 08/16/2012 :  03:37:49  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2004 Posts

Posted - 08/16/2012 :  15:25:48  Show Profile  Visit jackv's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000