Restoring Large SQL Databases

By James|08/27/2011|SQL, Tips

When attempting to restore a large database using SQL Server 2008 Management Studio, the following error may be thrown:

An exception occurred while executing a Transact-SQL statement or batch
Timeout expired

Microsoft Knowledge Base article 967205 discusses the problem, although the article is referring to problems restoring from tape. The article suggests that this problem has been corrected by a SQL Server 2008 Cumulative Update. Our environment is running SQL Server 2008 Service Pack 2 with the June 2011 cumulative update applied and the problem remains.

The workaround mentioned in the article did resolve the issue. SQL Management Studio can still be used to perform the restore, but a Transact-SQL query must be written to perform the restore. To restore a backup to a new database, the query looks like this:

RESTORE DATABASE My-Big-Fat-Database
FROM DISK = 'DRIVE:\PATH\MyBigFatDatabase.bak'
WITH RECOVERY
GO

RESTORE DATABASE My-Big-Fat-Database

FROM DISK = 'DRIVE:\PATH\MyBigFatDatabase.bak'

WITH RECOVERY

If a newer differential backup will also be restored, change WITH RECOVERY to WITH NORECOVERY like so:

RESTORE DATABASE My-Big-Fat-Database
FROM DISK = 'DRIVE:\PATH\MyBigFatDatabase.bak'
WITH NORECOVERY
GO

RESTORE DATABASE My-Big-Fat-Database

FROM DISK = 'DRIVE:\PATH\MyBigFatDatabase.bak'

WITH NORECOVERY

If restoring to an existing database, add the REPLACE option:

RESTORE DATABASE My-Big-Fat-Database
FROM DISK = 'DRIVE:\PATH\MyBigFatDatabase.bak'
WITH RECOVERY, REPLACE
GO

RESTORE DATABASE My-Big-Fat-Database

FROM DISK = 'DRIVE:\PATH\MyBigFatDatabase.bak'

WITH RECOVERY, REPLACE

If restoring to a different server where the SQL databases and logs reside in a different location, first do a FILELISTONLY restore:

RESTORE FILELISTONLY
FROM DISK = 'DRIVE:\PATH\My-Big-Fat-Database.bak'
GO

RESTORE FILELISTONLY

FROM DISK = 'DRIVE:\PATH\My-Big-Fat-Database.bak'

This will provide a list of logical and physical names for the files in the backup

LogicalName		PhysicalName
My-Big-Fat-Database		E:\My-Big-Fat-Database.mdf
My-Big-Fat-Database-log		E:\My-Big-Fat-Database-log.ldf

Perform the restore using the MOVE option to relocate the database files to the correct location:

RESTORE DATABASE My-Big-Fat-Database
FROM DISK = 'DRIVE:\PATH\MyBigFatDatabase.bak'
WITH NORECOVERY,
MOVE 'My-Big-Fat-Database' TO NEWDRIVE:\NEWPATH\My_Big_Fat_Database.mdf',
MOVE 'My-Big-Fat-Database-log' TO NEWDRIVE:\NEWPATH\My-Big-Fat-Database-log.ldf'
GO

RESTORE DATABASE My-Big-Fat-Database

FROM DISK = 'DRIVE:\PATH\MyBigFatDatabase.bak'

WITH NORECOVERY,

MOVE 'My-Big-Fat-Database' TO NEWDRIVE:\NEWPATH\My_Big_Fat_Database.mdf',

MOVE 'My-Big-Fat-Database-log' TO NEWDRIVE:\NEWPATH\My-Big-Fat-Database-log.ldf'