-1

Want to restore a database backup from linked server A to a database located on linked-server B using C#. prefer to use SMO.

I can restore from my local backup to local machine.

{
 conn = new ServerConnection
           {
               ConnectionString = @"Data Source =
 (localdb)\MSSQLLocalDb;Initial Catalog=master;Integrated Security=true",                
            };


        try
        {
            //Restore Full
            srv = new Server(conn);
            //lsrv = srv.LinkedServers[@"DEVSQL\ALPHA"]; need to figure out how to restore to linked server instead of local.
            //srv.KillAllProcesses("G4TestNew");
            var res = new Restore();
            res.Database = "G4TestNew";
            res.Action = RestoreActionType.Database;
            filePath = @"\\CABCSERVER\Database\Temp\Full.bak";
            res.Devices.AddDevice(filePath, DeviceType.File);
            res.ReplaceDatabase = true;
            res.NoRecovery = true;
            var dataFile = new RelocateFile("G4Test", @"C:\TBD\G4Test.mdf");
            var logFile = new RelocateFile("G4Test_log", @"C:\TBD\G4TestNew.ldf");

            res.RelocateFiles.Add(dataFile);
            res.RelocateFiles.Add(logFile);

            res.SqlRestore(srv);

}

EDIT(Adding more detail):. In this case the linked servers are accessed via 'sql server authentication' and application does not have access to the credential required to connect directly and can use 'Integrated Security' to connect to localdb only.

Farbod
  • 1
  • 2

1 Answers1

2

In SMO you would not connect to one server and then administer a linked server. Instead connect directly to the target server. eg:

ConnectionString = @"Data Source =
 DEVSQL\ALPHA;Initial Catalog=master;Integrated Security=true",
srv = new Server(conn);
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67