July 15, 2003 at 4:04 pm
SOFTWARE:
SQL Server 2000
ASP.Net
VB.Net
SITUATION:
I have created a new instance of my production database for use as a demo database. I plan to use the demo database for one-on-one walkthroughs of my website with potential customers. When I am done with each walkthrough, I want to reset the database back to its orignal state prior to the walkthrough. I thought it would be best to backup the database in its original state. Then, when each walkthrough was finished, I would simply restore the backup to get the database back to its original state.
QUESTIONS:
1. How can I initiate the restoration of the backup using VB.Net (the code behind an ASP.Net command button)?
2. Is this the best approach? Are there any suggestions of a better method to achieve the above objective?
Any help would be much appreciated.
Thanks 🙂
July 15, 2003 at 4:07 pm
I have tried the following code. However, I get an error stating that an exclusive connection could not be obtained. What more do I have to do? Do I need to detach the database first? If so, what is the command?
********************
' Connect to the database to restore. Note: my connection string is identified in my web.config file.
Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
' This is the T-SQL to restore the database. Note: mydbbackup is my backup device.
Dim mySQL as String = "RESTORE DATABASE mydb FROM mydbbackup WITH FILE = 1, RECOVERY"
Dim myCommand As SqlCommand(mySQL, myConnection)
' Execute the command.
myConnection.Open
myCommand.ExecuteNonQuery()
myConnection.Close
********************
Please assist.
Thanks 🙂
July 16, 2003 at 6:26 am
You cannot restore a database whilst you are connected to it. Make sure your connection connects to another database (eg master) with a user that has enough privilege to restore the db.
Far away is close at hand in the images of elsewhere.
Anon.
July 21, 2003 at 5:04 pm
I solved my problem. Here is the method I used:
The following code will enable you to restore a SQL Server 2000 database from a backup device using VB.Net in the code-behind page of an ASP.Net web form. I found this code in an article written by Andy Warren in the Database Journal dated February 26, 2001. The article was located at the following URL: http://www.databasejournal.com/features/mssql/article.php/1479521 .
To use this code, you must add a reference in your VS.Net project to SQLDMO (it is located in the tab entitled "COM"). Additionally, you must import the SQLDMO namespace (i.e. "Imports SQLDMO").
********************
Imports SQLDMO
Private Sub RestoreDB()
' Dim the server and restore objects used for this procedure.
Dim objServer As SQLDMO.SQLServer
Dim objRestore As SQLDMO.Restore
' Create an instance of the server object and connect
' to the server.
objServer = New SQLDMO.SQLServer()
objServer.Connect("SQLServerName", "LoginName", "Password")
' Create an instance of the restore object, set variables and
' execute the restore procedure.
objRestore = New SQLDMO.Restore()
With objRestore
' This is the database to which your backup will be restored.
.Database = "MyDatabaseName"
' Set the restore action to restore the entire database.
.Action = SQLDMO_RESTORE_TYPE.SQLDMORestore_Database
' This will force the restore to overwrite the existing database.
.ReplaceDatabase = True
' This is the name of the backup device that contains the backup
' you wish to restore.
.Devices = "MyBackupDeviceName"
' Execute the restore procedure
.SQLRestore(objServer)
End With
' Clean up objects and disconnect from the server.
objRestore = Nothing
objServer.DisConnect()
objServer = Nothing
End Sub
********************
Please note: If you want to restore your database from a file instead of a backup device, you can replace the ".Devices = "MyBackupDeviceName" code above with the following:
.Files = "PathToYourBackupFile"
I hope the above code will prove useful for those of you, like me, that could not find adequate information on the topic.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply