Thông tin tài liệu:
Khôi phục một cơ sở dữ liệu SQL Server Đôi khi người sử dụng cần để khôi phục lại cơ sở dữ liệu, và một lần nữa, nó là tốt đẹp nếu anh ta không có để đi đến Enterprise Manager để hoàn thành nhiệm vụ này. Điều này thế nào-Để giải thích làm thế nào để sử dụng đối tượng Khôi phục từ SQL-DMO để hoàn thành nhiệm vụ này
Nội dung trích xuất từ tài liệu:
Restore a SQL Server Database 7.3 Restore a SQL Server DatabaseSometimes the user needs to restore a database, and again, it is nice if he doesnt have togo to Enterprise Manager to accomplish this task. This How-To explains how to use theRestore object from SQL-DMO to accomplish this task.Its all well and good to be able to back up and verify a SQL Server database, but whatabout being able to restore the database if necessary? How do you create a dialog box torestore a SQL Server database?TechniqueFor this How-To, you will use the Restore object of the SQL-DMO object model. Youcan see the properties and methods that will be used in Table 7.5. Table 7.5. Properties and Methods of the Restore ObjectProperty/Method DescriptionAction This property allows you to specify what type of backup you want to take place. The choices are found in the SQLDMO.SQLDMO_RESTORE_TYPE namespace and are SQLDMORestore_Database, SQLDMORestore_Files, SQLDMORestore_Log.Database This property allows you to specify the database name to restore to.Devices This property shows which device(s) you are restoring from.ReplaceDatabase This property tells whether to replace the database.SQLRestore This method causes the restore to be executed.Using the objects listed in Table 7.5, you will create a form with options for the user torestore his database.StepsOpen and run the VB.NET -Chapter 7 solution. From the main Windows Form, click onthe command button with the caption How-To 7.3. You will then see the form displayedin Figure 7.8. Figure 7.8. This form restores a SQL Server database.As with How-To 7.2, a user clicks on the SQL Server for which he wants to display thedatabases. He can then choose the database and backup device. From there, the user canclick the Restore button to restore the database. 1. Create a Windows Form. Then place the controls shown in Figure 7.8, with the following properties set as in Table 7.6. Table 7.6. Label, ListBox, DataGrid, and Command Button Controls Property Settings Object Property Setting Label Name Label1 Text SQL Servers ListBox Name lstSQLServers Label Name Label2 Text Databases ListBox Name lstDatabases Label Name Label3 Text Backup Devices ListBox Name lstBackupDevicesCommand Button Name btnBackup Text &BackupLabel Name Label4 Text OptionsPanel Name Panel1CheckBox Name chkReplaceDB Text Replace Database? Checked TrueCommand Button Name btnClose Text &Close2. On the form, add the code in Listing 7.14 to the Load event. This will look familiar from the first How-To. For an examination of the LoadSQLServers routine, check out step 4 in How-To 7.1. Listing 7.14 frmHowTo7_3.vb: Calling the Routine That Loads Available SQL Servers into a List Box Private Sub frmHowTo7_3_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load -- Load up the SQL Servers LoadSQLServers(Me.lstSQLServers) End Sub3. On the lstSQLServers list box, add the code in Listing 7.15 to the SelectedIndexChanged event. This routine calls both GetSQLDatabases, described in step 6 of How-To 7.1, and GetBackupDevices, described in step 4 of How-To 7.2. Listing 7.15 frmHowTo7_3.vb: Populating the lstDatabases and lstBackupDevices List Boxes Private Sub lstSQLServers_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles lstSQLServers.SelectedIndexChanged GetSQLDatabases(Me.lstSQLServers.SelectedItem, Me.lstDatabases) GetBackupDevices(Me.lstSQLServers.SelectedItem, Me.lstBackupDevices) End Sub4. On the btnRestore button, add the code in Listing 7.16 to the Click event. After logging into the server, the Restore object is created and its properties are set from the form. The SQLRestore method is invoked and the connection is closed. Listing 7.16 frmHowTo7_3.vb: Performing the Backup Private Sub btnRestore_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnRestore.Click -- Create a connection to the server Dim osvr As New SQLDMO.SQLServer() osvr.LoginSecure = True osvr.Connect(Me.lstSQLServers.SelectedItem) -- Create the restore object, set the properties from the form, and execute the restore. Dim oRestore As New SQLDMO.Restore() With oRestore .Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database .Database = Me.lstDatabases.SelectedItem .Devices = [ & Me.lstBackupDevices.SelectedItem & ] .ReplaceDatabase = Me.chkReplaceDB.Checked .SQLRestore(osvr) End With -- Disconnect and clean up. osvr.DisConnect() osvr = Nothing oRestore = Nothing MessageBox.Show(Database Restored, Task Completed, _ ...