Monday, November 05, 2012

Back up SQL Server to the cloud

This week I had a problem with a restored SQL server database which 'lost' its users on being restored. Even though Windows Server Backup is supposed to be able to back up and restore running SQL servers it was not so successful at re-attaching the users.

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.

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

No comments: