Attaching of databases from MS SQL 2000 & MS2005 to MS SQL 2008 R2

Attaching of databases from MS SQL 2000 & MS2005 to MS SQL 2008 R2

As architecture of 32-bit & 64-bit versions of SQL server is different we cannot directly attach the databases by copying master & msdb databases, so in order to avoid the manual work of attaching the databases from previous versions of MS SQL to MS SQL 2k8 R2.
Following steps need to be followed :-
1. Need to copy the databases to the respective SQL 2k8 server.

2. A vb script needs to be run at command prompt where the following parameters have to be modified :-
ServerName=”server\mssqlserver”
MappedMDFLocation=”h:\dbdata”
ServerMDFLocation=”h:\dbdata”

3. In the MappedMDFLocation as well as in ServerMDFLocation- location of databases has to be specified.

4. mSQLServer.Connect “(local)”,”sa”,”sa” .Here in place of ‘local’ the ServerName entry has to be provided followed by credentials for sa.

5. SQL DMO (SQL Distributed Management Objects) is required for the script to run successfully & it is present by default in SQl 2K5 & not in 2K8. Here we have to install it manually.

6. To use SQL-DMO on SQL Server 2008, you need to download and install Microsoft SQL Server 2005 Backward Compatibility Components to solve the problem. You can go to Microsoft Download Center and search for “Microsoft SQL Server 2008 Feature Pack” and sort by Release Date to find the latest version.

7. There under Microsoft SQL Server 2005 Backward Compatibility Components download the following file :-

X64 Package (SQLServer2005_BC_x64.msi)

8. Install the following Package and run the vbscript at command prompt.

9. Execute the script with changes made as above & databases will be attached.

download script

Thanks

Prashant Deshpande

Advertisements

1 Comment

Filed under Uncategorized

One response to “Attaching of databases from MS SQL 2000 & MS2005 to MS SQL 2008 R2

  1. Yahoo Answers are loved by Google and many of the Yahoo Answers are sitting on high ranking especially for some highly competitive keywords. You can’t even imagine what effect can Yahoo Answers provide to the traffic of your site. Don’t wait and learn more about this at http://yanswertraffic.com/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s