none
RESTORE DATABASE is terminating abnormally.

    Question

  • hi All

    This code is for backup And Restore DataBase in Sql Exspress 2008 & vb.net 2010

     Dim StrPath As String
    
    
    
      Dim Cmd As New SqlClient.SqlCommand(Nothing, Cn)
    
    
    
      StrPath = "G:\"
    
      If Cn.State = ConnectionState.Closed Then Cn.Open()
    
    
    
      If rbtnBack.Checked = True Then
    
    
    
       Cmd.CommandText = "BACKUP DATABASE [" & My.Application.Info.DirectoryPath & "\DB.mdf] TO DISK ='" & StrPath & "BackUp.bak" & "' WITH NOFORMAT, NOINIT, NAME = 'x', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
    
       Cmd.ExecuteNonQuery()
    
    
    
      End If
    
    
    
      If rbtnRestore.Checked = True Then
    
    
    
       Cmd.CommandText = "RESTORE DATABASE DB FROM DISK='G:\BackUp.bak' WITH FILE=1, NORECOVERY; "
    
       Cmd.ExecuteNonQuery()
    
    
    
      End If
    
      Cn.Close()

    But there is an error for database recovery

    The error text is as follows:

    

     Message=The file 'G:\Vb20010\Project\Project\bin\Debug\Db.mdf' cannot be overwritten. It is being used by database 'G:\VB20010\Project\Project\BIN\DEBUG\DB.MDF'.
    
    File 'Project' cannot be restored to 'G:\Vb20010\Project\Project\bin\Debug\DB.mdf'. Use WITH MOVE to identify a valid location for the file.
    
    The file 'G:\Vb20010\Project\Project\bin\Debug\DB_log.ldf' cannot be overwritten. It is being used by database 'G:\VB20010\Project\Project\BIN\DEBUG\DB.MDF'.
    
    File 'Project_log' cannot be restored to 'G:\Vb20010\Project\Project\bin\Debug\DB_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.
    
     

     

    But I ask you

    First : there is the correct way to backup?

    Second : What can I do to fix the error recovery

    Thanks

    


    Name of Allah, Most Gracious, Most Merciful and He created the human
    Wednesday, August 31, 2011 10:19 AM

Answers

  • Hi Sh2007,

    Based on your description, I have demonstrated the code below:

    'please replace the Data Source(InstanceName) and database name (ReportServerTempDB)
    
    Imports System.Data.SqlClient
    
    Public Class Form1
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim cn As New SqlConnection("Data Source=InstanceName; Initial Catalog=master; Integrated Security=True")
            Dim Cmd As New SqlClient.SqlCommand(Nothing, cn)
            Dim StrPath As String
            StrPath = "G:\"
    
            If cn.State = ConnectionState.Closed Then cn.Open()
    
            'backup 
            If rbtnBack.Checked = True Then
                Cmd.CommandText = "BACKUP DATABASE [ReportServerTempDB] TO DISK ='" & StrPath & "BackUp.bak" & "' WITH NOFORMAT, NOINIT, NAME = 'x', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
                Cmd.ExecuteNonQuery()
            End If
    
            'restore 
            If rbtnRestore.Checked = True Then
                ' please make sure that the database has been backed up to:  G:\BackUp.bak
                Cmd.CommandText = "RESTORE DATABASE [ReportServerTempDB] FROM DISK='G:\BackUp.bak' WITH FILE=1, NORECOVERY; "
                Cmd.ExecuteNonQuery()
            End If
    
            cn.Close()
    
        End Sub
    End Class
    
    

     

     


    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    • Marked as answer by Stephanie Lv Wednesday, September 07, 2011 1:04 PM
    Friday, September 02, 2011 12:44 PM

All replies

  • Hi,

    The First error message says that your database is in use. check that any user uses the the database. Also, you haven't mentioned your connection string may you are using the database if this is the case then connect to master database. if this resolves then see below restore command to move database files.

    RESTORE DATABASE AdventureWorks2008R2
      FROM AdventureWorks2008R2Backups
      WITH NORECOVERY, 
       MOVE 'AdventureWorks2008R2_Data' TO 
    'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\NewAdvWorks2008R2.mdf', 
       MOVE 'AdventureWorks2008R2_Log' 
    TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\NewAdvWorks2008R2.ldf'
    RESTORE LOG AdventureWorks2008R2
      FROM AdventureWorks2008R2Backups
      WITH RECOVERY
    

    Also check restore command syntax on http://msdn.microsoft.com/en-us/library/ms186858.aspx

     

    Regards,

     

    Balwant.


    Failure in Life is failure to try... PGDCA-98(South Gujarat University),MCTS (SQL Server 2005)
    Wednesday, August 31, 2011 10:40 AM
  • Cmd.CommandText = "BACKUP DATABASE [" & My.Application.Info.DirectoryPath & "\DB.mdf] TO DISK ='" & StrPath & "BackUp.bak" & "' WITH NOFORMAT, NOINIT, NAME = 'x', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

    Also, your backup syntax is incorrect, you need to specify the database name and not the path of .mdf file:

    BACKUP DATABASE ["Database Name Here"] TO DISK ='" & StrPath & "BackUp.bak" & "' WITH NOFORMAT, NOINIT, NAME = 'x', SKIP, NOREWIND, NOUNLOAD, STATS = 10


    - Vishal

    SqlAndMe.com

    Wednesday, August 31, 2011 10:53 AM
  • Hi,

    The First error message says that your database is in use. check that any user uses the the database. Also, you haven't mentioned your connection string may you are using the database if this is the case then connect to master database. if this resolves then see below restore command to move database files.

    RESTORE DATABASE AdventureWorks2008R2
     FROM AdventureWorks2008R2Backups
     WITH NORECOVERY, 
      MOVE 'AdventureWorks2008R2_Data' TO 
    'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\NewAdvWorks2008R2.mdf', 
      MOVE 'AdventureWorks2008R2_Log' 
    TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\NewAdvWorks2008R2.ldf'
    RESTORE LOG AdventureWorks2008R2
     FROM AdventureWorks2008R2Backups
     WITH RECOVERY
    

    Also check restore command syntax on http://msdn.microsoft.com/en-us/library/ms186858.aspx

     

    Regards,

     

    Balwant.


    Failure in Life is failure to try... PGDCA-98(South Gujarat University),MCTS (SQL Server 2005)
    But when I want to Create backup I have a file name is BackUp.bak But I do not have to Restore the log file please help me
    Name of Allah, Most Gracious, Most Merciful and He created the human
    Wednesday, August 31, 2011 10:59 AM
  • I want to create a connection for recovery.
    How do I Create it?


    Name of Allah, Most Gracious, Most Merciful and He created the human
    Wednesday, August 31, 2011 11:20 AM
  • Cmd.CommandText = "BACKUP DATABASE [" & My.Application.Info.DirectoryPath & "\DB.mdf] TO DISK ='" & StrPath & "BackUp.bak" & "' WITH NOFORMAT, NOINIT, NAME = 'x', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

    Also, your backup syntax is incorrect, you need to specify the database name and not the path of .mdf file:

    BACKUP DATABASE ["Database Name Here"] TO DISK ='" & StrPath & "BackUp.bak" & "' WITH NOFORMAT, NOINIT, NAME = 'x', SKIP, NOREWIND, NOUNLOAD, STATS = 10


    - Vishal

    SqlAndMe.com


    i change my code but

    BACKUP DATABASE is terminating abnormally 


    Name of Allah, Most Gracious, Most Merciful and He created the human
    Wednesday, August 31, 2011 11:23 AM
  • Hi Sh2007,

    Based on your description, I have demonstrated the code below:

    'please replace the Data Source(InstanceName) and database name (ReportServerTempDB)
    
    Imports System.Data.SqlClient
    
    Public Class Form1
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim cn As New SqlConnection("Data Source=InstanceName; Initial Catalog=master; Integrated Security=True")
            Dim Cmd As New SqlClient.SqlCommand(Nothing, cn)
            Dim StrPath As String
            StrPath = "G:\"
    
            If cn.State = ConnectionState.Closed Then cn.Open()
    
            'backup 
            If rbtnBack.Checked = True Then
                Cmd.CommandText = "BACKUP DATABASE [ReportServerTempDB] TO DISK ='" & StrPath & "BackUp.bak" & "' WITH NOFORMAT, NOINIT, NAME = 'x', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
                Cmd.ExecuteNonQuery()
            End If
    
            'restore 
            If rbtnRestore.Checked = True Then
                ' please make sure that the database has been backed up to:  G:\BackUp.bak
                Cmd.CommandText = "RESTORE DATABASE [ReportServerTempDB] FROM DISK='G:\BackUp.bak' WITH FILE=1, NORECOVERY; "
                Cmd.ExecuteNonQuery()
            End If
    
            cn.Close()
    
        End Sub
    End Class
    
    

     

     


    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    • Marked as answer by Stephanie Lv Wednesday, September 07, 2011 1:04 PM
    Friday, September 02, 2011 12:44 PM
  • Hi Sh2007,

    Based on your description, I have demonstrated the code below:

    'please replace the Data Source(InstanceName) and database name (ReportServerTempDB)
    
    
    
    Imports System.Data.SqlClient
    
    
    
    Public Class Form1
    
    
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    
            Dim cn As New SqlConnection("Data Source=InstanceName; Initial Catalog=master; Integrated Security=True")
    
            Dim Cmd As New SqlClient.SqlCommand(Nothing, cn)
    
            Dim StrPath As String
    
            StrPath = "G:\"
    
    
    
            If cn.State = ConnectionState.Closed Then cn.Open()
    
    
    
            'backup 
    
            If rbtnBack.Checked = True Then
    
                Cmd.CommandText = "BACKUP DATABASE [ReportServerTempDB] TO DISK ='" & StrPath & "BackUp.bak" & "' WITH NOFORMAT, NOINIT, NAME = 'x', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
    
                Cmd.ExecuteNonQuery()
    
            End If
    
    
    
            'restore 
    
            If rbtnRestore.Checked = True Then
    
                ' please make sure that the database has been backed up to:  G:\BackUp.bak
    
                Cmd.CommandText = "RESTORE DATABASE [ReportServerTempDB] FROM DISK='G:\BackUp.bak' WITH FILE=1, NORECOVERY; "
    
                Cmd.ExecuteNonQuery()
    
            End If
    
    
    
            cn.Close()
    
    
    
        End Sub
    
    End Class
    
    
    
    

     

     


    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    thanks all

    but i see the error:

    Cannot open database "MbSystem" requested by the login. The login failed.
    Login failed for user 'Shahab-PC\Shahab'.

     

    Why I can not open databse?

    I design my database in the  VS 2010.

    please help me

     

     

     


    Name of Allah, Most Gracious, Most Merciful and He created the human
    Thursday, September 08, 2011 11:02 AM
  • Hi Sh2007,

    Please make sure that the windows account ‘Shahab-pc\Shahab’ has been mapped as a login to the SQL Server and has permission to backup and restore. Please run the following syntax on the resource SQL Server by SQL Server Manage Studio and start the project again:
    USE master 
    CREATE LOGIN [Shahab-pc\Shahab] FROM WINDOWS WITH DEFAULT_DATABASE=[DB_Permission]
    GO
    
    use MbSystem
    CREATE USER [Shahab-pc\Shahab] FOR LOGIN [Shahab-pc\Shahab] WITH DEFAULT_SCHEMA=[dbo]
    GO
    
    EXEC sp_addrolemember 'db_owner ', 'Shahab-pc\Shahab'
    GO
    
    
    


    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    Thursday, September 08, 2011 12:25 PM
  • Hi Sh2007,

    Please make sure that the windows account ‘Shahab-pc\Shahab’ has been mapped as a login to the SQL Server and has permission to backup and restore. Please run the following syntax on the resource SQL Server by SQL Server Manage Studio and start the project again:
    USE master 
    
    CREATE LOGIN [Shahab-pc\Shahab] FROM WINDOWS WITH DEFAULT_DATABASE=[DB_Permission]
    
    GO
    
    
    
    use MbSystem
    
    CREATE USER [Shahab-pc\Shahab] FOR LOGIN [Shahab-pc\Shahab] WITH DEFAULT_SCHEMA=[dbo]
    
    GO
    
    
    
    EXEC sp_addrolemember 'db_owner ', 'Shahab-pc\Shahab'
    
    GO
    
    
    
    
    
    


    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    thanks Stephanie Lv

    I'm not fluent;

    Please explain step by step

    For example:

    Where do I enter this code:

    USE master

    CREATE LOGIN [Shahab-pc\Shahab] FROM WINDOWS WITH DEFAULT_DATABASE=[DB_Permission]

    GO

    Which of the menu SQL Server Management Studio?

     

     

     

     

     


    Name of Allah, Most Gracious, Most Merciful and He created the human
    Friday, September 09, 2011 6:31 AM