I want to restore a SQL Server database using C# code

Question

I want to select path of restore then select my database backup file and restore it by clicking a button in Visual Studio 2019.

I have some code below which I already have written, but I get an error.

string connection = ConfigurationManager.ConnectionStrings["dbx"].ConnectionString;

SqlConnection con = new SqlConnection(connection);
con.Open();

String sqlquery = "Use Master ALTER DATABASE databasename SET OFFLINE WITH ROLLBACK IMMEDIATE RESTORE DATABASE FHMTailorDB FROM DISK ='" + RestoreTextBox.Text + "' ALTER DATABASE databasename SET ONLINE WITH ROLLBACK IMMEDIATE";

SqlCommand cmd = new SqlCommand(sqlquery, con);
cmd.ExecuteNonQuery();

con.Close();
con.Dispose();

lblError.Text = "Database Restore successfully";

Error:

System.Data.SqlClient.SqlException: 'User does not have permission to alter database 'databasename', the database does not exist, or the database is not in a state that allows access checks.

ALTER DATABASE statement failed.

Directory lookup for the file "F:\FHMTailorManager\FHMTailorDB.mdf" failed with the operating system error 2(The system cannot find the file specified.).

File 'FHMTailorDB' cannot be restored to 'F:\FHMTailorManager\FHMTailorDB.mdf'. Use WITH MOVE to identify a valid location for the file.

Directory lookup for the file "F:\FHMTailorManager\FHMTailorDB_log.ldf" failed with the operating system error 2(The system cannot find the file specified.).

File 'FHMTailorDB_log' cannot be restored to 'F:\FHMTailorManager\FHMTailorDB_log.ldf'. Use WITH MOVE to identify a valid location for the file. Problems were identified while planning for the RESTORE statement. Previous messages provide details.

RESTORE DATABASE is terminating abnormally.

User does not have permission to alter database 'databasename', the database does not exist, or the database is not in a state that allows access checks.

ALTER DATABASE statement failed.

Changed database context to 'master'.'

I've seen that happen a lot. Do not backup which causes permission to change. Instead use Deattach and Atttach where permission do not change. You will need an admin to fix the permissions. — jdweng
– jdweng, Commented Oct 2, 2019 at 16:42

user11380812 · Accepted Answer · 2019-10-02 18:43:22Z

1

You have two problems

The first one the credential provided by your connection string does not have enough rights to accomplish the task of putting the database offline.

The second one is that you trying to select MDF/LDF files from your client PC-a and the file system on the server-side does not know or has no permission to access that folder.

So, my answer is that you need to try something completely different.

Try using SMO. There is an article on the internet that gently introduces readers to SMO.

The article points to the solution on GitHub which contains everything that you need.

It means a set of automatized procedures written in .NET ( C# ) for backup, restore and much more.

So, try this one Programming SQL Server with SQL Server Management Objects Framework

answered Oct 2, 2019 at 18:43

user11380812

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

Collectives™ on Stack Overflow

I want to restore a SQL Server database using C# code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related