Append backup files

Question

Append backup files

Ed7

SSCrazy Eights

Points: 8519
More actions
June 2, 2010 at 8:09 am

#222159

Hello room,
I created a database backup plan in Microsoft SQL Server Management Studio 2008.
Then, I specified the backup location and select APPEND (If backup file exist).
However, I noticed that the backup file was overwrite.
Can anyone help?
Thanks,
Edwin

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

Are you backing up to a device ? With append, the size should grow by the size of the database. With overwrite, the size should stay about the same as the database.

I prefer to backup to separate, individual .bak & .trn files. I find it easier to manage and verify.

Ed7 SSCrazy Eights Points: 8519 More actions · Answer 2

Would you please share with me how to backup to separate, individual bak & trn files.

I only know to accomplish those by setup separate jobs.

Thanks,

Edwin

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

Use the WITH INIT in the backup command, or don't check "append" in a maintenance plan. You typically then have the files named differently each day. There are lots of scripts on this site to help with that, or the maintenance plan will handle that.

Typically log files are backed up more often, so you do have two jobs.

Ed7 SSCrazy Eights Points: 8519 More actions · Answer 4

Hello Steve,

Yes. There are many database backup scripts and I run into errors.

I create a job that runs on Daily at 3:00 AM and step is

BACKUP DATABASE Operations

TO DISK = 'F:\Backup\Operations_Full.bak'

MIRROR TO DISK = 'F:\Backup\Operation_Full_2.bak'

WTIH COMPRESSION, INIT, CHECKSUM, STOP_ON_ERROR

GO

I noticed that the bak file will get overwrite.

Thanks,

Edwin

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 5

This will overwrite the backup every day. I suggest you look at our scripts section and get a script that will change the name of the file everyday used for the backup, and then delete older backups after they are a couple days old.

homebrew01 SSC Guru Points: 55548 More actions · Answer 6

As Steve mentioned you can use a maintenance plan. Then you can specify how many days to retain your individual backup files. It can be done through scripting also but is more cumbersome.

Ed7 SSCrazy Eights Points: 8519 More actions · Answer 7

I downloaded a database backup script from this links

http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/69022/

My test computer has the following environment:

Computer Name: xServer03

Database Instance : xServer03\ALG

Backup folder: D:\Backup

I modified those script on:

@BackupSeverName VARCHAR(50) = N'xServer03',

@BackupShareInput VARCHAR(200) = N'D:\Backup',

@DoAfullBackupInput INT =1

Run the script, and errors indicated "Must declare the scalar variable" @backupServerNameInput.

Msg 102, Level 15, State 1, Line 7

Incorrect syntax near '?'.

Msg 137, Level 15, State 2, Line 36

Must declare the scalar variable "@BackupServerNameInput".

Msg 137, Level 15, State 2, Line 37

Must declare the scalar variable "@BackupShareInput".

Msg 137, Level 15, State 2, Line 38

Must declare the scalar variable "@DoAFullBackupInput".

Msg 102, Level 15, State 1, Line 47

Incorrect syntax near '?'.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 8

Steve Jones - SSC Editor

SSC Guru

Points: 736270

June 2, 2010 at 2:07 pm

#1175563

Those are syntax errors. You have mistyped something.

Ed7 SSCrazy Eights Points: 8519 More actions · Answer 9

I selected those script, copied and paste into my Microsoft SQL Server Management Studio.

Then, I changed two parameters:

@BackupServerNameInput VARCHAR(50) = N' xServer03',

@BackupShareInput VARCHAR(200) = N'D:\Backup'

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 10

Those are still syntax errors. They say that the variable hasn't been declared. Somewhere either the declaration is wrong or the use of the variable has the name typed incorrectly.