0

Here is the situation I am in. I have backup files for databases that I am using to restore them on a test server(B). The issue that I am having is that linked server logins appear to be on the master server level and I am unable to use those through a database's backup file.

I create these backup files from another server(A) and store them in a remote location. Is there a way to create a file or something with all the linked server logins from server A and use that file to restore them somehow when I use backup files to create Databases on Server B?

4
  • Hi, Welcome to the site. Why can't you just script the linked server and the logins and execute it after the restore? Is there any problem with that I'm missing?
    – Tom V
    Oct 24 '18 at 18:20
  • @TomV Thank you for the reply. I could definitely do that. But I want the script to reside in a remote file storage. Reason: I don't want server B(does not contain logins) to depend on Server A(has the login info) every time I restore each database from backup files. Let me know if I can explain better.
    – Shrav
    Oct 24 '18 at 18:26
  • 1
    yes I don't understand what you mean by that comment. What problem are you trying to avoid?
    – Tom V
    Oct 24 '18 at 18:29
  • This is the situation: 1. I have a job that creates backup files for each DataBase on server A and dumps into remote file storage. 2. I have another job that utilizes these backup files to create Databases on the specified server B. And that could be Sql server running on a docker container too. 3. In job 1 if I could somehow generate a script that has all the linked server logins etc and dump that too in the file storage, I would be able to use that file in job 2. Is there a good way that you can suggest to achieve this?
    – Shrav
    Oct 24 '18 at 19:38
1

You can use Copy-DbaLinkedServer from dbatools to migrate linked servers.

For login migration, you can use copy-dbalogin

Just install dbatools and you will get all the cmdlets.

Also, see my answer if you want to dump the scripted output into file.

2
  • Thank you for that tip. Is there a way that I could do this without dbatools? Like dump all the info to a file and then use that file in code and execute a command?
    – Shrav
    Oct 24 '18 at 17:44
  • You have to build your own. But why reinvent the wheel when you have a solution that does it for you ? dbatools has an option to dump the generated code into file as well.
    – Kin Shah
    Oct 24 '18 at 17:53

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