Backups

Full Backup

DECLARE @BackupLocation NVARCHAR(100);
SET @BackupLocation = 'c:\data\AdventureWorks2008_' +
CONVERT(NVARCHAR(8), GETDATE(), 112) + '.bak';
BACKUP DATABASE AdventureWorks2008
TO DISK = @BackupLocation
WITH INIT;

Creates a backup file like c:\AdventureWorks2008_20130325.bak

Differential Backup

DECLARE @BackupLocation NVARCHAR(100);
SET @BackupLocation = 'c:\data\AdventureWorks2008_' +
CONVERT(NVARCHAR(8), GETDATE(), 112) + '_diff.bak';
BACKUP DATABASE AdventureWorks2008
TO DISK = @BackupLocation
WITH INIT, DIFFERENTIAL;

This kind of backup will contain all the changes since the last full backup.

Log Backup

BACKUP LOG AdventureWorks2008 TO DISK = 'c:\data\AdventureWorks2008_log.bak'

As is this will append the log backup to the previously created log file. You can use the same options such as INIT and dynamically name th files to assist in managing your log backup files.

Creating a copy backup for development and testing

BACKUP DATABASE AdventureWorks2008 TO DISK = 'c:\data\AdventureWorks2008_Copy.bak' WITH COPY_ONLY

This will not break the chain of when the regular full backup occurs for the production backups.

Automated backup of all user databases

DECLARE @backupscript NVARCHAR(MAX)
	,@dbname NVARCHAR(100)
	,@dbpath NVARCHAR(100);
	
DECLARE DBList CURSOR FAST_FORWARD
	FOR SELECT	name
		FROM	sys.databases
		WHERE	NAME NOT IN ('master', 'model', 'tempdb', 'msdb')
		AND	state = 0; -- Make sure that the database is online
		
OPEN DBList;

FETCH NEXT FROM DBList INTO @dbname;

WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @dbpath = 'c:\data\' + @dbname + '_' + CONVERT(NVARCHAR, GETDATE(), 112) + '.bak';
		BACKUP DATABASE @dbname TO DISK = @dbpath WITH INIT;
	END

CLOSE DBList;
DEALLOCATE DBList;

Encrypt Backups

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'FenskeMediaBackup'
GO
CREATE CERTIFICATE FenskeMediaCertificate WITH SUBJECT 'Fenske Media Certificate'
GO

USE AdventureWorksLT
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE FenskeMediaCertificate
GO
ALTER DATABASE AdventureWorksLT
SET ENCRYPTION ON
GO

Once this script is run, the database is encrypted.

Backup the certifiate and private key

USE [master]
GO
BACKUP CERTIFICATE FenskeMediaCertificate TO FILE = 'c:\data\FenskeMediaCertificate'
WITH PRIVATE KEY (FILE = 'c:\data\pkFenskeMediaCertificate', ENCRYPTION BY PASSWORD = 'FenskeMediaBackup');

Verify a backup is good

RESTORE VERIFYONLY FROM DISK = 'c:\data\dbfile.bak';

Restore

Simple Restore from full backup (overwriting existing database)

RESTORE DATABASE RestoreTest
FROM DISK = 'c:\data\restoretest.bak'
WITH REPLACE;

Simple Restore from full backup (creating a new copy)

RESTORE DATABASE WholeNewRestore
FROM DISK = 'c:\data\restoretest.bak'
WITH MOVE 'RestoreTest' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\DATA\WholeNewRestore.mdf'
,MOVE 'RestoreTest_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\DATA\WholeNewRestore_Log.ldf';

Restore Differential Backup

RESTORE DATABASE DiffTest
FROM DISK = 'c:\data\difftest.bak'
WITH REPLACE, NORECOVERY; -- Restore from full backup first, do not complete recovery pending differential

RESTORE DATABSE DiffTest
FROM DISK = 'c:\data\difftest_diff.bak' -- Restore differential

Restore Log Backup

RESTORE DATABASE LogTest
FROM DISK = 'c:\data\logest.bak'
WITH REPLACE, NORECOVERY; -- Restore from full backup first, do not complete recovery pending differential

RESTORE LOG LOGTest
FROM DISK = 'c:\data\logtest_log1.bak'
WITH NORECOVERY;

RESTORE LOG LogTest
FROM DISK = 'c:\data\logtest_log2.bak'
WITH STOPAT = 'March 30 2013 21:53:30'; -- Assuming you can isolate exactly when the problem happened.

kveratis/MSSQLBackupRoutines.md