Last updated on December 7th, 2016 at 04:29 pm

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:

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

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

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

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: