Question:  Can I back up a SQL 2008 database and restore it to SQL 2005?  For example:

• My database at 3Essentials is hosted on SQL 2008, and I want to restore a copy of it to my system for test/development purposes, but I'm running MSSQL 2005.

Answer:  You can't create a MSSQL FULL BACKUP in BAK format on a MSSQL 2008 database server and restore it to a database on a MSSQL 2005 server, however there is an alternate approach.  You can export the data from the MSSQL 2008 database using the "generate scripts" option, which creates an SQL script that creates the databases objects (tables, etc) and populates them - this script can be generated to be compatible with an earlier version.   Then that SQL could be run against the MSSQL 2005 database to create those objects/data in that database.  The following details the steps in that process:

• Use the scripting wizard in SQL Server 2008 to script data as well as schemas into SQL Server 2005 compatibility mode.
  
   
• Run "Generate SQL Server Scripts" wizard in SQL Server Management Studio (once Object Explorer is connected to the appropriate instance) by right clicking on database and selecting "Tasks –> Generate Scripts."
   
• Click "Script all objects in selected database" & then click "Next."
   
• Change ADVANCED script options: Specifically, set "Script for Server Version" to "SQL Server 2005" and set "Script Data" to "True". (SQL Server 2000 is also supported.)
  • if the database you're going to restore this to already exists on the SQL 2005 server, then do NOT check the "Script Database Create" option.
     
• Click "Next" when you have the options set appropriately for your requirements.
   
• Finish the wizard.
   
• When the script has completed, you will need to open it and look for a CREATE DATABASE statement near the top of the script. You will need to alter the paths to valid paths on your system. This must be done prior to attempting to use the script to restore the database to your system.

CREATE DATABASE [DatabaseName] ON  PRIMARY
( NAME = N'DatabaseName', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DatabaseName.mdf' , SIZE = 84160KB , MAXSIZE = 512000KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'DatabaseName_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DatabaseName_log.ldf' , SIZE = 10176KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

Below those lines you should also find an sp_dbcmptlevel statement such as the one below. You should comment that statement.

EXEC dbo.sp_dbcmptlevel @dbname=N'DatabaseName', @new_cmptlevel=90
GO

Comment the lines as below.

--EXEC dbo.sp_dbcmptlevel @dbname=N'DatabaseName', @new_cmptlevel=90
--GO

If you need this process performed on your SQL 2008 database hosted with 3Essentials, you may do so using our Secure DB method to connect to your database with SQL Management Studio. However, for larger databases, you may want to submit a support request so we can generate the SQL script from your SQL 2008 database and make it available for you to download through FTP. If you have not requested Secure DB access in the past, you may do so. See the article Secure DB Admin.