Create a Script for Daily SQL Backup Job with Unique Name

Scope

Create a SQL script or job that manage daily sql backup with various options.

Code

The below script is generated from Sql Server Management Studio with my own little tweak that append a timestamp to the file name. This will create backup with file name MyDatabase_yyyy-mm-ddThh.mi.ss.mmm.bak

Declare @SQLPath nvarchar(4000)
Set @SQLPath = N'D:\Backup\MyDatabase_' + Replace(CONVERT(nvarchar(30), GETDATE(), 126), ':', '.') + '.bak' 
BACKUP DATABASE [MyDatabase] TO DISK = @SQLPath WITH NOFORMAT, INIT, NAME = N'MyDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

PS. According to Microsoft. By default, SQL server will backup as NOINIT (append). For those who do not use the timestamp and always backup to the same filename, must make sure the right options are set for INIT or NOINIT depending on backup strategy of the business.

Example

A simple cursor example that does backup on all user databases and override the last backup.

DECLARE @name VARCHAR(50)
DECLARE @path VARCHAR(255)
DECLARE @fileDate VARCHAR(20)
DECLARE @fileName VARCHAR(255)
 
SET @path = 'D:\Backup\'  
SET @fileDate = Replace(CONVERT(nvarchar(30), GETDATE(), 126), ':', '.')

DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb') -- system databases
and [state] = 0 -- online
 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '.bak'  
       BACKUP DATABASE @name TO DISK = @fileName
       WITH NOFORMAT, INIT, 
       SKIP, NOREWIND, NOUNLOAD, STATS = 10

       FETCH NEXT FROM db_cursor INTO @name
END   

CLOSE db_cursor   
DEALLOCATE db_cursor