Page 1 of 1
Using SDAC to backup & restore SQL Server databases
Posted: Fri 03 Jan 2014 00:55
by 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
Re: Using SDAC to backup & restore SQL Server databases
Posted: Fri 03 Jan 2014 11:56
by 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.
Re: Using SDAC to backup & restore SQL Server databases
Posted: Mon 06 Jan 2014 23:41
by 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?
Re: Using SDAC to backup & restore SQL Server databases
Posted: Thu 09 Jan 2014 14:44
by 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.
Re: Using SDAC to backup & restore SQL Server databases
Posted: Tue 14 Jan 2014 02:23
by jeremyw
Excellent, thank you again!
Re: Using SDAC to backup & restore SQL Server databases
Posted: Tue 14 Jan 2014 08:16
by AlexP
Hello,
Glad to see that the problem was solved. If you have any other questions, feel free to contact us.