• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3413
  • Last Modified:

SQLCMD backup and overwrite

I am running SQL Express 2005 and I am backing up SQL databases by scheduling SQLCMD scripts. I have a script for each day.

How can I get the backup to overwrite the existing backup file?
0
ajdratch
Asked:
ajdratch
  • 3
  • 2
  • 2
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
This should help you out with both Scheduling and overwriting older backup files.

http://www.sqldbatips.com/showarticle.asp?ID=27
0
 
ajdratchAuthor Commented:
I tried the @delfirst command but that did not work. Below is the script that I currently have

BACKUP DATABASE [datgabasename] TO  DISK = N'\\server\backupfile.bkf' WITH NOFORMAT, INIT,  NAME = N'sqlexpress\databasename10-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Can I put something in there to overwrite the file?
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
With INIT means scratching the backup in the file and adding a new one. So the file remains, but the backup in the file is new. You could for example run 7 schedules, one for each weekday, and use with init. would give you a weekly rollover of your backups. Remember to name the file like fullMonday, fullTuesdat etc

Regards Marten
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
ajdratchAuthor Commented:
The date modified on the backup file never changes. That is why I think it is not working
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Try restore header only

RESTORE HEADERONLY
FROM DISK = N'\\server\backupfile.bkf'
WITH NOUNLOAD;
GO

Lookat BackupStartDate and BackupFinishDate

//Marten
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> I tried the @delfirst command but that did not work.

If you have a backup device configured using sp_addumpdevice, then you can delete older files in the backup device. Script you are using above would create individual backup files and not in backup set and hence it can't be deleted easily. And you need to use windows scripting to identify older files and delete those files.

>> Can I put something in there to overwrite the file?

Have you tried using the script in the link which I have provided. Have tested that in one of my environment and works fine..
0
 
ajdratchAuthor Commented:
I gort a script to finally work
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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