Automate archival of daily SQL Server backups

The backup scheduler in Teratrax Database Manager provides more control over backups by allowing you to customize the actual SQL script the scheduler runs. The following example illustrates the steps required to add archiving to your backups. The resulting backup job will keep separate backup files with time stamps instead of overwriting the backup file every time the scheduler runs:

1. Connect to your server and navigate to the “Databases” node in the Server Explorer pane.
2. Right-click your database name and select “Backup…” (This example uses the Northwind database).
3. In the “Backup File” box, enter the path of the backup file (For example, “C:\Northwind.bkp”).
4. Click the “Schedule” button to open the Job dialog.
5. In the “SQL Script” box, Teratrax Database Manager will generate the following script:

BACKUP DATABASE [Northwind]
TO DISK = N'C:\Northwind.bkp'
WITH INIT, NOUNLOAD, NAME = N'Northwind backup',
NOSKIP, STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM DISK = N'C:\Northwind.bkp'

1. Insert the following code at the beginning of the script (Replace “Northwind_” with your own path and file name):

DECLARE @filename VARCHAR(255)

SELECT @filename = 'c:\Northwind_' +
  LTRIM(STR(DATEPART(year, GETDATE()))) + '_' +
  LTRIM(STR(DATEPART(month, GETDATE()))) + '_' +
  LTRIM(STR(DATEPART(day, GETDATE()))) + '_' +
  LTRIM(STR(DATEPART(hour, GETDATE()))) + '_' +
  LTRIM(STR(DATEPART(minute, GETDATE()))) + '_' +
  LTRIM(STR(DATEPART(second, GETDATE()))) + '.bkp'

1. Change the original part of the script (Replace “C:\Northwind.bkp” with @filename and “Northwind” with your database name):

BACKUP DATABASE [Northwind]
TO DISK = @filename
WITH INIT, NOUNLOAD, NAME = N'Northwind backup',
NOSKIP, STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM DISK = @filename

1. Set the schedule for the job from the “Schedule” box in the Job dialog.
2. Click “Ok” to schedule the backup. This will take you back to the Backup Database dialog. Click “Cancel” to exit.
3. Test your backup by navigating to the “Jobs” node in Server Explorer and right-clicking the new job name then selecting “Start”.

At this point, you have a scheduled backup job that will run at the specified date and time. Each resulting backup file will have a different time stamp appended to its name leaving you with an archive of backups at your disposal. Make sure you check disk space from time to time so your disk will not run out of space. Database backups can take a lot of space.

While SQL Agent needs to be running for the job to execute, Teratrax Database Manager does not have to be open when the scheduler runs.