Having a good recovery plan that ensures database backups are being taken on a regular basis and safely stored on specific or multiple storage drives greatly increases database safety and recovery options when a disaster or accident occurs. Nevertheless, creating regular full database backups doesn’t provide full point-in-time recovery, since restoring the latest full backup will restore a database to a proper state at the time a backup was created, but all changes, both schema and data, which have occurred after the last full backup will be practically lost.
When confronted with a disaster recovery scenario with a very large database, but a small group of effected records, an opportunity exists to both speed the process and reduce risk of further damaging data, by updating more rows than necessary, simply by narrowing the subset of compared records.
ApexSQL Data Diff offers the ability to narrow the rows that should be synchronized, so only affected rows are updated. But if the filtering isn’t done properly, you risk “false positives”, rows that you have flagged as different/changed and should be synchronized, even if they weren’t in the subset of affected rows. Synchronizing these rows will update “good” data and roll back any production changes that may have been made, further damaging your data.
No matter how well managed your systems are, accidents may still occur, and potentially lead to disastrous consequences. In order to ensure that there is a disaster recovery solution available, it is always good to have a standby copy of a primary database on another SQL Server instance.
The first way to achieve this is to utilize the SQL Server Log shipping.
More and more companies are creating online catalogues to allow customers to browse their objects prior to coming to their store
Having a list with a price and description just doesn’t cut it anymore. People want to see what the product looks like and as such having images associated to each item is pivotal
When you ask a DBA what feature they would most like to see added to the SQL Server recovery functionality, they will undoubtedly say: “The ability to recover a single table from a database backup”.
Of course, it is possible to retrieve the data of a specific table from a database backup, but you cannot do it without restoring the entire backup.
Why do DBA’s want this? Well, the reasons are simple:
There are certain circumstances in which you may need to roll back your database to a specific point in time. There may be various reasons why this could be necessary but it is usually related to the execution of accidental or malicious DML or DDL statements. For example:
How often have you wished you could just quickly undo a DML statement without having to go through the lengthy process of restoring your database backup?
SQL Server database corruption recovery with transaction log backups
The worst case scenario a DBA can encounter is a SQL Server database data-file corruption (due to physical or some other occurrence, the data files can be damaged and inaccessible)
Every SQL Server database is mapped over a set of operating-system files. These files store data and log information. Individual files are used only by one database, and data and log information are never mixed in the same file. While data is stored in an MDF file, all transactions and the SQL Server database modifications made by each transaction are stored in an LDF file – a transaction log file which is an essential component of the database. Conceptually, the log file is a string of log records. Physically, the log records are stored in one or the set of physical LDF files that implement the transaction log
Tracking changes is an essential SQL Server security task. Besides data change history, which includes DML operations (e.g. INSERT, UPDATE, and DELETE), tracking DDL changes in SQL Server, changes that affect database objects, is of high importance too. In this regard, various techniques can be used as a schema change auditing solution. One of the most common method are DDL Triggers