Restore database 2008 to 2005

Question

Post reply

Restore database 2008 to 2005

honey47

SSC-Addicted

Points: 459
More actions
July 9, 2010 at 3:41 am

#387312

Hi ,
i m currently running database server 2008 , but due to some reason i want to switch to sql 2005. but i am enable to restore my date base to 2005
is there any way to restore it
regards

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 1

Backward compatability is not there for SQL Server..

I dont know whether its possible using any third party applications..

Better u script out the things and run it in 2005

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 2

SQL Server 2008 databases are not backwardly compatible with earlier versions. Assuming you haven't used any 2008-only features, you should script out the entire database structure, and use the Import/Export wizards to copy the data back into a database on 2005.

You can also use Management Studio 2008 to script out all data, but this may be a lot to put into a text file - the Import/Export wizard is much easier with larger amounts of data.

NickDBA Hall of Fame Points: 3792 More actions · Answer 3

There is no way to restore a SQL Server 2008 backup ".bak" file to SQL Server 2005. Scripting objects and importing data is your best option

honey47 SSC-Addicted Points: 459 More actions · Answer 4

Thanks a lot for the replies..

The problem is solved . I just run the script of database from 2008 and run it on 2005 by using the magical Sqlcommand on cmd as

sqlcmd -s YOURDATABASESERVERNAME -i "THE PATH OF THE SCRIPT"

and i m on my way 🙂

Sankar Reddy SSCrazy Points: 2410 More actions · Answer 5

honey47 (7/9/2010)
Hi ,
i m currently running database server 2008 , but due to some reason i want to switch to sql 2005. but i am enable to restore my date base to 2005
is there any way to restore it
regards

[font="Courier New"]I know you already got your answer and issue is resolved but I will add some details if someone else is looking at this thread.

SQL Server has internal database version and this is different from the SQL Server version (SQL Server 6.5, SQL Server 7.0, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2)

Relationship between the internal database version and the SQL Server version

515, 'SQL Server 7.0'

539, 'SQL Server 2000'

611, 'SQL Server 2005, VARDECIMAL disabled'

612, 'SQL Server 2005, VARDECIMAL enabled'

655, 'SQL Server 2008'

660, 'SQL Server 2008 R2'

In every version, SQL Server maintains lot of meta-data and the schema of these meta-data structures change a lot for each version. When you restore a lower version onto a higher version server then SQL Server automatically upgrades the internal database version and in this case you don’t have to do anything specific. These meta-data structures aren’t backward compatible and that means you can’t restore a db that is created in a higher version onto a lower version as the meta-data structures in higher version aren't recognized in the lower versions.

This internal database version is stamped on the boot page of the database and can be retrieved using DBCC DBINFO WITH TABLERESULTS

I wrote a script to pull this information for all databases on a server, just in cas you want to look at it.

http://sankarreddy.com/2010/05/database-internal-version-create-version-and-current-version/

[/url]

HTH and adds some internal information.[/font]

[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]