x

How to Copy a SQL 2000 Database into SQL 2008 Instance?

Hi,

Whats the best method to copy or restore a Sql Server 2000 database into a Sql Server 2008 instance? Are there likely to be any compatability issues?

TIA

more ▼

asked Jul 25, 2013 at 11:29 AM in Default

avatar image

David 2 1
1.4k 59 64 73

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

I suggest using the Upgrade Advisor from Microsoft.

http://msdn.microsoft.com/en-us/library/ms144256(v=sql.100).aspx

As for moving the database take a look at this.

http://msdn.microsoft.com/en-us/library/ms190775(v=sql.100).aspx

I suggest you do all this in a test environment first to work out any issues.

more ▼

answered Jul 25, 2013 at 12:22 PM

avatar image

sqlaj 1
5.5k 4 6

Thanks. So far I've managed to use your method to copy a small database from 2000 to 2008 however the larger one fails as it appears that the copy database is trying to copy some views before the underlying tables, hence it fails as the tables don't exist.

Jul 25, 2013 at 01:32 PM David 2 1

If you are having issues with copying, then do a restore as Tim suggested.

Jul 25, 2013 at 01:42 PM sqlaj 1

Many thanks, especially for the links.

Jul 25, 2013 at 01:59 PM David 2 1
(comments are locked)
10|1200 characters needed characters left

I have always simply taken a full backup on SQL 2000, then restored that backup to SQL 2008 or 2008R2. No issues as of yet. You have to manually change the compatibility mode. I have come across a few deprecated commands in sprocs and views that had to be updated. Upgrade Advisor detected those for me.

Best advise I can give you is test then test again.

more ▼

answered Jul 25, 2013 at 01:04 PM

avatar image

Tim
40.9k 39 95 168

Thanks also. I'm looking into this method to copy over the larger database. I'm relatively new to compatibility mode. Whats the best way to set it for the freshly copied 2000 database's?

Jul 25, 2013 at 01:34 PM David 2 1

Right click on the restored database, I believe it is under options, you will see compatibility. Change it from 80 to 100.

Jul 25, 2013 at 01:35 PM Tim

Many thanks.

Jul 25, 2013 at 01:58 PM David 2 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2221
x512
x134
x92

asked: Jul 25, 2013 at 11:29 AM

Seen: 1488 times

Last Updated: Jul 25, 2013 at 01:59 PM

Copyright 2018 Redgate Software. Privacy Policy