| Author |
Topic |
|
sgroom
Starting Member
4 Posts |
 Posted - 2002-07-11 : 10:18:52
|
| HiCan anyone help ?I need to be able for the users of a specific application double click an Icon on their desktop and enter a file name and the SQL Database will then be backed up with the entered name.Is this possible ??Stuart |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-11 : 10:34:02
|
| Yes, that should be pretty easy to do, using dynamic SQL:http://www.sqlteam.com/item.asp?ItemID=4599http://www.sqlteam.com/item.asp?ItemID=4619You'd construct a BACKUP DATABASE command as a string and incorporate the file name in it, then EXECUTE the string. Books Online has details on the BACKUP and EXECUTE commands. |
 |
|
|
sgroom
Starting Member
4 Posts |
Posted - 2002-07-11 : 11:00:24
|
| HiThanks for the information. How would I run the constructed SQL statement as the application as not been written in house and cannot be changed.Do I need to write a VB Program or can it be done another way ?Stuart |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-11 : 11:07:16
|
| Can the application accept SQL statements? Or does it only use pre-defined statements?If you can send it SQL statements, how do you go about doing it? Is there a user interface (window form with text boxes) only? Can it accept command-line parameters?Sorry if this is confusing, but there may be a couple different ways of doing this depending on how this app operates. |
|
|
|
sgroom
Starting Member
4 Posts |
Posted - 2002-07-11 : 11:12:46
|
| HiThe application as far as I know cannot accept a command line parameter. The application is actually a Payroll Application with the data held on SQL Server.It does not matter if the backup is completely seperate as the user will have instructions on what to do.Stuart |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-11 : 12:10:22
|
| You can use the osql or isql utilities to pass SQL statements to the server. You can write a batch file (backup.bat) that accepts a file name to run the command:osql "BACKUP DATABASE myDB TO %1" -E -SserverAnd then you'd call the batch file:backup myBackupDeviceWhich would execute:osql "BACKUP DATABASE myDB TO myBackupDevice" -E -SserverSee Books Online for more details on osql and its parameters.If you are using different file names each time, you need to create a new dump device with that file name (using sp_adddumpdevice) I'd recommend against that though, you can easily end up with a bewildering number of backup files. You should set up a limited number of available backup devices and also limit the number of people who can run these backups. Backing up is an administrator's function that the casual end user should not perform.There are ways of doing these functions in VB as well, but the batch file is the simplest way to get you started. |
|
|
|
sgroom
Starting Member
4 Posts |
Posted - 2002-07-12 : 04:50:45
|
| HiThanks for the information the only thing is that the device has to exist to allow the backup to be created and the stored procedure for for adding the device is not in SQL Server.Can I get this from anywhere ??Stuart |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-12 : 08:11:31
|
 I misspelled it, it's sp_addumpdevice. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-12 : 08:24:33
|
| If you look up the BACKUP DATABASE syntax in BOL, you'll see there is syntax for FILE =. You should be able to specify the destination file at run-time, and I don't see any reason why that wouldn't work through the cmd line tools.<O> |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-12 : 09:03:57
|
| I. Need. More. Coffee.Actually, the "FILE=" syntax refers to data files or filegroups being backed up, not the actual backup file name. You would use the "DISK=" syntax to specify a backup file:BACKUP DATABASE myDB DISK='C:\backups\myNewBackup.dat' |
|
|
|
|
Automated User Backup