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

Rather than backup and restore, it sounds like you're attempting to replicate a database from one server to another. There is a thorough link on MSDN about how to do this, using multiple options.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f5ee85c7-c9d3-438e-a835-daa2c785026b/copy-sql-database-tables-to-another-sql-server?forum=sqlgetstarted

You might consider building out your transfer process in SSIS (and any other steps you might wish to include such as data cleansing and other preparation), then calling that SSIS package either natively from your app or have the app call a SQL agent task which executes the package.

Answer 2

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.

Answer 3

My first instinct when some says "I want to copy a DB in C#" is: That is with 95% propabiltiy the wrong track. Do it in the DB or OS layer. Trying to pull that off in C# only adds a whole layer of overhead and stuff that can go wrong.

It think what you want in the end is for System C to have a copy of System B. The DB Termn for that is Replication. Particular if you want System C to be a backup DB if System B goes down, the proper replication/cluster approaches are always preferable. https://en.wikipedia.org/wiki/Replication_(computing)#Database_replication

Most commerical DBMS even have build in support for that kind of stuff and you are better off just using that. If you can not use those: Good luck.

Answer 4

If the account you are running against doesn't have access to the machines, but you have another admin account...you can access using impersonation ( impersonate the higher priviledge acct,) and the admin share (e.g. \servername\c$ to access a machine's c drive. I've used this impersonation class before...call the higher priviledge methods inside it https://www.codeproject.com/Articles/10090/A-small-C-Class-for-impersonating-a-User