SQL 2005
I have a scheduled back up running at 1am everynight which after a few problems is running ok.
Can I get it to overwrite the existing nights back up? no data has yet to be added to the database as we are still testing yet the back up is doubling in size each night.
Not really a SQL specialist this has been dropped on me so go easy on me please
Thanks
Hello,
If you wish your backup to overwrite an existing backup (as opposed to appending to it), you can use the following command:
backup database MyDatabase to disk = 'Z:\BackupFolder\MyDatabase.bak' WITH INIT
WITH INIT initialises the backup...
Cheers
Rob
Rob
Many Thanks for the speedy reply I will try it out over the weekend.
Matt.
One BIG word of caution here:
I would ALWAYS make sure that you have at least one good backup at all times, preferably on another system or offline.
If you always use the same filename for backups, and use WITH INIT, as soon as the backup starts, you are without a backup. If for any reason that backup fails (worst case, a crash causing corruption in the middle of the backup), you have nothing to go back to.
A better scheme might be to generate filenames which include the current date. That way you always write to a fresh file, and you can create another maintenancd job to prune off old backups, while keeping <n> copies available.
