I have production database with a size of 1TB in SQL Server 2008. I can't close the database for backup/restore process, because it needs so long time for perform this operation.

How to migrate this database to another server which has SQL Server 2016?

I am thinking of creating a database on the new server and then bulk inserting the existing data. But what about logins, roles, permissions? How to figure it out this problem ?


1 Answer 1


You dont need to close the database for backup, just setup log-shipping with two servers (legacy and target), once you decide to migrate (cut-over) to target server just take log backup from legacy server and restore on target server "with recovery".

  • its good and effective way but management wants firstly bulk insert then migrate roles users permissions. In oracle dbms metadata gives all necessarily commands, is there any this script to get this metadata from sql server ?
    – microracl
    Mar 3, 2017 at 11:50
  • If your management have really decided to take hard route than try import /export utility, for Metadata (schema script) you have option to generate script as well.
    – Bilal Ayub
    Mar 3, 2017 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.