INIT option in DATABASE BACKUP

[TheBugSlayer]

I am trying to understand the INIT option of the DATABASE BACKUP statement. I undestand it overwrites (recreates) the backup in the same file while leaving the header intact, is that correct? What does the header contain?

Thanks for your help.

 

[SQLBill]

Yes you are correct.

To view the header information, do this in Enterprise Manager:

Expand the server group,
expand the server
expand MANAGEMENT
click on BACKUP
in the details pane, right-click on a named backup device,
select properties.

That is from the Books OnLine (BOL). I used the Index tab and entered Media Header.

-SQLBill

 

[TheBugSlayer]

USE MyDB
BACKUP DATABASE MyDB
TO Disk = 'E:\DataSql\MyDB.bak'
WITH FORMAT,
NAME = 'MyDB'
GO

I above script creates a backup. From what I understand the FORMAT option implies SKIP and INIT, therefore each that the script runs data is overwritten in the same backup file, am I correct?

I have seen instances when everytime you create a backup a file is written in the media with a date-time value added at the end of the filename...

Thanks for your help.

 

[SQLBill]

Yes that is true.

HOWEVER, it also means that ALL parts of the media set are formatted. Let's say you are backing up to tape. You have 5 tapes in a media set that you are using for full backups. You do a backup to one of the tapes using FORMAT, ALL 5 tapes will be FORMATted.

-SQLBill

 

[TheBugSlayer]

Good to know. Thanks a lot.

 

[babeo]

Hi all,

I want to back up a new database on the same backup device, called Dailybackup, since this is a new database, and I want to have full backup without init, does it mean the first dump of this databse on this device with init will erase other databases and initialize the device or it does not?
Thanks

 

[SQLBill]

Babeo,

You really should have started a new post and not tagged on to this one.

If you backup using INIT it will overwrite whatever file you are backing up to. It just leaves the header intact. However, if you are using EXPIREDATE and RETAINDAYS options, then INIT may not cause the files to be overwritten and you won't be able to write to them until those options have run out.

I really recommend going to the BOOKS ONLINE, use the Index tab and enter BACKUP. Then select BACKUP (described).

-SQLBill