Diff database backup question - 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 2005 Forums

SQL Server Administration (2005)

Diff database backup question

Author

Topic

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2008-07-17 : 11:33:30

I am taking Full db backup once a week(sunday) and Diff db backup the rest of the week(Mon-sat). If I want to limit the number of diff db backup files like just have 3 days worth of backup's just like we had in sql 2000 maint plan wizard, where can I do that in sql 2005, otherwise I think the diff db backup files will keep piling up in the directory. Thanks!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-17 : 11:43:25

quote:
Originally posted by sqlserverdeveloper

I am taking Full db backup once a week(sunday) and Diff db backup the rest of the week(Mon-sat). If I want to limit the number of diff db backup files like just have 3 days worth of backup's just like we had in sql 2000 maint plan wizard, where can I do that in sql 2005, otherwise I think the diff db backup files will keep piling up in the directory. Thanks!

Don't do from wizard. Do it through SSIS package (Next option from MAint plan wizard) and add history cleanup task.you can specify how long to keep backups and etc.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-17 : 23:49:52

You can choose maintenance cleanup task in wizard. By the way, history cleanup task cleans maintenance history in msdb. It does nothing on backup files.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-18 : 08:00:49

My bad. It is maintenance Cleanup task . And you can't do in wizard.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-19 : 01:08:53

>> you can't do in wizard.

What? Did you ever apply sql2k5 sp on your server?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-19 : 01:44:37

You can use my script, which does what you want and doesn't require wizards or maintenance plans (which I can't stand): http://weblogs.sqlteam.com/tarad/archive/2008/05/21/60606.aspx

Here's all of my maintenance routines: http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Here's what I've got running in my production environments: http://weblogs.sqlteam.com/tarad/archive/2008/06/30/SQL-Server-jobs-on-production-instances.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2008-08-07 : 09:28:44

quote:
Originally posted by tkizer

You can use my script, which does what you want and doesn't require wizards or maintenance plans (which I can't stand): http://weblogs.sqlteam.com/tarad/archive/2008/05/21/60606.aspx

Here's all of my maintenance routines: http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Here's what I've got running in my production environments: http://weblogs.sqlteam.com/tarad/archive/2008/06/30/SQL-Server-jobs-on-production-instances.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Thanks a lot Tara for this info, When I was trying to create the proc [isp_Backup] which is in your link provided I am getting the below errors:
Cannot add rows to sysdepends for the current object because it depends on the missing object 'master.dbo.xp_backup_database'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'master.dbo.xp_backup_database'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'master.dbo.xp_backup_log'. The object will still be created.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-07 : 12:53:05

If you'll notice in the output, it says they are warnings, not errors. Ignore the warnings. It works fine.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2008-08-07 : 15:03:32

quote:
Originally posted by tkizer

If you'll notice in the output, it says they are warnings, not errors. Ignore the warnings. It works fine.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Thanks, it works great!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources