How to Set Up Every Day Database Auto Backup in SQL Server

In the world of database management, regular backups are crucial to safeguard against data loss or corruption. This article provides a step-by-step guide on how to set up daily SQL Server backups on a Windows system.

Backups are an essential aspect of maintaining the integrity and availability of data in a SQL Server database. They are critical for ensuring that in the event of a system failure or data loss, the database can be restored to its previous state. In this article, we will explore the vital importance of regular database backups and examine the various backup types available in SQL Server. We will provide two methods to help you set up daily backups in SQL Server. The first method involves creating a backup project file that generates a .sql file with both CREATE database object scripts and INSERT data scripts. You can launch and schedule this project file execution using a .bat file. The second method involves using a SQL script to create a native SQL Server backup that can also be scheduled, simplifying and automating the backup process.

Contents

• SQL Server backup types
• Organizing your backup strategy: Methods for daily SQL Server backups on Windows
  • Method 1: Scheduling daily backups with a .sql file created in dbForge Studio that includes scripts for database object creation and data insertion
  • Method 2: Scheduling daily backups using a script to create a native SQL Server backup
• Conclusion

SQL Server backup types

There are several types of backups in SQL Server, each with its own advantages and disadvantages:

1. Full backup: This type of backup creates a complete copy of the entire database. It is the most comprehensive type of backup and can be used to restore the database to its exact state at the time the backup was taken. Full backups are essential for disaster recovery scenarios.
2. Differential backup: This type of backup captures only the changes made to the database since the last full backup. It is faster than a full backup and requires less storage space, making it an efficient way to capture incremental changes.
3. Transaction log backup: This type of backup captures all the transactions made to the database since the last backup, including insertions, deletions, and modifications. Transaction log backups are useful for point-in-time recovery, where you need to restore the database to a specific point in time.

When it comes to SQL Server databases, backups are usually performed directly on the SQL Server, either by the SQL Server Agent or by third-party backup software. Regularly backing up data and storing it on a separate server or disk can provide an extra layer of protection against data loss. This type of backup, usually called native SQL Server backup, is typically perfectly reliable because it is less prone to human error.

You can also perform a backup on the client side by the client application, rather than the SQL Server itself. This approach is beneficial when you require a custom backup of specific tables or data that cannot be carried out using native backups or when you need to store the backup file physically on your computer. However, this type of backup is generally considered less reliable than native backups, as they are more prone to human error. In this article, we will provide step-by-step instructions for setting up and scheduling both native backups and those made with the help of third-party tools.

It is important to have a backup strategy in place that includes regular backups and periodic testing to ensure that backups can be restored successfully. It is also essential to store backups in a secure location, such as a remote server or cloud storage, to prevent loss in the event of a disaster.

Overall, backups are critical to maintaining the availability and integrity of data in a SQL Server database. By using a combination of full, differential, and transaction log backups, you can ensure that you have multiple recovery options available in the event of an unexpected failure or data loss.

Organizing your backup strategy: Methods for daily SQL Server backups on Windows

Method 1: Scheduling daily backups with a .sql file created in dbForge Studio that includes scripts for database object creation and data insertion

The overall idea behind this method is to use dbForge Studio for SQL Server to create a backup project file and a .bat file with command-line keys to execute the generated backup project and then use Windows Scheduler to schedule daily backups at the desired time.

Step 1. Create a backup project file with customized options.

On the execution of the project file, a .sql file that includes DDL statements to CREATE SQL Server database objects and DML statements to INSERT data is generated. This file can be used to restore a database by simply executing it, using tools such as the sqlcmd utility, the Execute Large Script wizard of dbForge Studio, or the command-line functionality of the Studio with the /execute key.

1.1 In Database Explorer, right-click the database for which you want to set up daily backups and go to Tasks > Generate Scripts.

1.2 On the General page of the Generate Scripts Wizard that opens, set up the general script generation options: check the connection and database names, provide a path to the directory that the generated script file will be saved to, specify the output file name, select whether to append a timestamp to the file name, etc. Once done, click Next to proceed.

1.3 On the Script content page, you can select the database objects you want to include in the script, and choose whether to include the database structure, data, or both. Click Next to continue.

1.4 On the Options page, you can customize the script options. Click the option to view its description. Once you have completed customizing the options, click Next to continue.

1.5 On the Errors Handling page, you can configure how errors or issues encountered during the script generation should be handled; you can also set up the logging options.

1.6 Once you have completed the configuration of the database script generation, your next step is to save the options as a project file. To do so, click the Save dropdown and select Save Project. Then, specify the destination folder where you would like to save the project.

Step 2. Create a .bat file to invoke the generated backup project.

Now that you have a backup project file ready, you need to generate a .bat file with the command-line keys that will launch the said backup project.

2.1 Click the Save dropdown and select Save Command Line. In the dialog that opens, specify the path to the backup project file generated during Step 1, check the connection and database settings, and then provide a path to the result file.

2.2 Optional: Click Validate to check the generated script.

2.3 Click Save and specify a file name to save the .bat file.

This command invokes dbForge Studio for SQL Server, launches the backup project created during Step 1, and generates a database backup according to that project.

Step 3. Create a scheduled backup task.

3.1 Open Windows Task Scheduler by typing Task Scheduler in the Windows search bar and selecting Task Scheduler from the results.

3.2 Click Create Task in the Actions pane on the right side of the Task Scheduler window and give the task a name and a description. Click Next to continue.

3.3 On the Trigger page of the wizard, set the frequency of the backup task to Daily. Then specify the start time. Once done, click Next.

3.5 On the Actions page, select Start a Program and specify the location of the .bat file created during Step 2. Then click Next to proceed.

3.6 On the Finish tab, check the settings and click Finish to create the scheduled backup task.

Method 2: Scheduling daily backups using a script to create a native SQL Server backup

This method involves using a SQL script to create a native SQL Server backup, a .bat file to run the script, and Windows Scheduler to schedule the .bat file execution.

Step 1. Create a .sql file with a script to generate a native SQL Server backup.

Open any text editor and paste the following script. Make sure you modify the variables in the script to suit your specific needs. This includes changing the database name and specifying the backup destination. It is also recommended to verify that the backup folder exists and that the specified path is correct. Once the variables have been updated, save the file with the .sql extension.

SET CONCAT_NULL_YIELDS_NULL, ANSI_NULLS, ANSI_PADDING, QUOTED_IDENTIFIER, ANSI_WARNINGS, ARITHABORT, XACT_ABORT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS OFF
GO

--
-- Create backup folder
--
IF OBJECT_ID('xp_create_subdir') IS NOT NULL
  EXEC xp_create_subdir N'D:\Backups'
GO

--
-- Backup database to the file with the name: <database_name>_<yyyy>_<mm>_<dd>_<hh>_<mi>.bak
--
DECLARE @db_name SYSNAME
SET @db_name = N'AdventureWorks2019Dev'

DECLARE @filepath NVARCHAR(4000)
SET @filepath =
/*define base part*/ N'D:\Backups\' + @db_name + '_' +
/*append date*/ REPLACE(CONVERT(NVARCHAR(10), GETDATE(), 102), '.', '_') + '_' +
/*append time*/ REPLACE(CONVERT(NVARCHAR(5), GETDATE(), 108), ':', '_') + '.bak'

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 
    N'BACKUP DATABASE ' + QUOTENAME(@db_name) + ' TO DISK = @filepath WITH INIT' + 
      CASE WHEN EXISTS(
                SELECT value
                FROM sys.configurations WITH (NOLOCK)
                WHERE name = 'backup compression default'
          )
        THEN ', COMPRESSION'
        ELSE ''
      END

EXEC sys.sp_executesql @SQL, N'@filepath NVARCHAR(4000)', @filepath = @filepath
GO

This script generates a .bak file, which can be used to restore the database via the Restore SSMS wizard or the Restore wizard of dbForge Studio for SQL Server.

Step 2: Create a .bat file to execute the .sql file we have created in the previous step.

To create a batch file (also known as a .bat file), you need to open Notepad or any other plain text editor. Enter the following command:

"C:\Program Files\Devart\dbForge Studio for SQL Server\dbforgesql.com" /execute /connection:"Data Source=JordanS\SQLSERVER2019;Integrated Security=False;User ID=sa" /encoding:1251 /inputfile:"C:\Users\JordanS\Scripts\backup_script.sql"

Make sure to change the variables as required. Once done, save the file with the .bat extension.

This command launches dbForge Studio for SQL Server to execute the script created in Step 1.

Step 3. Create a scheduled backup task.

To schedule the execution of the created .bat file, you can use the Windows Task Scheduler. For this, just follow the actions described in Step 3 of Method 1.

Conclusion

Regularly backing up your SQL Server databases is critical for maintaining data integrity and availability. By understanding different types of backups—such as full, differential, and transaction log backups—and implementing a backup strategy that includes regular backups and periodic testing, you can minimize the risk of data loss and ensure quick recovery in case of a system failure. In this article, we have given you two methods of scheduling daily backups on Windows using a backup project file generated with the help of dbForge Studio or a custom SQL script. By utilizing these methods, you can automate the backup process, save your time, and rest assured that your data will not be lost. Implementing these best practices for SQL Server backups will help you achieve a more reliable and secure process of database environment.

If you’re looking to effectively streamline your regular SQL Server backups, we invite you to try out dbForge Studio for SQL Server. Besides its powerful backup and restore functionality, you can get quite a few essential features to take your database development and management to a new level. Don’t take any risks with your valuable data – download dbForge Studio for SQL Server today for a 30-day free trial and see it in action!