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 ?
Best Answer
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