CODEWITHSUNDEEP
c#sql-serversmo
Faheem Ullah
Faheem Ullah

Reputation: 1

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

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'.'

Upvotes: 0

Views: 1687

Answers (1)

user11380812
user11380812

Reputation:

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

Upvotes: 1

Related Questions