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
    {
        stri开发者_高级运维ng 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);
    }
}

I think if you concat

" WITH REPLACE "

with your restore string it would work.

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.

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:

• Performing a complete database restore (Full recovery mode)
• Tail-log backups