SQL Server backup and restore from C# problem

Question

I have written a program in C# to use SQL 2008.

I want to backup the database and restore it. My code for backup works correctly but restore doesn't work and the database becomes single user.

Get the following error :

The tail of the log for the database "doctor" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log. RESTORE DATABASE is terminating abnormally. Nonqualified transactions are being rolled back. Estimated rollback

My code:

private void Backup(string strFileName)
{
    try
    {
        string command = @"BACKUP DATABASE doctor TO DISK='"+ strFileName+"'";
        SqlCommand oCommand = null;
        SqlConnection oConnection = null;
        oConnection = new SqlConnection("Data Source=.;Initial Catalog=doctor;Integrated Security=True");
        if (oConnection.State != ConnectionState.Open)
        oConnection.Open();
        oCommand = new SqlCommand(command, oConnection);
        oCommand.ExecuteNonQuery();


    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}   

private void Restore(string strFileName)
{
    try
    {
        string command = "ALTER DATABASE doctor SET SINGLE_USER with ROLLBACK IMMEDIATE " + "use master " + " RESTORE DATABASE doctor FROM DISK='" + strFileName + "'";
        SqlCommand oCommand = null;
        SqlConnection oConnection = null;
        oConnection = new SqlConnection("Data Source=.;Initial Catalog=doctor;Integrated Security=True");
        if (oConnection.State == ConnectionState.Closed)
        {
            oConnection.Open();
            oCommand = new SqlCommand(command, oConnection);
            oCommand.ExecuteNonQuery();               
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Felipe Miosso · Accepted Answer · 2017-09-19 19:21:05Z

1

I think if you concat

" WITH REPLACE "

with your restore string it would work.

edited Sep 19, 2017 at 19:21

Felipe Miosso

7,3396 gold badges46 silver badges56 bronze badges

answered Jul 3, 2011 at 6:44

DooDoo

13.1k70 gold badges193 silver badges320 bronze badges

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

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

The error you've got is a MSSQLSERVER_3159 error, the explanation and user action ca be found on

msdn : MSSQLSERVER_3159 documentation

Hope it helped.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jul 3, 2011 at 6:46

painotpi

7,0122 gold badges40 silver badges72 bronze badges

Comments

Quick Joe Smith · Accepted Answer · 2011-07-03 06:55:54Z

0

It would appear that your database is using full recovery mode, and under normal circumstances a log backup is required.

Depending on your scenario, Nima's answer may suffice.

See the following topics on MSND for more information on the topic:

answered Jul 3, 2011 at 6:55

Quick Joe Smith

8,2683 gold badges31 silver badges33 bronze badges

Collectives™ on Stack Overflow

SQL Server backup and restore from C# problem

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related