新手求帮忙，怎么对数据库进行备份和还原
delphi吧
全部回复
仅看楼主
level 1
灵秀还朴质灬彩霞z 楼主
各位大神，我想对我建立的数据库进行备份和还原，我用ADO进行连接数据库的，数据库是sqlserver2008，请问具体代码怎样啊，怎么弄我都弄不出，求帮忙，谢谢了。
2014年04月01日 12点04分 1
level 7
OOPhappy
这个可以百度，我刚才百度了一下，有答案的。
2014年04月01日 14点04分 2
level 10
wang_80920
try
DataModuleMain.ADOQueryCodeTemp.Close;
DataModuleMain.ADOQueryCodeTemp.SQL.Text := 'Select * from sysfiles';
DataModuleMain.ADOQueryCodeTemp.Open;
DataModuleMain.ADOQueryCodeTemp.First;
while not DataModuleMain.ADOQueryCodeTemp.Eof do
begin
TempStr := Trim(VarToWideStr(DataModuleMain.ADOQueryCodeTemp.FieldByName('FileName').AsVariant));
if FileExists(TempStr) then
begin
if UpperCase(ExtractFileExt(TempStr)) = UpperCase('.mdf') then
begin
SQLMDFFile := TempStr;
end;
if UpperCase(ExtractFileExt(TempStr)) = UpperCase('.ldf') then
begin
SQLLogFile := TempStr;
end;
end;
DataModuleMain.ADOQueryCodeTemp.Next;
end;
DataModuleMain.ADOConnectionCode.Close;
Sleep(100);
Application.ProcessMessages;
G_OutMsg := '';
if G_ConfigerSettings.Settings_SQLUseWinLogin then
DataModuleMain.ADOConnectionCode.ConnectionString := Format(G_SQLWinConnStr,[
'Master',
Utf8ToAnsi(G_ConfigerSettings.Settings_SQLSourceName)
])
else
DataModuleMain.ADOConnectionCode.ConnectionString := Format(G_SQLConnStr,[
'Master',
Utf8ToAnsi(G_ConfigerSettings.Settings_SQLUserName),
Trim(DESWA.DecryStrHexW(Utf8ToAnsi(G_ConfigerSettings.Settings_SQLDenCryptPWD),Utf8ToAnsi(G_ConfigerSettings.Settings_SQLUserName))),
Utf8ToAnsi(G_ConfigerSettings.Settings_SQLSourceName)
]);
DataModuleMain.ADOConnectionCode.Open;
try
LabelProcessTip.Caption := '正在停止 IIS，请稍候...';
Application.ProcessMessages;
WinExecAndWait('IISReset /STOP',SW_HIDE,True,'',60000);
try
LabelProcessTip.Caption := '正在备份“' + FCaptionToTextList.Strings[i] + '”，请稍候...';
Application.ProcessMessages;
WangCommon_Unit.KillSqlConnection(
Utf8ToAnsi(G_ConfigerSettings.Settings_SQLInitialCatalog),
DataModuleMain.ADOConnectionCode);
DataModuleMain.ADOQueryCodeTemp.Close;
//分离
DataModuleMain.ADOQueryCodeTemp.SQL.Text := 'sp_detach_db ' + QuotedStr(Utf8ToAnsi(G_ConfigerSettings.Settings_SQLInitialCatalog));
2014年04月02日 01点04分 3
level 10
wang_80920
try
DataModuleMain.ADOQueryCodeTemp.ExecSQL;
Sleep(500);
ToSaveFileName := SQLMDFFile;
ToBackupFileName := PathForSave + G_CodeSubPath + ExtractFileName(ToSaveFileName);
if FileExists(ToBackupFileName) then
begin
FileSetAttr(ToBackupFileName,faArchive);
end;
if not CopyFileW(PWideChar(ToSaveFileName),PWideChar(ToBackupFileName),False) then
begin
IsHasError := True;
MyWarning('复制“' + ExtractFileName(ToSaveFileName) + '”时发生错误！' + GetLastTrueErrorText,Self);
Break;
end;
FileSetAttr(ToBackupFileName,faArchive or SysUtils.faReadOnly);
ToSaveFileName := SQLLogFile;
ToBackupFileName := PathForSave + G_CodeSubPath + ExtractFileName(ToSaveFileName);
if FileExists(ToBackupFileName) then
begin
FileSetAttr(ToBackupFileName,faArchive);
end;
if CopyFileW(PWideChar(ToSaveFileName),PWideChar(ToBackupFileName),False) then
begin
FileSetAttr(ToBackupFileName,faArchive or SysUtils.faReadOnly);
end;
finally
DataModuleMain.ADOQueryCodeTemp.Close;
//附加
DataModuleMain.ADOQueryCodeTemp.SQL.Text := 'sp_attach_db ' + QuotedStr(Utf8ToAnsi(G_ConfigerSettings.Settings_SQLInitialCatalog)) + ', ' +
QuotedStr(SQLMDFFile) + ', ' + QuotedStr(SQLLogFile);
try
DataModuleMain.ADOQueryCodeTemp.ExecSQL;
except
end;
end;
finally
LabelProcessTip.Caption := '正在启动 IIS，请稍候...';
Application.ProcessMessages;
WinExecAndWait('IISReset /START',SW_HIDE,True,'',20000);
LabelProcessTip.Caption := '正在备份“' + FCaptionToTextList.Strings[i] + '”，请稍候...';
Application.ProcessMessages;
end;
except
on E:Exception do
begin
Raise Exception.Create('备份系统数据库发生错误：' + E.Message + ConCR + '可能是数据库正在被其他程序使用，请关闭所有客户端再试。')
end;
end;
finally
DataModuleMain.ADOConnectionCode.Close;
if G_ConfigerSettings.Settings_SQLUseWinLogin then
DataModuleMain.ADOConnectionCode.ConnectionString := Format(G_SQLWinConnStr,[
Utf8ToAnsi(G_ConfigerSettings.Settings_SQLInitialCatalog),
Utf8ToAnsi(G_ConfigerSettings.Settings_SQLSourceName)
])
else
DataModuleMain.ADOConnectionCode.ConnectionString := Format(G_SQLConnStr,[
Utf8ToAnsi(G_ConfigerSettings.Settings_SQLInitialCatalog),
Utf8ToAnsi(G_ConfigerSettings.Settings_SQLUserName),
Trim(DESWA.DecryStrHexW(Utf8ToAnsi(G_ConfigerSettings.Settings_SQLDenCryptPWD),Utf8ToAnsi(G_ConfigerSettings.Settings_SQLUserName))),
Utf8ToAnsi(G_ConfigerSettings.Settings_SQLSourceName)
]);
DataModuleMain.ADOConnectionCode.Open;
end;
2014年04月02日 01点04分 4
level 10
wang_80920
DataModuleMain.ADOQueryCodeTemp.Close;
DataModuleMain.ADOQueryCodeTemp.SQL.Text := 'Select * from sysfiles';
DataModuleMain.ADOQueryCodeTemp.Open;
DataModuleMain.ADOQueryCodeTemp.First;
while not DataModuleMain.ADOQueryCodeTemp.Eof do
begin
TempStr := Trim(VarToWideStr(DataModuleMain.ADOQueryCodeTemp.FieldByName('FileName').AsVariant));
if FileExists(TempStr) then
begin
if UpperCase(ExtractFileExt(TempStr)) = UpperCase('.mdf') then
begin
SQLMDFFile := TempStr;
end;
if UpperCase(ExtractFileExt(TempStr)) = UpperCase('.ldf') then
begin
SQLLogFile := TempStr;
end;
end;
DataModuleMain.ADOQueryCodeTemp.Next;
end;
DataModuleMain.ADOConnectionCode.Close;
Sleep(100);
Application.ProcessMessages;
G_OutMsg := '';
ToSaveFileName := SQLMDFFile;
ToRestoreFileName := PathForLoad + G_CodeSubPath + ExtractFileName(ToSaveFileName);
if FileExists(ToRestoreFileName) then
begin
ToSaveFileName := SQLLogFile;
ToRestoreFileName := PathForLoad + G_CodeSubPath + ExtractFileName(ToSaveFileName);
if FileExists(ToRestoreFileName) then
begin
try
if G_ConfigerSettings.Settings_SQLUseWinLogin then
DataModuleMain.ADOConnectionCode.ConnectionString := Format(G_SQLWinConnStr,[
'Master',
Utf8ToAnsi(G_ConfigerSettings.Settings_SQLSourceName)
])
else
DataModuleMain.ADOConnectionCode.ConnectionString := Format(G_SQLConnStr,[
'Master',
Utf8ToAnsi(G_ConfigerSettings.Settings_SQLUserName),
Trim(DESWA.DecryStrHexW(Utf8ToAnsi(G_ConfigerSettings.Settings_SQLDenCryptPWD),Utf8ToAnsi(G_ConfigerSettings.Settings_SQLUserName))),
Utf8ToAnsi(G_ConfigerSettings.Settings_SQLSourceName)
]);
DataModuleMain.ADOConnectionCode.Open;
try
LabelProcessTip.Caption := '正在停止 IIS，请稍候...';
Application.ProcessMessages;
WinExecAndWait('IISReset /STOP',SW_HIDE,True,'',60000);
2014年04月02日 01点04分 5
level 10
wang_80920
try
LabelProcessTip.Caption := '正在恢复“' + lstRestoreChoose.Items.Strings[i] + '”数据，请稍候...';
Application.ProcessMessages;
WangCommon_Unit.KillSqlConnection(
Utf8ToAnsi(G_ConfigerSettings.Settings_SQLInitialCatalog),
DataModuleMain.ADOConnectionCode);
DataModuleMain.ADOQueryCodeTemp.Close;
//分离
DataModuleMain.ADOQueryCodeTemp.SQL.Text := 'sp_detach_db ' + QuotedStr(Utf8ToAnsi(G_ConfigerSettings.Settings_SQLInitialCatalog));
try
DataModuleMain.ADOQueryCodeTemp.ExecSQL;
Application.ProcessMessages;
Sleep(500);
ToSaveFileName := SQLMDFFile;
ToRestoreFileName := PathForLoad + G_CodeSubPath + ExtractFileName(ToSaveFileName);
if FileExists(ToSaveFileName) then
begin
FileSetAttr(ToSaveFileName,faArchive);
end;
if not CopyFileW(PWideChar(ToRestoreFileName),PWideChar(ToSaveFileName),False) then
begin
IsHasError := True;
MyWarning('复制“' + ExtractFileName(ToRestoreFileName) + '”时发生错误！' + GetLastTrueErrorText,Self);
exit;
end;
FileSetAttr(ToSaveFileName,faArchive);
ToSaveFileName := SQLLogFile;
ToRestoreFileName := PathForLoad + G_CodeSubPath + ExtractFileName(ToSaveFileName);
if FileExists(ToSaveFileName) then
begin
FileSetAttr(ToSaveFileName,faArchive);
end;
if CopyFileW(PWideChar(ToRestoreFileName),PWideChar(ToSaveFileName),False) then
begin
FileSetAttr(ToSaveFileName,faArchive);
end;
finally
DataModuleMain.ADOQueryCodeTemp.Close;
//附加
DataModuleMain.ADOQueryCodeTemp.SQL.Text := 'sp_attach_db ' + QuotedStr(Utf8ToAnsi(G_ConfigerSettings.Settings_SQLInitialCatalog)) + ', ' +
QuotedStr(SQLMDFFile) + ', ' + QuotedStr(SQLLogFile);
2014年04月02日 01点04分 6
level 10
wang_80920
try
DataModuleMain.ADOQueryCodeTemp.ExecSQL;
except
end;
BringToFront;
Application.ProcessMessages;
end;
finally
LabelProcessTip.Caption := '正在启动 IIS，请稍候...';
Application.ProcessMessages;
WinExecAndWait('IISReset /START',SW_HIDE,True,'',20000);
LabelProcessTip.Caption := '正在恢复“' + lstRestoreChoose.Items.Strings[i] + '”数据，请稍候...';
Application.ProcessMessages;
end;
except
on E:Exception do
begin
Raise Exception.Create('恢复系统数据库发生错误：' + E.Message + ConCR + '可能是数据库正在被其他程序使用或是存在正在考试的考生，请关闭所有客户端再试。')
end;
end;
finally
DataModuleMain.ADOConnectionCode.Close;
if G_ConfigerSettings.Settings_SQLUseWinLogin then
DataModuleMain.ADOConnectionCode.ConnectionString := Format(G_SQLWinConnStr,[
Utf8ToAnsi(G_ConfigerSettings.Settings_SQLInitialCatalog),
Utf8ToAnsi(G_ConfigerSettings.Settings_SQLSourceName)
])
else
DataModuleMain.ADOConnectionCode.ConnectionString := Format(G_SQLConnStr,[
Utf8ToAnsi(G_ConfigerSettings.Settings_SQLInitialCatalog),
Utf8ToAnsi(G_ConfigerSettings.Settings_SQLUserName),
Trim(DESWA.DecryStrHexW(Utf8ToAnsi(G_ConfigerSettings.Settings_SQLDenCryptPWD),Utf8ToAnsi(G_ConfigerSettings.Settings_SQLUserName))),
Utf8ToAnsi(G_ConfigerSettings.Settings_SQLSourceName)
]);
DataModuleMain.ADOConnectionCode.Open;
end;
end;
end;
2014年04月02日 01点04分 7
level 10
wang_80920
虽然是我5年前写的。
但是我自己都看晕了。
你晕了吗？
2014年04月02日 01点04分 8
ayi033
只实现备份，不要这样麻烦吧，添加一个存储过程，把要备份的库 BACKUP DATABASE 'TABLENAME' TO DISK 'PATH',用AdoStoredProc执行一下就搞定了 还原restore database 'dataname' from disk 'path'
2014年04月02日 02点04分
wang_80920
回复 ayi033 :你就不考虑 数据库正在使用吗？当然，我这个是各种备份中的一部分代码。所以有很多多余的地方。
2014年04月02日 02点04分
ayi033
回复 wang_80920 :备份应该就不用考虑使用状态了，如果是还原，一样在过程中可以查询当前的连接数，要么强制kill的所有连接，要么返回失败，或者不做处理，等待异常。不管考不考虑，这些事一样倒要做。
2014年04月02日 05点04分
灵秀还朴质灬彩霞z
晕了 看得我晕死了 我自己写了一份 但是只能备份 还原不了
2014年04月03日 00点04分
level 10
wang_80920
Utf8ToAnsi 只兼容 D7.建议都换成
Utf8ToString
当然，这主要是我的数据都是 UTF8 的。你的数据不是 UTF8 的就可以省掉这个。
2014年04月02日 01点04分 9
1