public List <RestoreItemSource> GetLatestBackupLocations(string databaseName)
{
List <RestoreItemSource> latestLocations = new List <RestoreItemSource>();
Enumerator en = null;
DataSet ds = new DataSet();
ds.Locale = System.Globalization.CultureInfo.InvariantCulture;
Request req = new Request();
en = new Enumerator();
req.Urn = "Server/BackupSet[@DatabaseName='" + Urn.EscapeString(databaseName) + "']";
req.OrderByList = new OrderBy[1];
req.OrderByList[0] = new OrderBy();
req.OrderByList[0].Field = "BackupFinishDate";
req.OrderByList[0].Dir = OrderBy.Direction.Desc;
try
{
ds = en.Process(this.sqlConnection, req);
if (ds.Tables[0].Rows.Count > 0)
{
string mediaSetID = Convert.ToString(ds.Tables[0].Rows[0]["MediaSetId"], System.Globalization.CultureInfo.InvariantCulture);
ds.Clear();
req = new Request();
req.Urn = "Server/BackupMediaSet[@ID='" + Urn.EscapeString(mediaSetID) + "']/MediaFamily";
ds = en.Process(this.sqlConnection, req);
int count = ds.Tables[0].Rows.Count;
if (count > 0)
{
for (int i = 0; i < count; i++)
{
RestoreItemSource restoreItemSource = new RestoreItemSource();
DeviceType deviceType = (DeviceType)(Convert.ToInt16(ds.Tables[0].Rows[i]["BackupDeviceType"], System.Globalization.CultureInfo.InvariantCulture));
string location = Convert.ToString(ds.Tables[0].Rows[i]["LogicalDeviceName"], System.Globalization.CultureInfo.InvariantCulture);
bool isLogical = (location.Length > 0);
if (false == isLogical)
{
location = Convert.ToString(ds.Tables[0].Rows[i]["PhysicalDeviceName"], System.Globalization.CultureInfo.InvariantCulture);
}
else
{
// We might receive the logical name as "logicaldevicename(physicalpath)"
// We try to get the device name out of it
int pos = location.IndexOf('(');
if (pos > 0)
{
location = location.Substring(0, pos);
}
}
restoreItemSource.RestoreItemDeviceType = deviceType;
restoreItemSource.RestoreItemLocation = location;
restoreItemSource.IsLogicalDevice = isLogical;
latestLocations.Add(restoreItemSource);
}
}
}
}
/// LPU doesn't have rights to enumerate msdb.backupset
catch (Exception)
{
}
return(latestLocations);
}
public int GetLatestBackup(string databaseName, string backupSetName)
{
Enumerator en = new Enumerator();
Request req = new Request();
DataSet backupSets = new DataSet();
backupSets.Locale = System.Globalization.CultureInfo.InvariantCulture;
OrderBy orderByBackupDate;
req.Urn = "Server/BackupSet[@Name='" + Urn.EscapeString(backupSetName) + "' and @DatabaseName='" + Urn.EscapeString(databaseName) + "']";
req.OrderByList = new OrderBy[1];
orderByBackupDate = new OrderBy("BackupFinishDate", OrderBy.Direction.Desc);
req.OrderByList[0] = orderByBackupDate;
backupSets = en.Process(this.sqlConnection, req);
if (backupSets.Tables[0].Rows.Count > 0)
{
return(Convert.ToInt32(backupSets.Tables[0].Rows[0]["Position"], System.Globalization.CultureInfo.InvariantCulture));
}
else
{
return(-1);
}
}
/// <summary>
/// It creates a new ProxyAccount or gets an existing
/// one from JobServer and updates all properties.
/// </summary>
private bool CreateOrUpdateProxyAccount(
AgentProxyInfo proxyInfo,
bool isUpdate)
{
ProxyAccount proxyAccount = null;
if (!isUpdate)
{
proxyAccount = new ProxyAccount(this.DataContainer.Server.JobServer,
proxyInfo.AccountName,
proxyInfo.CredentialName,
proxyInfo.IsEnabled,
proxyInfo.Description);
UpdateProxyAccount(proxyAccount);
proxyAccount.Create();
}
else if (this.DataContainer.Server.JobServer.ProxyAccounts.Contains(this.proxyAccountName))
{
// Try refresh and check again
this.DataContainer.Server.JobServer.ProxyAccounts.Refresh();
if (this.DataContainer.Server.JobServer.ProxyAccounts.Contains(this.proxyAccountName))
{
// fail since account exists and asked to create a new one
if (!isUpdate)
{
return(false);
}
proxyAccount = AgentProxyAccount.GetProxyAccount(this.proxyAccountName, this.DataContainer.Server.JobServer);
// Set the other properties
proxyAccount.CredentialName = proxyInfo.CredentialName;
proxyAccount.Description = proxyInfo.Description;
UpdateProxyAccount(proxyAccount);
proxyAccount.Alter();
// Rename the proxy if needed
// This has to be done after Alter, in order to
// work correcly when scripting this action.
if (this.proxyAccountName != proxyInfo.AccountName)
{
proxyAccount.Rename(proxyInfo.AccountName);
}
}
}
else
{
return(false);
}
return(true);
#if false // @TODO - reenable subsystem code below
// Update the subsystems
foreach (AgentSubSystem subsystem in this.addSubSystems)
{
proxyAccount.AddSubSystem(subsystem);
}
foreach (AgentSubSystem subsystem in this.removeSubSystems)
{
proxyAccount.RemoveSubSystem(subsystem);
// Update jobsteps that use this proxy accunt
// when some subsystems are removed from it
string reassignToProxyName = this.reassignToProxyNames[(int)subsystem];
if (reassignToProxyName != null)
{
// if version is sql 11 and above call SMO API to reassign proxy account
if (Utils.IsSql11OrLater(this.DataContainer.Server.ServerVersion))
{
proxyAccount.Reassign(reassignToProxyName);
}
else
{
// legacy code
// Get a list of all job step objects that use this proxy and this subsystem
Request req = new Request();
req.Urn = string.Format(System.Globalization.CultureInfo.InvariantCulture,
"Server/JobServer/Job/Step[@ProxyName=\'{0}\' and @SubSystem={1}]",
Urn.EscapeString(proxyAccount.Name),
(int)subsystem);
req.Fields = new string[] { "Name" };
req.ParentPropertiesRequests = new PropertiesRequest[1] {
new PropertiesRequest()
};
req.ParentPropertiesRequests[0].Fields = new string[] { "Name" };
Enumerator en = new Enumerator();
DataTable table = en.Process(this.DataContainer.ServerConnection, req);
foreach (DataRow row in table.Rows)
{
// Get the actual job step object using urn
string urnString = string.Format(System.Globalization.CultureInfo.InvariantCulture, "Server/JobServer/Job[@Name=\"{0}\"/Step[@Name=\"{1}\"", row["Job_Name"], row["Name"]);
Urn urn = new Urn(urnString);
JobStep jobStep = (JobStep)this.DataContainer.Server.GetSmoObject(urn);
jobStep.ProxyName = reassignToProxyName;
jobStep.Alter();
}
}
}
}
#endif
}