Last Updated: February 14, 2016
·
787
· nathankoop

SQL Server quick restore

I'll often need to restore a database on my local dev box so I keep this in my snippets section. (DO NOT USE ON PRODUCTION)

Just replace [DB] with [your-database-name] and provide the correct information in the RESTORE DATABASE section and run this. This ensures that all current connections to SQL server are closed before attempting the restore.


use [master]

--Make Database to single user Mode
ALTER DATABASE [DB]
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

--Restore Database
RESTORE DATABASE [DB]
FROM DISK = '.bak'
WITH MOVE '' TO '.mdf',
MOVE '' TO '.ldf'

--If there is no error in statement before database will be in multiuser mode.
--If error occurs please execute following command it will convert database in multi user.
ALTER DATABASE [DB] SET MULTI_USER
GO