none
SQL SERVER MIGRATION

    Question

  • Hello Sir,

    What is the best approach to go live on production server.We are migrating SQL Server 2008 to SQL Server 2012 in the term of database migration.Ex-How do we restrict the user from accessing database during final move?

    Cheers,

    Thursday, March 9, 2017 6:46 PM

All replies

  • How do we restrict the user from accessing database during final move?

    Hi, one solution may be to disable their login in SQL Server.
    Thursday, March 9, 2017 8:18 PM
  • On the source server do this

    backup log DatabaseName to disk='c:\temp\DatabaseName.trn' with norecovery 
    This will take the databases offline.

    Thursday, March 9, 2017 8:26 PM
  • Not to confuse you with too many options, but another one to consider would be switching the database 'Restrict access' state from MULTI_USER to RESTRICTED_USER mode or SINGLE_USER mode, whichever meets your requirement better.  Be sure and follow recommended guidelines if considering latter option:

    Set a Database to Single-user Mode

    Restricted user

    Then switch back to MULTI_USER mode when ready to go live in Production and let users start hitting the database.

    HTH,

     

    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Friday, March 10, 2017 1:42 AM
    Thursday, March 9, 2017 8:47 PM
  • Thanks for reply.If database is offline then how database will be taken backup for new server?I mean to say before go live no transaction should be active on source server.

    Cheers,

    Friday, March 10, 2017 4:28 AM
  • Thanks for reply.If database is offline then how database will be taken backup for new server?I mean to say before go live no transaction should be active on source server.

    Cheers,

    To make that sure first you need to stop the application, this would solve lot of issue with application. Then as suggested either disable login so that people cannot log and run queries or set database in single user mode. If you do above no changes would be made in the database.

    After you are done moving or restoring then bring database in multi user mode, enable logins and then start the application


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, March 10, 2017 4:57 AM
    Moderator