Restore SQL Server database on a remote server using C#

Question

I need to write a C# program that restores a SQL Server database at a remote computer and I need to do this from local computer. My code is restoring database but I need to put path to backup .bak file at remote computer using "From Disk".

string sql =  "ALTER DATABASE [" + MyDatabaseName + "] ; Restore Database [" + MyDatabaseName + "] From Disk= '" + pathToBackupAtRemoteComputer +   "'WITH REPLACE;"

When I delete database using SQL Server Management Studio and run code, I get an error that database does not exist or I don't have permission. I want to 1st: delete database, 2nd: create new database, 3rd: put data from backup file. Is there an option to write something like: "DROP SCHEMA IF NOT EXITS" or "CREATE DATABASE IF NOT EXIST" (like in MySQL)? I tried but it didn't work.
Is there an option that I can use .bak file at my local computer (like in MySQL, when I transfer .sql file at local computer into string then restore database remotely)? Or maybe send .bak file from local to remote computer using C# application and do it automatically? I tried it out using PsExec, but there is no option to transfer file between two computers.

Gauravsa · Accepted Answer · 2019-05-03 12:35:32Z

1

One way to delete database is by using the following:

USE master;
ALTER DATABASE [MyDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [MyDatabaseName] ;

This way, it will drop the database and the physical files .ldf and .mdf files. Another thing is you can also rename the database (and maybe compare the database with restored one)

A good article on renaming a database logical and physical files are here:

https://www.mssqltips.com/sqlservertip/1891/steps-to-rename-a-sql-server-database/

answered May 3, 2019 at 12:35

Gauravsa

6,6382 gold badges25 silver badges33 bronze badges

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

Comments

Dan Guzman · Accepted Answer · 2019-05-03 10:42:47Z

0

DROP DATABASE IF EXISTS is possible in SQL Server in SQL Server 2016 onwards.

Because it is the SQL Server database engine performing the restore, the backup file must be accessible to the remote SQL Server and could be a UNC path.

answered May 3, 2019 at 10:42

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

1 Comment

jdweng Over a year ago

Avoid using DROP. It changes the permission of the MDF file and then you require a ADMIN to fix the mess. It is better to DEATTACH the database then make a copy of the database in windows and then Reattached. You can actually have two copies of the same database by renaming the MDF file before Reattaching. This is really a great method for testing because you can test on the copy without affecting the original database.

Collectives™ on Stack Overflow

Restore SQL Server database on a remote server using C#

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related