Way to get rid off old backups in a DB Maint. plan
-
Hello everybody,
I have created a maintenance plan for backing up my databases which was scheduled for a particular time. In that i mentioned to delete the databases which were 4 days old. but to my surprise, the backup folder is being appended with the new date backup. the backup folder is not the default one. also breif me what the two file extensions are ie., BAK & TRN.
I am of the view that .TRN will be the transaction log whereas .BAK consists of ? A normal backup will be having 2 files ie., MDF and LDF extensions.
Pl elaborate if anyone has a solution.
Ravi
Lucky -
The BAK extension is usually a full backup. It contains the data and schema, not the mdf and ldf files.
Not sure why your backups are not being deleted. Which version of SQL? Which options have you checked on the bacakup tabs?
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Ravi,
You might have scheduled a weekly job & mentioned 4 in the options in the command. Hence SQL Server will keep 4 weeks of Backups. If you schedule a Daily Job, then It will keep 4 days of Backups.
If this is not the case, please paste the command (which is in the job) & let us know wheather its a Weekly or Daily Job.
Also let us know what Version of SQL Server you are running (per Andy's Note).
Hope this helps!!!
Regards,
Murali Damera.
.
-
A normal backup will NOT have the extension .MDF or .LDF! To have backups that use those extensions, you have to detach the database and COPY the .MDF and .LDF files. But then those are not true backups (you can't RESTORE them, you have to copy them back and attach them).
A true SQL Server backup (using the BACKUP command) creates a file with the extension .BAK (if you use the database maintenance, then the log backup is .trn).
-SQLBill
-
Note those extensions are defaults. You can call them anything you want.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Hello All,
Thanks for your valuable input to my query. I am providing some more information and think that you people will shed more light towards my question posted earlier.
I am having 2 servers with version & releases as follows:
Server 1 - SQL 7 7.00.0623
--------------------------
On Server 1 the Database Maintenance Plan settings are as follows:
Tabs
----
General All Databases
Optimizations Update the statistics used by the query optimizer
Percentage of databases to sample 10%
Remove unused space from database files
Shrink database when it grows beyond 50 MB
Amount of free space to remain after shrink 10% of the data space.
Schedule 1 day at 11.30AM
Integrity Check database integrity
Include indexes
Perform these tests before backing up of the databases or transaction log
Schedule 1 day at 1AM
Complete Backup Backup the databases as part of the maintenence plan.
Verify the integrity of the backup upon completion.
Disk
Use this directory C:\MSSQL7\BACKUP
Create a sub-directory for each database
Remove files older than 3 day(s)
Backup file extension BAK
Schedule 1 day at 1.01PM
Transaction Log Backup Backup the databases as part of the maintenence plan.
Verify the integrity of the backup upon completion.
Disk
Use the default backup directory
Remove files older than 2 day(s)
Backup file extension TRN
Schedule Occurs every 1 week from Monday to Saturday at 12AM
Reporting Text Reports
Write report to a text file C:\MSSQL7\BACKUP
Delete text report files older than 7 Day(s)
History on this server
Write history to the table msdb.dbo.sysdbmaintplan_history
Limit rows in the table to 1000 rows for this plan
Server 2 - SQL 2000 8.00.760
----------------------------
On Server 1 the Database Maintenance Plan settings are as follows:
Tabs
----
General All Databases
Optimizations No settings
Integrity No settings
Complete Backup Backup the databases as part of the maintenence plan.
Verify the integrity of the backup upon completion.
Disk
Use this directory H:\sqlbackup
Remove files older than 4 day(s)
Backup file extension BAK
Schedule 1 day at 2AM
Transaction Log Backup Backup the databases as part of the maintenence plan.
Verify the integrity of the backup upon completion.
Disk
Use this directory H:\sqlbackup
Remove files older than 4 day(s)
Backup file extension TRN
Schedule Occurs every 1 day at 3AM
Reporting History on this server
Write history to the table msdb.dbo.sysdbmaintplan_history
Limit rows in the table to 1000 rows for this plan
Ravi
Ravi
Lucky -
This looks ok. When you say the backup folder is being appended, what does that mean? Can you give an example?
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Hello Steve,
When appending the backup folder i mean that today if the schedule runs (ie., 23/4/03 dated backup), then the backup folder which is consisting of the earlier backups from 10/4/03 (for example). When we check the "Delete backups older than 4 days" the folder should contain only the backups from 19/4/03 to 22/04/03 as on today before todays backup is taken.
Hope you have understood my problem.
Ravi
Lucky -
Hello Steve,
I did not get any reply from your end for the example provided as you asked me on the other day.
I am waiting for your suggestion.
Ravi
Lucky -
I have noticed from past experience that if the job fails for some reason, it will not delete the old files. For example, I have a very large database that is backed up with a Maintenance plan and I only keep the three days worth of bak files. When the scheduled maintenance plan fails for some reason, it does not delete the files older than the three days. I typically keep tabs to make sure that it is working correctly.
SJ
-
First of all the deletion of old BAK files takes place after the current BAK file is successfully created. If it fails, then the earlier bak files that are to be deleted will not be deleted.
Ravi, It seems something has changed. Did you edit the Maint Plan or change the Bak location by copying over the older files.
I would suggest drop the maint Plan and set it up again. Make sure you remove the old files. After four days you should see the Maint Plan deleting these Baks regularly.
Accepting all invitesViewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply