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
go
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