Asked By
Amelia
240 points
N/A
Posted on - 05/18/2011
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?
Answered By
Mathias
0 points
N/A
#100622
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.
Answered By
Edvard
0 points
N/A
#100624
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.
Answered By
Ingrid
0 points
N/A
#100625
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.
Answered By
Amelia
240 points
N/A
#100626
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?
Answered By
Ingrid
0 points
N/A
#100627
How to create a database rolling backup in Microsoft SQL Server?
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
Amelia
240 points
N/A
#100628
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
——————————
Answered By
Mathias
0 points
N/A
#100629
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.
Answered By
Amelia
240 points
N/A
#100630
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!
