How to create a database rolling backup in Microsoft SQL Server?

Asked By 240 points N/A Posted on -
qa-featured

Our company uses Microsoft SQL Server 2005 as the database management system. We have several databases hosted on it. Everyday we create a scheduled backup using the Microsoft SQL Server Agent. The backup runs at 7 pm every night. Each backup runs up to around several gigabytes. We need to retain only 2 days backup on the production server and overwrite the older files. How do I go about doing a rolling backup for databases in Microsoft SQL Server?

SHARE
Best Answer by Ingrid
Answered By 0 points N/A #100622

How to create a database rolling backup in Microsoft SQL Server?

qa-featured

Microsoft SQL Server Agent is a good method to use, for creating backups of the database. It allows you to specify an SQL statement and executes it at the predefined time. In order to create a rolling backup, you will need to arrive at an algorithm to create the filename. This algorithm could be based on the day of the year. If the day is an odd number, you can have one file name, if the day is even you can have another filename. An example is as follows:

database_name_1.bak – for odd days
database_name_2.bak – for even days

You will need to compensate for months that have an odd amount of days, as the next day will also be an odd day.

Answered By 0 points N/A #100624

How to create a database rolling backup in Microsoft SQL Server?

qa-featured

You can write a batch file to delete older files, that are older than two days. This file can then be scheduled using Windows Scheduler. In the event your backups are in a certain folder, you can write a batch script that will iterate over all the files in the directory, check the created date time and remove the files that need to be removed. You can always modify the script centrally so that you do not need to be opening Microsoft SQL Server 2005 for editing the scheduled jobs. Alternatively, you can use the Database Maintenance plan to define the whole process of backing up and cleaning up. This method requires super user access to the database system and cannot be done remotely.

Answered By 0 points N/A #100625

How to create a database rolling backup in Microsoft SQL Server?

qa-featured

Microsoft SQL Server 2005 ships with a database maintenance plan utility. It allows you to define a workflow based activities. For example if you want to backup a database, and then send an email, you can configure a database maintenance plan for the same. There are "activities" such as:

  • Backup Database Task
  • Email Task
  • Index Task
  • Clean Maintenance Logfiles Task

These can be dragged and dropped onto the drawing canvas. Thereafter you define actions to take based on the task outcome. The outcomes can be:

  • Success
  • Failure
  • Success or Failure

You can use the Maintenance task to tell to remove backup files that are older than a specified number of days.

Answered By 240 points N/A #100626

How to create a database rolling backup in Microsoft SQL Server?

qa-featured

Using a database maintenance plan sounds interesting. Thank you for posting various options available for creating a rolling database backup. I opened up Microsoft SQL Server 2005 using the Microsoft SQL Server Management Studio snap in. It expanded the "Management" tab and found a "Maintenance Plans" tab. I am a bit confused on what I should do from there. Now what do I do?

Best Answer
Best Answer
Answered By 0 points N/A #100627

How to create a database rolling backup in Microsoft SQL Server?

qa-featured

You can create a Database Maintenance Plan in two ways. One way is to use the "Wizard". The next is to create it from a blank template. You can right click the "Maintenance Plans" node and pick one of the two choices. I would advise you to use the "Wizard". The key activity that you need to incorporate is, the "Maintenance Clean Up Task". The Maintenance Clean Up Task allows you to give a filter on the files that you need to remove.

You can specify the number of days it should look for and the file extension pattern. Based on the given details, the task will remove files that match the criteria. You can also configure the directory it should look under. There is an option where you can tell it to look in all the subfolders. The following screenshot will help you. Notice that I have set the clean up task to retain only 2 days of files.

Answered By 240 points N/A #100628

How to create a database rolling backup in Microsoft SQL Server?

qa-featured

I am getting an error when I tried to use the "New Maintenance Plan" or the "Wizard" option in the "Maintenance Plans" tab. I have copied the error text and the screen shot for reference. Is this something to do with Windows XP? PLEASE HELP! TITLE: Microsoft SQL Server Management Studio.
——————————

'Agent XPs' component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online. (Object Explorer)

——————————
BUTTONS:

OK
——————————

Answered By 0 points N/A #100629

How to create a database rolling backup in Microsoft SQL Server?

qa-featured

To use the Microsoft SQL Server 2005 Database Maintenance Plans, you need to enable execution of Extended Stored procedures. Extended Stored procedures are a set of very powerful stored procedures that allows the database system to access and manipulate operating system recourses. By default these stored procedures are disabled for security reasons. You can execute the following commands to enable extended stored procedures. Agent XP's allows the maintenance plan to interact with the operating system and do advanced procedures such as file deletions and file writings.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

Hope this will overcome your problem. The error message you are experiencing has nothing to do with Windows XP, it's just a shortened form for Extended Stored Procedures.

Answered By 240 points N/A #100630

How to create a database rolling backup in Microsoft SQL Server?

qa-featured

Enabling Extended Stored Procedures worked. Now I can create the maintenance plan as outlined by Ingrid. I also modified the backup routines so that I can get an alert if the database backup fails. I also can see that I can bypass the file deletion, if the backup fails. Database Maintenance Plan is the answer to my problem. Thank you Mathias for helping me. Thank you all for the time and knowledge posted here! I will get back if I face problems. Thank you again!

Related Questions