Backup and Restore Database Examples (T-SQL)

backup-example.sql

BACKUP DATABASE MyDb TO DISK = 'C:\sql-backups\MyDb-20151119.bak' WITH INIT
GO

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

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