3

I have several databases on production and i have also test environment. I don't have permission to do backup and restore operations. Actually i am using wizard to generate scripts (schema + data) and run sql on another database. Is there a way i can do this via script (SQL, PowerShell, C#, etc..) ? Can i move all database objects from production to test on one click ideally, it is possible that script will universal and input parameter will be database name for example ? The problems with generated scripts are two => SQL can be too large to open in sql manager and also it is time consuming to run wizard everytime, because i need production data often.

The final vision based on that universal script or job is that i have an web dashboard, where i can click on database or to have set of command shell scripts and the database will replicate to the test environment where i can modify data.

Any Idea ?

2

You can also use the transfer SQL Server Objects task which is used to transfer one or more SQL Server objects to a different database, either on the same or another SQL Server instance.

This allows you to select different types of objects you want to transfer. You can select tables, views, stored procedures, user defined functions etc. Not only this, you can select a combination of these types of objects to transfer and even select particular objects of a particular object type.

Please refer to this MS article on how to achieve:

Transfer SQL Server Objects Task

Also Transfer Database Task and Transfer SQL Server Objects Task in SSIS

3

You can use SMO to script a database schema from Powershell, C# or VB. A great article on the subject can be found here.

Regarding data, I think that the simplest option is to pipe each table's data to a file using BCP, then import it back to the destination database, again using BCP. I strongly suggest the native format (-n option), as it simplifies the process enormously.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.