3. Creating Backup-and-Restore Applications
The most common
application that developers created with SQL-DMO over the years was a
backup-and-restore application—sometimes for customers who do not have
security access to the database and cannot use Enterprise Manager and
sometimes for customers who need remote administration and verification.
These backup-and-restore applications eventually led to the creation of
Web-based tools monitoring. SMO makes it easy to create an application
for backup, verification, and restore. To demonstrate those
capabilities, we will work with a simple backup-and-restore application
created entirely in SMO, as shown in Figure 6.
Backup
This
application allows you to search for servers by reusing the code from
prior examples. To obtain the list of servers, we will use our reusable ListAllKnownInstances() method as well as the FillComboWithDatabases()
method to list all the databases on the server. The application then
allows you to select a backup file—c:\adventureworksbackup.bak, for
example. As shown in Figure 6,
we also have a button in the user interface for the user to run the
backup. Alternatively, this process could be automated in a console
application and a .config file or easily be used from an ASP .NET Web
page.
Once you have a server and a database selected, the code that runs the backup can be run. This code creates a Server and a Backup object and then assigns the Action property to BackupActionType.Database
as the action to take for the backup. (We will look at log files next.)
You have to add the device where you want to back up to (most likely a
file), but you can back up to tape and other standard SQL devices as
well. You can use an Incremental Boolean property to indicate whether to
make only a differential backup, as shown in the following code. You
perform the backup by executing the SqlBackup method, as shown in Listing 7.
Listing 7. Performing a backup programmatically using SMO
private void btnBackup_Click(object sender, EventArgs e)
{
//use the server selected in the Server combo box
//this server can be typed in as well to the Cbo
Server svr = new Server(cboServers.Text.ToString());
Backup bkp = new Backup();
Cursor = Cursors.WaitCursor;
dataGridView1.DataSource = "";
//attempt the backup and show a progress meter
//a lot can go wrong here, from file access
//to DBCC issues, so it is important to handle all
//errors that may arise
try
{
string strFileName = txtFileName.Text.ToString();
string strDatabaseName = cboDatabase.Text.ToString();
bkp.Action = BackupActionType.Database;
bkp.Database = strDatabaseName;
//set the device: File, Tape, etc.
bkp.Devices.AddDevice(strFileName, DeviceType.File);
//set this when you want to do Incremental
bkp.Incremental = chkIncremental.Checked;
//progress meter stuff
progressBar1.Value = 0;
progressBar1.Maximum = 100;
progressBar1.Value = 10;
//this gives us the % complete by handling the event
//provided by SMO on the percent complete; we will
//update the progress meter in the event handler
//set the progress meter to 10% by default
bkp.PercentCompleteNotification = 10;
//call to the event handler to increment the progress meter
bkp.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);
//this does the backup
bkp.SqlBackup(svr);
//alert the user when it is all done
MessageBox.Show("Database Backed Up To: " + strFileName, "SMO Demos");
}
catch (SmoException exSMO)
{
MessageBox.Show(exSMO.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
Cursor = Cursors.Default;
progressBar1.Value = 0;
}
}
|
Progress Meter
You
might notice the progress meter on the bottom of the screen in the
previous example, which displays the progress of the backup being
performed. You can implement this quite easily. The following code shows
the event handler for the backup with the PercentCompleteEventArgs parameter, which contains the percent complete value as an integer between 0 and 100. We set the progress bar’s Value property equal to that value.
public void ProgressEventHandler(object sender, PercentCompleteEventArgs e)
{
//increase the progress bar up by the percent
progressBar1.Value = e.Percent;
}
To get this delegate working, just pass it to the PercentCompleteEventHandler object, as shown in the following code. By default, you can set the PercentCompleteNotification
value to any value you want; we set it to 10 percent. This is the
starting value of your progress meter, so 10 will represent 10 percent
of the progress meter getting filled.
//this gives us the % complete by handling the event
//provided by SMO on the percent complete; we will
//update the progress meter in the event handler
//set the progress meter to 10% by default
bkp.PercentCompleteNotification = 10;
//call to the event handler to increment the progress meter
bkp.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);
Tip
For larger databases,
you might want to set the percentage to a lower value because 10 percent
of a large backup might take a long time. You also want the user to see
that the application is still functioning correctly. Using a smaller
value will cause the progress meter to change more frequently. |
Log Backup
You do back up your log files, don’t you? Backing up a log file requires exactly the same code shown in Listing 6-3 for backing up your database, except that you set the Action property to Log instead of to Database:
bkp.Action = BackupActionType.Log;
Verify
After you perform a backup, you might want to verify that the backup was performed properly and that your backup device
is not corrupt. (Or you might want to verify that your backup device is
not corrupt before you perform the backup.) You can do this by using
the Restore object’s SqlVerify method, as shown in Listing 8. What’s really cool is that SMO gives you back a DataTable filled with all the properties of the backup, and you can bind it to a DataGrid, as shown in Figure 7.
This is quite useful if you want to build a Web page to show the
results of a backup (which you can even check using your cell phone).
Listing 8. Verifying a backup via SMO
private void btnVerify_Click(object sender, EventArgs e)
{
Server svr = new Server();
Restore rest = new Restore();
bool blnVerify;
DataTable dt=new DataTable();
Cursor = Cursors.WaitCursor;
dataGridView1.DataSource = "";
try
{
string strFileName = txtFileName.Text.ToString();
rest.Devices.AddDevice(strFileName, DeviceType.File);
blnVerify = rest.SqlVerify(svr);
if (blnVerify == true)
{
MessageBox.Show("Backup Verified!", "SMO Demos");
dt = rest.ReadFileList(svr);
dataGridView1.DataSource = dt;
}
else
{
MessageBox.Show("Backup NOT Verified!", "SMO Demos");
}
}
catch (SmoException exSMO)
{
MessageBox.Show(exSMO.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
Cursor = Cursors.Default;
}
}
|
Restore
What good is a backup without a restore? As shown in Listing 9, you must instantiate a Server and a Restore
object and provide the name of the backup file (or device) and the name
of the database you are restoring to. Be sure to set the Action property of the Restore object to RestoreActionType.Database to restore your database. If you are going to restore the log, set the Action property of the Restore object to RestoreActionType.Log.
Notice that we are using the same event handler for the Progress meter
as in the backup example, reusing as much code as possible.
Listing 9. Performing a restore with SMO
private void btnRestore_Click(object sender, EventArgs e)
{
Server svr = new Server(cboServers.Text.ToString());
Restore res = new Restore();
Cursor = Cursors.WaitCursor;
dataGridView1.DataSource = "";
try
{
string strFileName = txtFileName.Text.ToString();
string strDatabaseName = cboDatabase.SelectedValue.ToString();
res.Database = strDatabaseName;
res.Action = RestoreActionType.Database;
//use for restore of the log
//res.Action = RestoreActionType.Log;
res.Devices.AddDevice(strFileName, DeviceType.File);
//progress meter stuff
progressBar1.Value = 0;
progressBar1.Maximum = 100;
progressBar1.Value = 10;
res.PercentCompleteNotification = 10;
res.ReplaceDatabase = true;
res.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);
//use below if you want to relocate the mdf files
//res.RelocateFiles.Add(new RelocateFile("aw_data", @"c:\aw_dat.mdf"));
//preform the restore
res.SqlRestore(svr);
MessageBox.Show("Restore of " + strDatabaseName + " Complete!", "SMO Demos");
}
catch (SmoException exSMO)
{
MessageBox.Show(exSMO.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
Cursor = Cursors.Default;
progressBar1.Value = 0;
}
}
|
Restoring to a Different Location
The
SMO backup and restore functionality has many other features, for
example, changing the file location of your restore. SMO allows you to
do this by simply using the RelocateFiles.Add method of the Restore object, as shown here:
res.RelocateFiles.Add(new RelocateFile("adventureworks1_data",
@"c:\adventureworks1_dat.mdf"));
4. Performing Programmatic DBCC Commands with SMO
Another feature worth exploring in SMO is
database integrity checks. You can perform just about all the Database
Consistency Check (DBCC) functions using SMO; Table 3 shows a subset of what is available. (DBCC can do many administrative checks besides integrity checks.)
Table 3. Integrity Checks with SMO
| SMO Method | DBCC Function |
|---|
| CheckAllocations() | DBCC CHECKALLOC |
| CheckAllocationsDataOnly() | DBCC CHECKALLOC(‘databasename’, NOINDEX) |
| CheckCatalog() | DBCC CHECKCATALOG |
| CheckTables() | DBCC CHECKDB |
| CheckTablesDataOnly() | DBCC CHECKDB(‘databasename’, NOINDEX) |
The following simple application uses the DBCC commands shown in Figure 8.
The application allows you to search for servers reusing code from
prior examples and then list all the databases on that server. To obtain
the list of servers, we will use our reusable ListAllKnownInstances() method as well as the FillComboWithDatabases() method to list all the databases on the server. The code hardcodes the CheckCatalog SMO method (which is the DBCC command); however, you can use any of the DBCC commands listed in Table 3 with the exact same code.
The CheckCatalog() method, like all integrity-checking SMO methods, returns a StringCollection object filled with the results. As the code in Listing 10
shows, you must iterate through the results and then take the
appropriate actions. If all is fine in your database, as it is here, you
get the ubiquitous message from DBCC: “DBCC execution completed. If
DBCC printed error messages, contact your system administrator.”
Listing 10. Performing a Database Consistency Check (DBCC) with SMO
private void button1_Click(object sender, EventArgs e)
{
Server svr = new Server(cboServers.Text.ToString());
Database db = svr.Databases[cboDatabase.Text.ToString()]; StringCollection strCol;
strCol = db.CheckCatalog();//can also do most DBCC commands
for (int intCount = 0; intCount < strCol.Count; intCount++)
{
if (strCol[intCount].Length > 0)
label1.Text+=System.Convert.ToString((strCol[intCount]));
}
}
|
