How to backup multiple SQL Server databases automatically

 In situations with few databases, maintaining the regular backup routine can be achieved easily, either with the help of a few simple scripts or by configuring a SQL Server agent job that will perform the backup automatically. However, if there are hundreds of databases to manage, backing up each database manually can prove to be a quite time-consuming task. In this case, it would be useful to create a solution that would back up all, or multiple selected SQL Server databases automatically, on a regular basis. Furthermore, the solution must not impact the server performance, or cause any downtime.

There was a common misconception that some objects in the database get locked during backup operations, thus denying full access to the database. The misconception was based on situations where some of the user transactions got blocked during the database backup operations. All backup operations in SQL Server are online operations and they are not supposed to cause locks on user objects. However, taking a database backup does take some system resources. Each database backup operation requires both disk reads and disk writes. This induces certain pressure to the IO subsystem, and if not configured correctly, can further cause timeouts for some user transactions. For example, backing up a database to the same disk drive where data files are located could cause the timeout errors, especially on the disk drives with a low number of spindles. In this case, the system has to:

• Read and write to the database files for all user transactions
• Read the database files in order to create backup file
• Write the read data for the backup file to the same disk drive

On high traffic servers, the pressure on the IO system, in this case, is simply too high. Therefore, a timeout error is returned for some of the transactions. If multiple larger databases are backed up, the backup process may last for quite some time. To prevent the timeouts, it is highly recommended to plan all backup operations while there is low activity on the server and to avoid using the same disk for the database and backup files.

In this article, two different ways of backing up multiple SQL databases will be demonstrated:

• creating a SQL Server agent job,
• configuring a maintenance plan

Writing T-SQL backup scripts

Before we start configuring a backup job for the SQL Server agent, it is necessary to create backup script that will be used for the job. There is an option to use common backup command with each database that needs to be backed up. In this case, the following script can be used:

--Script 1: Backup selected databases only

BACKUP DATABASE Database01
TO DISK = 'C:\Database01.BAK'
BACKUP DATABASE Database02
TO DISK = 'C:\Database02.BAK'
BACKUP DATABASE Database03
TO DISK = 'C:\Database03.BAK'

This script is quite simple but it contains hard coded names for the backup files. The script can be used for the one-time jobs, but as soon as it is included in a regular schedule, the old backup files will simply be overwritten by the new ones. The most elegant way to prevent this situation, is to include the creation date in the backup filename. This can be easily achieved with the dynamic T-SQL script:

--Script 2: Include creation date in backup filenames

--1. Variable declaration

DECLARE @BackupFileName varchar(1000)

--2. Specifying backup path and filename

SELECT @BackupFileName = (SELECT ' E:\Backup\AdventureWorks2008_' + 
convert(varchar(500),GetDate(),112) + '.bak') 

--3. Executing the backup command

BACKUP DATABASE AdventureWorks2008 TO DISK=@BackupFileName

--4. Repeat steps 2. and 3. for all other databases

SELECT @BackupFileName = (SELECT ' E:\Backup\AdventureWorks2012_' + 
convert(varchar(500),GetDate(),112) + '.bak') 

BACKUP DATABASE AdventureWorks2012 TO DISK=@BackupFileName

SELECT @BackupFileName = (SELECT ' E:\Backup\AdventureWorks2014_' + 
convert(varchar(500),GetDate(),112) + '.bak') 

BACKUP DATABASE AdventureWorks2014 TO DISK=@BackupFileName

In the first step of the script, a variable for the backup file name is declared.

In step two, the BackupFileName variable is defined by combining strings for the backup path and database name with the string generated by the GetDate() function. The backup paths and the backup filenames must be provided for each database that needs to be backed up. In this example, databases AdventureWorks2008, AdventureWorks2012 and AdventureWorks2014 are used.

In step three, backup command for the specified database is executed. Steps 2 and 3 must be repeated for each other database that needs to be included in the backup job.

If there are too many databases on the server (a few hundred for example), and most of them need to be backed up, it would be impractical to apply the method used in script 2. Also, writing the script itself would take a a lot of time. To back up all databases on the server, with the possibility to exclude system (or any specific database), script 3 can be used. One of the greatest advantages of this script is that it will always back up all non-system databases on the server, even those databases that are added to the server after the agent job creation.

--Script 3: Backup all non-system databases

--1. Variable declaration

DECLARE @path VARCHAR(500)
DECLARE @name VARCHAR(500)
DECLARE @filename VARCHAR(256)
DECLARE @time DATETIME
DECLARE @year VARCHAR(4)
DECLARE @month VARCHAR(2)
DECLARE @day VARCHAR(2)
DECLARE @hour VARCHAR(2)
DECLARE @minute VARCHAR(2)
DECLARE @second VARCHAR(2)
 
-- 2. Setting the backup path

SET @path = 'E:\Backup\'  

 -- 3. Getting the time values

SELECT @time = GETDATE()
SELECT @year   = (SELECT CONVERT(VARCHAR(4), DATEPART(yy, @time)))
SELECT @month  = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mm,@time),'00')))
SELECT @day    = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(dd,@time),'00')))
SELECT @hour   = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(hh,@time),'00')))
SELECT @minute = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mi,@time),'00')))
SELECT @second = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(ss,@time),'00')))

-- 4. Defining cursor operations
 
DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- system databases are 
excluded

--5. Initializing cursor operations

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN

-- 6. Defining the filename format
   
       SET @fileName = @path + @name + '_' + @year + @month + @day + @hour + 
@minute + @second + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

 
       FETCH NEXT FROM db_cursor INTO @name   
END   
CLOSE db_cursor   
DEALLOCATE db_cursor

Script 3 uses separate string variables for year, month, day, hour, minute and second to create the unique name for each subsequent backup file. To apply this script in a specific environment, it is necessary to configure the following parameters in the script 3:

• Set the custom backup path in step 2
• In step 4, add names of the databases that should be excluded from the script. In this example, only four system databases are excluded.
• If needed, change the sequence of variables in step 6 to define the backup file name

After deciding which script to use for the backup job, we can start to configure SQL Server agent job. In further examples, script 3 will be used.

Create SQL Server agent job

In order to create any SQL Server Agent job, make sure that SQL Server Agent is running first.

Start SQL Server Management Studio, and locate the icon that represents SQL Server Agent at the bottom of the Object Explorer. If there is “Agent XPs disabled” message in parenthesis, the agent is not running. To start the SQL Server Agent, right click on its label, and select Start from the context menu.

As soon as agent is running, proceed with the following steps to configure the job:

1. Expand SQL Server Agent node in Object Explorer, right click on Jobs, and select New job… in the context menu.

   

2. In the General tab of the New Job form, provide the name, owner and description for the job. Since script 3 will be used for the agent job, Name and Description fields should describe situation where backups are taken for all databases on the server. If job needs to be active right after it is created, check the Enabled box.

   

3. In Steps tab, click on the New… button to configure job steps.

   

4. In General tab of the New Job Step wizard, provide the step name, set the job type to Transact-SQL script (T-SQL), and select master database in the Database box. Paste the script that will be used for this job in the Command box. In this example, script 3 will be used. Click on OK button to save the job step.

   

5. Back in the New job wizard, select Schedules tab, and click on New… button.

   

6. In New Job Schedule window, set the name for the schedule, select the schedule type, and set the frequency. In this case, the schedule that runs at 12AM each day will be used. Click OK button to save changes.

   

7. Optionally, set alerts and notifications in respective tabs of the New Job wizard, and click OK to complete agent job configuration.

8. The best way to check if the job is working, is to run it, and see the results. As mentioned in the introduction, avoid running this job during the high-traffic hours on the server. To run created jobs immediately, expand SQL Server Agent and Jobs nodes in Object Explorer, right click on the created job, and select Start job at step… option.

   

9. Success message is displayed as soon as job is executed.

   

10. Finally, check the backup folder for the created backup files. Since script 3 adds values for the exact time in seconds to all backup filenames, the job can be run all over again – and each new run will create set of new, separate backup files.

    

Create a maintenance plan to back up selected databases

The same result can be achieved with the use of maintenance plans. One of the advantages of maintenance plans is that there is no need for the T-SQL scripts. To configure a maintenance plan, perform the following steps:

1. Expand Management node in Object Explorer, right click on Maintenance Plans, and select Maintenance Plan Wizard from the context menu.

   

2. In first step of the wizard, provide name and description for the maintenance plan. Check the option to use single schedule for the entire plan. Also, click on the Change… button to configure the schedule.

   

3. In New Job Schedule window, specify name, schedule type and frequencies. Check the description in Summary, and click OK to save changes if schedule is configured properly. Click on the Next button in Maintenance Plan Wizard to proceed with the configuration.

   

4. In second step of the wizard, select Back Up Database (Full) option, and click on Next to proceed.

   

5. Since only one task got selected in the previous step, there is no need to set the task execution order. Click Next to proceed.

   

6. In the General tab, open the drop-down menu for Database(s), and select option to back up All databases.

   

7. In the Destination tab, select the option to Create a backup file for every database. Provide the backup destination path in Folder text box, and click on Next button.

   

8. Set the options for text and E-mail reports if needed, and click Next.

   

9. In the final step, review all configured settings, and click Finish to save changes.

   

10. The success message is generated as soon as wizard executes all given instructions.

    

11. To execute created maintenance plan, expand the Management and Maintenance Plans nodes in the Object Explorer. Right click on the created maintenance plan (Back up all databases in this case), and select Execute.

    

12. A “Success” message is generated upon successfull execution.

    

13. To make sure that the maintenance plan worked, check the folder specified as the backup destination. When maintenance plans are used, the names for the backup files are generated automatically, and they contain strings for database name, year, month, date, hours, minutes and seconds. Unfortunately, there is no option to set custom naming rules for backup files when maintenance plans are used.

    

  Source : https://solutioncenter.apexsql.com/how-to-backup-multiple-sql-server-databases-automatically/

 

How to delete old database backup files automatically in SQL Server

Introduction

While working with a number of databases, creating a recovery plan can be challenging, especially if we want a foolproof design. Automated backup and restore strategies ensure the recovery plan is successful. However, strategies relying on frequent backups, although part of a sound strategy, can start causing issues when available storage space becomes limited. Old database backups are in many cases unnecessary, and it is often prudent to delete those backups to increase available storage. This can be done manually or automatically. There are several options to delete old backup files automatically in SQL Server:

• Delete old database backup files automatically in SQL Server using a SQL Server Agent Job

• Delete old database backup files automatically in SQL Server using a SQL Server Maintenance plan

Delete old database backup files automatically in SQL Server using SQL Server Agent:

For this option, the SQL Server Agent service must be installed and running. It requires basic knowledge and understanding of Transact-SQL (T-SQL) scripting, but the process is then entirely automated, so there is no further input from the user needed. It is necessary to create a stored procedure first upon which the SQL Server Agent job we make would call to execution. The advantage of this is the ability to use the same stored procedure across different jobs with different input parameters. To create a SQL Server Agent scheduled to delete old backup files, we must do the following steps:

1. SQL Server Agent service must be running. In the Object Explorer and check the icon beside SQL Server Agent. Should the message “Agent XPs disabled” is shown beside it, go to Control Panel/System and Security/Administrative Tools and run Services (the exact location may vary due to different operating systems). Locate the SQL Server Agent service for the corresponding SQL Server instance, select it and click on Start in the top left of the window, or simply right click on it and select Start. If the service is already running, you can skip this step:

   

2. Create a user stored procedure which will use the input from the SQL Server Agent scheduled job to delete old backup files. Right click on the database upon which we want to act and select New Query:

   

   In the new query window enter the following T-SQL:

   CREATE PROCEDURE [dbo].[usp_DeleteOldBackupFiles] @path NVARCHAR(256),
   	@extension NVARCHAR(10),
   	@age_hrs INT
   AS
   BEGIN
   	SET NOCOUNT ON;
   
   	DECLARE @DeleteDate NVARCHAR(50)
   	DECLARE @DeleteDateTime DATETIME
   
   	SET @DeleteDateTime = DateAdd(hh, - @age_hrs, GetDate())
   
           SET @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))
   
   	EXECUTE master.dbo.xp_delete_file 0,
   		@path,
   		@extension,
   		@DeleteDate,
   		1
   END
   

   As it can be seen in this query, an extended system stored procedure xp_delete_file is being used. It reads the file header to check what type of file it is and will only delete certain types based on the input parameters we choose. After running the query, we should end up with a stored procedure:

   

3. After creating the stored procedure, we need to create a scheduled job with SQL Server Agent which will use the stored procedure with our parameters to delete the old backup files.

   To do that, right click on SQL Server Agent and select New then Job…

   

4. On the General tab, enter a descriptive name and optionally a description for the job:

   

5. On Steps tab, go to New…:

   

6. In the New Job Step window, under General tab, enter a descriptive job name, under Database select the database which we want the job to work on and under Command: insert the following line:

   usp_DeleteOldBackupFiles ‘D:\MSSQL_DBBackups’, ‘bak’, 720

   

   To explain the line above:

   usp_DeleteOldBackupFiles – calls the stored procedure we created earlier

   ‘D:\MSSQL_DBBackups’ – the first parameter tells the stored procedure where to look
   ‘bak’ – the second parameter tells what extension or file type to look

   Note: for the extension, do not use dot before the extension as the xp_delete_file already takes that into account. ‘.bak’ is incorrect use as opposed to ‘bak’, which is correct use.

   720 – the third parameter which tells the stored procedure the number of hours a backup file must be older than to get deleted.

7. Under Advanced tab, choose what will be done after this step is done successfully. Seeing that this is the only step, we can select ‘Quit the job reporting success’. Here we can also set how many times the step will retry as well as time interval between tries in minutes.

   

8. Next tab is Schedules. Under this tab we can set up when will this job work.

   Go to New…:

   

9. In the new window, enter a descriptive name for the schedule. Also, for Schedule Type check that it is set to Recurring for proper scheduled operation. After that, we can set up the schedule using the options below:

   

   The rest of the tabs is optional in this use case, though, we could use Notifications tab to set up email notifications when the job is completed.

10. After completing the previous step, our Job is created. It will run according to schedule, deleting old database backup files as set in the job itself. SQL Server Management Studio by default doesn’t show any real-time notifications when the job is performed. If we want to check the job history, we can right click on the job and select View History:

    

11. In the new window we can see the history for the selected job which executed successfully:

    

Delete old database backup files automatically in SQL Server using SQL Server Maintenance plan:

SQL Server Maintenance plans are another way of deleting old backup files by using the cleanup task.

1. When connected to the server, expand it and the Management folder after. Then right click on Maintenance Plans and click on Maintenance Plan Wizard

   

2. In the Maintenance Plan Wizard, click Next

   

3. Enter a descriptive name and, optionally, a description, and in the bottom right click on Change…

   

4. When New Job Schedule window pops up, check if the Schedule type is set to Recurring. After that, we can set up the schedule using the options below. After that, click OK.

   

5. Check if everything is correct before clicking on Next:

   

6. In the next window, check Maintenance Cleanup Task and click on Next:

   

7. In the following window, because we have only one task, there is no ordering, so we proceed by clicking on Next:

   

8. In the following window:

   1. Under Delete files of the following type: we select Backup files by clicking on the radio button.

   2. Under File location: we select Search folder and delete files based on an extension. Under that, we specify where to search for the folders and what type of an extension to look for. Also, we can check the Include first-level subfolders option if the backups are stored in separate subfolders.

      Note: The file extension we input must not contain dot (‘.’) – ‘.bak’ is incorrect, ‘bak’ is correct

   3. Under File age: we check the option Delete files based on the age of the file at task run time and specify the age of the files below

   After checking that everything is correct, we proceed by clicking Next:

   

9. In the next window, we may select that a report is written and/or mailed to the email address we input every time the Maintenance plan runs.

   

10. In the next window we press Finish to complete the creation of our Maintenance Plan. After that we can check under Management → Maintenance Plans for our newly created plan:

    

    

Downloads

Please download the script(s) associated with this article on our GitHub repository

Source : https://solutioncenter.apexsql.com/how-to-delete-old-database-backup-files-automatically-in-sql-server/