使用SSIS達到自動還原資料庫
控制流程
變數說明
DBServer:伺服器名稱。
DBName:使用者資料庫名稱。
FileDateFormat:指定還原日期。
SystemDBDir:系統資料庫備份檔案路徑。
RestoreMasterDB:是否還原Master資料庫。
RestoreOtherSystemDB:是否還原其他系統資料庫。
UserDBDir:使用者資料庫備份路徑。
UserDBLogDir:使用者資料庫交易紀錄檔備份路徑。
SQLstatement:restoresql。
備份檔案格式: 資料庫名稱_備份類型_yyyy-MM-dd HHmmss.bak
DB Restore指令碼
程式碼大概就是判斷要還原Master或還原其他資料庫(含msdb、model)
還原資料庫要考慮資料庫備份檔案以下4種情況
1.only full bak file
2.have full and diff bak file
3.have full and log bak file
4.hava full and diff and log bak file
StringBuilder restoresql = new StringBuilder();
string GetFileDateFormat = Dts.Variables["FileDateFormat"].Value.ToString();
if (string.IsNullOrEmpty(GetFileDateFormat))
{
Dts.Log("FileDateFormat is null", 0, null);
Dts.TaskResult = (int)ScriptResults.Failure;
return;
}
string assemblyFolder = Directory.GetCurrentDirectory();
if (string.IsNullOrEmpty(assemblyFolder))
assemblyFolder = Directory.GetCurrentDirectory();
//master database
SortedDictionary<string, string> SystemDBlists = new SortedDictionary<string, string>();
string SystemDBbackupfolder = Dts.Variables["SystemDBDir"].Value.ToString();
int RestoreMasterDB = int.Parse(Dts.Variables["RestoreMasterDB"].Value.ToString());
if (RestoreMasterDB == 1 && string.IsNullOrEmpty(SystemDBbackupfolder))
{
Dts.Log("SystemDBDir is null", 0, null);
Dts.TaskResult = (int)ScriptResults.Failure;
return;
}
if (RestoreMasterDB == 1 && !string.IsNullOrEmpty(SystemDBbackupfolder))
{
DirectoryInfo SystemDBdir = new DirectoryInfo(SystemDBbackupfolder);
foreach (FileInfo file in SystemDBdir.GetFiles())
{
if (file.Name.IndexOf(GetFileDateFormat) != -1 && file.Name.ToUpper().IndexOf("FULL") != -1)
{
int charidx = file.Name.IndexOf("_");
string dbname = file.Name.Substring(0, charidx);
if (!string.IsNullOrEmpty(dbname))
{
if (dbname.ToLower().Trim() == "master" )
{
SystemDBlists.Add(dbname, file.FullName);
}
}
}
}
if (SystemDBlists.Count > 0)
{
if (SystemDBlists.ContainsKey("master"))
{
restoresql.AppendLine(RestoreDBrecovery("master", SystemDBlists["master"]));
}
Dts.Variables["SQLStatement"].Value = restoresql.ToString();
File.WriteAllText(Path.Combine(assemblyFolder, "sqltaskstatement.log"), restoresql.ToString(), Encoding.UTF8);
Dts.Log("Successfully set variables for systemDB Full", 0, null);
Dts.TaskResult = (int)ScriptResults.Success;
return;
}
}
//msdb model database
int RestoreothersystemDB = int.Parse(Dts.Variables["RestoreOtherSystemDB"].Value.ToString());
if (RestoreMasterDB == 0 && RestoreothersystemDB==1 && !string.IsNullOrEmpty(SystemDBbackupfolder))
{
DirectoryInfo SystemDBdir = new DirectoryInfo(SystemDBbackupfolder);
foreach (FileInfo file in SystemDBdir.GetFiles())
{
if (file.Name.IndexOf(GetFileDateFormat) != -1 && file.Name.ToUpper().IndexOf("FULL") != -1)
{
int charidx = file.Name.IndexOf("_");
string dbname = file.Name.Substring(0, charidx);
if (!string.IsNullOrEmpty(dbname))
{
if (dbname.ToLower().Trim() == "model" || dbname.ToLower().Trim() == "msdb")
{
restoresql.AppendLine(RestoreDBrecovery(dbname, file.FullName));
}
}
}
}
}
/*
1.only full bak file
2.have full and diff bak file
3.have full and log bak file
4.hava full and diff and log bak file
*/
//UserDB
bool haslog = false;
bool hasdiff = false;
bool onlyfull = false;
string UserDBName = Dts.Variables["DBName"].Value.ToString();
if (string.IsNullOrEmpty(UserDBName))
{
Dts.Log("DBName is null", 0, null);
Dts.TaskResult = (int)ScriptResults.Failure;
return;
}
string UserDBDirbackupfolder = Dts.Variables["UserDBDir"].Value.ToString();
if (string.IsNullOrEmpty(UserDBDirbackupfolder))
{
Dts.Log("UserDBDir is null", 0, null);
Dts.TaskResult = (int)ScriptResults.Failure;
return;
}
string UserDBLogDirbackupfolder = Dts.Variables["UserDBLogDir"].Value.ToString();
if (string.IsNullOrEmpty(UserDBLogDirbackupfolder))
{
Dts.Log("UserDBLogDir is null", 0, null);
Dts.TaskResult = (int)ScriptResults.Failure;
return;
}
string tmpdbname = "";
DirectoryInfo UserDBLogDir = new DirectoryInfo(UserDBLogDirbackupfolder);
foreach (FileInfo file in UserDBLogDir.GetFiles())
{
if (file.Name.IndexOf(GetFileDateFormat) != -1 && file.Name.ToUpper().IndexOf("LOG") != -1)
{
int charidx = file.Name.IndexOf("_");
tmpdbname = file.Name.Substring(0, charidx);
if (!string.IsNullOrEmpty(tmpdbname) && tmpdbname.Trim().ToLower() == UserDBName.Trim().ToLower())
{
haslog = true;
break;
}
}
}
string DiffFileLastTime = "";
DirectoryInfo UserDBDir = new DirectoryInfo(UserDBDirbackupfolder);
foreach (FileInfo file in UserDBDir.GetFiles())
{
if (file.Name.IndexOf(GetFileDateFormat) != -1 && file.Name.ToUpper().IndexOf("DIFF") != -1)
{
int charidx = file.Name.IndexOf("_");
tmpdbname = file.Name.Substring(0, charidx);
if (!string.IsNullOrEmpty(tmpdbname) && tmpdbname.Trim().ToLower() == UserDBName.Trim().ToLower())
{
hasdiff = true;
}
}
if (file.Name.IndexOf(GetFileDateFormat) != -1 && file.Name.ToUpper().IndexOf("FULL") != -1)
{
int charidx = file.Name.IndexOf("_");
tmpdbname = file.Name.Substring(0, charidx);
if (!string.IsNullOrEmpty(tmpdbname) && tmpdbname.Trim().ToLower() == UserDBName.Trim().ToLower())
{
DiffFileLastTime = file.CreationTime.ToString("yyyyMMddHHmmss");
if (hasdiff || haslog)
restoresql.AppendLine(RestoreDBNorecovery(UserDBName, file.FullName));
else
{
restoresql.AppendLine(RestoreDBrecovery(UserDBName, file.FullName));
onlyfull = true;
}
}
}
}
if (string.IsNullOrEmpty(UserDBName))
{
Dts.Log("UserDBName is null", 0, null);
Dts.TaskResult = (int)ScriptResults.Failure;
return;
}
if (!onlyfull)
{
SortedDictionary<string, string> DIFFfilelists = new SortedDictionary<string, string>();
foreach (FileInfo file in UserDBDir.GetFiles())
{
if (file.Name.IndexOf(GetFileDateFormat) != -1 && file.Name.ToUpper().IndexOf("DIFF") != -1)
{
int charidx = file.Name.IndexOf("_");
tmpdbname = file.Name.Substring(0, charidx);
if (!string.IsNullOrEmpty(tmpdbname) && tmpdbname.Trim().ToLower() == UserDBName.Trim().ToLower())
{
DIFFfilelists.Add(file.CreationTime.ToString("yyyyMMddHHmmss"), file.FullName);
}
}
}
if (string.IsNullOrEmpty(DiffFileLastTime))
{
Dts.Log("DiffFileLastTime is null", 0, null);
Dts.TaskResult = (int)ScriptResults.Failure;
return;
}
if (DIFFfilelists.Count > 0 && !string.IsNullOrEmpty(UserDBName))
{
foreach (string key in DIFFfilelists.Keys)
{
DiffFileLastTime = key;
}
string diffvalue = DIFFfilelists[DiffFileLastTime];
if (haslog)
restoresql.AppendLine(RestoreDBNorecovery(UserDBName, diffvalue));
else
restoresql.AppendLine(RestoreDBrecovery(UserDBName, diffvalue));
}
if (haslog)
{
SortedDictionary<string, string> Logfilelists = new SortedDictionary<string, string>();
foreach (FileInfo file in UserDBLogDir.GetFiles())
{
if (file.Name.IndexOf(GetFileDateFormat) != -1 && file.Name.ToUpper().IndexOf("LOG") != -1)
{
int charidx = file.Name.IndexOf("_");
tmpdbname = file.Name.Substring(0, charidx);
if (!string.IsNullOrEmpty(tmpdbname) && tmpdbname.Trim().ToLower() == UserDBName.Trim().ToLower())
{
string logfiletime = file.CreationTime.ToString("yyyyMMddHHmmss");
if (Int64.Parse(logfiletime) > Int64.Parse(DiffFileLastTime))
{
Logfilelists.Add(logfiletime, file.FullName);
}
}
}
}
int logcount = 1;
if (Logfilelists.Count > 0 && !string.IsNullOrEmpty(UserDBName))
{
foreach (var item in Logfilelists.Keys)
{
if (logcount == Logfilelists.Count)
{
restoresql.AppendLine(RestoreLogrecovery(UserDBName, Logfilelists[item]));
}
else
{
restoresql.AppendLine(RestoreLogNorecovery(UserDBName, Logfilelists[item]));
}
logcount++;
}
}
}
}
Dts.Variables["SQLStatement"].Value = restoresql.ToString();
File.WriteAllText(Path.Combine(assemblyFolder,"sqltaskstatement.log"), restoresql.ToString(), Encoding.UTF8);
Dts.Log("Successfully set variables for Full or Differential distinction: UserDB=" + UserDBName , 0,null);
Dts.TaskResult = (int)ScriptResults.Success;
執行SQL工作的連接管理員設定動態連線字串
選擇connectionstring
設定變數DBServer
還原master
啟動SQL Server獨佔服務模式
net stop "SQLSERVERAGENT"
net stop "MSSQLSERVER" /Y
net start "MSSQLSERVER" /m
執行SSIS
"C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /FILE "D:\Package.dtsx" /CHECKPOINTING OFF /SET "\Package.Variables[User::FileDateFormat].Properties[Value]";"2016-04-19" /SET "\Package.Variables[User::SystemDBDir].Properties[Value]";"D:\testbackup" /SET "\Package.Variables[User::UserDBDir].Properties[Value]";"D:\testbackup" /SET "\Package.Variables[User::UserDBLogDir].Properties[Value]";"D:\testbackup" /SET "\Package.Variables[User::DBServer].Properties[Value]";"myserver" /SET "\Package.Variables[User::DBName].Properties[Value]";"mydb" /SET "\Package.Variables[User::RestoreMasterDB].Properties[Value]";"1" /SET "\Package.Variables[User::RestoreOtherSystemDB].Properties[Value]";"0"
還原使用者資料庫(含msdb、model)
正常啟動SQL Server服務
net stop "SQLSERVERAGENT"
net stop "MSSQLSERVER" /Y
net start "MSSQLSERVER"
執行SSIS
"C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /FILE "D:\Package.dtsx" /CHECKPOINTING OFF /SET "\Package.Variables[User::FileDateFormat].Properties[Value]";"2016-04-19" /SET "\Package.Variables[User::SystemDBDir].Properties[Value]";"D:\testbackup" /SET "\Package.Variables[User::UserDBDir].Properties[Value]";"D:\testbackup" /SET "\Package.Variables[User::UserDBLogDir].Properties[Value]";"D:\testbackup" /SET "\Package.Variables[User::DBServer].Properties[Value]";"myserver" /SET "\Package.Variables[User::DBName].Properties[Value]";"mydb" /SET "\Package.Variables[User::RestoreMasterDB].Properties[Value]";"0" /SET "\Package.Variables[User::RestoreOtherSystemDB].Properties[Value]";"1"
確認資料庫狀態
select name,user_access_desc,state_desc
from sys.databases
where name in ('master','msdb','model','mydb')
更新資料庫統計值
use [mydb]
EXEC sp_updatestats;
USE [master];
EXEC sp_updatestats;
USE [msdb];
EXEC sp_updatestats;
啟動SQL Agent job Service
net start "SQLSERVERAGENT"
確認所有SQL Agent job執行正常
