If you ask me, can you able to restore sql server 2008 backup to sql server 2005 environment ? my answer is No. It is not possible to restore backup of higher to lower versions of sql server due to backward compatibility issue. if you will try to restore, it will throw error like “restore headeronly is terminating abnormally error: 3241”. Backup created on sql server 2008 can only be restored on 2008 instance and higher version.
On a second thought, if you try to change the compatibility level(100) of sql server 2008 database to 90 then take a backup and restore it on sql server 2005 instance. I tried but no luck so answer is no here.
compatibility level will tell you, what syntax rules will be applied on the database, it does not change the internals of database the way data stored.
if compatibility level is set to 100 that means it will allow all rules of sql server 2008 on database that’s why it is easy to switch compatibility level instantly. If you want to change it to older version (like 70) then you wont able to access advance features of sql server 2005 and 2008.
One way you can copy the database from sql server 2008 to sql server 2005 by scripting data using below steps:
1. Connect to sql server using Management studio
2. Choose the database, right click on it and click on “Tasks ->Generate Scripts”.
3. It will show welcome screen to generate sql server script. Click on “Next” to proceed.
4. Here select the database you want to script from below list. Click on the “Script all objects in the selected database” option, and then click “Next.”
5. Change the following script options:
7. If you are trying to restore database on new instance for the first time then make sure the “Script Database Create” option is set to “True.”, Click on “Next”.
8. Here you can save script either in file or in clipboard and new query window.
Select “Script to file” using browse option and give the file name and choose “Single file” and Click “Next” for a summary.
9. Click on “Finish” to complete the task.
If the generation process fails, then you can use the “Report” option to see why.
When the scripting is completed, look for the following lines:
CREATE DATABASE [Restoredb] ON PRIMARY
(NAME = N’Restoredb’, FILENAME =
N’C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Restoredb.mdf’ ,
SIZE = 4000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
(NAME = N’Restoredb_log’, FILENAME =
N’C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Restoredb.ldf’ ,
SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
Change the path of the database according your environment on which sql server 2005 are running also will need to comment out the following lines like this:
–EXEC sys.sp_db_vardecimal_storage_format N’Restoredb’, N’ON’
Once changes are done in the script, you can run the script in SQL Server 2005 Management Studio to recreate the database in your development environment.
Every techniques has some pros and cons.
1. Using this data is insure as its visible, make sure that file should deleted after loading in the sql server 2005.
2. Database having large amount of data will take time to generate file, it will be huge file.