Can you compare an older SQL Server Snapshot to a newer one and recreate any changes made?

  • We are changing server providers for our three servers: Development, QA, and Production.

    Our old hosts are creating images of the servers which will be sent to us and then sent off to the new providers to be uploaded.

    Once the images are loaded and the servers are running, the new servers will be 1-2 weeks behind our current servers.

    We are running SQL server 2012 along with Master Data Services.

    Can I create database snapshots at the time the images are made and then compare those to snapshots created at a future date to find all changes made to the databases and then recreate them on the new servers?

    Or could I create a snapshot of the databases on our current servers once the new servers are ready to go, and then use that snapshot (made on the old servers) to upload the data onto the new servers, bringing them up-to-date?

    Are there any other suggestions on ways we can bring our new servers up-to-date with our current servers once the images are installed and the servers are running?

    Thanks!

  • I think there are some potentially simpler options:

    1. If you have connectivity between sites you could look at setting up mirroring and then "failover" to your mirror when everything is caught up.

    2. Use backups. The day of your planned cutover get full backups of the existing databases, restore at the new site, apply transaction log backups. At cutover time, take final log backups, take the db's offline, restore the final log backups at the new site and point your apps there.

  • A snapshot is like another database, but it doesn't allow you to backup/restore.

    As Jack mentioned, a backup is probably a better choice for bringing things up to date. Otherwise you are really doing a database comparison and trying to move objects and data over, which can be tricky.

    I work for Red Gate Software, and you could use our SQL Compare/Data Compare to do this, but if the databases are large, it might not work well for data.

  • What if I use the copy database function in SQL Server Management Studio once the new servers are up and running?

    If I copy the databases over to the new servers, delete the out-of-date databases and rename the copies to their original names, would they work just like the old databases? Would MDS and other applications continue to connect to these databases as long as they have the correct name?

  • Thanks Jack. Is it possible to restore backups to an older version of the database?

    What I mean is the database backups will be taken from one server, and then restored on a different server that has the same database but about two weeks behind in data.

    Do you see that being a problem at all?

    Thanks.

  • acapone (8/27/2014)


    Thanks Jack. Is it possible to restore backups to an older version of the database?

    What I mean is the database backups will be taken from one server, and then restored on a different server that has the same database but about two weeks behind in data.

    Do you see that being a problem at all?

    Thanks.

    If you are restoring over an existing database you need to specify WITH REPLACE. Typically when restoring a database on a different server the RESTORE command will look something like this:

    RESTORE DATABASE [DatabaseName] FROM DISK = 'Backup Path' WITH

    MOVE 'datafile1' TO 'data file path\physical_file_name.mdf',

    MOVE 'logfile' TO 'log file path\physical_file_name.ldf',

    REPLACE /* only needed if the database already exists */

  • If you are restoring over an existing database you need to specify WITH REPLACE. Typically when restoring a database on a different server the RESTORE command will look something like this:

    RESTORE DATABASE [DatabaseName] FROM DISK = 'Backup Path' WITH

    MOVE 'datafile1' TO 'data file path\physical_file_name.mdf',

    MOVE 'logfile' TO 'log file path\physical_file_name.ldf',

    REPLACE /* only needed if the database already exists */

    Thanks I will give this a try. When I type out the MOVE command, do I type the actual name of the datafile and logfile after MOVE or do I type "datafile" like you have above? (might be a dumb question but I am trying to get the game-plan for this migration well documented)

  • You type in the Logical Name for the file and you get that by using RESTORE FILELISTONLY. Check out RESTORE in BOL.

  • Thanks, I'll let you know how it works out. The new servers should be up and running early next week.

  • I am restoring the databases now and am wondering if I need to restore the system databases as well? Do I need to backup and restore the Master and Model dbs?

    I have already restored the Master Data Services database and that seems to be working just fine.

  • You restore msdb if you need your jobs, alerts, operators back. It depends on your instance if you need/want this. Restoring master will recover your logins, but it also sets pathing for user databases, so if something changed, you might not want to do this.

  • Thanks Steve. Yeah I restored msdb, but I didn't restore model or master and it seems to be working great. Thanks for all the help!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply