Generate backup and restore the SQL Database on LAN systems

Question

I am creating one windows desktop application but I am stucked at a point where I need guidance from SO developers.

My requirements :

My main requirement is to create a windows form application that can copy database of one system to another.

see below snap.

What I tried So Far?

I successfully create backup file and restored it in local system using C# but right now it only works for local system (System A).

What issues I am facing?

I want to achieve this both using C#. I can handle all this using shared folder but that's not recommended.

1. how to access backup generated in another system of LAN because we will never have access to other system's directory. I think I can do it with sharing folder, but is there any other way that I can copy backup file and store it in the system where my application is running.

2. How can I restore backup file to another LAN system. as I know SQL is not giving rights to restore databases from outside local directory.

Is there any other way to achieve this kind of (Copy database) functionality?

Any help related to flow or any reference will be appreciated.

Answer 1

You can create a backup of a DB into a remote location. For example, System B would be creating a backup in a shared folder of System C. Then you can restore the backup on System C. You could also do it the other way round, create a shared folder on System B, create the backup into this folder and restore the backup on System C from the remote location.

Considering the nature of what you want to accomplish, I assume the following:

1. The user account running application on System A has enough priviliges to access both SQL Server instances in System B and System C.
2. If recommendations were followed, then SQL Server Logins were created using Windows Athentication Mode. If this is the case then the application in System A authenticates to both servers with the user account running the application.
3. A shared folder should be created on System C. For the sake of example lets say D:\TEST\ is shared as \\SYSTEM C\TEST\.
4. The user account running the application on System A should be granted enough previliges to access and write to the shared folder from System C

If all previous conditions can be met, then from the Application in System A you can execute the following command on System B:

BACKUP DATABASE [MyDataBase] TO  DISK = N'\\SYSTEM C\TEST\MyDataBase.bak' WITH NOFORMAT, INIT,  NAME = N'MyDataBase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Supposing \\SYSTEM C\TEST\ is the remote address for D:\TEST\ (in System C, obviously) then, from application in System A, you can execute the following command on System C:

RESTORE DATABASE [MyDataBaseCopy] FROM  DISK = N'D:\TEST\MyDataBase.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

Both commands can be exectuted from SqlCommand instances, one with a connection to System B and the other with a connection to System C.

Hope it helps.

EDIT 1

Thanks to @PMF's comments pointing out that there may be scenarios in which this solution, as is, won't work. In the case you get this kind of error:

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\SYSTEM C\TEST\MyDataBase.bak. Operating system error 5(Access is denied.).

You can try one of the following options given your scenario:

1. If your network is under a Windows Domain and SQL Server's Service Account in System B is a managed service account or a Domain Account, then you can grant write priviliges on the shared folder in System C to SQL Server's Service Account. This is considered best practice.
2. If your network is under a Windows Domain and SQL Server's Service Account in System B is not a managed service account or a Domain Account, then you can set SQL Server's Service Account to be one of these and proceed to option 1. This is considered best practice.
3. In any other case and if you don't mind sharing the folder to everyone then do so granting write access. In this case you will not have problems with this solution no matter what kind of account SQL Server is running on. This is not best practice.