Backup Database Commands

SQL Server Administration: Backup database to one file or multiple files. Use these commands to back your databases up without using a maintenance plan. Backing up to multiple files is good to use for large databases and will break up a single large backup file to several smaller files. Good to use when large backups are hard to move across the network.

—
— Backup a database to a single backup file
—
DECLARE @BackupFile NVARCHAR(200)
DECLARE @Timestamp VARCHAR(50)
— create a timestamp for a unique name
SET @Timestamp = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(), 20),’-‘,”),’:’,”),’ ‘,”)
— create backup file string
SET @BackupFile = ‘D:\SQLBackups\AdventureWorks2012_Full1_’+@Timestamp+’.bak’
— backup the database
BACKUP DATABASE AdventureWorks2012
TO DISK = @BackupFile
WITH NOFORMAT, NOINIT, NAME = N’AdventureWorks2012-Full Database Backup’,
SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO

—
— Backup a database to multiple backup files
—
DECLARE @BackupFile1 NVARCHAR(200)
DECLARE @BackupFile2 NVARCHAR(200)
DECLARE @BackupFile3 NVARCHAR(200)
DECLARE @BackupFile4 NVARCHAR(200)
DECLARE @Timestamp VARCHAR(50)
— create a timestamp for a unique name
SET @Timestamp = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(), 20),’-‘,”),’:’,”),’ ‘,”)
— create backup file strings
SET @BackupFile1 = ‘D:\SQLBackups\AdventureWorks2012_Full1_’+@Timestamp+’.bak’
SET @BackupFile2 = ‘D:\SQLBackups\AdventureWorks2012_Full2_’+@Timestamp+’.bak’
SET @BackupFile3 = ‘D:\SQLBackups\AdventureWorks2012_Full3_’+@Timestamp+’.bak’
SET @BackupFile4 = ‘D:\SQLBackups\AdventureWorks2012_Full4_’+@Timestamp+’.bak’
— backup the database
BACKUP DATABASE [AdventureWorks2012] TO DISK = @BackupFile1,
DISK = @BackupFile2,
DISK = @BackupFile3,
DISK = @BackupFile4
WITH NOFORMAT, NOINIT, NAME = N’AdventureWorks2012-Full Database Backup’,
SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO