Newbie backup question

Hi, am really new to all of this, so please be gentle. :)

On computer A (the server) I have SBS2k3 with SQL server 2k. On computer B (the workstation) I have MS Office/Access. I have created a couple of access projects on the workstation that have tables, stored procedures, etc.. in a couple of database on the server. Now I need some kind of backup plan.

I backup the Access projects on removable USB drives plugged into the workstation. I'd like to do scheduled backups of the databases on the server and also copy those backups to the USB drives on the workstation.

I've read that I can use Enterprise manager on the server to do scheduled backups to any drive on the server. I figure if I do that to a shared folder on the server that I can then copy that backup via the workstation and save a copy of it to the USB drive on the workstation.

1 - Do you see any problems with this scheme?

2 - Does the backup from enterprise manager really back everything up (table structures, stored procedures, etc...)?

3 - Assuming that I cycle between onsite and offsite USB drives on the workstation, if the place burned down and I had to rebuild the machines from scratch, is the backup saved to the USB drive mentioned above going to contain everything I need to recreate my databases on new blank hardware (after a fresh install of SBS2k3 and SQL server, of course)?there are many ways of doing this.
1. create a shared on the serverA (i.e. c:\shared -> \\serverA\shared)
2. create a sql job to backup your db:
backup database [your_db]
to disk 'c:\shared\your_db.bak'
with init
3. on your workstation, you can just create a windows schedule to connect to \\serverA\shared and copy the backup.

In general, I think it's a bad idea to try to schedule a backup job on the server to backup to your workstation usb drive. This can be done but is not really reliable. You have a regular backup job on the server and do copy/pull of the backup file as needed.|||Thanks.

No, I wasn't planning to schedule a task on the server to backup directly to the workstation's USB drive. I figured I'd do that manually from the workstation, since I don't always have one of the USB drives turned on. But I was planning to do a scheduled backup on the server to a folder on the server. (probably in the middle of the night)

> backup database [your_db]
> to disk 'c:\shared\your_db.bak'
> with init

So, that will backup everything, including the table structures and stored procedures, so the database can be recreated on a different computer in addition to being able to restore on the same one?|||The BAK file should contain everything. However, you shouldn't take anyone here's word for it. Create a backup of "DatabaseName", then take that BAK file and restore it to "NewDatabaseName". See if the restored database contains what you want. You can delete it after that, but then you'll know for sure what you'll get.