March 31, 2023 By Teratrax

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:

Beekeeper Studio Ist Eine Kostenlose & Open-Source-Datenbank-GUI

Das beste SQL-Abfrage- und Editor-Tool, das ich je benutzt habe. Es bietet alles, was ich zur Verwaltung meiner Datenbank brauche. - ⭐⭐⭐⭐⭐ Mit

Beekeeper Studio ist schnell, intuitiv und einfach zu bedienen. Beekeeper unterstützt viele Datenbanken und funktioniert hervorragend unter Windows, Mac und Linux.

download Kostenlos Herunterladen
Die Linux-Version von Beekeeper ist zu 100% vollständig ausgestattet, ohne Abstriche und ohne Funktionskompromisse.
  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.