Making a good backup plan - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Making a good backup plan

Author

Topic

skybvi
Posting Yak Master

193 Posts

Posted - 2012-05-14 : 03:28:52

i have two database servers...one is QA and one is PROD

there are about 5 separate databases on each server

i want them to back up on a schedule to a mapped drive as follows

First saturday of the month: 1st full back up of all databases on 100
monday - incremental backup
tuesday - incremental backup
wednesday - incremental backup
thursday - incremental backup
friday - incremental backup
2nd saturday of the month: 2nd full back up /delete the incremental backups
sunday - incremental backup
monday - incremental backup
tuesday - incremental backup
wednesday - incremental backup
thursday - incremental backup
friday - incremental backup
3nd saturday of the month: 3rd full back up /delete the incremental backups/ delete 1st full back up
sunday - incremental backup
monday - incremental backup
tuesday - incremental backup
wednesday - incremental backup
thursday - incremental backup
friday - incremental backup
similarily the 4th saturday too

Any hint, how to accomplish this task via t sql script or through Maintenence plans?
Whcih is easier and better...

Regards,
Skybvi

Regards,
Sushant
DBA
Virgin Islands(U.K)

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-05-14 : 15:00:57

First, you cannot backup to a mapped drive. SQL Server knows nothing about a mapped drive - you will need to use the UNC path to that folder instead. Be aware that these backups will take longer, and could fail due to network connectivity issues.

Second, I would not recommend deleting the incrementals until you delete the full backup they rely on. In your plan, if you find out on the Sunday after the 3rd Saturday that you need to restore from the Thursday prior to the second Saturday you won't be able to. For example, you are informed on the Monday after the 3rd Saturday that a change was made 2 weeks ago that caused data to be deleted - and they didn't discover the issue until just now you don't have any way to recover.

Now, to answer your question:

You can create a script in T-SQL that reads from the backupset and related tables in MSDB to get the list of backup files. From this, you would identify the full backup that is the latest version you want to keep. Get the end date and time from that backup job - and pass that information to the xp_delete_file stored procedure (undocumented procedure - so use at your own risk). This will then delete all files specified before the date/time passed to the procedure.

You would execute the procedure once for all full backups, and once for all differential backups you want deleted.

Jeff

Sachin.Nand

2937 Posts

Posted - 2012-05-14 : 16:49:13

First there is no concept of incremental backup in sql server.Except for full backup there are Differential backup and log backup.log backup can be called as a kind of incremental backup but they are not data backups.

After Monday and Tuesday even the calendar says W T F ....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources