on my web server I have SQL databases containing my data (named Live) and the ASP.net users (named Live_aspnetdb). These I would like to copy into my test database (named Test and Test_aspnetdb), which reside on the same server.
So far I have restored database backups from Live and Live_aspnetdb to Test and Test_aspnetdb, but after that had to fix access rights manually, because the SQL users that can access the Live db may not access the Test db and vice versa. Having to do this for every such Copy process is pretty time consuming...
What is the best way to quickly copy the contents of the Live dbs into to Test dbs, without having to manually adjust access rights?
Thanks a lot!
2. Other options is to create a linked server and add production server as linked server and then you can write Insert/update query (one time effort), using this way you dont need to move database files physically.
below is simple example for MySQL to MS-SQL, you can consider it as MS-SQL production to MS-SQL Test