备份的代码如下: //开始备份 with adoquery1 do begin close; sql.Clear; sql.Add('Backup database library to disk=:p1 with init'); parameters.ParamByName('p1').Value:=self.SaveDialog1.FileName; try Execsql; ShowMessage('备份成功!!'); except ShowMessage('备份失败!!'); exit; end; end; //End of 备份 下面来说一下还原,还原与备份不一样,备份不需要关闭数据库,但还原就要先关闭数据库才行.第一次写的代码如下: with ADOQuery1 do begin Close; SQL.Clear; SQL.Add('use master alter database library set offline WITH ROLLBACK IMMEDIATE '); SQL.Add('restore database library from disk=:p1 with REPLACE'); SQL.Add('alter database library set online with rollback immediate'); parameters.ParamByName('p1').Value := self.Edit1.Text; try ExecSQL; ShowMessage('还原成功!!'); except Showmessage('还原失败!!'); exit; end; end; 运行,还原成功!!但是數據庫會變爲脫機狀態,導致其他用戶不能連接。解決的辦法有两种: 一:用存储过程: 在master数据库中加入下面的存储过程if exists(select name from sysobjects where name='usp_restoredb') drop proc usp_restoredb go /* exec usp_restoredb "library","D:\dbbak.bak" */ create proc usp_restoredb ( @dbname varchar(255), --数据库名字 @filepath varchar(255) --文件路径 ) as set nocount on --exec ('use master go') exec ('alter database '+@dbname+' set offline WITH ROLLBACK IMMEDIATE') exec ('restore database '+@dbname+' from disk="'+@filepath+'" with REPLACE') exec ('alter database '+@dbname+' set online with rollback IMMEDIATE') if @@error<>0 begin select 'F','数据库恢复失败' return end else begin select 'T','数据库恢复成功' return end运行. 然后在Delphi的调用这一存储过程,调用过程如下://注意:这时的ADOQuery1要连到master数据库,不能连到library数据库!! with ADOQuery1 do Begin Close; SQL.Clear; // filename是你备份文件的路径加文件名 SQL.Add('exec usp_restoredb "Library","'+ filename+'"'); Open; if ADOQuery1.Fields[0].AsString='F' then ShowMessage('还原失败!!') else ShowMessage('还原成功!!'); end; 运行成功!且library数据库不会变为脱机! 第二种方法: 也是运行ADOQuery1来实现,不用存储过程,ADOQuery1一定要连到master数据库,否则就会让library脱机!,代码如下:begin DataModule17.ADOConnection1.Close;//这个是连到library数据库的,所以要先断开 with ADOQuery1 do begin Close; SQL.Clear; SQL.Add('use master alter database library set offline WITH ROLLBACK IMMEDIATE '); SQL.Add('restore database library from disk=:p1 with REPLACE'); SQL.Add('alter database library set online with rollback immediate'); parameters.ParamByName('p1').Value := self.Edit1.Text; //显示备份文件的路径和文件名 try ExecSQL; ShowMessage('还原成功!!'); except Showmessage('还原失败!!'); exit; end; end; end; //End Of 还原数据库 运行成功!library数据库被还原
Var AFile,ABackSql,ARestoreSql:String;Begin AFile:='c:\DataBase.bak'; //恢复语句 //ARestoreSql := RESTORE FILELISTONLY FROM DISK = 'c:\Northwind.bak' 备份 ABackSql:= format('BACKUP DATABASE Northwind TO DISK =%s',[QuotedStr(AFile)]); With Query1 do begin Close; Sql.Clear; Sql.Add(ABackSql); execSql; end;
多看看sql server的联机帮助,里面写的很清楚
备份数据库:procedure TFrm_sqlHuanyuan.Button1Click(Sender: TObject); var sqlstr :string; begin Screen.cursor:= crHourGlass; //备份数据 sqlstr :=' backup database NewRtu ' ; sqlstr :=sqlstr+' to disk= '+QuotedStr(Edit1.text); ADOCommand1.CommandText:=sqlstr; ADOCommand1.Execute; Screen.cursor:= crdefault; showmessage('备份数据库成功'); end;
//开始备份 with adoquery1 do
begin
close;
sql.Clear;
sql.Add('Backup database library to disk=:p1 with init');
parameters.ParamByName('p1').Value:=self.SaveDialog1.FileName;
try
Execsql;
ShowMessage('备份成功!!');
except
ShowMessage('备份失败!!');
exit;
end;
end; //End of 备份 下面来说一下还原,还原与备份不一样,备份不需要关闭数据库,但还原就要先关闭数据库才行.第一次写的代码如下: with ADOQuery1 do
begin
Close;
SQL.Clear; SQL.Add('use master alter database library set offline WITH ROLLBACK IMMEDIATE ');
SQL.Add('restore database library from disk=:p1 with REPLACE');
SQL.Add('alter database library set online with rollback immediate');
parameters.ParamByName('p1').Value := self.Edit1.Text;
try
ExecSQL;
ShowMessage('还原成功!!');
except
Showmessage('还原失败!!');
exit;
end;
end;
运行,还原成功!!但是數據庫會變爲脫機狀態,導致其他用戶不能連接。解決的辦法有两种: 一:用存储过程: 在master数据库中加入下面的存储过程if exists(select name from sysobjects where name='usp_restoredb')
drop proc usp_restoredb
go
/*
exec usp_restoredb "library","D:\dbbak.bak"
*/
create proc usp_restoredb
(
@dbname varchar(255), --数据库名字
@filepath varchar(255) --文件路径
)
as
set nocount on
--exec ('use master go')
exec ('alter database '+@dbname+' set offline WITH ROLLBACK IMMEDIATE')
exec ('restore database '+@dbname+' from disk="'+@filepath+'" with REPLACE')
exec ('alter database '+@dbname+' set online with rollback IMMEDIATE')
if @@error<>0
begin
select 'F','数据库恢复失败'
return
end
else
begin
select 'T','数据库恢复成功'
return
end运行. 然后在Delphi的调用这一存储过程,调用过程如下://注意:这时的ADOQuery1要连到master数据库,不能连到library数据库!! with ADOQuery1 do
Begin
Close;
SQL.Clear; // filename是你备份文件的路径加文件名
SQL.Add('exec usp_restoredb "Library","'+ filename+'"');
Open;
if ADOQuery1.Fields[0].AsString='F' then
ShowMessage('还原失败!!')
else
ShowMessage('还原成功!!');
end; 运行成功!且library数据库不会变为脱机! 第二种方法: 也是运行ADOQuery1来实现,不用存储过程,ADOQuery1一定要连到master数据库,否则就会让library脱机!,代码如下:begin
DataModule17.ADOConnection1.Close;//这个是连到library数据库的,所以要先断开
with ADOQuery1 do
begin
Close;
SQL.Clear; SQL.Add('use master alter database library set offline WITH ROLLBACK IMMEDIATE ');
SQL.Add('restore database library from disk=:p1 with REPLACE');
SQL.Add('alter database library set online with rollback immediate');
parameters.ParamByName('p1').Value := self.Edit1.Text; //显示备份文件的路径和文件名
try
ExecSQL;
ShowMessage('还原成功!!');
except
Showmessage('还原失败!!');
exit;
end;
end;
end; //End Of 还原数据库 运行成功!library数据库被还原
AFile,ABackSql,ARestoreSql:String;Begin
AFile:='c:\DataBase.bak';
//恢复语句
//ARestoreSql := RESTORE FILELISTONLY FROM DISK = 'c:\Northwind.bak'
备份
ABackSql:= format('BACKUP DATABASE Northwind TO DISK =%s',[QuotedStr(AFile)]);
With Query1 do
begin
Close;
Sql.Clear;
Sql.Add(ABackSql);
execSql;
end;
var sqlstr :string;
begin
Screen.cursor:= crHourGlass;
//备份数据
sqlstr :=' backup database NewRtu ' ;
sqlstr :=sqlstr+' to disk= '+QuotedStr(Edit1.text);
ADOCommand1.CommandText:=sqlstr;
ADOCommand1.Execute;
Screen.cursor:= crdefault;
showmessage('备份数据库成功');
end;
我做的怎么总是出错呢?
为什么用到 parameters.ParamByName('p1').Value:=self.SaveDialog1.FileName;
P1 代表着什么?sqlstr :=sqlstr+' to disk= '+QuotedStr(Edit1.text);
这个里的edit1.text 应该填什么啊?
我有点笨 呵呵 现在有点转不过来 帮忙在弄弄
P1 代表着什么?
-----------------P1是代表參數
sqlstr :=sqlstr+' to disk= '+QuotedStr(Edit1.text);
这个里的edit1.text 应该填什么啊?
---------------------------
輸入文件路徑,也可以這樣寫parameters.ParamByName('p1').Value:=self.opendialog1.FileName;