I'm hoping to restore a backup from SQL Server 2008 into a Sql Server 2005 instance. Is there a way to have Sql 2008 back up in a format that 2005 will understand?
4 Answers
Not as a backup (natively).
I know of three options to get an MSSQL2008 database into MSSQL2005, though.
1) Third-party software. The DBAs here love Redgate.
2) Scripts. An example of which can be found here, and is [mostly] included below.
- Run the
Generate SQL Server Scripts
wizard in SQL Server Management Studio by right clicking on the database and selecting "Tasks –> Generate Scripts." - Click
Next
to get past the initial dialog. - Click
Script all objects in the selected database,
and then click "Next." - Script for Server Version as "SQL Server 2005" and set "Script Data" to "True".
- If you are putting the database on a new instance for the first time, make sure the
Script Database Create
option is set toTrue.
ClickNext
. - Select
Script to file
, input a filename and selectChoose Single file
. - When the script is completed, locate the the following lines:
Blockquote
CREATE DATABASE [databasename] ON PRIMARY
(NAME = N'[databasename]', FILENAME = N'[path to file][filename]' , SIZE = [size]KB , MAXSIZE = [size], FILEGROWTH = [filegrowthsize]KB )
LOG ON
(NAME = N'[databasename]_log', FILENAME = N'[path to file][filename].ldf' , SIZE = [size]KB , MAXSIZE = [size]GB , FILEGROWTH = [x]%)
GO
You will need to amend the paths to a valid path for the computer on which you are moving the database to. You also will need to comment out the following lines like this:
--EXEC sys.sp_db_vardecimal_storage_format N'Northwind', N'ON'
--GO
Once you have made these changes, move the database file over to the other server, and you can run the script in SQL Server 2005 Management Studio to recreate the database there.
3) SQL Publishing Wizard. (Read the description, it may not be applicable to your enviornment.)
![](../../users/profiles/118258.webp)
- 53,385
- 32
- 133
- 208
Short answer:
No
You cannot restore to a lower major version of SQL Server.
You can however Generate Scripts from your database for SQL Server 2005, just choose to include data in the wizard, and then you'll just need to rebuild indices and such, once you've run the scripts on your 2005 instance
![](../../users/profiles/105072.webp)
- 24,907
- 4
- 62
- 95
No, the formats are incompatible. In order to go backward you will need to script out your database and create it via script in the older version.
Note that you could run into incompatabilities due to new features and datatypes in SQL 2008 that weren't available in 2005.
![](../../users/profiles/1054.webp)
- 37,618
- 10
- 90
- 145
You can use a tool like Talend to migrate your tables. It has a GUI interface and can work with SQL Server 2008/2005/2000 and is OSS/Free
![](../../users/profiles/129536.webp)
- 11
- 1
-
Dynamic schema capabilities in the enterprise version of Talend make this even easier to do. Ps: I work for Talend. – Christopher Klaus May 13 '18 at 20:38