Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Backup and Restore Database Examples (T-SQL)
BACKUP DATABASE MyDb TO DISK = 'C:\sql-backups\MyDb-20151119.bak' WITH INIT
GO
USE master
Alter database [MyDb] set single_User with ROLLBACK IMMEDIATE
RESTORE DATABASE [MyDb] FROM DISK = N'c:\sql-backups\MyDb.bak' WITH NOUNLOAD, REPLACE, STATS = 10, FILE = 1
Alter database [MyDb] set multi_User with ROLLBACK IMMEDIATE
-- display current names
RESTORE FILELISTONLY FROM DISK='C:\sql-backups\MyDb-20160427.bak'
-- actually restore (to new db/location)
RESTORE DATABASE [MyDb] FROM DISK = 'C:\sql-backups\MyDb-20160427.bak'
WITH
MOVE 'MyDb' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MyDb.mdf',
MOVE 'MyDb_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MyDb_Log.mdf'
--SELECT name, physical_name AS current_file_location
--FROM sys.master_files
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.