- C# Scripts
- DWH INTERVIEW QUESTIONS
- MS Dynamics AX 2012 R2 Video Tutorial
- Project / Work Support
- SQL SERVER DBA INTERVIEW QUESTIONS
- SQL SERVER DBA Video Tutorial
- SQL Server / TSQL Tutorial
- SQL Server 2016
- SQL Server Scripts
- SSIS INTERVIEW QUESTIONS
- SSIS Video Tutorial
- SSRS INTERVIEW QUESTIONS
- SSRS Video Tutorial
- TSQL INTERVIEW QUESTIONS
- Team Foundation Server 2013 Video Tutorial
- Team Foundation Server 2015 Video Tutorial
- Windows 10
- Windows Server 2012 R2 Installation Videos
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
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
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
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