none
SQL Server Database migration RRS feed

  • Question

  • HI All,

    We are planning to move few databases from one server to other server by backup and restore method.  

    1) To ensure no write operation during migration activity Please let us know the options we can have.

    2) if we can set the databases in read only mode and take a backup and restore on new server do we need to ensure anything else?

    Regards,

    Varu

    Wednesday, May 25, 2016 12:14 PM

Answers

All replies

  • Hi Varu,

    To ensure no write operation during migration, you need to place the databases in READ ONLY mode, then take backups of SQL Server databases.

    After restoring the databases on new server, databases need to be changed to READ WRITE mode. For more details, please review the following blog.

    https://www.mssqltips.com/sqlservertip/1921/best-practices-for-working-with-read-only-databases-in-sql-server/

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support




    Saturday, May 28, 2016 3:08 AM
    Moderator
  • Varun,

    To be on safer side I suggest you stop application connection to database by taking prior approvals. Yes read_only is an option but cannot be done on all databases. A better approach would be to leave database as it is and stop application connecting to the database


    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

    Monday, May 30, 2016 6:27 AM
    Moderator
  • Hi Varu,

    I would - to be honest - use non of the given advices because all will cause a longer downtime. I would implement a mirror between the two servers and do a failover when the mirror has been implemented. This will give you the opportunity of a downtime of a few seconds only instead of a full BACKUP period and RESTORE period for each database.

    get more details here: https://msdn.microsoft.com/en-us/library/ms190941.aspx

    While you are running your backup / restore the users can work with the database. You only have to restore the LOG-delta on the mirror side before you implement it (or stop log backups as long as you implement it).

    A good fellow MCM (Edwin Sarmiento) has written a blog article about the "upgrade" to newer version of SQL Server but you can use it for your approach of a database move the same way:

    http://www.edwinmsarmiento.com/upgrading-sql-server-2005-databases-to-sql-server-2014-using-database-mirroring/

    I use mirroring for...

    • - transfer to new servers
    • - update to new versions of SQL Server

    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Monday, May 30, 2016 11:09 AM
  • Uwe,

    Well OP did not mentioned anything about downtime so we assume he was OK with it. He has no considerations with downtime so I guess no one suggested.

    May be his database is just few GB and they have downtime approved.

    If OP really wants to minimize downtime instead of mirroring I would go for Logshipping. Mirroring has far greater overheads than log shipping. But ye sync mirroring has advantage in terms of amount of work to do for failover.


    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

    Monday, May 30, 2016 11:40 AM
    Moderator