Solved

SQLCMD backup and overwrite

Posted on 2010-09-06
7
2,766 Views
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?
0
Comment
Question by:ajdratch
  • 3
  • 2
  • 2
7 Comments
 
LVL 57

Expert Comment

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

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

Author Comment

by:ajdratch
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
GO

Can I put something in there to overwrite the file?
0
 
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
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

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

Expert Comment

by:Marten Rune
ID: 33612909
Try restore header only

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

Lookat BackupStartDate and BackupFinishDate

//Marten
0
 
LVL 57

Accepted Solution

by:
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..
0
 

Author Closing Comment

by:ajdratch
ID: 33748952
I gort a script to finally work
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

808 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