The backup solution I came up with was one .sql file each for full and incremental backup, and two .cmd files which execute the respective .sql files via sqlcmd.
The full backup SQL file looks like this:
PRINT ‘backup.sql ‘ + CONVERT(VARCHAR, GETDATE(), 120)
DECLARE @backupSetId AS INT
DECLARE @Filename NVARCHAR(256)
DECLARE @Database NVARCHAR(256)
DECLARE @Backup NVARCHAR(256)
SET @Database = N”
SET @Filename = N’E:Backups.’ +
REPLACE(CONVERT(NVARCHAR, GETDATE(), 102), ‘.’, ‘-‘) +
SET @Backup = @Database + N’ Full Database Backup’
BACKUP DATABASE @Database
TO DISK = @Filename
WITH NOFORMAT, NOINIT,
NAME = @Backup,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
SELECT @backupSetId = position
WHERE database_name = @Database
AND backup_set_id =
(SELECT MAX(backup_set_id) FROM msdb..backupset
WHERE database_name=@Database )
IF @backupSetId IS NULL
PRINT N’Verify failed. Backup information for database ”’ +
@Database + N”’ not found.’
RESTORE VERIFYONLY FROM DISK = @Filename
WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
This piece of code is repeated for every database you want to backup.
The incremental backup file has the WITH DIFFERENTIAL option in the BACKUP command, and excludes verification.
The batch file which executes the backup.sql file simply calls sqlcmd:
sqlcmd -S -i backup.sql -o backup.log
Next Scheduling Task
To setup a scheduled task you need to open the folder where you can create a new scheduled task. This can be found under Accessories -> System Tools -> Scheduled Tasks or under Control Panel.
The first thing to do is to click on “Add Scheduled Task” and the following wizard will run.
Select the application that you want to run. For our purposes we will be using SQLCMD.EXE.
In order to find SQLCMD.EXE you will need to click on the Browse… button.
You should be able to find this in the following directory “C:Program FilesMicrosoft SQLServer90ToolsBinn“.
Give the scheduled task a name and specify when to perform the task.
Specify the time that this should be run.
Provide the credentials for the account that will run this task.
Finish and save the task. One thing you want to do is click on the “Open advanced properties” so you can edit the command.
Below is the advanced properties screen. You will need to change the “Run” command to the following:
sqlcmd -S serverName -E -i C:BackupBackup.sql
This is broken down as follows:
• -S (this specifies the serverinstance name for SQL Server)
• serverName (this is the serverinstance name for SQL Server)
• -E (this allows you to make a trusted connection)
• -i (this specifies the input command file)
• C:BackupBackup.sql (this is the file that we created above with the command steps)