Post reply

BACKUP WITH FORMAT

  • January 18, 2012 at 3:42 pm

    #391347

    All,

    What does BACKUP WITH FORMAT do?

    I have a Sql 2008 Enterprise database server with multiple databases over 1 TB in size. All these years we've been performing backups to Disk. Then some other process writes them to TAPE.

    Now I want to implement backup COMPRESSION. But when I try that on a test machine, I get an error saying that I need to FORMAT media before performing COMPRESSION on backups.

    So I read on MSDN on what FORMAT does, but its not making any sense to me.

    Use FORMAT carefully. Formatting any volume of a media set renders the entire media set unusable. For example, if you initialize a single tape belonging to an existing striped media set, the entire media set is rendered useless.

    What does it mean for disk backups? What is "media set" ? Does it mean I cannot restore from old backups on disk and tape? Could some one please translate from English to English (simple)?

    Edit: Question 2: Why is COMPRESSION backup allowed for a database at compatibility level 90 on a Sql 2008 instance? [Is it because, backup is an instance level operation and can compress any database, independent of its compatibility level]

    Thanks,

    _UB

    PS: Sorry for sound silly, I never understood the concept of media-set, media-header, media-volume, backup-set, etc. I can run queries on the MSDB tables to get necessary information related to Sql Jobs (anyone with Sql expertise can do it)

    [Edit: Added another question.]

    SqlTimes Blog on Sql Server

  • January 18, 2012 at 3:59 pm

    #1435592

    It simply means that you cannot have compressed and uncompressed backups on the same backup media, which means a file (or set of files if you're striping).

    FORMAT will overwrite any existing data in the backup media.

    As far as the compatibility mismatch is concerned, I'm not surprised you can take compressed backups even if comp level is 90. I think the idea behind is that it's something that belongs to the instance and can be applied to any database, regardless of comp settings.

    Thanks for explicitng this, however. I never had noticed it before.

    -- Gianluca Sartori

  • January 19, 2012 at 9:15 am

    #1435966

    Thank you for the response. I have a couple of follow up questions.

    Can I store compressed backup and un-compressed backup on the same LUN drive (disk drive attached to the server) without causing any data loss? [Not tape, LUN disk drive]

    My guess: Yes, we can.

    So when I perform BACKUP WITH FORMAT does it actually FORMAT that drive (LUN) or the provided path?

    My guess: FORMAT only works with TAPEs

    Now, I am curious to try if a compressed backup (of compatibility 90 database) from a Sql 2008 server can be restored on a Sql 2005 machine. It should fail, right?

    Thanks,

    _UB

    SqlTimes Blog on Sql Server

  • January 19, 2012 at 9:56 am

    #1436022

    Good question. I don't have a spare drive now I want to test FORMAT on. I think it applies only to TAPES, but I'm not positive.

    Nothing in there: http://msdn.microsoft.com/en-us/library/ms186865.aspx

    Disk, you can have compressed and uncompressed on the same drive, just not the same media set (file). This works fine:

    BACKUP database db1 TO DISK = 'firstfull.bak' WITH init

    GO

    BACKUP DATABASE db1 TO DISK = 'secondfull.bak' WITH init, COMPRESSION

  • January 19, 2012 at 10:33 am

    #1436043

    Thank you for the quick response. I will try this on a test machine. May be adding a "backup device" could help me understand this better.

    SqlTimes Blog on Sql Server

Viewing 5 posts - 1 through 4 (of 4 total)

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