SQL Server 2005 backups - 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)

SQL Server 2005 backups

Author

Topic

larrys@itech.biz
Starting Member

2 Posts

Posted - 2008-06-25 : 10:16:25

In SQL Server 2000 when creating a maintenance plan to backup a database and transaction logs, there was an option to specify retention for the backup files (how many days/weeks to keep the backup files).

So, how can I do this with SQL Server 2005?

Answers can be posted on this forum or can be e-mailed to me at larrys@itech.biz.

Thanks for any help.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-25 : 11:52:37

First of all,

Don't put your Email address while asking? We have to charge you if we send answer by email.

Second, option to specify retention for the backup files is not included in Maintenance plan wizard in SQL 2005. You will have to choose other maintenance plan(SSIS package) and choose History cleanup task to specify how long to keep backups.

contrari4n
Starting Member

27 Posts

Posted - 2008-06-25 : 12:38:02

Even better, don't use maintenance plans at all.

Use Tara's script here: http://weblogs.sqlteam.com/tarad/archive/2008/05/21/60606.aspx

It is the most complete backup script I have seen, and I may start to use it instead of mine in the near future.

The only potential issue is if xp_cmdshell is disabled in your environment. I have described an xp_cmdshell alternative here: http://www.sql-server-pro.com/xp_cmdshell.html You would have to edit her stored procedure to include this.

Richard Fryar
http://www.sql-server-pro.com
SQL Server Articles and Tips

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-25 : 12:51:14

quote:
Originally posted by sodeep

Don't put your Email address while asking? We have to charge you if we send answer by email.

That is not true.

If larrys doesn't care about the spam he'll get to that account, then so be it.

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

Subscribe to my blog

larrys@itech.biz
Starting Member

2 Posts

Posted - 2008-06-25 : 13:36:49

What about adding DELBKUPS to the SQL script that performs the backups? I found it under SQL 2000 - is this supported in SQL 2005?

Here is an example:

sqlmaint -S MyServer -PlanName MyUserDBPlan -BkUpDB -BkUpMedia DISK -UseDefDir -CrBkSubDir -DelBkUps 2weeks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-25 : 13:41:26

Yes that is supported in SQL Server 2005. It is very easy to see what parameters to pass the sqlmaint utility by typing in sqlmaint into SQL Server Books Online.

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

Subscribe to my blog

mdubey
Posting Yak Master

133 Posts

Posted - 2008-06-25 : 13:53:13

But cleanup History task will also do the same for Retention.

Manoj
MCP, MCTS

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-25 : 14:45:52

quote:
Originally posted by mdubey

But cleanup History task will also do the same for Retention.

Manoj
MCP, MCTS

It has already been answered.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources