Sql-server – Backing up and restoring SQL Server scheduled jobs

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

If you do not have a domain, you can set up the same user (name) on all machines, and then use the local user to run SQL Server.

For example, on ServerA (which has SQL Server running), I can set up the user MySQLServerUser as a local user. Use Configuration Manager to set this as the account to run SQL Server.

Then on ServerB, which is where I want to send the backups, I set up the same account (MySQLServerUser) with the same password.

Since Windows will pass this through as the first user/password, it should authenticate.

Note that backups across the network are not recommended. The SQL backup process is intolerant of network delays. Anything that hiccups will fail the backup. And it will likely happen when your disks fail. Back up locally, copy to the remote machine. Copies will retry themselves automatically.