Restoring database to original from full backup file.

-- Database Backup File Info
RESTORE FILELISTONLY  
   FROM DISK = 'G:\MSSQL\DBBackups\FULL\MyDatabase_20200409.bak' 

-- Place Database in Single User Mode
USE [master]
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDatabase')
BEGIN
ALTER DATABASE MyDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
END
GO

-- Drop Database
USE [master]
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDatabase')
DROP DATABASE MyDatabase
GO

-- Restore Database
RESTORE DATABASE MyDatabase FROM DISK = 'G:\MSSQL\DBBackups\FULL\MyDatabase_20200409.bak'
WITH FILE = 1, replace, maxtransfersize = 4194302, buffercount = 96, stats = 5

Note:

After restore completed, need to re-map user permissions to that database.

Time

100 GB = 1 hr

Last modified: April 17, 2020

Author

Comments

Write a Reply or Comment