What I really wanted to do was make a backup with the intention of uploading it to an Amazon S3 bucket. I looked at the three methods Microsoft recommend SQL Server Management Studio , Transact-SQL , PowerShell and all of these will allow backup to a tape drive or disc.
You still need to upload to Amazon.
In the end I plumped for using Cloudberry S3 Server Backup edition although I cant wait to try their SQL Server edition. This allowed me to schedule backups from a shared network drive directly to my S3 storage..
You can download a trial of S3 Server Backup here.
Late News:For a complete SQL server Backup solution the SQL Server edition is available here
What and When to Backup
Ask yourself the following questions about your SQL database.
- From when til when is the database in use?
If there is a predictable off-peak time, do full database backups fthen. - How often are changes likely to occur?
If changes are frequent, consider the following:- Under the simple recovery model, consider scheduling differential backups between full database backups. A differential backup captures only the changes since the last full database backup.
- Under the full recovery model, you should schedule frequent log backups. Scheduling differential backups between full backups can reduce restore time by reducing the number of log backups you have to restore after restoring the data. - Microsoft
- Are changes likely to occur in only a small part of the database or in a large part of the database?
For a large database in which changes are concentrated in a part of the files or filegroups, partial backups and or file backups can be useful. - What size will a full database backup be?
1Gb, 10GB or what?
Find out the Size of the Full Database Backup
You can estimate the size of a full database backup by using the sp_spaceused system stored procedure. For more information, see sp_spaceused (Transact-SQL).
Schedule Backups
SQL Backup will easily run whilst in use. The backup process has a minimal footprint.
You should do regular backups and schedule them as part of your regular maintenance.
You should do regular backups and schedule them as part of your regular maintenance.
Test Your Backups
You do not have a restore strategy until you have tested your backups. It is very important to thoroughly test your backup strategy for each of your databases by restoring a copy of the database onto a test system. You must test restoring every type of backup that you intend to use.
We recommend that you maintain an operations manual for each database. This operations manual should document the location of the backups, backup device names (if any), and the amount of time that is required to restore the test backups.-More from Microsoft