Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Migrate a SQL 2000 database to a different server Expand / Collapse
Author
Message
Posted Wednesday, November 13, 2013 10:35 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, November 24, 2013 12:37 PM
Points: 85, Visits: 159
I need to migrate an old SQL 2000 database, setting on a windows 2000 server, to a SQL 2008 setting on a 2008 server OS (different server).

I have a backup of the database with me (which I got by using a db full backup. The database size is 50 GB.

What's the best way to do this?


Update:
Never mind. I was able to do it . It's straight forward restore with replace.

Thanks,
Post #1513986
Posted Thursday, November 14, 2013 1:54 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:45 AM
Points: 1,284, Visits: 2,827
Since you are going from SQL2000 to 2008 you need to do a dbcc update usage.... also, at bare minimum run Update stats as SQL2008 handles indexes differently. Really you should rebuild all indexes when you move a db from an older version of SQL server to a newer one.


Post #1514471
Posted Sunday, November 17, 2013 10:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 5, 2014 6:19 AM
Points: 147, Visits: 591
if you restore SQL 2005 on SQL 2008 you will not be able to make use of some of DMV's unless you change compatibility level to 100 and also you might get errors for few sql syntax like *= or =* (left joins & right joins)





Praveen D'sa
MCITP - Database Administrator 2008
http://sqlerrors.wordpress.com
Post #1515066
Posted Sunday, November 17, 2013 11:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 39,952, Visits: 36,308
Restore then, run a checkDB with data_purity, update all stats with fullscan, run DBCC updateusage. Then consider whether you're going to change the compatibility level


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1515091
Posted Sunday, November 24, 2013 12:42 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, November 24, 2013 12:37 PM
Points: 85, Visits: 159
What does changing the compatibility level do?

Thanks
Post #1517107
Posted Sunday, November 24, 2013 1:41 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 39,952, Visits: 36,308
http://technet.microsoft.com/en-us/library/bb510680%28v=sql.105%29.aspx


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1517118
Posted Wednesday, November 27, 2013 5:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:53 AM
Points: 209, Visits: 573
You can access data of old version in new version. That is you can access data of SQL 2000 in SQL 2008
Post #1518029
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse