Moving database objects to another database without wizard

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 ?

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.

StackExchange Database Administrators Q#104054, answer score: 3