[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3221
  • 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 RuneCommented:
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
ajdratchAuthor Commented:
The date modified on the backup file never changes. That is why I think it is not working
0
 
Marten RuneCommented:
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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