Back up SQL Server database files to a Local Machine

{********************************

Copyright jack_0424@126.com 2006, 4, 16

Function Description:
Back up SQL Server database files to a Local Machine

Implementation ideas:
Because the backup files of SQL Server can only be stored on the machine where the database server is located, the client data backup is inconvenient.
To break through this restriction,ProgramThe following ideas are adopted:
1. Back up data on the server first
2. Load the backup file to the data table in the image column.
3. The client reads the table and saves the image column as a local file.

Scenario:
1. Use ADO as the connection component
2. Use sqlserver2000 as the database

Call method:
Backupdbfile (adoconnection1 );

********************************}

Unit usqlbackfile;

Interface
Uses dB, ADODB, sysutils, forms, dialogs;

Type
Tsqlback = Class
Private
Fdbname, fbakfilename: string;
Fuser, fpass: string;
Fremotedbdatapath, fremotedbbinnpath,
Flocaldbdatapath, flocaldbdataname, flocaldbdatapathname: string;
Fadoconn: tadoconnection;
Fadoquery: tadoquery;
Procedure backupdbfile;
Function getdata (msql: string): string;
Procedure backdbtoremote;
Procedure execsql (msql: string );
Procedure backfiletoimg;
Procedure ensureimgtable;
Procedure LoadImage;
Procedure initparams;
Function getparams (const Param: string): string;
Procedure getbakfiletolocal;
Procedure docleanwork;
Public
Constructor create (adoconn: tadoconnection );
Destructor destroy;

End;

Procedure backupdbfile (adoconn: tadoconnection );

Implementation

Procedure backupdbfile (adoconn: tadoconnection );
VaR
Sqlback: tsqlback;
Begin
Sqlback: = tsqlback. Create (adoconn );

With sqlback do
Try
Backupdbfile;
Finally
Free;
End;

End;

{Tsqlback}

Procedure tsqlback. backdbtoremote;
Begin
Fremotedbdatapath: = extractfilepath (getdata ('select filename from sysfiles '));
Execsql (format ('backup database % s to disk = '% s' with init'
, [Fdbname, fremotedbdatapath + fbakfilename]);
End;

Procedure tsqlback. backfiletoimg;
Begin
Ensureimgtable; // make sure that the image is in the correct table.
LoadImage; // load the server backup file into the table
End;

Procedure tsqlback. ensureimgtable;
Begin
Execsql (format ('If object_id (''bakfile') is null in in'
+ #13 #10 + 'create table bakfile (IMG image null )'
+ #13 #10 + 'insert into bakfile values ('''')'
+ #13 #10 + 'end'
, []);
End;

Procedure tsqlback. LoadImage;
Begin
Fremotedbbinnpath: = extractfilepath (
Getdata ('select filename from Master .. sysdatabases where name = ''master ''')
);
Fremotedbbinnpath: = stringreplace (fremotedbbinnpath, 'mssql \ data', 'mssql \ binn ', [rfignorecase]);

// Because the path of textcopy.exe cannot contain spaces, copy it to the root directory.
Execsql (format ('execute master .. xp_cmdshell ''Copy "% s" C: \ textcopy.exe '''
, Using fremotedbbinnpath='textcopy.exe ']);

Execsql (format ('execute master .. xp_cmdshell '% S/U % S/P % S/D % S/T bakfile/c img'
+ '/W "where 1 = 1"/F "% s"/I' ', no_output'
, ['C: \ textcopy.exe'
, Fuser
, Fpass
, Fdbname
, Fremotedbdatapath + fbakfilename
]);

// Delete a temporary file
Execsql (format ('execute master .. xp_mongoshell ''del % s''', ['C: \ textcopy.exe ']);
End;

Procedure tsqlback. docleanwork;
Begin
Execsql ('Update bakfile set IMG = ''''');
End;

Procedure tsqlback. backupdbfile;
Begin
Backdbtoremote; // back up the database to the server
Backfiletoimg; // convert the database file to the image Column
Getbakfiletolocal; // The backup file is returned to the client.
Docleanwork; // clear the database

Showmessage ('database backed up successfully to: '+ flocaldbdatapathname );
End;

Procedure tsqlback. getbakfiletolocal;
Begin
With fadoquery do begin
Close;
SQL. Text: = 'select IMG from bakfile ';
Open;

Tblobfield (fields [0]). savetofile (flocaldbdatapathname );
Close;
End;
End;

Constructor tsqlback. Create (adoconn: tadoconnection );
Begin
Fadoconn: = adoconn;
Fadoquery: = tadoquery. Create (NiL );
Fadoquery. Connection: = fadoconn;
Fadoquery. paramcheck: = false;

Initparams;
End;

Procedure tsqlback. initparams;
Begin
Fuser: = getparams ('user id ');
Fpass: = getparams ('Password ');
Fdbname: = getparams ('initial catalog ');
Fbakfilename: = fdbname + '. Bak ';

Flocaldbdatapath: = extractfilepath (application. exename) + 'databackup \';
Flocaldbdataname: = 'db' + formatdatetime ('yyyy-MM-DD_HH-NN-SS ', now) +'. Bak ';
Flocaldbdatapathname: = flocaldbdatapath + flocaldbdataname;

Forcedirectories (flocaldbdatapath );
End;

Function tsqlback. getparams (const Param: string): string;
Begin
{Connectionstring = 'provider = sqloledb.1; Password = sa; persist Security info = true;
User ID = sa; initial catalog = pubs; Data Source = 192.168.1.30;
Use procedure for prepare = 1; Auto translate = true; packet size = 4096;
Workstation id = jacksoft; use encryption for Data = false;
Tag with column collation when possible = false '}
Result: = fadoconn. connectionstring;
Result: = copy (result, pos (';' + Param + '=', result) + 1,100 );
Result: = copy (result, 1, pos (';', result)-1 );
Result: = copy (result, length (PARAM) + 2,100 );
End;

Destructor tsqlback. Destroy;
Begin
Fadoquery. Free;
End;

Procedure tsqlback. execsql (msql: string );
Begin
With fadoquery do begin
Close;
SQL. Text: = msql;
Execsql;
End;
End;

Function tsqlback. getdata (msql: string): string;
Begin
With fadoquery do begin
Close;
SQL. Text: = msql;
Open;

Result: = Fields [0]. asstring;
End;
End;

End.