SQL Storage Compress - 6.0

Reverting SQL Storage Compress databases back to native format

If you have been evaluating SQL Storage Compress, and the trial period expires, you will no longer be able to access any compressed databases that you created during the trial period. SQL Server will return an error when you try to access a compressed database (the exact error you see may vary from these examples):

Msg 823, Level 24, State 2, Line 1
The operating system returned error 6 (The handle is invalid.) to SQL Server during a read [...]

or,

Msg 945, Level 14, State 2, Line 1
Database 'AdventureWorks_Compressed' cannot be opened due to inaccessible files or insufficient memory or disk space [...]

Databases you created using SQL Storage Compress cannot be accessed in their compressed form unless you purchase a SQL Storage Compress license.

However, you can convert compressed database files back to native SQL Server format database files. You can do this using the "HyperBac WinExtractor" or "HyperUtil.exe" utilities that are installed with SQL Storage Compress.

You do not need a SQL Storage Compress license to use these utilities.

Converting a compressed database to a native SQL Server database

Before you start, make sure you know where the compressed database files are located, and that "HyperBac WinExtractor" or the "HyperUtil.exe" utility is available on the SQL Server. By default, these utilities are copied to C:\Program Files\Red Gate\HyperBac\gui\WinExtractor.exe and C:\Program Files\Red Gate\HyperBac\bin\HyperUtil.exe when you install SQL Storage Compress.

To convert a compressed database to a native SQL Server database:

Take the compressed database offline.
Either:
- Right-click the 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.
  Press F5 to refresh the database status, and confirm that it is offline.
  
  or,
- Ensure you are connected to the master database, then run the following T-SQL statement:
  ALTER DATABASE <database name> SET OFFLINE
Convert each compressed database file to native SQL Server format. You can do this with "HyperBac WinExtractor" (a Windows GUI utility), or using "HyperUtil.exe", a command-line utility.

To convert a compressed database file using HyperBac WinExtractor:

a.  Start HyperBac WinExtractor from the Windows Start menu (All Programs > Red Gate > HyperBac > HyperBac WinExtractor)

b.  Click  for Source File (HyperBac Archive), and choose the database file you want to convert.

     By default, compressed files used by SQL Storage Compress have extension *.mdfx, *.ndfx, or *.ldfx.

c.  Click  for Destination File, and specify a name for the converted database file.

d.  Click Extract to create a native SQL Server version of the compressed database file.

e.  Repeat steps b to d for every compressed database file used by the database.

To convert a compressed database file using HyperUtil.exe:

a.  Open a Command Prompt window by typing cmd in the Run dialog (Start > Run)

b.  Change directory to the HyperBac 'bin' folder (for example, 'cd C:\Program Files\Red Gate\HyperBac\bin')

c.  Type the following command, and press RETURN:

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

   where <compressed_database_file> is the location of the database file you want to convert,

     and <destination_file> is the location for the converted database file.

d.  Repeat step c for every compressed database file used by the database.
You can now use the converted database files to create a standard SQL Server database. For example:
CREATE DATABASE [AdventureWorks_Orig] ON PRIMARY
( NAME = N'AdventureWorks_Orig', FILENAME = N'D:\Backups\AdventureWorks_Data_Orig.mdf' )
LOG ON
( NAME = N'AdventureWorks_Orig_Log', FILENAME = N'D:\Backups\AdventureWorks_Log_Orig.ldf' )
FOR ATTACH