SQL Server T-SQL examples for Backup

USE [master]
GO
EXEC master.dbo.sp_addumpdevice 
@devtype = N'disk', @logicalname = N'BackupStore', @physicalname = N'C:\MSSQL12.PROD1\MSSQL\Backup\BackupStore.bak'
GO

BACKUP DATABASE [NORTHWND] TO  DISK = N'C:\Northwind.bak' WITH NOFORMAT, INIT, 
NAME = N'NORTHWND-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [NORTHWND] TO  DISK = N'C:\Northwind.bak' WITH  RETAINDAYS = 2, -- EXPIREDATE = N'03/14/2015 00:00:00'
    FORMAT, INIT, 
NAME = N'NORTHWND-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10, CONTINUE_AFTER_ERROR
GO

BACKUP DATABASE [NORTHWND] TO [BackupStore] WITH NOFORMAT, NOINIT, 
NAME = N'NORTHWND-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [NORTHWND] TO  [BackupStore] WITH  DIFFERENTIAL , NOFORMAT, NOINIT, 
NAME = N'NORTHWND-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP LOG [NORTHWND] TO  [BackupStore] WITH NOFORMAT, NOINIT, 
NAME = N'NORTHWND-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM
GO

BACKUP LOG [NORTHWND] TO  [BackupStore] WITH NOFORMAT, NOINIT,  NAME = N'NORTHWND-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'NORTHWND' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'NORTHWND' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''NORTHWND'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  [BackupStore] WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

-- Backup Tail of Translog
BACKUP LOG [NORTHWND] TO  [BackupStore] WITH  NO_TRUNCATE , NOFORMAT, NOINIT, 
NAME = N'NORTHWND-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  NORECOVERY , COMPRESSION,  STATS = 10
GO

/*
Msg 33101, Level 16, State 1, Line 51
Cannot use certificate 'DatabaseSecureBackup', because its private key is not present or it is not protected by the database master key. SQL Server requires the ability to automatically access the private key of the certificate used for this operation.
Msg 3013, Level 16, State 1, Line 51
BACKUP DATABASE is terminating abnormally.
USE [Master];
CREATE CERTIFICATE DatabaseSecureBackup
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
   WITH SUBJECT = 'Database Secure Backup',
   EXPIRY_DATE = '20181031';
GO
*/

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';
go
CREATE CERTIFICATE DatabaseSecureBackup2 WITH SUBJECT = 'Database Secure Backup2';
go

BACKUP DATABASE [NORTHWND] TO  DISK = N'C:\Northwind.bak'
WITH FORMAT, INIT,  MEDIANAME = N'New Secure Media',  NAME = N'NORTHWND-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, ENCRYPTION(ALGORITHM = AES_256, SERVER CERTIFICATE = [DatabaseSecureBackup2]),  STATS = 10
GO

/*
Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.
11 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
51 percent processed.
61 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 576 pages for database 'NORTHWND', file 'Northwind' on file 1.
100 percent processed.
Processed 1 pages for database 'NORTHWND', file 'Northwind_log' on file 1.
BACKUP DATABASE successfully processed 577 pages in 0.845 seconds (5.327 MB/sec).
*/