Great review of the best available options on How to automate SQL Server database backups
SQL Server health checks and backups often seem secondary to the primary business objectives. And, generally, it is so until your server is down or the data is lost. Only then the true importance of automating these mundane SQL Server maintenance tasks become apparent.
SqlBak.com is there to take care of such “secondary” issues and make sure that database admins don’t have to think about it. That is why on top of backup services we added SQL Server Automatic Health Check option.
Imagine how cool would it be: you are on vacation on a beach when you receive a panicked email from your client telling you he has lost all his data. You take your smartphone, log in to SqlBak.com, press “Restore” next to the last backup and in a few minutes tell the client that his database was restored. This is as close to James Bond as a database administrator could ever get! Well, it is not a dream any more.
Continue reading How to restore SQL Server database remotely
Amazon S3 is a great and cheap place to keep your SQL Server database backups.
SQLBackupAndFTP starting from the Standard version now allows you to backup SQL Server databases to Amazon S3 directly.
To set up the backups you need to have your Access Key and Secret Key that you can find under “Security Credentials” in Amazon Web Services: https://aws-portal.amazon.com/gp/aws/securityCredentials#access_credentials
Google Drive starts you off with 5GB for free. Seems like a great place to keep you SQL Server database backups.
SQLBackupAndFTP starting from the Standard version now allows you to backup SQL Server databases to Google Drive directly. On the main form just select Google Drive as your backup destination, authorize the program with Google – and you are done!
SkyDrive from Microsoft gives you 25GB of cloud space for free. Seems like a great place to keep you SQL Server database backups.
Here’s how to backup SQL Server databases to SkyDrive using SQLBackupAndFTP:
- When you install SkyDrive, it creates a special folder. Everything that you put into this folder will be synced to the SkyDrive servers in the cloud.
- Open SQLBackupAndFTP, select databases to backup, check “Store backups in a local/network folder” and set the folder to be that SkyDrive folder.
Note that there’s a big drawback – SkyDrive will sync only when you are logged in.
Dropbox allows you to store 2GB for free – this should be enough space to store SQL Server backups for the majority of small clients. Free version of SQLBackupAndFTP now allows you to backup SQL Server databases to Dropbox directly.
- Select the databases you want to backup
- Click “Add backup Destination” and select Dropbox
- Click Authorize button to allow SQLBackupAndFTP to save to your Dropbox account
- Click Test or Run Now to verify that backups work correctly
- The backups will be stored in Apps\mySQLBackupAndFTP folder in your Dropbox account
- Now schedule your SQL backups and sleep well
There are few common tasks in SQL server that should work out of the box, but give errors that confuse less experiences developers. Like when you try to restore a backup from another server, you get:
Restore failed for Server ‘.\SQLEXPRESS’. (Microsoft.SqlServer.SmoExtended)
System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘SomeDb’ database. (Microsoft.SqlServer.Smo)
The solution could not be simpler: you should use WITH REPLACE option of RESTORE command, or in SQL Server Management studio select “Options” page and check “Overwrite the existing database (WITH REPLACE)”
SQL Server is smart in selecting great default options for a new database. If you can afford to lose the changes between backups to keep maintenance simple (and in most of non-critical databases it is a reasonable compromise) and you backup your database regularly using a tool like SQLBackupAndFTP, then the “Simple” recovery model (selected by default) is appropriate for you. However if you ever had massive data operations, you may notice that the size of your transaction log (LDF) file is huge. The reason for it is that SQL server does not automatically shrinks the size of transaction log.
To keep log file under control, it may be tempting to enable Auto Shrink option. That would be a mistake – it is an evil option that should always be off. Shrink is a heavy procedure that should only be used rarely.
Instead just run this command to decrease the size of the data and log files to leave 10 percent free space (read more):
DBCC SHRINKDATABASE (YourDatabaseName, 10)
It is not over yet! Most often shrink will increase fragmentation of your indexes. To defragment indexes, you’ll need to use ALTER DATABASE – see this link