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:
- Connect to your server and navigate to the "Databases" node in the
Server Explorer pane.
- Right-click your database name and select "Backup..."
(This example uses the Northwind database).
- In the "Backup File" box, enter the path of the backup file (For
example, "C:\Northwind.bkp").
- Click the "Schedule" button to open the Job dialog.
- 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'
- 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'
- 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
- Set the schedule for the job from the "Schedule" box in the Job
dialog.
- Click "Ok" to schedule the backup. This will take you back
to the Backup Database dialog. Click "Cancel" to exit.
- 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.
|
