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は無料でオープンソースのデータベースGUIです

今まで使った中で最高のSQLクエリ＆エディタツールです。データベース管理に必要なすべてが揃っています。 - ⭐⭐⭐⭐⭐ Mit

Beekeeper Studioは高速で直感的、使いやすいです。Beekeeperは多くのデータベースをサポートし、Windows、Mac、Linuxで快適に動作します。

download 無料ダウンロード
BeekeeperのLinux版は100%フル機能で、機能の妥協はありません。
  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.