asked on

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?

Raja Jegan R

This should help you out with both Scheduling and overwriting older backup files.

http://www.sqldbatips.com/showarticle.asp?ID=27

ajdratch

ASKER

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?

Marten Rune

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

ajdratch

ASKER

The date modified on the backup file never changes. That is why I think it is not working

Marten Rune

Try restore header only

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

Lookat BackupStartDate and BackupFinishDate

//Marten

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ajdratch

ASKER

I gort a script to finally work