Tag Archives: backup

SQL express doesn’t have the luxury of SQL maintenance plans, but you can still write a SQL script to dump a database to a particular location and run it via task scheduler on a daily basis.

Create SQL query and save it to a particular location… (NightlyBackup.sql)

DECLARE @pathName NVARCHAR(512)

SET @pathName = ‘D:\SQL Backup\database_‘ + Convert(varchar(8), GETDATE(), 112) + ‘.bak’

BACKUP DATABASE [database] TO DISK = @pathName WITH NOFORMAT, NOINIT, NAME = N’db_backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

Create cmd / bat and save to similar location to above… (Backup DB to disk.cmd)

sqlcmd -S servername\INSTANCENAME -U sqluser -P sqluserpassword -i “NightlyBackup.sql”

forfiles /p “C:\SQLBackupLocation” /m *.bak /s /d -2 /c “cmd /c del @file : date >= 7 days >NUL”

Create windows task schedule event to run above at 5:30pm everyday.