Skip to content

Instantly share code, notes, and snippets.

@drmohundro

drmohundro/backup-example.sql

Last active January 4, 2017 01:58
Show Gist options
  • Save drmohundro/ca20b859f464dce0ca0db2db0427d1e6 to your computer and use it in GitHub Desktop.
Save drmohundro/ca20b859f464dce0ca0db2db0427d1e6 to your computer and use it in GitHub Desktop.
Download ZIP
Backup and Restore Database Examples (T-SQL)
Raw
backup-example.sql
BACKUP DATABASE MyDb TO DISK = 'C:\sql-backups\MyDb-20151119.bak' WITH INIT
GO
Raw
restore-example.sql
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
Raw
restore-to-new-db.sql
-- 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