How to copy prod_database to test_database
-
I'm a FoxPro programmer, new to SQL and really liking it.
Question: Can I create a sp which would copy my production database and everything in it, to a test database on the same server? This would allow my users to "refresh" test data, and from within my app, I can change connections to the prod or test database.
Thanks-dxb
-
Backup you database and restore it into the new Test database, is the easiest method of doing this and keeping all your constraints. Syncronization can be done with third party tools like Red Gate SQL Compare...etc.
HTH,
-Mike Gercevich
-
DTS has a "Copy SQL Objects" task that you can use to copy literally an entire database between servers. The only catches are that the database itself has to exist on the target server, and it doesn't copy security (nobody does) due to SID's being different on the different servers, but that's a minor problem to fix.
The biggest drawback of using DTS is that it doesn't honor dependencies so if you have PK-FK relationships defined between tables and the FK table appears first in the list it will fail since the PK table isn't there yet. Same with objects (views, SP's, UDF's) that depend on other objects. If DTS tries to copy the dependent object first it will fail since the parent object doesn't exist yet.
That having been said it's a pretty good facility for simple databases or if all you need to do is refresh the tables.
We use the Red Gate suite to synchronize schema and data and it does a much better job of sequencing the changes to ensure dependent objects are copied after their parents. The only place it has a problem (as does everything) is if you have renamed a view by changing the name in Enterprise Manager which screws up the View Properties. EM thinks the view is named with the new name but the View Properties (CREATE VIEW statement) still have the old name. This will kill any copy in almost any tool.
-
But he said he's using the same server (so security should not be issue) and wants to know if a stored procedure can be created to duplicate his production database. I believe that most people reproducing a production database would almost always will use a backup and restore. The problem with creating a stored procedure to do this is that you have to feed the name and location of the backup file to it for it to work (and not being a developer, I personally like things done the easy way
).If the goal is to automatically refresh data on a fixed schedule and if it were me, I'd be tempted just to create a DTS package for this and set it to fie periodically rather than trying to create a stored procedure. Just my 2 pesos.
My hovercraft is full of eels.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply