SQL Server 2012: Backup restore from a compressed backup

Question

I am trying to restore a database from the backup (.bak) file which is [saved] inside a ZIP file, but not been successful so far. However, I am able to restore it after extracting from the ZIP file.

This MS page says every edition of SQL Server 2008 and later can restore a compressed backup with the following restrictions.

Restrictions: The following restrictions apply to compressed backups:

1. Compressed and uncompressed backups cannot co-exist in a media set.
2. Previous versions of SQL Server cannot read compressed backups.
3. NTbackups cannot share a tape with compressed SQL Server backups.

I do not clearly understand the first restriction. Could someone please clarify/elaborate this?

I have done the following steps:

1. Taken a backup on a staging SQL Server [MyTestDB.bak]; Compressed it (by Right Click > Send To - Compressed(zipped) folder); Now, named it as MyTestDB.ZIP

2. FTP'd the ZIP file to local development SQL Server and trying to restore it from the ZIP file. But the database name is not available to backup.

Both SQL Server versions are exactly same.

SQL Servers Version: Microsoft SQL Server 2012 - 11.0.5058.0 (X64)-Standard Edition (64-bit)

Would the usage of term 'compressed backup' for the backup files saved inside ZIP files is correct or Is this need to be backed up in a different way so it can be called as a compressed backup?

3. However, if I select the .bak file after extracting it from the ZIP file it all works fine.

I am not sure where I am going wrong? I can simply extract the backup and restore it without any problem, but would like to know the cause why it is not working, as it would have been a much better solution to just back up it from the ZIP itself.

Answer 1

Backup compression is something different.

You specify this when you configure or start the backup and you then get a backup file that contains compressed data, as opposed to it containing uncompressed data if you don't enable compression.

SQL Server is not able to use a zip file, while the backup file is certainly compressed, it is not a "compressed backup" that this refers to.

So yes, you need to extract the backup file before restoring from it.

If you want to learn how to make compressed backups correctly, check out this page full of links to related material:

• Configure Backup Compression