October 13, 2011 at 8:15 am
Hi,
About 6 months ago, I copied our production database to another test database that I could test some development code on.
I was wondering, is there a way to "refresh"/update the data in the test database with all the new data in the production database?
I can't just copy the production database again because I removed some tables and added some indexes, new tables, stored procedures, etc.
Can the "Generate Scripts" option in SQL Server Management Studio accomplish this?
Thanks!
October 13, 2011 at 8:44 am
Generate scripts can be used to set up your table structure and copy any other objects needed such as stored procedures and functions. To move the data, I would use the SQL Server Import and Export Wizard. Script out the structure first because the wizard will not create the table with identities and indexes. Definitely backup your development db in case you make a mistake. Truncate any non-changed tables and populate with new data using the Wizard.
If you don't know what objects that changed, consider Redgate SQL compare. You could download an evaluation version and run a comparison to only update objects that have changed.
Usually the development environment is a copy of production. The best way is just to restore production to development once all the changes are in place.
October 13, 2011 at 9:14 am
Aluminum (10/13/2011)
Usually the development environment is a copy of production.
Spot on. Assuming that you'll be rolling this code out to Production, you should test it on a Production copy.
Regards, Iain
October 13, 2011 at 11:31 am
The simplest method to refresh is to take a backup and restore the latest production copy
October 13, 2011 at 12:19 pm
R_S (10/13/2011)
The simplest method to refresh is to take a backup and restore the latest production copy
I can't just copy the production database again because I removed some tables and added some indexes, new tables, stored procedures, etc.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
October 13, 2011 at 12:35 pm
If you only removed tables, added indexes and added tables, the Red Gate's SQL Data Compare would be perfect. I've used it for years. For any objects that it doesn't see in both, it won't do anything with. But you just need an update of the production tables you did not delete on your test/dev copy. It would be perfect for that.
Indexes won't make any difference for comparing data unless you removed primary key's. If you did, SQL Data Compare allows you to specify the columns to use for identifying a unique row for the purpose of the data comparison operation. It works great and it's saved my butt on a number of occasions when I've had to take over miserable databases that were missing keys.
If you're going to be doing a lot of development in the future, if you're maintaining a system of any kind of complexity at all, you should consider separating the dev, test, and prod environments. When you're developing, you should create the script needed to modify current state of the database and version it in source control along with your application code. When promoting from dev to test, you would refresh the test environment with a fresh database copy from production and then execute your sql script for that build against that. By doing so, you're testing your deployment capability against test, thereby giving you the opportunity to weed out any deployment issues before you try to promote to production.
October 13, 2011 at 1:13 pm
tnk (10/13/2011)
If you only removed tables, added indexes and added tables, the Red Gate's SQL Data Compare would be perfect. I've used it for years. For any objects that it doesn't see in both, it won't do anything with. But you just need an update of the production tables you did not delete on your test/dev copy. It would be perfect for that.Indexes won't make any difference for comparing data unless you removed primary key's. If you did, SQL Data Compare allows you to specify the columns to use for identifying a unique row for the purpose of the data comparison operation. It works great and it's saved my butt on a number of occasions when I've had to take over miserable databases that were missing keys.
If you're going to be doing a lot of development in the future, if you're maintaining a system of any kind of complexity at all, you should consider separating the dev, test, and prod environments. When you're developing, you should create the script needed to modify current state of the database and version it in source control along with your application code. When promoting from dev to test, you would refresh the test environment with a fresh database copy from production and then execute your sql script for that build against that. By doing so, you're testing your deployment capability against test, thereby giving you the opportunity to weed out any deployment issues before you try to promote to production.
I agree, RedGate SQL Data Compare is perfect for this. We use this extensively when we want to get recent data into our dev database that has had ddl changes. You can even try it out for 30 days I believe.
Thanks,
Jared
Jared
CE - Microsoft
October 14, 2011 at 10:56 am
Ok I downloaded and installed Redgate but I don't really see how this will copy data from the production database to my development database. I can see how Redgate is useful for certain circumstances, but I don't see or can't find any options to copy only the data between 2 different databases.
Thanks
October 14, 2011 at 12:04 pm
You must make sure it is DATA compare. They also have one that is just COMPARE which does schemas. IF you have the DATA compare, it only compares and syncs data. There are some tricks though... For instance you may have to tell it what some keys are if the tables do not have a primary key. Also, you can have it drop and recreate indexes and many other options.
Thanks,
Jared
Jared
CE - Microsoft
October 17, 2011 at 12:00 am
An alternative solution may be to use a merge join on the tables (and columns) that are still in common from your production and development servers. Basically left joining on the key(s) at the grain of the table can establish whether the record exists or not on the dev server. That would supply updates and deletes. From that point, for existing records, comparing either all of the columns that exist between the two systems for any changes would provide which rows need to be updated. Merge joins are particularly good for this type of operation, but temp tables that feed upsert and delete operations work nicely, as well.
October 17, 2011 at 1:41 am
I can't just copy the production database again because I removed some tables and added some indexes, new tables, stored procedures, etc.
If #<modified objects> is less than 5% (or even 10%) of #<total objects> in database, backup / restore is still preferable. If you are not comfortable with third party tools, please don’t go with them. You may do this R&D some other time when you are done with your primary issues.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply