DBA - How To Migrate Database From One SQL Server Instance To Another SQL Server Instance

Suppose your company has one SQL Server that has hundreds of databases which are accessed by different application. Over the time the numbers of Databases are increased on this server and hurting performance. Your company has made decision to purchase a new SQL Server. They want you to move (migrate) some of the databases from current SQL Server to new build SQL Server.

Let’s follow step by step approach. In our Example we will be migrating TEST_PUB Database from MSSQL2008 Instance to MSSQL Instance as shown below
Fig 1: Migrate SQL Server Database

Step 1:
Detach database from current SQL Server Instance.  Before Detaching database run below query to get location for mdf and log file.

USE TEST_PUB --> Change with your Database Name

select * From sys.database_files

Now we know the location of mdf and ldf files of database. Let's detach the database. Right Click on SQL Database--> Tasks-->Detach

Fig 2: Detach SQL Server Database

Fig 3: Kill Connections and Detach SQL Database

Step 2: 
Copy mdf and ldf files to the folder/s which you are using for new SQL Server instance. For this post, I have mdf and ldf files for MSSQL2008 Instance in below location

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008\MSSQL\DATA

My folder location for MSSQL SQL instance is below. This is the instance where we are going to attach our database.

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL\MSSQL\DATA

Please note that I am using the same folder for mdf and ldf files but that is not best practice. You will be using different Drives for mdf and ldf.
Fig 4: Copy mdf and ldf files to required folders

Step 3:
Once the mdf and ldf files are copied to the required folder. Let's go to SQL Server Instance where we want to attach the database.

Right Click on the Databases Tab and then Click Attach Tab as shown below
Fig 5: Attach SQL Server Database

Fig 6: Attach SQL Server Database Attach Wizard

Navigate to the location where you have copied mdf file for your Database and Choose mdf file as shown below.
Fig 7: Attach SQL Server DB by using Attach Wizard

Fig 8: Attach SQL Server Database step by Step by using Attach Wizard

Once you hit Ok, it is going to take few seconds to attach database to SQL Server Instance. TEST_PUB is successfully attached to MSSQL SQL Server Instance.

Fig 9: Database Attached Successfully