April 21, 2006 at 9:02 am
I'm using a tsql script to create a job and schedule it to auto backup the databases.
It's appending the file each day but I want it to create a new BAK file each day. I thought the WITH INIT would do that. But maybe my syntax is wrong?
How can I either get it to overlay the BAK file instead of append, or put a time stamp into the name of the BAK file? Thanks for any ideas.
This is what I'm using as part of a sql script:
USE msdb
EXEC sp_add_jobstep @job_name = 'mybackupjob',
@step_name = 'Backup BESMgmt Data',
@subsystem = 'TSQL',
@command = 'backup database BESMgmt to disk = ''c:\Backups\BESMgmt_db.BAK'' with INIT',
@on_success_action = 3'
@retry_attempts = 5'
@retry_interval = 5
go
April 21, 2006 at 10:12 am
You need to set the ExpireDate/RetainDays. From Backup in BOL:
If EXPIREDATE or RETAINDAYS is not specified, expiration is determined by the media retention configuration setting of sp_configure. These options only prevent SQL Server from overwriting a file. Tapes can be erased using other methods, and disk files can be deleted through the operating system. For more information about expiration verification, see SKIP and FORMAT in this topic.
April 21, 2006 at 10:50 am
I don't see a clear example of the syntax to do this. Would the command be like this:
@command = 'backup database BESM to disk = 'c:\Backups\BESM_db.BAK' WITH INIT, RETAINDAYS = 2',
Thanks!
April 21, 2006 at 11:02 am
Yes, however, if you wish to overwrite your backup file each day, you will need to set your RETAINDAYS to zero or 1.
April 26, 2006 at 3:19 am
Hello! Iam wondering how i can make a script some wold take one tabeln in row tod do a backup in a database and save it to a device
like A: och C: but the size should be defined to exampel 40mb or something.
I hope for answer !
April 26, 2006 at 8:27 am
Hasan,
You should create a new post for your question. It is more likely to get answered in its own post since it is a new question.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply