Converting SQL Storage Compress databases to native format

You can convert compressed or encrypted database files to native SQL Server format using HyperBac WinExtractor (a Windows program) or HyperUtil.exe (a command line program). You do not need a SQL Storage Compress license to use these utilities. This is useful if you have been evaluating SQL Storage Compress and your trial has expired, and you need to access databases that you created during the trial period.

Before you start, make sure you know where the compressed database files are located and that HyperBac WinExtractor or HyperUtil.exe is available on the SQL Server.

• HyperBac WinExtractor is copied to %ProgramFiles%\Red Gate\HyperBac\gui on 32-bit machines and %ProgramFiles(x86)%\Red Gate\HyperBac\gui on 64-bit machines by default.
• HyperUtil.exe is copied to %ProgramFiles%\Red Gate\HyperBac\bin on 32-bit machines and %ProgramFiles(x86)%\Red Gate\HyperBac\bin on 64-bit machines by default.

Using HyperBac WinExtractor

1. Take the compressed database offline. Either:
   • Right-click each database in SQL Server Management Studio (SSMS), and select Tasks > Take Offline. You may need to run this task twice before SSMS reports the database as being offline.

   or,

   • Ensure you are connected to the master database, then run the following T-SQL statement for each database:

     ALTER DATABASE <database name> SET OFFLINE

     You may need to run this statement twice before SQL Server reports the database as being offline.

2. Open HyperBac WinExtractor.
3. Click the browse button next to Source File (HyperBac Archive) and choose the database file you want to convert. Compressed database files use the extensions .mdfx, .ndfx and .ldfx. Encrypted and compressed database files use the extensions .mdfe, .ndfe and .ldfe.
4. Click the browse button next to Destination File and specify a name and location for the converted database file.
5. If the database file is encrypted (i.e. if it uses the extensions .mdfe, .ndfe or .ldfe), click the browse button next to Encryption Key File and specify the key file that was used to encrypt the file. See Working with key files for encrypted data for more information.
6. Click Extract to create an uncompressed, native SQL Server version of the compressed database file.

   

7. Repeat steps 3-6 for each of the compressed database files.
8. You can use the converted database files to create a standard SQL Server database. For example:

   CREATE DATABASE [AdventureWorks_Uncompressed]

   ON PRIMARY
( NAME = N'AdventureWorks_Uncompressed', FILENAME = N'D:\Backups\AdventureWorks_Data_Uncompressed.mdf' )
LOG ON
( NAME = N'AdventureWorks_Log_Uncompressed', FILENAME = N'D:\Backups\AdventureWorks_Log_Uncompressed.ldf' )
FOR ATTACH

The original compressed database files are not automatically deleted. Once you have verified that the new database and converted files are working as expected, you can drop the compressed database and delete the compressed database files, if required.

Using HyperUtil.exe

1. Take the compressed database offline. Either:
   • Right-click each database in SQL Server Management Studio (SSMS), and select Tasks > Take Offline. You may need to run this task twice before SSMS reports the database as being offline.

   or,

   • Ensure you are connected to the master database, then run the following T-SQL statement for each database:

     ALTER DATABASE <database name> SET OFFLINE

     You may need to run this statement twice before SQL Server reports the database as being offline.

2. Open a Command Prompt window in the HyperBac bin folder (hold SHIFT, right-click and select Open command window here). Alternatively, open a Command Prompt window anywhere and change directory to the HyperBac bin folder (for example, cd C:\Program Files\Red Gate\HyperBac\bin).
3. Type the following command and press ENTER:

   HyperUtil /S"<compressed_database_file>" /O"<destination_file>" /E

   where <compressed_database_file> is the full path of the database file you want to convert, and <destination_file> is the full path of the converted database file you will create. For example:

   HyperUtil /S"C:\Program Files\MSSQL\DATA\AdventureWorks_SSC_AdventureWorks_Data.mdfx" /O"C:\Program Files\MSSQL\DATA\AdventureWorks_Data.mdf" /E

   If you have encrypted the database file, you will also need to include the /K argument and specify the full path for the key file that was used to encrypt it. For example /K"C:\Program Files\Red Gate\HyperBac\keys\AES_256.key". See Working with key files for encrypted data for more information.

   HyperUtil.exe prints metadata about the compressed file to the Console Window then creates an uncompressed, native SQL Server version of the compressed database file in the specified location.

4. Repeat step 3 for each of the compressed files.
5. You can use the converted database files to create a standard SQL Server database. For example:

   CREATE DATABASE [AdventureWorks_Uncompressed]

   ON PRIMARY
( NAME = N'AdventureWorks_Uncompressed', FILENAME = N'D:\Backups\AdventureWorks_Data_Uncompressed.mdf' )
LOG ON
( NAME = N'AdventureWorks_Log_Uncompressed', FILENAME = N'D:\Backups\AdventureWorks_Log_Uncompressed.ldf' )
FOR ATTACH

The original compressed database files are not automatically deleted. Once you have verified that the new database and converted files are working as expected, you can drop the compressed database and delete the compressed database files, if required.