Hello all.
SQL Server installation includes the SQLDMO object library, that's avaliable to execute many SQL Server operations and features by VB code. Unfortunately, these objects are not as widely documented as some other objects we are used to work with, and sometimes the usage of them is somehow difficult. And moreover, there are many SQL Server tasks which don't require including these objects in our project. Two of these tasks are Backup and Restore of a database.

In fact, they don't even require VB. The trick is to create two sql scripts.
An sql script is a plain text file, containing one or more SQL instruction to be executed, and named with the .sql extension. Both SQL Server and MSDE install the command line utility osql.exe, that we can use to execute sql scripts. Its command line usage is like:

osql.exe -S [server_name] -U [user_id] -P [user_password] -i [script_file_path_and_name]

The -S, -U, -P and -i switches have to be written in the specified upper and lower case. We can easily create an sql script to backup our db and another script to restore it, as backup and restore are two valid sql operations.

Let me make an example. Assume that we have a database named MyDB. We'll have to create two text files, with the .sql extension, containing the appropriate SQL instructions.
1) db backup:

USE MyDB GO BACKUP DATABASE [MyDB]   TO DISK = 'C:\MyFolder\MySubFolder\MyDB.dat'   WITH INIT, NOUNLOAD   , name = 'MyDB Backup'   , NOSKIP   , STATS = 10   , Description = 'backup script of MyDB database with MSDE/SQL Server'   , NOFORMAT GO QUIT

This SQL instruction backups our db in the MyDB.dat file, into the C:\MyFolder\MySubFolder directory. Non existing folders are automatically created. This path will have to be specified when we'll attempt to restore our db.
2) db restore:

USE Master GO RESTORE DATABASE MyDB   FROM DISK = 'C:\MyFolder\MySubFolder\MyDB.dat' WITH REPLACE GO QUIT

This second SQL instruction is a bit simpler (but not that much).
These two sql files can be saved anywhere, and can be invoked by an osql.exe command line call. Since osql.exe is a 16 bit utility, it doesn't like much any blank space in the script path. So I suggest you not to save it into the desktop, as its path would be like C:\Documents and Settings\... and osql.exe could not work (however, there is a trick to bypass this. I'll tell it at the end of this speech).

Let's go on. Let's assume that we have named our two scripts BackupDb.sql and RestoreDb.sql, both saved in C: disk root, and we operate on a server named MyServer with the userid "sa" and with no password.
In order to backup our db, click Start/Run, and in the Run box write down:

osql.exe -S MyServer -U sa -P -i C:\BackupDb.sql

This call creates a backup file, by the name MyDB.dat in the specified path. Later, in order to restore it, click Start/Run, and in the Run box write down:

osql.exe -S MyServer -U sa -P -i C:\RestoreDb.sql

This call restores the previously created backup.
Please notice that the local server has to be passed by the "(local)" word, enclosed in parenthesis. Remember also that, in order the backup operations will be executed, the SQL Server Agent service have to be running.

Easy, isn't it? Well, we still can improve our technique. The command line osql.exe call is not friendly to write down, especially with long pathnames. So we can put the whole call in a batch file. A batch file is nothing but a text file with a .bat extension. It contains some DOS instructions which are executed sequentially. I prepared two batch files to execute the two scripts.
The first batch file, named MakeBackup.bat, contains the following instructions:

rem this file launches the SQL script that executes a db backup osql.exe -S MyServer -U sa -P -i C:\BackupDb.sql pause

The second batch file, named Restore.bat, contains the following text:

rem this file launches the SQL script that restores a db backup osql.exe -S MyServer -U sa -P -i C:\RestoreDb.sql pause

Now, you can simply double click on the first batch file to launch the backup, and double click the second batch file to restore it.
(*/far voices/* "Hey, man, here's a VB forum!...")
Yep, we can VB launch the two batch files by the Shell instruction or by the ShellExecute API. A setup packet can create either the batch files and the sql scripts in a given directory, and we can associate the batch files execution to two menu voices into our application to implement the backup/restore feature.

Any other hint? Of course. I recently bought for fun an old MS-DOS book, and I found out into it that batch files can accept parameters, in the %1 format. So, we can modify the text into a batch file like following:

rem this file launches an SQL script if not exist %1 goto :endpoint osql.exe -S MyServer -U sa -P -i %1 :endpoint pause

Now we can simply drag and drop the sql script file onto the batch file to execute it. So we can use this batch file either with the backup script or with the restore script. We can also call it by the Shell instruction, specifying which sql file has to be excuted within the Shell function arguments. This way the osql.exe utility works even with spaces in the file path, as the spaces are resolved by Windows itself.

If you don't find this technique easy, keep in mind that once you prepared you custom batch files, with servername, username, password and script path, you have nothing else to do and you can do and restore as many backups you need without any more work. I usually prepare the structure of my batch files, like

osql.exe -S SSSS -U UUUU -P PPPP -i %1

then I replace the SSSS, UUUU, PPPP strings with the server name, user id and password during install phase, and finally I leave the modified batch files saved in a known dir, to be reached from my application's menus.

That's all. I hope you'll find it useful. For details on SQL syntax and arguments, please refer to SQL Server Online Books, or to SQL manuals. You can test the working of the two SQL statements design in the SQL Server Query Analyzer window. Let me know your improvements, or post your comments if you want to.

[Edited by yronium on 15-12-2006 at 12:40 PM GMT]

____________________________
Real Programmer can count up to 1024 on his fingers