Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 914
  • Last Modified:

Backup Database syntax question, retain 5 days before overwriting

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
larrybye
Asked:
larrybye
  • 3
1 Solution
 
YveauCommented:
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
 
larrybyeAuthor Commented:
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
 
YveauCommented:
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
 
YveauCommented:
Glad I could be of any help !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now