[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQLCMD backup and overwrite

Posted on 2010-09-06
7
Medium Priority
?
3,151 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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 2000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

656 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