There are actually many common scenarios where recover sql database from backup without restoring the backup is recommended:

  • To lower a database downtime.
  • many data versions are essential for development uses, together with the demand for fast accessibility and low system over head.
  • only certain rows should be restored.

SQL

Reducing a database downtime:

Among the important needs each and every DBA needs to make sure is returning for the most recent SQL database backup immediately on a request. You will discover a myriad number of scenarios if the SQL database back-up recovery should be instantly conducted:

  • several system problems, whether it’s a hard disk or some other essential server hardware aspect, or perhaps something software break down.
  • accidental or destructive DROP, TRUNCATE, or DELETE function executions, as well as UPDATEs without having WHERE clauses.

No matter what the scenario is, the speed in which the database is going to be recovered back in life is of heart and soul.

Shown to be several methods to speed up the SQL database backup recovery procedure. One of these is to keep database backups as well as the database by itself on various physical drives. Even though this can be a popular protection process, if the disk that has the data source fails, another one containing the backup will stay accessible – its effect for the recovery process isn’t insignificant. The specific file system I/O functions throughout the database recovery will likely be divided on two distinct disks (one using the backup will do the majority of the reed operations, as the one using the database carry out the write functions) therefore improving the task speed substantially.

The other would be to make use of the native database backup compression in SQL Server. As using the earlier defined technique, this offers a two-fold advantage as well. The backups take up fewer disk space because of compression, and are generally restored two times as fast as uncompressed ones. It is due to important variation within the CPU as well as the memory speed vs the hard disk speed – the data compression cuts down on backup size that leads to much less disk action and eventually within the restoration procedure speed improve.

On the other hand, no matter what you do, restoring a large database (e.g. a number of  GB or much more) needs a much level of each sources and time.

Our suggestions, that may also carry out each defined methods, reducing the database downtime much more, is to use third-party SQL restore tools, just like Red Gate SQL Virtual restore or ApexSQL Restore, which are capable to connect SQL backups and make use of them as live data source, which have been available via SQL Server Management Studio, Visual Studio, and so on.

The Red Gate SQL Virtual Restore allows you to mount a back-up file being a completely efficient data source, providing you accessibility to backed-up data. Compared with an average restore process, little extra disk space is needed.

The ApexSQL Recover uses the backup restoring abilities from the SQL Server by itself, using exclusive methods to slow up the size of the recovered backup, and helps make the recover process as much as 25 times quicker, based on the size as well as the data types held in the data source backup.

Decreasing the database downtime, caused by data source recovery process, could be reduced by splitting the database plus the backup files on various disks, and taking advantage of the backup compression.

Offering various data versions

Any large-scale project needs several environments throughout the development. For example various server machine options, SQL Server editions, schema configurations, as well as the data versions tests.

Because of higher expenses, needed environment configurations are generally setup utilizing the virtualization technology and virtual machines that works on a single or even more physical servers.

Finally, is definitely of fact that a final development project, as soon as it enters the development, basically performs which is formally analyzed with several data versions. The measure and the real of data required while in the development may, and must be huge. Additionally, various data is generally used by developers and testers based on their particular requirements.

When considering towards the various data versions, the process isn’t complex, but instead time and resources taking .The different data variations need could be obtained by ‘basically’ restoring and overwriting earlier used data source using its backup including distinct data version. This approach needs not just disk space and time, but uses major system resources like CPU and memory as well.

Recovering specific rows

There may be many circumstances where recovery of just specific rows is needed. Once again, one of the most common are due to malicious or accidental performance of UPDATE or DELETE transactions. Despite that such situations could be fixed while using tools like Quest’s Toad for SQL Server, or ApexSQL Log, by developing an undo SQL script, some requirements are needed (e.g. the complete recovery model, as well as the entire sequence of transaction logs) which could avoid successful recovery.

However, even though a database is with the Easy recovery model, the particular rows should be recovered is usually obtained through the database backups, if the backups have been managed on normal basics as well as the losing data is available with them.

The SQL Server database recovery might do the job, only if there weren’t any data modifications used after the database backup creation. If that’s incorrect, to recover the particular rows, make sure you:

  • Restore the database on the different SQL Server case, or a similar case by using a various database names.
  • Find and recover the particular rows while using original ones through the restored database.

This technique uses the SQL Server Import and Export Data function, however that needs an extra SQL scripting as well as schema mapping. In addition, some issues with breaking primary and foreign keys, or inserting duplicate data may happen if the rows from several tables have been retrieved.

Since the remedy for recovering specific rows, I will suggest using professional SQL Server data comparison and synchronization resources (there are actually a lot of these around the market), which may find data variations and give restoration of lost and or broken data with extensive records on recognized variations.

These tools can cut the need of both the native backup restoration, as well as the difficulty of selecting and inserting lost data, within a few easy clicks.

Conclusion

With luck , these methods will help you for the performing database backup recovery. We have defined a few of these, so you can just opt for the ones that are most right to suit your needs and or your business. If you have ever required to carry out this type of job, and you have efficiently done it with a couple other alternatives, you can place that in the comments section below.

82 Shares
+1
Tweet
Share5
Pin75
Share1