I am trying to restore database from a full back up (.fbk) and a quick backup (.qbk). In that sequence when it comes to
USE MASTER
RESTORE DATABASE [" + DB_NAME + "]
FROM DISK = \'" + BackupFile_Txt.Text + "\' WITH NORECOVERY";
statement, the database is stuck in recovery state.
When seeing the inner exception, it says
ALTER DATABASE is not permitted while a database is in the Restoring state
ALTER DATABASE statement failed
When I open SSMS, it says "Restoring" on the database.
However when I try just a full backup (.fbk) option only, it restores fine (in this case it's using "With Recovery").
Please see the else if condition in the WriteFile method.
private void WriteFile()
{
try
{
// Creates Restore.sql file.
string strTSQLFile = Environment.CurrentDirectory + "\\Restore.sql";
FileInfo File = new FileInfo(strTSQLFile);
StreamWriter Writer = File.CreateText();
// Write open database
string strTemp;
Writer.WriteLine("ALTER DATABASE [" + DB_NAME + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
Writer.WriteLine("Go");
if (Complete_Opt.Checked == true) // Full restore
{
strTemp = "USE MASTER RESTORE DATABASE [" + DB_NAME + "] FROM DISK = \'" + BackupFile_Txt.Text + "\' WITH RECOVERY";
Writer.WriteLine(strTemp);
}
else if (Differential_Opt.Checked == true) // Quick restore
{
strTemp = "USE MASTER RESTORE DATABASE [" + DB_NAME + "] FROM DISK = \'" + BackupFile_Txt.Text + "\' WITH NORECOVERY";
Writer.WriteLine(strTemp);
Writer.WriteLine("GO");
Writer.WriteLine("WAITFOR DELAY '00:00:10'");
Writer.WriteLine("GO");
Writer.WriteLine("ALTER DATABASE [" + DB_NAME + "] SET MULTI_USER");
Writer.WriteLine("GO");
Writer.WriteLine("ALTER DATABASE [" + DB_NAME + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
Writer.WriteLine("Go");
strTemp = "USE MASTER RESTORE DATABASE [" + DB_NAME + "] FROM DISK = \'" + txt_Quick.Text + "\' WITH RECOVERY";
Writer.WriteLine(strTemp);
}
Writer.WriteLine("GO");
Writer.WriteLine("ALTER DATABASE [" + DB_NAME + "] SET MULTI_USER");
Writer.WriteLine("GO");
Writer.WriteLine("USE PharmSpecDB");
Writer.WriteLine("GO");
Writer.WriteLine("IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'D_TRG_TestResults')");
Writer.WriteLine("BEGIN");
Writer.WriteLine("DROP TRIGGER D_TRG_TestResults");
Writer.WriteLine("END");
Writer.WriteLine("GO");
Writer.WriteLine("CREATE TRIGGER D_TRG_TestResults ON tblTestResults WITH ENCRYPTION INSTEAD OF DELETE AS BEGIN raiserror (50003,14,1) END");
Writer.WriteLine("GO");
Writer.WriteLine("IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'D_TRG_SampleIDParameter')");
Writer.WriteLine("BEGIN");
Writer.WriteLine("DROP TRIGGER D_TRG_SampleIDParameter");
Writer.WriteLine("END");
Writer.WriteLine("GO");
Writer.WriteLine("CREATE TRIGGER D_TRG_SampleIDParameter ON tblSampleIDParameter WITH ENCRYPTION INSTEAD OF DELETE AS BEGIN raiserror (50003,14,1) END");
Writer.WriteLine("GO");
Writer.WriteLine("IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'D_TRG_TestRun')");
Writer.WriteLine("BEGIN");
Writer.WriteLine("DROP TRIGGER D_TRG_TestRun");
Writer.WriteLine("END");
Writer.WriteLine("GO");
Writer.WriteLine("CREATE TRIGGER D_TRG_TestRun ON tblTestRun WITH ENCRYPTION INSTEAD OF DELETE AS BEGIN raiserror (50003,14,1) END");
Writer.WriteLine("GO");
Writer.WriteLine("IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'D_TRG_ReviewApprove')");
Writer.WriteLine("BEGIN");
Writer.WriteLine("DROP TRIGGER D_TRG_ReviewApprove");
Writer.WriteLine("END");
Writer.WriteLine("GO");
Writer.WriteLine("CREATE TRIGGER D_TRG_ReviewApprove ON tblReviewApprove WITH ENCRYPTION INSTEAD OF DELETE AS BEGIN raiserror (50003,14,1) END");
Writer.WriteLine("GO");
Writer.WriteLine("IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'D_TRG_ActiveParamValues')");
Writer.WriteLine("BEGIN");
Writer.WriteLine("DROP TRIGGER D_TRG_ActiveParamValues");
Writer.WriteLine("END");
Writer.WriteLine("GO");
Writer.WriteLine("CREATE TRIGGER D_TRG_ActiveParamValues ON tblActiveParamValues WITH ENCRYPTION INSTEAD OF DELETE AS BEGIN raiserror (50003,14,1) END");
Writer.WriteLine("GO");
Writer.WriteLine("IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'D_TRG_ActivityLogger')");
Writer.WriteLine("BEGIN");
Writer.WriteLine("DROP TRIGGER D_TRG_ActivityLogger");
Writer.WriteLine("END");
Writer.WriteLine("GO");
Writer.WriteLine("CREATE TRIGGER D_TRG_ActivityLogger ON tblActivityLogger WITH ENCRYPTION INSTEAD OF DELETE AS BEGIN raiserror (50003,14,1) END");
Writer.WriteLine("GO");
Writer.WriteLine("EXEC sp_revokedbaccess 'PharmSpecUsr'");
Writer.WriteLine("EXEC sp_grantdbaccess 'PharmSpecUsr','PharmSpecUsr'");
Writer.WriteLine("EXEC sp_addrolemember 'db_owner','PharmSpecUsr'");
Writer.WriteLine("EXEC sp_addrolemember 'db_accessadmin','PharmSpecUsr'");
Writer.WriteLine("EXEC sp_addrolemember 'db_datareader' ,'PharmSpecUsr'");
Writer.WriteLine("EXEC sp_addrolemember 'db_datawriter ', 'PharmSpecUsr'");
Writer.WriteLine("EXEC sp_addrolemember 'db_ddladmin', 'PharmSpecUsr'");
Writer.WriteLine("EXEC sp_addrolemember 'db_securityadmin', 'PharmSpecUsr'");
Writer.WriteLine("EXEC sp_addrolemember 'db_backupoperator','PharmSpecUsr'");
Writer.WriteLine("GO");
Writer.Close();
}
catch (Exception Exc)
{
throw Exc;
}
}
I am executing this SQL from C# code as follows:
try
{
string strTSQLFile = Environment.CurrentDirectory + "\\Restore.sql";
if (!File.Exists(strTSQLFile))
{
throw new FileNotFoundException();
}
using (StreamReader srSQL = new StreamReader(strTSQLFile))
{
string sqlLine;
StringBuilder sqlString = new StringBuilder();
while (!srSQL.EndOfStream)
{
sqlLine = srSQL.ReadLine();
if (string.IsNullOrEmpty(sqlLine) == false)
{
// We don't actually execute the "GO" lines but can use them to determine when to call the executenonquery function
if (string.Compare(sqlLine, "GO", true) == 0)
{
// Make sure we have something to execute
if (string.IsNullOrEmpty(sqlString.ToString()) == false)
{
ServerActionResult.ConnectionContext.ExecuteNonQuery(sqlString.ToString());
System.Diagnostics.Debug.WriteLine(sqlString);
}
sqlString.Clear();
}
// Add the next line to the stringbuilder object
else
{
sqlString.AppendLine(sqlLine);
}
}
}
}
}
catch (Exception exc)
{
throw exc;
}
Debugging:
When saying System.Diagnostics.Debug.WriteLine(sqlString); it prints:
ALTER DATABASE [PharmSpecDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
USE MASTER RESTORE DATABASE [PharmSpecDB] FROM DISK = 'C:\PharmBackup\Backup\PharmSpecDB_13-Nov-2024-12-42-57-717_Full.Fbk' WITH NORECOVERY
WAITFOR DELAY '00:00:10'
EDIT:
With this comment and what it says online "To restore the database and bring it online so that users can connect, you can use the RESTORE DATABASE statement with the WITH RECOVERY option". I am trying something like this,
strTemp = "USE MASTER RESTORE DATABASE [" + DB_NAME + "] FROM DISK = \'" + BackupFile_Txt.Text + "\' WITH NORECOVERY";
Writer.WriteLine(strTemp);
Writer.WriteLine("Go");
strTemp = "USE MASTER RESTORE DATABASE [" + DB_NAME + "] FROM DISK = \'" + txt_Quick.Text + "\' WITH RECOVERY";
Writer.WriteLine(strTemp);
Writer.WriteLine("Go");
Doing this is complaining,
"The log or differential backup cannot be restored because no files are ready to rollforward.\r\nRESTORE DATABASE is terminating abnormally.\r\nChanged database context to 'master'."
Have you tried this?
Database stuck in "Restoring" state
Probably the solution to the problem,
stuck in recovery state
is...
RESTORE DATABASE MyDatabase
FROM DISK = 'file_path_here'
WITH REPLACE,RECOVERY