Backup SQL Server database using WITH FORMAT

Question

I'm having a bit of trouble understanding the purpose of the WITH FORMAT option of the SQL Server BACKUP DATABASE command.

Using the MSDN example:

USE AdventureWorks2012;
GO

BACKUP DATABASE AdventureWorks2012
    TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak'
       WITH FORMAT,
            MEDIANAME = 'Z_SQLServerBackups',
            NAME = 'Full Backup of AdventureWorks2012';
GO

I understand this deletes any existing backups and creates a new one.

If I was to omit the WITH FORMAT line, and I already had a backup at Z:\SQLServerBackups\AdventureWorks2012.Bak, what does this mean to my backup?

Is it incremental or does it overwrite with a full one?

SqlACID · Accepted Answer · 2014-05-12 02:10:29Z

8

It's a full backup, essentially appended to the file if it exists already, you'll see a File number 2 reference when you run it, and you'll see the backup file double in size. Each backup is independent of each other and either can be restored.

answered May 12, 2014 at 2:10

SqlACID

4,02423 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Paperwaste Over a year ago

Thanks for response. If i was to RESTORE FROM DISK where the backup has been appended which backup is restored?

SqlACID Over a year ago

The most recent, unless you specify a sequence. Personally I think the appended files are a pain and prefer unique, timestamped file each day.

Collectives™ on Stack Overflow

Backup SQL Server database using WITH FORMAT

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related