Periodic SQL Database Backups

Backing up SQL database regularly is a critical part of maintaining data integrity and availability. Implementing a reliable, periodic backup strategy can protect your organization against data loss caused by hardware failure, software bugs, or malicious attacks such as ransomware.

In this post, we'll discuss different types of backups, and provide practical methods to automate and schedule these backups.

Periodic SQL Database Backups

Getting Started

A periodic SQL backup strategy is a non-negotiable aspect of modern database management. By understanding your database system, automating backups with the right tools, and adhering to best practices, you can ensure your data is safe, compliant, and recoverable in any scenario.

To back up an SQL database periodically, you can automate the process using scheduled tasks or jobs depending on your database system and environment.

For automating SQL database back ups, T-SQL script will be required. Heance create a T-SQL script to back up your database. For example:

Database back up script

 DECLARE @FileName NVARCHAR(200)  
 SET @FileName = 'C:\Backups\[YourDatabaseName]_' +   
         CONVERT(VARCHAR(20), GETDATE(), 112) + '_' +   
         REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '') + '.bak'  
 BACKUP DATABASE [YourDatabaseName] TO DISK = @FileName  

You can modify the type of backup:

• Full Backup: BACKUP DATABASE
• Differential Backup: WITH DIFFERENTIAL
• Transaction Log Backup: BACKUP LOG

Scheduling Periodic Backups Using SQL Job

To schedule periodic backups using an SQL Server Agent Job in SQL Server, you can follow these steps:

1. Open SQL Server Management Studio (SSMS).
2. In Object Explorer, expand SQL Server Agent → Right-click Jobs → Choose New Job.
3. Define Job Properties
   • Name: e.g., Daily Full Backup
   • Description (optional): Daily full backup of YourDatabaseName
4. Add a Job Step
   • Go to the Steps page → Click New.
   • Enter:
     • Step Name: Full Backup Step
     • Type: Transact-SQL script (T-SQL)
     • Database: master or your target DB
     • Command: Paste your BACKUP DATABASE script
   • Click OK.
5. Schedule the Job
   • Go to the Schedules page → Click New.
   • Enter:
     • Name: Daily Schedule
     • Schedule Type: Recurring
     • Frequency: e.g., Daily
     • Time: e.g., 02:00 AM
   • Click OK.

Scheduling Periodic Backups Using Scheduled Tasks

To schedule periodic backup, we will be needed a .sql file that contains the SQL queries or script to database back up and a .bat(batch) file file that will execute the SQL the script from the .sql file.

Save the SQL query give above in a .sql file in the path you want and name it as Backup.sql. then create a .bat file in the same directory where you have created the SQL file with below batch codes

 @echo off  
 SET SERVER="SQLServerName"  
 SET DATABASE="SQLDatabaseName"  
 SET USERNAME="SQLLOGIN"  
 SET PASSWORD="SQLPASSWORD"  
 sqlcmd -S %SERVER% -d %DATABASE% -U "%USERNAME%" -P "%PASSWORD%" -I -i %0\..\CleanUpQuery.sql  
 echo Backup Complete: %BACKUP_FILE%  

Replace the values:

• SQLServerName: Replace it with your sql server name.
• SQLDatabaseName: Replace it with your sql database name.
• SQLLOGIN: Replace it with your sql login.
• SQLPASSWORD: Replace it with your sql password.

Test the Batch File

1. Open Command Prompt.
2. Navigate to the Folder Containing the Batch File using cd command.
   Syntax:

    cd path\to\your\batchfile

   Example:

    cd D:\Backups

3. Run the Batch File: enter the batch file name with extention, for example Backup.bat

Schedule the Backup

1. Open Task Scheduler.
2. Click Create Basic Task.
3. Give it a name like SQL Server Backup.
4. Set the Trigger (Daily, Weekly, etc.).
5. For Action, choose Start a program.
6. Browse to your backup.bat file.
7. Finish the wizard.
8. Optional Advanced Settings:
   • Set it to run whether user is logged in or not.
   • Use a service account with permissions to access the database.
   • Set "Run with highest privileges".

Summary

Whether you're running a small business application or managing enterprise-level systems, investing time in setting up proper backup routines can save your organization from costly downtime and data loss. I hope this was helpful to you.

Thanks