CODEWITHSUNDEEP
c#sql-servert-sqlsql-server-2008.net-4.0
mo_al
mo_al

Reputation: 101

SQL Server backup and restore from C# problem

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);
    }
}

Upvotes: 1

Views: 2211

Answers (3)

DooDoo
DooDoo

Reputation: 13487

I think if you concat

" WITH REPLACE "

with your restore string it would work.

Upvotes: 1

painotpi
painotpi

Reputation: 6996

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.

Upvotes: 1

Quick Joe Smith
Quick Joe Smith

Reputation: 8232

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:

Upvotes: 0

Related Questions