?
Solved

Backup Database syntax question, retain 5 days before overwriting

Posted on 2007-11-26
4
Medium Priority
?
912 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
  • 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 400 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

621 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