0

I want to export all the strcuture of my databases. The reason is because I don´t have two servers so I need to use the same server for DEV and QA.

I Know that I can use the wizard but I want to schelude this task. I found this information but this procedure export the structure per each database and table. Export database schema into SQL file

But I want to export everything per each database. I tried to modify the procedure to be able to export everything but it was not possible.

Someone can help me? Thank you in advance

Community
  • 1
  • 1
Raúl Alfonso Jiménez Morocho
  • 13
  • 6
  • If you need a copy of you production database for testing or development, just create a full backup of the database and then restore it on your local servers. – gotqn Jul 22 '15 at 11:03
  • generate script with data and constraints OR create a full backup and reload it – wiretext Jul 22 '15 at 11:03
  • Hi, the problem is that PROD is huge and just I want the data from PROD. Example: DEV team deploy something and I need to test it. I don't have QA environment or other server so I need to do it in the same DEV server but copying the structure of the databases and intregate data from PROD. if I have a database called STG, I want to create a new database PRE_STG and in this database I will test my new deployment. That´s why I wanted a T-sql script or something. – Raúl Alfonso Jiménez Morocho Jul 22 '15 at 12:25
  • If you create a database that is mostly similar to PROD is that even a valid test? You said you want all the data from prod, the data is what takes up the space. You need to talk to the business and plead your case to get a proper environment for production support. – Sean Lange Jul 22 '15 at 14:09
  • Hi Sean, I know and I already did and the answer is "no budget for the moment" :( so we must to find a way how to have a "Test" envrionment. Also I don´t want all the data of PROD, just 2 or 3 months of historic – Raúl Alfonso Jiménez Morocho Jul 23 '15 at 07:45

1 Answers1

0

Have you considered this ? :

Create a new empty TEST DB instance within the the prod server using the same structure as production, then, using the wizard ( don't freak...stay with me here.... ) you populate the smaller tables ( lookups, etc ) until you have everything except the big transaction tables, logs and join tables, ect.

At this point, you back it up, then delete the operational TEST DB.

Next, you restore the backup to DEV/TEST, and again, using the wizard you populate all the other tables ( from PROD ) BUT...you filter for just the data ranges you need. Run BackUp again. You now have a clean TEST Env, restoreable as needed.

When you need a newer set of test data, you go to backup # 1, restore it, and you populate the transaction, join, log tables using the filters as needed. Back that up. You now have a new, restorable Test database.

Once you have the process well defined, you can start writing VB utilities ( C# if you prefer ) to do the filtered copying from PROD to TEST.

First do it the slow manual way, so you have every step defined. Then add automation.

Juan-Carlos
  • 377
  • 2
  • 8