[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Backup Database syntax question, retain 5 days before overwriting

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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

649 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