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
.PNG){kind=small}
