Azure SQL Database Point-in-time restore feature

Microsoft China TechNet 7 Oct 9:17 PM

• Comments 0
• Likes

Original address: Http://blogs.technet.com/b/azuretw/archive/2014/10/06/azure-sql-database-point-in-time-restore.aspx

This article describes the point-in-time restore feature of Azure SQL database , which is a feature in the Azure SQL database Basic ,Standard andpremium are available. in the previous article, theAzure SQL database team has introduced the Azure SQL database new features, which also include point-in-time restores, which you can refer to in the following chart. in this chart we can see that the point-in-time restore function is to use the most recent data backup to restore a damaged or deleted database.

RTO (Recovery time Objective): The length of time that tolerable data is lost

RPO (Recovery point Objective): How long will the system return to normal?

Operational continuity and disaster recovery (BCDR, business continuity and disaster Recovery) related features	Basic Edition	Standard Edition	Premium Edition
Real-time restore (point in time Restore)	Span lang= "Zh-chs" to revert to the past 7 a point in the day	restore past 14 a point in the day	revert past 35
Geo-restore (geo-restore)	rto<24 hours rpo<24 hour	rto<24 hours rpo<24 hours	rto<24 hours rpo<24 Hours
Standard offsite data replication redundancy (Standard geo-replication)	Not supported	Rto<2 hours rpo<30 min	Rto<2 hours rpo<30 min
Proactive Offsite data replication redundancy (Active geo-replication)	Not supported	Not supported	Rto<1 hours rpo<5 min

What is point-in- time restore (Point-in-time Restore)?

all databases in the Azure SQL database service are protected by an automated backup system (automated backup systems) . the retention period for backups will vary depending on the Azure SQL Database tier of your subscription, with premium for 35 days, standard for days, and Basic for 7 days.

Point-in-time reverts to a self-service (self-service)that allows the customer to restore the database with backups made during the retention period. When you use the point-in-time restore feature, a new database is re-established.

Azure SQL Database backup takes automatic backups, you do not need to set up and there is no additional charge for backups, you only need to pay extra when using point-in-time restore functionality. The new database created at the time of restoration is charged the same as normal database charges.

All in all, automated backup systems and point-in-time restores provide 0 cost and 0 management ways to protect your data, and you can respond at any point in the retention period for any reason that the database is damaged.

Learn what automatic backup is (Automatic Backups)

The Basic, Standard, Premium editions of all Azure SQL database databases provide automatic backup capabilities. Azure SQL Database will do a full backup once a week (backups), make a differential backup (differential backup) every day, and make a transaction history backup every five minutes (log backups) . The first full backup is started when the database is established, which typically takes up to two minutes or more to complete. If the database is inherently large (born big) (for example, if a database is created as a database copy or a database is restored from a large database), the time taken to backup takes longer. after the first backup is complete, all subsequent backups are automatically scheduled by the system and are "silently" managed on the backend.

the exact execution point of full backup (fully backups) and differential backup (differential backups) is determined by the system's current load on the system basis. The backup file is stored in the same data center as the current database.

When you restore a database, the backup files required for the restore are obtained from the local redundancy . the weekly and daily backups are replicated to Microsoft Azure geo-backup's paired data center (paired region) for disaster recovery across data centers. the description of geo-redundancy is described in more detail in previous articles published by the Azure SQL Database team.

to a database in use (Live Database) for Point -in-time restore

Go to the Azure Management Portal to restore versions of Azure SQL Database (Basic, Standard, Premium) to any point in time through simple operations ( But must be within the retention period of the backup.)

You can choose to select the database you want to restore in the list of databases, or go to the dashboard of the database and click the " Restore" option.

You will be prompted to enter a new database name and use the swipe component to select the point in time that you want to restore (which must be within the retention period), and youcan manually enter the closest point in time. when you are done pressing the Confirm key, the database will begin to revert to the point you set.

The time required to restore a database is determined by a number of factors, including: Database size, pick-up time, the number of States to restore to the point-in-time database that needs to be re-architected, and so on, usually a large database that takes several hours to restore.

The restored database will be built on the same server as the original database, and therefore we need to re-give the database a new name. The service level for restoring a database is the same as the service level set by the database at restore point in time.

You need to make sure that your database has enough database transfer Unit (DTU), and also note that the new database created at the time of restoration may have a different service level than the current database.

Once the restore action has been completed, the restored database charges are the same as for the normal database.

You can restore the database to replace the original database, or use the restored database as a data retrieval, and then update the raw database.

If you are restoring a database to replace an existing database, you should verify that the service level and performance level are appropriate and expand as needed. By changing the name of the original database, and then using the T-SQL Directive ALTER DATABASE to change the name to the name of the original database, this would completely replace the original database.

If you plan to read data from a backup database, you will need to write and execute the Script that is relevant to the data you need to reply separately .

Although restoring a database takes a considerable amount of time, restoring the database appears on the list of databases when the process is restored. You can remove the restore database during the restore process, which will cancel the restore action, and you will not have to pay any fees.

Restore a recently deleted database

You can restore a database that was accidentally deleted within its retention period to a point in time that was deleted, or earlier.

You can use the Azure Management portal to restore a deleted database, first in the Azure SQL Database list, choose the deleted database option, You will see a list of databases that have been deleted within the retention period.

Note: If you reuse the same database name too many times, you need to pay more attention to the time of the deletion so that it does not revert to the wrong database.

As with previous restore steps, you need to add a name to the restore database, and your restored database can only be restored to the same server as the original database. in particular, in this case the restore step is only able to select a fixed point in time for the restore. It is also important to note that after you remove the server (servers), you will not be able to recover all the databases that were previously stored on that server.

Backup (Backup)/ restore (Restore) vs. copy (copy)/ export / Import

Point-in-time restores can restore data to a damaged or deleted database. Point-in-time restore does not use the previous Azure SQL database web/business to apply replication to the technical base, the actual export/auto export (Automated export) method (This method is more expensive) to reply to the database. This improvement alone proves the benefits of the new Azure SQL Database.

• new service tier backup and restore costs are relatively inexpensive ( In terms of backup, if there is no additional backup to add, there is no charge, and the charge is to ensure that your copy of the database is exported consistent with the storage Bacpac file).
• better RPO: You can revert to a specific point in time, And time can be used to (1 minutes) in a finer scale than using export import.
• RTP: Restoring from a backup is faster than the way you import it.

Note: Although it is relatively inexpensive and fast to reply to a database during the retention period, using the Copy/ export/ import approach still applies to longer term database backup strategies (long terms archival).

Use APIs Restore Database

In addition to restoring your database through the Azure management portal, you can also use PowerShell (start-azuresqldatabaserestore) and SQL database Management API to restore the database.

Summarize

The automatic backup and point-in-time restore (self-service) feature protects your database from data corruption and deletion, a zero-cost and 0 management approach, regardless of which service tier Basic, Standard, Premium) databases are available. Backup and restore functionality is better than using Copy/import/export in the short-term response, so for short-term database responses We encourage you to use this approach as your business continuity response and only use export/import when long-term archiving or data transfer is required.

• 0 Comments

Azure SQL Database Point-in-time restore feature