Creating compressed databases:which method should I use? - SQL Storage Compress

You can create a compressed version of an existing database in two ways:

1. Use the Restore backup to compressed database wizard from the SQL Storage Compress user interface
2. Write a T-SQL script using your preferred SQL Server management tool (for example, SQL Server Management Studio)

If you don't already have a backup of the database you want to compress, you will need to create one before proceeding with either method.

For more information about how SQL Storage Compress works, see the Technical Overview.

1. Creating a compressed database using the wizard

You should use the wizard if:

• you are new to SQL Storage Compress and want to familiarise yourself with the process (see the Guide for evaluators)
• you want to generate T-SQL scripts which you can then modify and run manually
• your requirements for the compressed database are straightforward (for example, you want to compress all the data files rather than only certain files)
• you want to restore from a single, full backup file
• the backup file you are restoring from is in one of the following formats: native SQL Server, SQL Backup Pro, SQL HyperBac, or Quest LiteSpeed™ (unencrypted)

See the worked example for further details.

2. Creating a compressed database using T-SQL scripts

You should use a T-SQL script if:

• you are familiar with writing T-SQL RESTORE DATABASE commands and want full control over the command options
• your requirements for the compressed database are complex (for example, you only want to compress selected data files)
• you want to compress the transaction log file (.ldf) without encrypting it
• you want to restore from a backup split into multiple files or a combination of backups (for example, full, differential and log)
• the backup file you are restoring from is in Quest Litespeed™ encrypted format, or another third-party backup-tool format
• you need to create compressed databases as part of a batch or scheduled job

See the worked examples for further details.