Simple program to run the SQL Backup job

carmelcw

Hi,
I have installed the SQL Backup in my MS SQL Server and I can create the backup job with this software.

My question is:
I want to allow user (not technical staff) to do the backup from their PC (client computer). Is there a way to write a small/simple program to handle it? Since user may need to do the backup as ad-hoc base.
They may also store the backup file in their local computer.

I'm a technical staff but not a programmer, so want to seek any advise from this forum.
Many thanks,
Carmel Lee

Brian Donahue

Hello Carmel,

There are a few possibilities. SQL Backup ships with a command-line version of the program (SQLBACKUPC.exe) that you could put in a batch file. That needs to be licenced on the local machine, though, to work remotely.

What may be better, especially if you have the standard version, would be to create a VBScript to run the backup. This would require nothing to be installed on the user's workstation as the ADO components are 'part of the operating system'.

Set conn = CreateObject("ADODB.Connection") 
conn.open "provider=SQLOLEDB;data source=(local);INITIAL CATALOG=master;ConnectTimeout=0;Trusted_Connection=Yes" 
Set cmd = CreateObject("ADODB.Command") 
Set cmd.ActiveConnection = conn 
cmd.CommandTimeout=0 ' default is 30 seconds. Without setting this, large db backups will fail. 
cmd.CommandText="master..sqlbackup ' -SQL ""BACKUP DATABASE [Northwind] TO DISK = ''c:\Northwind_Full.sqb'' WITH NAME = ''Database (Northwind)'', INIT, ERASEFILES = 2, COMPRESSION = 1"" -E'" 
Set rs = cmd.Execute 
ret = "" 
While Not rs.EOF 
'Read each line of results and strip the NULLS that SQL Backup pads the line with 
ret = ret & REPLACE(rs(0), CHR(0), "") &vbCrLf 
rs.MoveNext 
WEnd 
MsgBox(ret) 
rs.close 
Set cmd = Nothing 
Set conn = Nothing

carmelcw

Hi Brian,

Does it mean the code you provide, I can create a simple program that use the VB to do the backup?

As I plan to buy the product soon, and make sure it is working for me, like I said, user can do the backup without install any software except this simple program.

Do you think your develop team can also prepare this kind of request when ship with the product? So that make us more easily to implement to our client.

I will try to ask our programmer to do me a favour with the code you provided and let you know the result soon.

Many thanks in advance for your kind help.
Regards,
Carmel Lee

Brian Donahue

Even easier than that. Copy the code and paste it into notepad, then save it with a vbs extension. You can run it by double-clicking it.

You could even put requesters in to ask for the name of the database you want to back up, etc. If you have VB6, it would be fairly simple to modify this code to use in a stand-alone executable. You'd need to create references to the ADO library. That's about the only difference.