I created a short run book at work for doing restores, part of it was this post on how to do restores in both SSMS using the GUI and using T-SQL. I tried to add a few things that i’ve seen catch people out on.

Exclusive access could not be obtained because the database is in use:

exclusiveAccessError

Problem:

The restore is being ran against a database that is in use.

Resolution:

Put the database into Single User mode and run the restore from script.

/* Put the database into SINGLE_USER mode so the restore can completed without issue */

USE [master]
ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
GO

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 database multiple times to the same Instance:

 

RestoreDatabaseWithDifferentName

 

Problem:

You need to use the WITH MOVE syntax as the RESTORE is trying to use the original file locations for the files which are already in use.

Resolution:

Use the RESTORE WITH MOVE

NOTE – Use RECOVERY FILELISTONLY to obtain the logical file names.

/* 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

RestoreResults

 

Change Physical File name while Restoring:

Problem:

You have a database that’s physical file names do meet your requirements.

Resolution:

Use WITH MOVE. This is only one solution to the problem. It is possible to do this in multiple ways, but I find this a easy fix if im doing a restore.

The Files at the start look like:

PhysicalFiles

But you want to remove the “2012”

/*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\AdventureWorks_2_Data.mdf',
MOVE 'AdventureWorks2012_Log' TO 'F:\SQLData\AdventureWorks_2_Log.ldf',
NORECOVERY, STATS = 10
GO

RestoreResults2

And the Physical File now looks like:

PhysicalFiles2

(Note this doesn’t leave old files with the old file names. The command re-names the files!)

 

Database is showing in SSMS as (Restoring…) / Database ‘AdventureWorks2012_2’ cannot be opened. It is in the middle of a restore

RestoringState

Problem:

When trying to connect to a database you get the error

Database ‘AdventureWorks2012_2’ cannot be opened. It is in the middle of a restore.

And looking at the database in SSMS it shows Restoring

RestoringState2

Resolution:

This is due to a restore being ran with NORECOVERY.

If you are sure that the database is ready for use. Then you can use the RECOVERY option.

RESTORE DATABASE AdventureWorks2012_2 WITH RECOVERY

RestoringState3

 

Restore Database using Full and Differential Backups

Problem:

You need to restore a backup but use FULL and Differential Backups

Solution:

Use RESTORE WITH NORECOVERY

To do the restore you will need RESTORE the FULL backup with NORECOVERY. Then RESTORE the Differential backup with RECOVERY (If this is the closest restore you need, othereise use NORECOVERY for the DIFF and continue to restore Log files.)

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 Database to Point in Time

Problem:

The database has been found to have an issue caused at certain time. The database was backed up using Log Backups so you want to restore to the latest possible time before the issue.

Solution:

RESTORE WITH STOPAT. Restore the using the Latest Full then Differential backups. Restore all the Log backups up till the log file that contains the time you want to stop at

In this example there is a table called timetest:

CREATE TABLE timetest (
id INT IDENTITY (1,1),
T DATETIME DEFAULT GETDATE(),
d varchar(20)
)

After a diff backup I insert data into the table for IDs 1-2 then run a log backup and repeat for IDs 3-4

Id 3 is added at 2:22 and Id 4 is ran at 2:23. Then another log backup is completed.

RestoreTimeExample

Transaction Id 4 in our case is the one that we need to restore before. (Time 02:23:00)

Restore in TSQL

In TSQL we would do this with Full -> Diff -> Log restores with NORECOVERY the a STOPAT

USE [master]

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

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

RESTORE LOG [AdventureWorks2012] FROM  DISK = N'F:\SQLData\Backups\ELLIS\AdventureWorks2012\LOG\ELLIS_AdventureWorks2012_LOG_20131020_022223.trn' WITH   NORECOVERY,   STATS = 10

RESTORE LOG [AdventureWorks2012] FROM  DISK = N'F:\SQLData\Backups\ELLIS\AdventureWorks2012\LOG\ELLIS_AdventureWorks2012_LOG_20131020_022647.trn' WITH    STATS = 10,  STOPAT = N'2013-10-20T02:23:00'

GO

Restore in SSMS

RestoreTimeExample2

This opens the restore window, which will look up the MSDB records of backups and list out the solution to get to the latest restore possible:

RestoreTimeExample3

As we need to restore to a point in time you then click Timeline… and use the Specific date and time Radio button, so you update the Time to 02:23:00 (before Transaction ID 4)

RestoreTimeExample4

Click ok to close the timeline window and OK again to restore the database.

Links

BOL

http://technet.microsoft.com/en-us/library/ms186858.aspx

SQL Server Backup and Restore eBook

http://www.red-gate.com/community/books/sql-server-backup-and-restore

Advertisements