MS SQL Server database backup solution

澳洲大头

The following has been tried and work well

In task scheduler:

1. sqlcmd -i "E:\db_backup\ADbk.sql"

复制代码

where E:\db_backup\ADbk.sql is the backup script to run, which contains the codes:

1. DECLARE @MyFileName varchar(50)
   
2. SELECT @MyFileName = (SELECT 'E:\db_backup\ADbk_' + datename(dw,GetDate()) + '.bak')
   
3. BACKUP DATABASE [AD] TO DISK = @MyFileName WITH NOFORMAT, INIT, NAME = N'AD-Full Database Backup'
   
4. GO

复制代码

This will include day of the week in the backup file name. Thus 7 copies of backup files always kept.

Note
INIT = overwire existing file
NOINIT = append to existing file