Solved

Backup Database syntax question, retain 5 days before overwriting

Posted on 2007-11-26
4
898 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 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql 2016 Integration Service connecting to 2012 3 29
SSRS - Date Report Options 2 26
Re-appearing SQL Server Agent jobs 7 28
SQL R 21 22
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

789 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