How to Create Backup/restore Ms SQL Server Database Using Delphi

How to create backup/restore MS Sql Server Database without typing scripts repeatedly? Create it with Delphi.

Data stored in the database will grow much. Such a large number will slow down the query to be performed. Thus we need to backup / restore database for complementing the software that we made.

To backup the database, you can use the tool on the MS SQL server and type the command like this:

backup database strDatabaseName to disk =  ‘c:BackupName.dat’

while restoring the database are as follows:

restore database NamaDatabase from disk =  ‘ c:BackupName.dat’

If the backup and restore done every day, then typing the above many times will seem boring. To overcome this, we need a little programming. Both the backup and restore database, we only need the object TConnection, TAdoQuery only. Connect the Connection property on the object TConnection TAdoQuery. When the object TConnection in open, then TAdoQuery will perform the work.

Set TConnection as follows:

with Connetion1 do
begin   
    Close;   
    ConnectionString :=   
        ‘Provider=SQLOLEDB.1;’ +
        ‘Password=’ + ServerPassword + ‘;’ +
        ‘Persist Security Info=True;’ +
        ‘User ID=’ + ServerUserName + ‘;’ +
        ‘Initial Catalog=’ + ServerDatabaseName + ‘;’ +
        ‘Data Source=’ + ServerName;

    LoginPrompt := false;
   Open;  
end;


Backup Database

Set TAdoQuery like this:

with AdoQuery1 do
begin
    Close;
    Clear;
    CommandTimeout := 0;
    Add(’backup database strDatabaseName to disk = ‘ + QuotedStr(strFileName));   
    ExecSQL;
end;

Restore Database
Restore the database is a bit different than typing on MS SQL Server tool. The things that influence success are: In the event of a restore, the database should only be accessed by a single user. To do this, we need to change the database to a single user with the following command:

alter database strDatabaseName set SINGLE_USER with ROLLBACK immediate

After that, the backup database file can not be held by anyone. To implement this, we can replace it by holding the master database with the following command:

use master;

then restore database :

restore database strDatabaseNama from disk = ‘ + QuotedStr(edtPathFile.Text)

Execution of the above commands may be beyond the deadline of TAdoQuery Components. To be granted without limit of time, you can give

CommandTimeout := 0;

The following is a complete program code described above:

with AdoQuery1 , SQL do
begin
     Close;
     Clear;
     CommandTimeout := 0;
     Add(’alter database strDatabaseName set SINGLE_USER with ROLLBACK immediate’);
     ExecSQL;

    Close;
    Clear;
    CommandTimeout := 0;
    Add(’use master’);
    ExecSQL;

    Close;
    Clear;
    CommandTimeout := 0;
    Add(’restore database strDatabaseName from disk = ‘ + QuotedStr(edtPathFile.Text));
    ExecSQL;
end;

 


comments powered by Disqus
Loading