Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Can you compare an older SQL Server Snapshot to a newer one and recreate any changes made? Expand / Collapse
Author
Message
Posted Thursday, August 14, 2014 5:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 1:02 PM
Points: 26, Visits: 72
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!
Post #1603507
Posted Friday, August 15, 2014 10:39 AM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:26 AM
Points: 10,381, Visits: 13,436
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.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1603783
Posted Wednesday, August 20, 2014 8:29 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:37 PM
Points: 31,367, Visits: 15,832
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1605467
Posted Thursday, August 21, 2014 10:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 1:02 PM
Points: 26, Visits: 72
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?
Post #1605920
Posted Wednesday, August 27, 2014 11:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 1:02 PM
Points: 26, Visits: 72
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.
Post #1607955
Posted Wednesday, August 27, 2014 11:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:26 AM
Points: 10,381, Visits: 13,436
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 */





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1607958
Posted Wednesday, August 27, 2014 1:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 1:02 PM
Points: 26, Visits: 72

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)
Post #1608006
Posted Wednesday, August 27, 2014 1:40 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:26 AM
Points: 10,381, Visits: 13,436
You type in the Logical Name for the file and you get that by using RESTORE FILELISTONLY. Check out RESTORE in BOL.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1608009
Posted Wednesday, August 27, 2014 2:39 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 1:02 PM
Points: 26, Visits: 72
Thanks, I'll let you know how it works out. The new servers should be up and running early next week.
Post #1608038
Posted Thursday, September 4, 2014 2:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 1:02 PM
Points: 26, Visits: 72
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.
Post #1610689
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse