0

I have an ASP.NET web form which have three buttons Backup, Reset and Restore Database.

When i click BackUp, it start backup of database and create a .bak file. The Reset button truncate all tables. But the Restore database will act as Undo i.e when i click Restore, it will restore the same database again from .bak file.

Currently, when i click restore it says that the Database is in Use and i cannot restore it. For this, I thought to detach the database first and then restore it with same name from .bak file.

My Idea is to connect to master database and from there i will detach my database and then restore it from .bak file. But I am not sure, if it is a good idea or will it work.

I used the following to restore the Database but I want to restore it with same name.

    SQLcon.ConnectionString = "Data Source=" + servername + ";Initial Catalog=master;pwd=" + pwd + ";user=" + user + ";"
    SQLcon.Open()
    Try
       SQLCmd = New SqlCommand("Restore database MyDatabaseNew from disk='E:\MyDatabase.bak' WITH MOVE 'MyDatabase_Data' TO 'D:\DATA\MyDatabase .mdf', MOVE 'MyDatabase_Log' TO 'D:\DATA\MyDatabaseLog.ldf',REPLACE,STATS=10", SQLcon)
        SQLCmd.CommandTimeout = CInt(CommandTimeout)
        SQLCmd.ExecuteNonQuery()
        Response.Write("restore database successfully")

How i will achieve this? Any suggestion will be appreciated.

Abdul
  • 2,002
  • 7
  • 31
  • 65
  • 1
    Your avoiding the error message. If you have active connections to the database you cannot restore or detach. You have to disconnect all connections to the database. – Tak Mar 30 '17 at 17:54

1 Answers1

0

I did the following in master Database and it worked. I am still not sure whether it is a good approach or not.

SET @str = 'ALTER DATABASE [' + @DBName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE EXEC master.dbo.sp_detach_db @dbname = ''' + @DBName + ''''    
exec (@Str)     
Print 'Alter Done'    

SET @StrRes = 'Restore database ' + @DBName +' from disk = ''' + @BkPath +''' WITH   MOVE ''MyDB_Data'' TO '''+ @ResPathData +''' ,  MOVE ''MyDB_Log'' TO '''+ @ResPathLog +''' ,  REPLACE,STATS=10 '  

exec (@StrRes)

Print 'Restore Done'
Abdul
  • 2,002
  • 7
  • 31
  • 65
  • 1
    It shouldn't be necessary to run the `master.dbo.sp_detach_db` part at all. http://stackoverflow.com/questions/1154200/when-restoring-a-backup-how-do-i-disconnect-all-active-connections – Nick.Mc Apr 05 '17 at 06:43
  • you mean if i kill the active connections, it will work ? – Abdul Apr 05 '17 at 07:01
  • 1
    `SET SINGLE USER` should remove all connections except yours and solve the the "Database is in Use" error. You shoudn't need to detach as well. Test your script without the `EXEC master.dbo.sp_detach_db @dbname = ''' + @DBName + ''''` – Nick.Mc Apr 05 '17 at 07:16