mssqltips logo

MySQL to SQL Server Backup and Restore Differences



By:

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


Last Update: 5/27/2011




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools