SQL Server Database Migration

Print Friendly, PDF & Email
SQL DataBase Migration

SQL DataBase Migration

Hi, in this post I will try to concentrate on migration process of SQL Server. Migration means moving one or set of database from one server to another server.
Scenarios for migration;
Situation 1: As a part of launch, Deployment, Go live.

  • Migration database from development to test & test to production. This we called as launch.
  • Whenever new built released to production as a part of enhancement we called as deployment.
  • When user can access the application on online we called as Go live.

In all these 3 type we are migrating database from a server to other.
Situation 2: Database refreshments
Moving production server database copy to develop environment for building next module. They test with production data. Using production they design new stored procedure as part of next requirement. We move production to test also, here tester will perform ad-hoc (An ad-hoc (or spontaneous) network is a local area network or other small network, especially one with wireless or temporary plug-in connections) testing on the data for tuning query.
Regularly we providing the production database backup for building next enhancement & testing of some store procedure & queries.
Situation 3: Moving from old hardware to new hardware.
Whenever we are buying system it come with a guarantee & warranty. Once it reaches the end of licence we will check with vendor who sells system. If vendor guarantee further we can continue otherwise we have to move all database from old server to new server.
Situation 4: Migration between data centers.
Data centers is a place where we are physically keeping server at particular location. We can have multiple data centers. We are maintaining production data enters & Data Reader data centers. As a part of high availability configuration we are moving all server databases from one data centre to another data center.

  • As a part of making Data Read server, we move all databases from one data center to new data center.
  • Whenever we are shutting down old data center we move data to new center.

Situation 5: Consolidation
Instead of storing Non critical databases on more servers, we move them to perform may effect.
Steps in Migration
Transferring logins: Before moving the databases, script out the logins on source server & keep them aside. There is a store procedure sp_help_revlogin, we have execute this store procedure in the server. First it creates the store procedure under master database. This will not come as part of SQL installation. Once the store procedure created we have to run the stored procedure second time it will script out all logins of source server including password in encrypted format. We can copy all login or few logins according to our requirement. Copy all the logins to destination server & execute them on server. Same login & password will be created. Second way, right click on login & script as copy them on other server. This process will not copy password.
Transfer job: Right click on job > script jobs as > create to new windows, copy the script & run on other server. Whenever we want to transfer all jobs we need to create a package & transfer that package.
Transfer the packages: We have to connect to integration service > goto MSDB database > under store package we will have SSIS package > right click on package > on the package wizard select export package provide destination server location. Now connect to destination server > go to integration services > go to store packages > under MSDB right click on packages & select import package. Package will be created on server.
Transfer the database:
(1) Backup & restore, Take backup on source server & copy them to destination server. Right click on database in destination server > select restore & provide the backup details & click ok. Backup & restore is online. Source will not be distributed here. Roll back is easy.
(2) Attach & detach, Right click on database of source server > go to task > select detach database, database will go offline copy files from source server & past them. Go to destination server > select attach – provide path details.
(3) Checking new database properties, Verify the database size from database properties from options verify – recovery model, DB owner, compatibility model & collation setting. Change the db owner using sp_change db owner “sa”.
(4) Finding & fix orphaned users, whenever taking full backup & copy & restore on destination server. All user account will copy to destination server. User account without corresponding login account is called orphaned user. As the login will present in active directory or syslogin of master db. Sometime synchronization will miss ever after transfer login also.

To find orphaned users

To fix

ref: https://msdn.microsoft.com/en-in/library/ms174378.aspx
Update one will establish synchronization between username & login name.
Moving system database
Start sql server in single user mode. Go to start up parameters & type – m.

ref: http://blog.sqlauthority.com
Once sql start in single user mode, restore database Master from disk = ‘path’ with replace. Whenever we restore Master database we can do it only in single user mode. Restoring model & msdb after removing (-m). From configuration manager > right click on services > goto advance > click on startup parameters, goto end type –m & apply then restart services.
Migrate tables
Right click on database > goto task > export data. It will open import & export wizard. Specify data source & server name, database.

  • Select copy data
  • Select tables which we want to migrate.

We can do modifications to size, datatype here.
Migration store procedures
Right click on database > goto tasks > generate script > select a database & select objects, stored procedures > select all > script to new windows (script to file). Copy the script to other server & execute these script on them.
Copy / Migrate to Excel

  • Connect to SQL management studio > under management folder > goto export option by right clicking > sql import & export wizard will appear.
  • Choose data source > sql server native client 11.0
  • Server name > Provide server name
  • Select Authentication > Windows or SQL Authentication
  • Database > Select database
  • Click next (goto next page)
  • Choose destination > provide Microsoft excel
  • Server name > provide destination server name
  • File path (authentication) > provide excel file path
  • Database > provide destination db
  • Choose destination > select Microsoft excel
  • Excel file path > provide location to store excel & provide name to file (select) First row has column names
  • Specify table copy > select copy data from one or more table or views
  • If we want we can edit mapping
  • Click finish.


Upgrading means migrating sql server old version to sql server latest version. Before Upgradation we need to run Upgradation adviser tool. Microsoft sql server 2012/2014 upgrade adviser.
Under installation menu > In planning > click upgrade advisor.
SQL server 2012/2014 upgrade advisor analyses sql server 2008r2 or other lower versions sql server instances & gives result that may impact upgrade process.
Steps in installation upgrade advisor:

  • Open installation > go to planning > select upgrade adviser > wizard will be open > click next
  • Enter server name & select the components to analyse
  • Select instance name & provide credential
  • Select the database, if you have SSIS packages provide path of the files
  • Select run to begin analysis
  • Once analysis complete, warning will be displayed

There are 2 type of Upgradation:

  • Inplace upgrade
  • Side by side upgrade

Inplace upgrade

Source server will become destination server after upgrading. It is going to install sql server 2012/2014 components on old version. All the lower version support files will be converted to sql server 2012/2014 supportable. This type of Upgradation is Inplace upgrade.


  • Fast, easy & automated process
  • No additional hardware required
  • Instance name will be the same as before


  • Sql server will be in offline state during upgrade hence we have downtime
  • Roll back process is very complex
  • Source server may be effected

Inplace upgrade process:
From installation > select upgrade from sql server 2008r2 & continue remaining installation process.
Side-By-Side upgrade
Side by side upgrade consist of installing sql server 2012/2014 & moving old server database to new server just like migration process.

  • Upgrade process is online with effecting business
  • Minimal downtime required in redirecting the user request from sql 2008r2 to sql server 2012/2014
  • Select database can migrate
  • Full control on Upgradation
  • Roll back will be easy as original server still available


  • Manual process

Side by side process

  • Follow the same process which pre formed in migration like scripting out logins, jobs & take backup & keep.
  • Install sql server 2012/2014
  • Copy the database backup & restore
  • Create logins & jobs by running scripts
  • Find & fix orphaned users
  • Verify database connectivity & functionality

Post Migration

After the migration is complete it is important to perform the following tasks:

  • Upgrade Database Compatibility Level
    • The database compatibility level needs to be manually changed on the 2014 instance
    • Checks for invalid data values based on column data type
    • Corrects page count inaccuracies to reflect accurate information when using sp_spaceused
  • Run sp_refreshview
    • Ensures that views are up to date
  • Update Statistics
    • Brings database statistics are up to date to allow the query optimizer to choose the best query plan
  • Take a Full Backup of the Database
    • Make sure you can recover the database with all of the migration efforts

Post upgrade steps

  • Change compatibility level
  • Check integrity of object
  • Set page verification using CHESKSUM
  • Upgrade statistics

After upgrade change the compatibility level of database.

ref: ALTER DATABASE Compatibility Level (Transact-SQL)

Check integrity

Check integrity of object in a database using DBCC CHECKDB (Transact-SQL)

ref: DBCC CHECKDB (Transact-SQL)
Page verification
Checksum verifies computation of pages & results stored in page header.

ref: ALTER DATABASE SET Options (Transact-SQL)
Updating statistics
The statistics need to update after upgrade to get full features of enhancements in sql server 2012/2014.

ref: 1) https://social.msdn.microsoft.com/Forums & 2) https://msdn.microsoft.com/en-us/library/ms187348.aspx
Monitor Performance between Cardinality Estimators
It’s important to note: SQL Server 2014 received a major overhaul of the cardinality estimator. For the vast majority of queries, performance will improve due to better row estimations. However, in some scenarios estimations could be worse which may result in performance degradation. It’s important to monitor most commonly used queries to see if any performance degradation is resulting from poor estimations. It is possible to utilize the old cardinality estimator by leveraging a database compatibility level of 110 or lower or by leveraging trace flag 9481.

Facebook Comments

About Author

SanjayHumania.Com is a notebook, where I have tried to cover information and tips related to Technology, Social Media, Life Style, Travelling and Others…

It’s owned by me and technically supported by my friend, Stiff Roy. I am from India and completed my M.Tech in Mechatronics from Jawaharlal Nehru Technological University Hyderabad in 2013. The main focus of my notebook is to cover information & tips related to the above topics. I will do my best to provide more informative and unique information to the readers at SanjayHumania.Com

  • SK


    Could you please share the complete article of SQL Server Database Migration,because i didn’t find the next option to move to the next page in this article at Steps in Migration…

    Thanks advance..

    • Today I will try to update full article…
      Thanks for ur valuable comments, be with us, keep visiting us.

  • K Narendra

    Please upload the clustering concepts of SQL Server
    Thanks &, Regards

    • Ok, I will try to post on clustering concepts of SQL Server

  • Ok, I will try to post on clustering concepts of SQL Server

  • Suman

    Very useful information even for common man
    good to know….helpful info
    Please explain more

  • Krishna Kumar Rai

    Very useful information even for common man
    good to know….helpful info
    Please explain more

  • Kapil

    There are some interesting points in time in this article but I don’t know if I see all of them center to heart. There may be some validity but I’ll take maintain opinion until I look into it further. Good article , thanks and we would like more!