| |
|
|
| Getting Started with SMO in SQL 2005 - Restores |
| Author |
Jasper Smith |
Hits |
4823 |
| Create Date |
30-08-2004 |
Last Updated |
08-08-2007 |
| Versions |
SQL2005 |
|
|
Overview
In this series of articles, I'll demonstrate
how to use SMO (SQL Management Objects) to do a variety of common DBA tasks
including Backups, Restores,Index Maintenance, Integrity checks and more. These
are some of the operations available in the SMO database maintenance utility
available on this site - ExpressMaint. In
this article we will concentrate on how to perform restores using SMO in SQL2005.
For information on how to build and compile a SMO application please review Getting
Started with SMO in SQL 2005.
Database Restores
In order to perform a restore using SMO we
require two objects, a Server object and a Restore object. In its simplest
form, a Restore object requires only a few properties to be set before calling
the
SqlRestore method and passing in the Server object as can be seen in the following
example that does a Full database restore of the SMO database from
the file c:\SMOTest.bak, replacing the database it it already exists. This
example also uses an Event Handler for the PercentComplete event to display
the restore progress.
[Visual Basic]
Imports Microsoft.SqlServer.Management.Smo
Module SMOTest
Sub Main()
Dim svr As Server = New Server()
Dim res As Restore = New Restore()
res.Devices.AddDevice("C:\SMOTest.bak", DeviceType.File)
res.Database = "SMO"
res.ReplaceDatabase = True
res.PercentCompleteNotification = 10
AddHandler res.PercentComplete, AddressOf ProgressEventHandler
res.SqlRestore(svr)
End Sub
Private Sub ProgressEventHandler(ByVal sender As Object, _
ByVal e As PercentCompleteEventArgs)
Console.Writeline(e.Percent.ToString + "% restored")
End Sub
End Module
[C#]
using System;
using Microsoft.SqlServer.Management.Smo;
namespace SMOTest
{
class Program
{
static void Main()
{
Server svr = new Server();
Restore res = new Restore();
res.Database = "SMO";
res.Action = RestoreActionType.Database;
res.Devices.AddDevice(@"C:\SMOTest.bak", DeviceType.File);
res.PercentCompleteNotification = 10;
res.ReplaceDatabase = true;
res.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);
res.SqlRestore(svr);
}
static void ProgressEventHandler(object sender, PercentCompleteEventArgs e)
{
Console.WriteLine(e.Percent.ToString() + "% restored");
}
}
} |
There are other properties that
can be set for the Restore object. For the full list see the Restore Object
in Books On Line (if you have SQL2005 Books On Line installed click
this link to be taken to
the correct page).
Restoring a Database to a New Location
Using SMO, the equivalent of the T-SQL WITH
MOVE syntax for restores is to use the RelocateFiles property of the Restore
Object and the RelocateFile object. In the example below, we will restore a
copy of the SMO database to a database called SMO2 with the data and
log files on the C: drive. The RelocateFile constructor can take two parameters, the first is the logical filename and
the second is the physical filename. This provides the mapping of where to move the files during the restore.
[Visual Basic]
Imports Microsoft.SqlServer.Management.Smo
Module SMOTest
Sub Main()
Dim svr As Server = New Server()
Dim res As Restore = New Restore()
res.Devices.AddDevice("C:\SMOTest.bak", DeviceType.File)
res.Database = "SMO2"
res.NoRecovery = False
res.ReplaceDatabase = True
res.RelocateFiles.Add(New RelocateFile("SMO", "c:\SMO2.mdf"))
res.RelocateFiles.Add(New RelocateFile("SMO_Log", "c:\SMO2.ldf"))
res.SqlRestore(svr)
End Sub
End Module
[C#]
using Microsoft.SqlServer.Management.Smo;
namespace SMOTest
{
class Program
{
static void Main()
{
Server svr = new Server();
Restore res = new Restore();
res.Database = "SMO2";
res.Action = RestoreActionType.Database;
res.Devices.AddDevice(@"C:\SMOTest.bak", DeviceType.File);
res.ReplaceDatabase = true;
res.RelocateFiles.Add(new RelocateFile("SMO", @"c:\SMO2.mdf"));
res.RelocateFiles.Add(new RelocateFile("SMO_Log", @"c:\SMO2.ldf"));
res.SqlRestore(svr);
}
}
} |
Reading Backup File Information
There are a number of methods of the Restore
object that can be used to obtain information about a backup device and the
files it contains including ReadBackupHeader, ReadFileList and ReadMediaHeader.
In the example below, we will
use the ReadFileList method to obtain the list of Logical filenames on the disk
device c:\SMOTest.bak and display them on the console.
[Visual Basic]
Imports Microsoft.SqlServer.Management.Smo
Imports System.Data
Module SMOTest
Sub Main()
Dim svr As Server = New Server()
Dim res As Restore = New Restore()
Dim dt As DataTable
Dim foundrows As DataRow()
res.Devices.AddDevice("C:\SMOTest.bak", DeviceType.File)
dt = res.ReadFileList(svr)
foundrows = dt.Select(Nothing)
For Each dr As DataRow In foundrows
Console.WriteLine(dr("LogicalName").ToString())
Next
End Sub
End Module
[C#]
using System;
using System.Data;
using Microsoft.SqlServer.Management.Smo;
namespace SMOTest
{
class Program
{
static void Main()
{
Server svr = new Server();
Restore res = new Restore();
DataTable dt;
DataRow[] foundrows;
res.Devices.AddDevice(@"C:\SMOTest.bak", DeviceType.File);
dt = res.ReadFileList(svr);
foundrows = dt.Select();
foreach (DataRow r in foundrows)
{
Console.WriteLine(r["LogicalName"].ToString());
}
}
}
} |
Since all these methods return a DataTable,
we can eaily extract all the columns returned by each method by enumerating
the Columns collection of the returned DataTable object as in the VB.NET example
below.
[Visual Basic]
Imports Microsoft.SqlServer.Management.Smo
Imports System.Data
Module SMOTest
Sub Main()
Dim svr As Server = New Server()
Dim res As Restore = New Restore()
Dim dt As DataTable
Dim foundrows As DataRow()
res.Devices.AddDevice("C:\SMOTest.bak", DeviceType.File)
dt = res.ReadFileList(svr)
foundrows = dt.Select(Nothing)
For Each col As DataColumn In dt.Columns
Console.Write(col.ColumnName + vbTab)
Next
Console.WriteLine()
For Each dr As DataRow In foundrows
For Each col As DataColumn In dt.Columns
Console.Write(dr(col.ColumnName).ToString() + vbTab)
Next
Console.WriteLine()
Next
End Sub
End Module |
Log Restores
Log restores are equally straightforward,
simply set the Action property of the Restore object to RestoreActionType.Log.
Additional properties can be set for Log backups such as ToPointInTime which allows recovery
to a specific point in time.
[Visual Basic]
Imports Microsoft.SqlServer.Management.Smo
Module SMOTest
Sub Main()
Dim svr As Server = New Server()
Dim res As Restore = New Restore()
res.Devices.AddDevice("C:\SMOTest.trn", DeviceType.File)
res.Database = "SMO"
res.NoRecovery = False
res.Action = RestoreActionType.Log
res.SqlRestore(svr)
End Sub
End Module
[C#]
using System;
using Microsoft.SqlServer.Management.Smo;
namespace SMOTest
{
class Program
{
static void Main()
{
Server svr = new Server();
Restore res = new Restore();
res.Database = "SMO";
res.Action = RestoreActionType.Log;
res.Devices.AddDevice(@"C:\SMOTest.trn", DeviceType.File);
res.NoRecovery = false;
res.SqlRestore(svr);
}
}
} |
Related Articles
 Getting Started with SMO in SQL 2005
Getting Started with SMO in SQL 2005 - Backups
Getting Started with SMO in SQL 2005 - Integrity Checks
Getting Started with SMO in SQL 2005 - Verifying Backups
|
|
articles
