Wednesday, 30 December 2009

Restore a database using TSQL

To restore a database use the following SQL:

USE [tempdb]
ALTER DATABASE [DatabaseName]

SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
GO

RESTORE DATABASE [DatabaseName]
FROM DISK = 'C:\BackupName.BAK'
WITH MOVE 'MDF_logical_name' TO 'C:\MDFName.mdf',
MOVE 'LDF_logical_name' TO 'C:\LDFName.ldf'
GO

ALTER DATABASE [DatabaseName] SET MULTI_USER
GO

Note that this puts the database into single user mode first (i.e. it kicks off other users). Don’t forget the USE [tempdb] statement or you may find the database still in use and the restore may fail. If you’re doing a straight restore you can get the logical filenames etc. from the backup file:

RESTORE FILELISTONLY FROM DISK = 'C:\BackupName.BAK'
GO