I'm working on a solution for the following issue:
About 5 - 10 times a day, I make a backup of a MS-SQL Database "dbname1" (SQL 2005 - 2014) on SQLServer1 (different databases on different versions). Then copy the file to SQLServer2 and restore the files onto "dbname2". There are some more steps in between (reading users/roles on pre-restore "dbname2", write user/roles after restore "dbname2", creating backups pre- and after-restore and so on...).
I already have some scripts, which make it easier to do it, but what I'm working on is one single script to do all of it at once...
So far, I tried several things but always ran in some problems I can't solve:
- Powershell. The problem here is, that I am not able to run the .ps1 file with the credentials that I need to read the permission from "dbname2". If I use a connection string with "user id='sa'; Password='mypwd';" it gets ignored and uses only the limited permissions that my current windows logged-on user has. Therefore, I only see my own permission on the database..
- T-SQL which would write and execute a copy statement with the xp_cmdshell command.. But the security settings for our SQL Server do not allow the execution of that...
- SQL-Agent-Job. With Step 1, I wanted to create the backup and save the filename in a variable (stored procedure already in place). Then Step 2, copy the file to the remote location. Problem here is that i cant pass the filename from step 1 to step 2...
Yeah.. That's about it, I guess...
I'm looking for some input (or solution ;-) ) how I can create a backup, copy the created file to a different server and restore it there. Preferable, it should also run some commands, which are created from a stored procedure (something like this 'CREATE USER [xxxcccvfr-username] FOR LOGIN [xxxcccvfr-username] WITH DEFAULT_SCHEMA=[dbo]', BUT that's just a bonus ;-) )
Any help is highly appreciated!
Thanks
D
Aucun commentaire:
Enregistrer un commentaire