Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
acapone
acapone
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
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!
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36072 Visits: 18736
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
My Blog: www.voiceofthedba.com
acapone
acapone
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
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?
acapone
acapone
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
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.
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
acapone
acapone
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
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)
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
acapone
acapone
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
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.
acapone
acapone
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search