Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shiyam198
Yak Posting Veteran
94 Posts |
 Posted - 2011-02-16 : 22:07:03
|
| Hi,Because of some licensing issues our company is dealing with currently, I am in a situation where I need to take a backup from a SQL server 2008 server and restore it to SQL server 2005.After trying a lot of things, I found out that the only way is to use "generate script" wizard.So, I am using this wizard and selecting the following:1. Script Database create - True2. Script for Server version - 20053. Script Data - Trueand it looks like the database is ok.But, only concern is, somewhere (I can't remember where), Using generate script wzard to copy over database to a different server is recommended only on Development/Test environment.Is that true? Mine is just 150 MB database. But I would like to know expert's opinion on this.Thanks a lot for your time.Regards,Shiyam |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-02-17 : 04:58:35
|
| Do not script the data unless you want a 150MB+ script. The schema is fine, the data will give you a script so huge it may not even open in SSMS.bcp out, bcp in, or use SSIS.--Gail ShawSQL Server MVP |
 |
|
|
shiyam198
Yak Posting Veteran
94 Posts |
Posted - 2011-02-17 : 07:20:07
|
| Thanks Gail.Sorry I made a mistake in my initial post. My database was 90 MB in total. So the script it spits out is around 95 MB. I was able to run the script and created the database on SQL server 2005 and got the front end running with no issues. Still, I am not sure if this approach is NOT recommended for Production. If the concern is with the volume of data, I think I am fine, I have minimal data in the database. If it is missing some DB objects, or relationships then I am in danger. Please let me know your thoughts. |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-17 : 10:26:48
|
as suggested by Gail.... just create the DB schema in SQL server 2005 instance .. once the schema is ready use BCP commands to import the data from 2008 to 2005 or you can use insert into table name ...Select ..... kind of statements for data import. since SQL server versions are not backward compatible. So I am not sure as if there could be any other (better) approach when it comes to backward migration in SQL server  . Cheers!MIK |
|
|
|
shiyam198
Yak Posting Veteran
94 Posts |
Posted - 2011-02-17 : 11:14:02
|
| Thanks a lot guys. If the schema is fine I think I am ok.Gail's concern was if it had too much data you can not copy it over to the SSMS (limitation on Virtual memory).I made sure it copied the entire script before running and made sure the entire script ran. Users are using the system now and I would have heard by now, if the data is missing. I was worried about the Database objects and schema. As long as Generate scipts wizard would not modify those (when I create the script from SQL 2008 and run on SQL server 2005), then I am good.Thanks a lot for your time again.- Shiyam |
|
|
|
|
|
|
|
|
Restoring a SQL 2008 backup to SQL 2005