By: Tibor Nagy
Overview
One of the daily administration tasks on every system is to manage backups. You have to take backups, verify the contents and sometimes you have to restore data. This is also true for SQL databases. However, MySQL and MS SQL implement different ways of backup and restore technology, so it is worth to reviewing the differences.
Explanation
In the MS SQL world you can use the built-in T-SQL commands to backup and restore databases. There is no need to use tools like mysqlhotcopy and mysqldump. The backup methods discussed in this chapter are online operations.
MS SQL Server provides you three different backup strategies:
- Simple Recovery Model
- Full Recovery Model
- Bulk-Logged Recovery Model
Simple model is useful when the database is rarely updated or for testing and development purposes. In case the database is damaged, only the most recent backup can be recovered and all changes since this backup are lost.
The recommended model is the full recovery if no data loss is acceptable. This mode is similar to the MySQL feature when the binary log is enabled. You can recover the database to any point of time, but you should regularly backup the transaction log as well as the database.
The bulk-logged model can be used for large bulk operations such as importing data or creating indexes on big tables. It does not support point-in-time recovery so it is generally used as a temporary solution.
General syntax for changing the recovery model::
ALTER DATABASE {database_name} SET RECOVERY <FULL | BULK_LOGGED | SIMPLE>General syntax of backup command:
--Database backup
BACKUP DATABASE {database_name} <file_or_filegroup_name> TO <backup_device> [ WITH DIFFERENTIAL ]
--Transaction log backup
BACKUP LOG { database_name} TO <backup_device> [ WITH NORECOVERY ]
General syntax to verify a backup::
RESTORE VERIFYONLY FROM <backup_device> [ WITH options ]
General syntax of restore command:
--Database restore
RESTORE DATABASE { database_name } <file_or_filegroup_name> [ FROM <backup_device> ] [ WITH options ]
--Transaction log restore
RESTORE LOG { database_name } <file_or_filegroup_or_pages> [ FROM <backup_device> ] [ WITH options ]
Additional Information
- SQL Server Backup Tutorial
- SQL Server Restore Tutorial
- Backup and Recovery Tips category at MSSQLTips.com
