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

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

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.

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

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.

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

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.

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

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?

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

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
——————————

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

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.

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

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!