Solved

Backup Database syntax question, retain 5 days before overwriting

Posted on 2007-11-26
4
899 Views
Last Modified: 2012-05-05
What is the proper syntax for creating a backup job that will keep the last 5 days before overwriting the file in SQL2000? I believe I need to add the expiredate and noskip arguments but not sure of the proper way to do this. Below is my current scheduled job backup statement.

BACKUP DATABASE [fms_prod] TO DISK = N'D:\MSSQL\DATA\MSSQL\BACKUP\fms_prod.BAK' WITH INIT,  DIFFERENTIAL ,  NAME = N'fms_prod',   STATS = 5,  NOFORMAT
0
Comment
Question by:larrybye
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 18

Expert Comment

by:Yveau
ID: 20350171
Try:

BACKUP DATABASE [fms_prod] TO DISK = N'D:\MSSQL\DATA\MSSQL\BACKUP\fms_prod.BAK' WITH RETAINDAYS = 5, INIT,  DIFFERENTIAL ,  NAME = N'fms_prod',   STATS = 5,  NOFORMAT

Hope this helps ...
0
 

Author Comment

by:larrybye
ID: 20350926
I thought that RETAINDAYS was the number of days until it can be overwriten? So If I said RETAINDAYS = 5 then it would not backup the DB to the file for 5 days.
0
 
LVL 18

Accepted Solution

by:
Yveau earned 100 total points
ID: 20352900
From BOL:
[ EXPIREDATE = date | RETAINDAYS = date ]
Specifies when the backup set for this backup can be overwritten. If these options are both used, RETAINDAYS takes precedence over EXPIREDATE.  If neither option is specified, the expiration date is determined by the media retention configuration setting.

So it has nothing to do with the backup schedule, just the number of days that the 'sfotware lock' prevents this backup set from being overwritten. You still can make a backup every ten minutes if you like, but SQL will not overwrite the backup set if the retain period has not yet passed. So you will get yourself another backup set on the same media.

Hope this helps ...
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20353177
Glad I could be of any help !
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question