我单位用的,只有一个要分离的数据库,而且在夜里1-4点间几乎没有数据传送,但白天不行,几乎每时每刻都有数据(医院收费用的)。用备份不太方便,而且我不大懂备份的具体用法。 我还有个想法,如果用另一个有相同数据库的SQL实例,怎么进行数据同步?如在机器“MYSQL”和机器“MYSQL1”上各运行一个SQL SERVER 2000,都有数据库‘MYDATA’,怎么让每时每刻在变的“MYSQL”中的---‘MYDATA’在不忙的时候将数据同步到MYSQL1的‘MYDATA’中去呢?用什么?而且我的机器装的都是MSDE,没有完全安装SQL SERVER 2000,没有企业管理器。只能用SQL语句操作。
procedure TForm1.Timer1Timer(Sender: TObject); var sqlstr,sqlstr2:string; filevar: textfile; buf:string; begin datetimepicker1.dateTime:=now; datetimepicker2.date:=now; datetimepicker3.date:=now; if datetimetostr(datetimepicker1.dateTime)=datetostr(datetimepicker3.date)+' '+timetostr(datetimepicker3.time) then begin try sqlstr2:='select count(*) from master..sysprocesses where hostname>'+#39+#39; sqlstr:='declare hcforeach cursor global for select '+#39+'kill '+#39+'+rtrim(spid) from sysprocesses where dbid=db_id('+#39+'hisdata'+#39+')'+#13+' exec sp_msforeach_worker '+#39+'?'+#39; with adoquery1 do begin Close; sql.Clear; sql.Add(sqlstr); open; end; adoquery1.ex buf:=datetimetostr(datetimepicker1.dateTime)+' 已断开数据库! '; assignfile(filevar,'f:\备份数据库.txt'); append(filevar); writeln(filevar,buf); closefile(filevar); except buf:=datetimetostr(datetimepicker1.dateTime)+' 未断开数据库! '+IntToStr(GetLastError); assignfile(filevar,'f:\备份数据库.txt'); append(filevar); writeln(filevar,buf); closefile(filevar); end; end; if datetimetostr(datetimepicker1.dateTime)=datetostr(datetimepicker1.date)+' '+timetostr(datetimepicker3.time+0.001) then begin try sqlstr:='sp_detach_db @dbname ='+#39+'hisdata'+#39+', @skipchecks =true'; with adoquery1 do begin Close; sql.Clear; sql.Add(sqlstr); open; end; buf:=datetimetostr(datetimepicker1.dateTime)+' 已分离数据库!'; assignfile(filevar,'f:\备份数据库.txt'); append(filevar); writeln(filevar,buf); closefile(filevar); except buf:=datetimetostr(datetimepicker1.dateTime)+' 未分离数据库! '+IntToStr(GetLastError); assignfile(filevar,'f:\备份数据库.txt'); append(filevar); writeln(filevar,buf); closefile(filevar); end; end; if datetimetostr(datetimepicker1.dateTime)=datetostr(datetimepicker1.date)+' '+timetostr(datetimepicker3.time+0.002) then begin if copyfile('D:\hisdata\hisdata.mdf','F:\hisdata.mdf',false) and copyfile('D:\hisData\hisdata_log.ldf','F:\hisdata_log.ldf',false) then begin buf:=datetimetostr(datetimepicker1.dateTime)+' 已复制数据库!'; assignfile(filevar,'f:\备份数据库.txt'); append(filevar); writeln(filevar,buf); closefile(filevar) end else begin buf:=datetimetostr(datetimepicker1.dateTime)+' 未复制数据库! '+IntToStr(GetLastError); assignfile(filevar,'f:\备份数据库.txt'); append(filevar); writeln(filevar,buf); closefile(filevar); end; end; if datetimetostr(datetimepicker1.dateTime)=datetostr(datetimepicker1.date)+' '+timetostr(datetimepicker3.time+0.006) then begin try with adoquery1 do begin Close; sql.Clear; sql.Add('EXEC sp_attach_db @dbname = N'+#39+'hisdata'+#39+',@filename1 = N'+#39+'D:\hisdata\hisdata.mdf'+#39+',@filename2 = N'+#39+'D:\hisData\hisdata_log.ldf'+#39); open; end; buf:=datetimetostr(datetimepicker1.dateTime)+' 已附加数据库!'; assignfile(filevar,'f:\备份数据库.txt'); append(filevar); writeln(filevar,buf); closefile(filevar); except buf:=datetimetostr(datetimepicker1.dateTime)+' 未附加数据库! '+IntToStr(GetLastError); assignfile(filevar,'f:\备份数据库.txt'); append(filevar); writeln(filevar,buf); closefile(filevar); end; end; if datetimetostr(datetimepicker1.dateTime)=datetostr(datetimepicker1.date)+' '+timetostr(datetimepicker3.time+0.0062) then begin if copyfile('F:\hisdata.mdf','j:\hisdata.mdf',false) and copyfile('F:\hisdata_log.ldf','j:\hisdata_log.ldf',false) then begin buf:=datetimetostr(datetimepicker1.dateTime)+' 数据库已复制到BCSVR1!'; assignfile(filevar,'f:\备份数据库.txt'); append(filevar); writeln(filevar,buf); closefile(filevar) end else begin buf:=datetimetostr(datetimepicker1.dateTime)+' 数据库复制到BCSVR1未成功! '+IntToStr(GetLastError); assignfile(filevar,'f:\备份数据库.txt'); append(filevar); writeln(filevar,buf); closefile(filevar); end; if copyfile('F:\hisdata.mdf','K:\hisdata.mdf',false) and copyfile('F:\hisdata_log.ldf','K:\hisdata_log.ldf',false) then begin buf:=datetimetostr(datetimepicker1.dateTime)+' 已复制到K盘数据库!'; assignfile(filevar,'f:\备份数据库.txt'); append(filevar); writeln(filevar,buf); closefile(filevar) end else begin buf:=datetimetostr(datetimepicker1.dateTime)+' 数据库复制到K盘未成功! '+IntToStr(GetLastError); assignfile(filevar,'f:\备份数据库.txt'); append(filevar); writeln(filevar,buf); closefile(filevar); end; if copyfile('D:\yygl1\yygl.mdb','I:\yygl.mdb',false) then begin buf:=datetimetostr(datetimepicker1.dateTime)+' yygl已复制到药房!'; assignfile(filevar,'f:\备份数据库.txt'); append(filevar); writeln(filevar,buf); closefile(filevar) end else begin buf:=datetimetostr(datetimepicker1.dateTime)+' yygl数据库复制到药房未成功! '+IntToStr(GetLastError); assignfile(filevar,'f:\备份数据库.txt'); append(filevar); writeln(filevar,buf); closefile(filevar); end; if timetostr(datetimepicker1.dateTime)=datetostr(datetimepicker2.Date)+' '+timetostr(datetimepicker2.time) then begin //ExitWindowsEx(EWX_SHUTDOWN,0); form1.Close; end; end; end;
当数据库比较大的时候(例如超过5G), 而且数据变更比较频繁(或者有大的数据变更)的时候, kill 会产生数据回滚, 如果这个回滚的事务较大(或者很多), 则回滚需要花费很长的时间, 这会导致你后续的操作出问题
我还有个想法,如果用另一个有相同数据库的SQL实例,怎么进行数据同步?如在机器“MYSQL”和机器“MYSQL1”上各运行一个SQL SERVER 2000,都有数据库‘MYDATA’,怎么让每时每刻在变的“MYSQL”中的---‘MYDATA’在不忙的时候将数据同步到MYSQL1的‘MYDATA’中去呢?用什么?而且我的机器装的都是MSDE,没有完全安装SQL SERVER 2000,没有企业管理器。只能用SQL语句操作。
var sqlstr,sqlstr2:string;
filevar: textfile;
buf:string;
begin
datetimepicker1.dateTime:=now;
datetimepicker2.date:=now;
datetimepicker3.date:=now;
if datetimetostr(datetimepicker1.dateTime)=datetostr(datetimepicker3.date)+' '+timetostr(datetimepicker3.time) then
begin
try
sqlstr2:='select count(*) from master..sysprocesses where hostname>'+#39+#39;
sqlstr:='declare hcforeach cursor global for select '+#39+'kill '+#39+'+rtrim(spid) from sysprocesses where dbid=db_id('+#39+'hisdata'+#39+')'+#13+' exec sp_msforeach_worker '+#39+'?'+#39;
with adoquery1 do
begin
Close;
sql.Clear;
sql.Add(sqlstr);
open;
end;
adoquery1.ex
buf:=datetimetostr(datetimepicker1.dateTime)+' 已断开数据库! ';
assignfile(filevar,'f:\备份数据库.txt');
append(filevar);
writeln(filevar,buf);
closefile(filevar);
except
buf:=datetimetostr(datetimepicker1.dateTime)+' 未断开数据库! '+IntToStr(GetLastError);
assignfile(filevar,'f:\备份数据库.txt');
append(filevar);
writeln(filevar,buf);
closefile(filevar);
end;
end;
if datetimetostr(datetimepicker1.dateTime)=datetostr(datetimepicker1.date)+' '+timetostr(datetimepicker3.time+0.001) then
begin
try
sqlstr:='sp_detach_db @dbname ='+#39+'hisdata'+#39+', @skipchecks =true';
with adoquery1 do
begin
Close;
sql.Clear;
sql.Add(sqlstr);
open;
end;
buf:=datetimetostr(datetimepicker1.dateTime)+' 已分离数据库!';
assignfile(filevar,'f:\备份数据库.txt');
append(filevar);
writeln(filevar,buf);
closefile(filevar);
except
buf:=datetimetostr(datetimepicker1.dateTime)+' 未分离数据库! '+IntToStr(GetLastError);
assignfile(filevar,'f:\备份数据库.txt');
append(filevar);
writeln(filevar,buf);
closefile(filevar);
end;
end;
if datetimetostr(datetimepicker1.dateTime)=datetostr(datetimepicker1.date)+' '+timetostr(datetimepicker3.time+0.002) then
begin
if copyfile('D:\hisdata\hisdata.mdf','F:\hisdata.mdf',false) and copyfile('D:\hisData\hisdata_log.ldf','F:\hisdata_log.ldf',false) then
begin
buf:=datetimetostr(datetimepicker1.dateTime)+' 已复制数据库!';
assignfile(filevar,'f:\备份数据库.txt');
append(filevar);
writeln(filevar,buf);
closefile(filevar)
end else
begin
buf:=datetimetostr(datetimepicker1.dateTime)+' 未复制数据库! '+IntToStr(GetLastError);
assignfile(filevar,'f:\备份数据库.txt');
append(filevar);
writeln(filevar,buf);
closefile(filevar);
end;
end; if datetimetostr(datetimepicker1.dateTime)=datetostr(datetimepicker1.date)+' '+timetostr(datetimepicker3.time+0.006) then
begin
try
with adoquery1 do
begin
Close;
sql.Clear;
sql.Add('EXEC sp_attach_db @dbname = N'+#39+'hisdata'+#39+',@filename1 = N'+#39+'D:\hisdata\hisdata.mdf'+#39+',@filename2 = N'+#39+'D:\hisData\hisdata_log.ldf'+#39);
open;
end;
buf:=datetimetostr(datetimepicker1.dateTime)+' 已附加数据库!';
assignfile(filevar,'f:\备份数据库.txt');
append(filevar);
writeln(filevar,buf);
closefile(filevar);
except
buf:=datetimetostr(datetimepicker1.dateTime)+' 未附加数据库! '+IntToStr(GetLastError);
assignfile(filevar,'f:\备份数据库.txt');
append(filevar);
writeln(filevar,buf);
closefile(filevar);
end;
end;
if datetimetostr(datetimepicker1.dateTime)=datetostr(datetimepicker1.date)+' '+timetostr(datetimepicker3.time+0.0062) then
begin
if copyfile('F:\hisdata.mdf','j:\hisdata.mdf',false) and copyfile('F:\hisdata_log.ldf','j:\hisdata_log.ldf',false) then
begin
buf:=datetimetostr(datetimepicker1.dateTime)+' 数据库已复制到BCSVR1!';
assignfile(filevar,'f:\备份数据库.txt');
append(filevar);
writeln(filevar,buf);
closefile(filevar)
end else
begin
buf:=datetimetostr(datetimepicker1.dateTime)+' 数据库复制到BCSVR1未成功! '+IntToStr(GetLastError);
assignfile(filevar,'f:\备份数据库.txt');
append(filevar);
writeln(filevar,buf);
closefile(filevar);
end;
if copyfile('F:\hisdata.mdf','K:\hisdata.mdf',false) and copyfile('F:\hisdata_log.ldf','K:\hisdata_log.ldf',false) then
begin
buf:=datetimetostr(datetimepicker1.dateTime)+' 已复制到K盘数据库!';
assignfile(filevar,'f:\备份数据库.txt');
append(filevar);
writeln(filevar,buf);
closefile(filevar)
end else
begin
buf:=datetimetostr(datetimepicker1.dateTime)+' 数据库复制到K盘未成功! '+IntToStr(GetLastError);
assignfile(filevar,'f:\备份数据库.txt');
append(filevar);
writeln(filevar,buf);
closefile(filevar);
end; if copyfile('D:\yygl1\yygl.mdb','I:\yygl.mdb',false) then
begin
buf:=datetimetostr(datetimepicker1.dateTime)+' yygl已复制到药房!';
assignfile(filevar,'f:\备份数据库.txt');
append(filevar);
writeln(filevar,buf);
closefile(filevar)
end else
begin
buf:=datetimetostr(datetimepicker1.dateTime)+' yygl数据库复制到药房未成功! '+IntToStr(GetLastError);
assignfile(filevar,'f:\备份数据库.txt');
append(filevar);
writeln(filevar,buf);
closefile(filevar);
end;
if timetostr(datetimepicker1.dateTime)=datetostr(datetimepicker2.Date)+' '+timetostr(datetimepicker2.time) then
begin
//ExitWindowsEx(EWX_SHUTDOWN,0);
form1.Close;
end;
end;
end;
2008-2-12 01:02:53 未复制数据库! 322008-2-12 01:08:39 未附加数据库!02008-2-12 01:08:56 数据库复制到药房未成功!22008-2-12 01:08:56 数据库复制到K盘未成功!22008-2-15 01:00:01 未断开数据库! 0
2008-2-15 01:01:27 未分离数据库! 0
2008-2-15 01:02:53 已复制数据库!
2008-2-15 01:08:39 未附加数据库! 0
2008-2-15 01:08:56 数据库已复制到BCSVR1!
2008-2-15 01:08:56 已复制到K盘数据库!
2008-2-15 01:08:56 yygl数据库复制到药房未成功! 3
begin
Close;
sql.Clear;
sql.Add(sqlstr);
open; //知道了,不能用open方法,要用ExecSQL
end; 谢谢大家 。结了。