Using SDAC to backup & restore SQL Server databases

[jeremyw]

Is this possible? If so, should I use a TMSSQL component to issue standard "BACKUP DATABASE" T-SQL statements? Is there any way to measure progress or provide feedback to the user while the database is being backed up or restored?

Thanks in advance,
Jeremy

[AlexP]

Hello,

You can perform database backup in two ways. The first one is to use the BACKUP DATABASE command, for example

Code: Select all

MSConnection1.ExecSQL('BACKUP DATABASE ....');

In this case, the backup will be created using the standard SQL Server functionality. Note that you specify the path not on the local computer, but on the computer where the SQL Server is installed. When using this way, the backup progress cannot be displayed because the process is performed on the server side.
Another way is to use our TMSDump component that creates a text backup file. You can find more detailed information on this component in the MyDAC documentation: http://www.devart.com/sdac/docs/devart_sdac_tmsdump.htm . When using this component, you can use its onBackupProgress event to display the backup progress.

[jeremyw]

Thank you, I appreciate the detailed response. For the first backup method, even if progress information is unavailable, is it possible to determine when the backup completes, and whether it was successful or not?

Perhaps I need to investigate the TMSDump component. Is its backup/restore speed mostly constrained by network bandwidth? Does it have any notable limitations, such as incompatibility with certain types of tables or columns? For example, how would it restore to a table with an identity field?

[AlexP]

Hello,

To execute the BACKUP DATABASE command, you can use the following code:

Code: Select all

  try
    MSConnection1.ExecSQL('BACKUP DATABASE ....');
    ShowMessage('Backup complete')
  except
    on E: Exception do
      ShowMessage(e.Message);
  end;

In case of successful execution, at the end of backup, a "Backup complete" message will be displayed. If errors occur during backup, a message about the occurred problems will be displayed.

When working with TMSDump, all the data are downloaded to the client, therefore the backup speed depends on both network bandwidth and the data volume. TMSDump supports all the types. To work with identity fields, TMSDump has an IdentityInsert option, that adds SET IDENTITY_INSERT on/off commands to the script for each table.

[jeremyw]

Excellent, thank you again!

[AlexP]

Hello,

Glad to see that the problem was solved. If you have any other questions, feel free to contact us.