SQLCMD backup and overwrite

Posted on 2010-09-06
Last Modified: 2012-08-13
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?
Question by:ajdratch
  • 3
  • 2
  • 2
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33611861
This should help you out with both Scheduling and overwriting older backup files.

Author Comment

ID: 33611953
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

Can I put something in there to overwrite the file?
LVL 20

Expert Comment

by:Marten Rune
ID: 33612154
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.


Author Comment

ID: 33612165
The date modified on the backup file never changes. That is why I think it is not working
LVL 20

Expert Comment

by:Marten Rune
ID: 33612909
Try restore header only

FROM DISK = N'\\server\backupfile.bkf'

Lookat BackupStartDate and BackupFinishDate

LVL 57

Accepted Solution

Raja Jegan R earned 500 total points
ID: 33615449
>> 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..

Author Closing Comment

ID: 33748952
I gort a script to finally work

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Optimization 14 44
SSRS 2013 - Overlapping reports 2 21
T-SQL Default value in Select? 5 26
SQL Syntax: How to force case sensitive query? 2 26
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

770 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