{"id":25266,"date":"2018-10-09T20:35:13","date_gmt":"2018-10-09T16:35:13","guid":{"rendered":"https:\/\/www.msp360.com\/resources\/?p=25266"},"modified":"2024-02-14T15:49:50","modified_gmt":"2024-02-14T11:49:50","slug":"sql-server-backup-automation","status":"publish","type":"post","link":"https:\/\/www.msp360.com\/resources\/blog\/sql-server-backup-automation\/","title":{"rendered":"Microsoft SQL Server Backup Automation"},"content":{"rendered":"

The backup routine for MS SQL Server includes various repetitive tasks that require a lot of time and attention. Fortunately, there are built-in Microsoft SQL Server tools that can automate many of these steps and simplify your daily routine. This article discusses those built-in tools, while also showing how to use MSP360 software for MS SQL Server automatic backup.<\/p>\n

<\/p>\n

Using Built-In Tools<\/h2>\n

SQL Server Backup Automation<\/h3>\n

One option is to use SQL Server Agent to create a backup script and configure a schedule for SQL Server automatic backup.<\/p>\n

You can use SQL Server Agent for backup jobs scheduling if you are running any of the SQL Server commercial editions. A free SQL Express does not have Agent feature.<\/p>\n

For this article, we will use T-SQL scripts as examples. You can test them using Query Analyzer in SQL Server Management Studio, or use them as a backup step in SQL Server Agent. You will also need to enable SQL Server Agent, since it is disabled by default. You can enable it by opening the navigation tree for SQL Server Management Studio, right-clicking and choosing Start.<\/p>\n

We suggest creating virtual \u201cbackup devices\u201d in SQL Server Agent, which allow you to distribute the data from several tasks into files. SQL uses a \u201cdevices\u201d concept for any backup job, and you can use the files on a disk drive, tape drives, etc. as a \u201cdevice.\u201d If you are using a disk drive, you can create a separate logical device for any file, and this file can include multiple backups.<\/p>\n

\n
\n
FREE WHITEPAPER<\/div>\n
SQL Server Backup and Recovery<\/div>\n
Check out the ultimate guide with best practices for MSPs:<\/div>\n\"CTA\"<\/a><\/span><\/span>\n<\/div>\n
\"WP<\/div>\n<\/div>\n

The first step in the SQL Server backup automation process is to create the logical device \"full_backups,\" which will represent target file \u201cC:\\BackupRepository\\sql_full.bak.\u201d You can do that using these commands:<\/p>\n

USE TestDB ;  \r\nGO  \r\nEXEC sp_addumpdevice 'disk', 'full_backups', 'C:\\BackupRepository\\sql_full.bak' ;  \r\nGO\r\n<\/pre>\n
Replace \u201cTestDB\u201d in the example above with the name you want to use for your database. Create at least one backup device per every backup schedule (e.g. daily, weekly, monthly, etc.).<\/p>\n
You can also set up backup devices from the GUI by navigating in the menu to Server Objects - Backup Devices.<\/p>\n
Now we are ready to create a backup. Choose the desired database in SQL Server Management Studio, right-click, and select Tasks - Back Up:<\/p>\n
\"Creating<\/p>\n
Next, in the backup job settings window, click Script Action to Job in the Script drop-down menu in order to automatically generate the script for SQL Server Agent:<\/p>\n
\"Click<\/p>\n
Type necessary job name and go to the Steps tab. You will find generated script here.<\/p>\n
Click Edit to review the script:<\/p>\n
\"Generated<\/p>\n
Modify the script if necessary - here is a quick description of the options:<\/p>\n