One of the main functions for anyone that manages a database is ensuring that they can recover the system in the event of any issues. My view is that restores are the most important skill and task that need to be performed on a database. Since restores require backups, I'd then rank backups as a 1a important task. They're a dependency and necessity to ensure that we can restore data. Having a set of the data, in a transactionally consistent state just feels critically important to me, over everything else..

I saw this new feature from Amazon Aurora for their MySQL compatible database. You can use Backtrack to rewind your database to a previous point in time. On one hand that's an amazing feature. Make a mistake, have an error, click a few buttons and get the database restored back to the minute (or second) when you made a mistake. On the other hand, if you delete a table, do you want to roll all tables back to that point in time?

This seems like an amazing feature. Amazon takes some of the hassles of managing some backups backups. You determine how far back you want to go, in hours, up to three days. Depending on the activity in your database, they charge differently. To me, that's interesting. It makes sense to me as a customer. I do more, they track more, I pay more. This also seems to be a way to capture more money for Amazon by cutting some of the consumer surplus that exists with flat fee pricing, which is something many of us prefer.

The way this works is also different than Azure. The Azure point in time feature allows you to go back, but you can't restore on top of your existing database. You'd need to restore elsewhere, then play the rename game or move data between databases. While that seems inconvenient, if you've ever had someone restore a local SQL Server backup over a database you needed, you might appreciate the safeguards of not allowing a restore on top of an existing database. While the process might seem like a hassle, this does help prevent mistakes during a stressful situation.

Which of these do I like? I prefer the Azure one, though I'd like the restores to be more granular than a minute. The reason is that I rarely want to restore in a disaster over the existing database. In most applications I've managed, there are updates to multiple parts of the database. A mistake in one table doesn't necessarily mean that data changed in other tables should be discarded. Even during deployments, when things go wrong, I've often just broken one set of tables and rolling back the entire database in a restore is painful. Usually I'd prefer to undo what I can and get the any missing data from a restored copy of my database.

Perhaps it's just me, but I find the idea of allowing clients, or even many technical people, to easily roll back an entire database after a mistake to be very dangerous. By the time we recognize the mistake, verify data, notify others, we might have lots of changes in many tables. Abandoning that data for the sake of convenience is something that's unnecessary. I also worry many people trying this feature don't think through the implications of rolling back an entire database. If you feel differently, let me know. There are cases this is certainly helpful, but I think I'd rather have a "restore to a new db and rename both" automated task instead of AWS Backtrack.

Join the debate, and respond to today's editorial on the forums

Answer: All the alternatives are correct.

Explanation: A page is considered "suspect" when the SQL Server Database Engine encounters one of the following errors when it tries to read a data page:

- An 823 error that was caused by a cyclic redundancy check (CRC) issued by the operating system, such as a disk error (certain hardware errors);

- An 824 error, such as a torn page (any logical error)
The page ID of every suspect page is recorded in the suspect_pages table. The Database Engine records any suspect pages encountered during regular processing, such as the following:

- A query has to read a page;

- During a DBCC CHECKDB operation;

- During a backup operation;

- The suspect_pages table is also updated as necessary during a restore operation, a DBCC repair operation, or a drop database operation; and

- The suspect_pages table contains one row per page that failed with an 824 error, up to a limit of 1,000 rows. The following table shows errors logged in the event_type column of the suspect_pages table.

References: click here

» Discuss this question and answer on the forums