Restore SQL Server database with TSQL

Recently I had troubles restoring a database in my SQL Server 2005 instance (SP3). I used the ‘wizard’ in the SQL Server Enterprise manager but after I got the message ‘restore completed successfully’ the database hung in ‘Restoring’ state, blocking connections.

I decided the try to restore the database with T-SQL, with T-SQL the restore completed successfully! Another advantage of using T-SQL is that T-SQL gave more logging. This logging shows me that the BAK file (delivered from a third party) is not a SQL Server 2005 backup but presumably a SQL Server 2000 database backup …

DECLARE
	@BackupFile varchar(8000),
	@sql varchar(8000)
	SET @BackupFile = 'E:\DB2000.BAK'
	SET @sql = 'RESTORE DATABASE ADB01 FROM DISK = ''' + @backupfile + ''' WITH FILE = 1,  
		MOVE N''DB2000_dat'' TO N''E:\mssql\data\ADB01\ADB01_01.mdf'',  
		MOVE N''DB2000_log'' TO N''F:\mssql\tlog\ADB01\ADB01_01.ldf'',  
		NOUNLOAD,  REPLACE,  STATS = 10, RECOVERY'
	exec (@sql)
go



10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 2792 pages for database 'ADB01', file 'DB2000_dat' on file 1.
Processed 1 pages for database 'ADB01', file 'DB2000_log' on file 1.
Converting database 'ADB01' from version 539 to the current version 611.
Database 'ADB01' running the upgrade step from version 539 to version 551.
Database 'ADB01' running the upgrade step from version 551 to version 552.
Database 'ADB01' running the upgrade step from version 552 to version 553.
Database 'ADB01' running the upgrade step from version 553 to version 554.
Database 'ADB01' running the upgrade step from version 554 to version 589.
Database 'ADB01' running the upgrade step from version 589 to version 590.
Database 'ADB01' running the upgrade step from version 590 to version 593.
Database 'ADB01' running the upgrade step from version 593 to version 597.
Database 'ADB01' running the upgrade step from version 597 to version 604.
Database 'ADB01' running the upgrade step from version 604 to version 605.
Database 'ADB01' running the upgrade step from version 605 to version 606.
Database 'ADB01' running the upgrade step from version 606 to version 607.
Database 'ADB01' running the upgrade step from version 607 to version 608.
Database 'ADB01' running the upgrade step from version 608 to version 609.
Database 'ADB01' running the upgrade step from version 609 to version 610.
Database 'ADB01' running the upgrade step from version 610 to version 611.
RESTORE DATABASE successfully processed 2793 pages in 1.513 seconds (15.118 MB/sec).

Leave a Reply

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>