Create database from backup file using C# on the remote SQL Server

Question

I've got a backup of my database.

After editing it in my program, it's a new newDb.bak file(on my PC).

How could I restore this new database on the remote SQL Server, if I have the connection string (I know ip, login and password)?

Mainly I have to do this only using C# (not the SQL Server Management Studio).

Ali Adlavaran · Accepted Answer · 2017-07-27 17:58:07Z

3

Unfortunately there is no any direct way to restore database remotely from local backup file.

But the alternative solution is making a shared location in some where (local side or server side it's not matter) which both of the client and server have access to there. (Update: Also, make sure the SQL server that will be performing the restore has permissions to the directory that you're copying the backup to. – @KSib) Then restore the database from file :

Copy newDb.bak to \\serverip\$sharedfolder\newDb.bak on the server.
RESTORE DATABASE YOURDB FROM DISK = N'\\serverip\$sharedfolder\newDb.bak';

if you want i can share the source code of restoring database in C#.

Update: Another solution is that create a table which contains varbinary column, Insert the backup file content from local to it by SqlCommand or SqlBulkCopy to and then export the file content to some where in your server by bcp such this:

BCP "SELECT FileContent FROM createdTable WHERE ID = 1000" queryout "C:\newDB.bak" -T

Then you can execute the restore command easily!

Also you can create a server side StoredProcedure containing a transaction to get the file content as input parameter and do all the command as one transaction.

I hope to be helpful for you :).

edited Jul 27, 2017 at 17:58

answered Jul 27, 2017 at 16:44

Ali Adlavaran

3,7452 gold badges25 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

KamikyIT Over a year ago

> there is no any direct way Oooh crap.

KSib Over a year ago

Also, make sure the SQL server that will be performing the restore has permissions to the directory that you're copying the backup to.

Ali Adlavaran Over a year ago

@KamikyIT I found a new way and update my post. Let me know if is not clear :)

Ali Adlavaran Over a year ago

@KSib I updated my answer and I put your recommendation in it. Regards.

Collectives™ on Stack Overflow

Create database from backup file using C# on the remote SQL Server

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related