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 :-
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.