Security – How to disable Ad Hoc Distributed Queries on SQL Server 2000

There's really no reason NOT to use SQL Server's native backup functionality. It's great, understands transaction logs, and gives you most of the functionality you need. (Third party SQL Server Backup solutions are great because they work with the APIs that Microsoft has exposed, and usually provide encryption and compression support - and compression not only saves disk space, but typically reduces backup and restore times.)

So yeah... I'd recommend doing backups WITH SQL Server.

And make sure to do REGULAR transaction log backups. (I'd recommend doing them every 15 minutes or less - regular log file backups help keep your log file lean/mean 1 and for some reason end-users are always grumpy when the server crashes and they have to redo all of their work over the last x hours since the last logfile or full/differential backup.) Then, for redundancy purposes, use robocopy, syncback, file system replication or something to move copies of those backups to another location to protect against hardware/disk crashes or fires in the data center and so on.

Feel free to check out these two free videos for more info and ideas:

http://www.sqlservervideos.com/video/backup-options

http://www.sqlservervideos.com/video/sqlbackup-best-practices/

[1] [SQL Server Magazine: Maximing Storage Performance]3

Use the UNC path when specifying the destination-- the SQL Agent doesn't have a concept of "mapped" "drives".

Also, SQL Agent typically runs as "Local Service" or "Local System" and, as such, doesn't have rights to remote shares on other computers.

You have a couple of choices:

• Run SQL Agent as a role account in the domain. Grant that account permission to write to the directory / share where you'd like the backups stored.

• Run SQL Agent as "Network Service". It will authenticate to the sharing server with the domain computer account of the machine the service is running on. Grant that account permission to write to the directory / share where you'd like the backup stored.

• If you don't have a domain, create an account with the same username and password on both the machine hosting SQL Agent and the machine hosting the backup files. Change SQL Agent to run as this "role" account, and grant that account permission to write to the directory / share where you'd like the backup stored. (The "poor man's domain"...)