我用BDE里面的控件连接数据库SQL,但是不知道怎么样 进行数据库的备份和恢复,导入与导出,哪位兄弟帮忙一下,能不能写一下代码让我参考一下,兄弟我急死了。能不能发到.cn,或者直接答在上面也可以
解决方案 »
- 通过ADOQUERY 查询完毕了,想直接修改出某一个字段的值怎么弄?
- 对查询出来的记录行进行分页显示,怎么做???在客户现场,哪位大虾帮忙?急急急
- 能否做到:AdOconnection闲时自动断开,需要时自动连接?
- 帮帮忙,adoquery 数据库操作(插入数据)
- 谁使用过TdxRTTIInspector控件
- Delphi的前景怎样?
- !!!请问,在DBGrid中如何设置一行可写,其他行可读
- 那么我在数据库不做关系图,access/ms sql)那么b表知道是那个人做的吗
- 提教controls的问题
- SQL2000数据库转储截止时间怎样修改
- Delphi7內如何调用Outlook Express发送一个带附件的邮件?
- dbgrid的自动增加问题(急)
begin
close;
Sql.Clear;
Sql.Add('use Master');
sql.Add('BACKUP DATABASE ['+dBaseName+'] TO DISK = '''+Bf_Filename+''' WITH NOINIT , NOUNLOAD , NAME = ''emanage2688备份'', NOSKIP , STATS = 10, NOFORMAT');
ExecSql;
end;
主要就是:
备份:backup database databasename to disk='c:\temp\abc.dat'
恢复:restore database databasename from disk='c:\temp\abc.dat'
http://search.csdn.net
搜索一下
一大把
但是在还原的时候用:restore database databasename from disk='c:\temp\abc.dat' 就会有问题
提示 数据库正在使用,在VB里我是先杀掉将所有连接databasename的进程 然后才恢复数据库
但是在Delphi里不知道杀掉连接databasename的进程的代码该怎么写 所以我是用先VB写个Dll 然后在Delphi里调用Dll杀掉进程
{描述:SQL SERVER 2000 数据库备份与恢复单元 ,本单元函数在SQL SERVER 2000 +Delphi7.0编译通过
时间 :2003.09.15
作者:chenshaizi
}
interfaceuses
adodb,db;
function repl_substr(sub_old, sub_new, s: string): string; //把sub_old换成sub_new,后面有用。
function BackupSQLDataBase(connstr_sql,DatabaseName,Backup_FileName:string):Boolean;//数据库备份函数
//SQL数据数据库备份,connstr_sql是ADO控件的connectionstring,DatabaseName是数据库名称,
//Backup_FileName要备份到的目 标文件
function RestoreSQLDataBase(connstr_sql,DatabaseName,Restore_FileName:string):Boolean;//数据库恢复函数
//Restore_FileName以前备份的数据库文件,
implementationfunction repl_substr(sub_old, sub_new, s: string): string;
var
i: integer;
begin
repeat
i := pos(sub_old, s);
if i > 0 then
begin
delete(s, i, Length(sub_old));
insert(sub_new, s, i);
end;
until i < 1;
Result := s;
end;
function BackupSQLDataBase(connstr_sql,DatabaseName,Backup_FileName:string):Boolean;
var
//备份SQL数据库SQL数据数据库备份,connstr_sql是ADO控件的connectionstring,DatabaseName是数据库名称,
//Backup_FileName要备份到的目标文件
aADOQuery:TADOQuery;
begin
try
aADOQuery:=TADOQuery.Create(nil);
aADOQuery.Close;
aADOQuery.ConnectionString:=connstr_sql;
aADOQuery.SQL.Clear;
aADOQuery.SQL.Add('backup database '+DatabaseName+' to disk = ' + '''' + Backup_FileName + ''' with format');
Try
aADOQuery.ExecSQL;
Result:=true;
Except
Result:=false;
exit;
end;
finally
aADOQuery.Free;
end;
end;
function RestoreSQLDataBase(connstr_sql,DatabaseName,Restore_FileName:string):Boolean;
var//数据库恢复函数,estore_FileName以前备份的数据库文件
aADOQuery:TADOQuery;
begin
try
aADOQuery:=TADOQuery.Create(nil);
aADOQuery.Close;//恢复数据库不能打开数据库,要打开系统数据库master,把连接字符串如adoconnetion的connectionstring中的数据库名称换成"master"数据库
aADOQuery.ConnectionString:=repl_substr(DatabaseName,'master',connstr_sql);
aADOQuery.SQL.Clear;
aADOQuery.SQL.Add('RESTORE DATABASE '+DatabaseName+' from disk = ' + '''' + Restore_FileName + '''');
Try
aADOQuery.ExecSQL;
Result:=true;
Except
Result:=false;
exit;
end;
finally
aADOQuery.Free;
end;
end;
end.
f:TshfileOpstruct;
begin
f.wnd:=handle;
f.wfunc:=fo_copy;//操作方式
f.pfrom:=panichar(extractfiledir(application.exename)+'\gz.mdb'+#0);
savedialog1.filter:='access文件(*.mdb)|*.mdb';
savedialog1.title:='选择备份路径和文件名';
savedialog1.initialdir:=extractfiledir(application.exename)+'\bak';
if savedialog1.exename then
f.pto:=panichar(savedialog1.filename+'.mdb')
else
exit;
f.fflags:=fof_allowundo or fof_renameoncollision//操作选项
if shfileoperation(f)<>0 then
messagebox(getactivewindow(),'数据文件备份失败','错误',mb_ok+mb_iconstop)
else
messagebox(getactivewindow(),'数据文件备份成功','成功',mb_ok+mb_iconinformation);
end;
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------------------------------------------2、将Excel的数据导入SQL server :-- ======================================================SELECT * into newtableFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]实例:SELECT * into newtableFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions-------------------------------------------------------------------------------------------------3、将SQL SERVER中查询到的数据导成一个Excel文件-- ======================================================T-SQL代码:EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'参数:S 是SQL服务器名;U是用户;P是密码说明:还可以导出文本文件等多种格式实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"' EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'在VB6中应用ADO导出EXCEL文件代码: Dim cn As New ADODB.Connectioncn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"-------------------------------------------------------------------------------------------------
4、在SQL SERVER里往Excel插入数据:
-- ======================================================
insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)T-SQL代码:
INSERT INTO OPENDATASOURCE('Microsoft.JET.OLEDB.4.0', 'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$] (bestand, produkt) VALUES (20, 'Test') -------------------------------------------------------------------------------------------------总结:利用以上语句,我们可以方便地将SQL SERVER、ACCESS和EXCEL电子表格软件中的数据进行转换,为我们提供了极大方便!把文本文件导入到ACCESS
在Form上放一个ADOConnection,连结指向目标Access库
比如txt文件在c:\temp\aaaa.txt
就执行
ADOConnection.Connected := True;
ADOConnection.Execute('Select * Into abcd From [Text;Database=c:\temp].aaaa.txt');=============================================================================================把DBF(Foxpro数据库)导入到SQLSERVER
告诉你一个最快的方法,用SQLServer连接DBF(Foxpro数据库)
在SQLServer中执行
SELECT * into bmk
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0,
'Data Source="e:\share";User ID=Admin;Password=;Extended properties=dBase 5.0')...bmk
procedure TForm1.Button3Click(Sender: TObject);
var
BackStr: string;
begin
if edit1.Text <> '' then
begin
BackStr := 'backup database test to disk = ''' + edit1.Text + '.bak''';
with DataModule2.ADOQuery2 do
begin
close;
sql.Clear;
sql.Text := BackStr;
try
Execsql;
except end;
end;
end;
end;
恢复:
procedure TForm3.Button1Click(Sender: TObject);
var
Str: string;
begin
DataModule2.ADOConnection1.Connected := false;
ADOConn.Connected := False;
ADOConn.ConnectionString := 'Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=master;Data Source=SAILOR';
ADOConn.Open;
if edit1.Text <> '' then
begin
Str := 'restore database test from disk = '+ #39 + edit1.Text + #39 + ' with replace, '
+ 'move ' + #39 + 'test_data' + #39 + ' to ' + #39 + 'C:\Program Files\Microsoft SQL Server\MSSQL\data\test_Data.MDF' + #39
+ ',move ' + #39 + 'test_log' + #39 + ' to ' + #39 + 'C:\Program Files\Microsoft SQL Server\MSSQL\data\test_Log.LDF' + #39;
with Adoquery1 do
begin
close;
sql.Clear;
sql.Add('ALTER DATABASE test SET OFFLINE WITH ROLLBACK IMMEDIATE');
try
ExecSQL;
except end;
close;
sql.Clear;
sql.Text := Str;
try
execsql;
application.MessageBox('成功','asdf',mb_ok+ mb_iconinformation);
except
application.MessageBox('错误','asdf',mb_ok+ mb_iconstop);
end;
close;
sql.Clear;
sql.Add('ALTER DATABASE test SET ONLINE WITH ROLLBACK IMMEDIATE');
try
ExecSQL;
except end;
end;
end;
ADOConn.Connected := false;
DataModule2.ADOConnection1.Connected := True;
end;
recordset1.Open "select spid from sysprocesses where dbid=db_id('宾馆管理系统')", connection1
Do While Not recordset1.EOF
If cn.State = adStateOpen Then cn.Close
connection1.Execute "kill " & recordset1("spid") '杀掉sql服务器的所有连接,否则会出现:数据库正在使用,无法完成排它操作等等,很重要
recordset1.MoveNext
Loop