Advertisements

Archive

Archive for the ‘Disaster Recovery’ Category

Quick Things to Do When DBCC CHECKDB Display Corruption Message

December 3, 2018 Leave a comment

Have you ever wondered that you are working on Microsoft SQL Server and database shows some abnormal file crash or error issue? And, when you execute the DBCC CHECKDB command, it reports corruption in your SQL Server database.

Definitely, this scenario is a nightmare and the saddest part, it is frequently faced by many SQL database administrators.

Now, the biggest question is ‘What to do when DBCC CHECKDB reports corruption in database?’

So, The long wait is finally over!

Here, we are going to tell you what step should be taken when the SQL database gets corrupted. Thus, one can secure its confidential and important database in any data disaster situation. But, before understanding the method, a user must know about DBCC CHECKDB and which type of corruption it is reported?

Without wasting any minute, Let’s begin!

Take a Quick Look on SQL Server DBCC CHECKDB

DBCC CHECKDB is a type of T-SQL command whose prime function is to monitor the logical as well as physical integrity of all object in a selected database. DBCC CHECKDB command is supported on SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, Azure SQL Database, and SQL Server 2019. Furthermore, it comprises three main commands in it for different purposes.

DBCC CHECKALLOC – Its function is to verify the organization of disk space allocation or page usage for a selected SQL database. Besides this, it also checks various internal structures to maintain the record of allocation of all pages in the database as well as relationship between them too.

DBCC CHECKTABLE – This Transact-SQL command is used to check the logical and physical consistency of the pages and structure that create table or system table indexes.

DBCC CHECKCATALOG – This command is used to perform various catalog consistency between system metadata tables. For the execution of this T-SQL command, the database must be in online mode.

The above-described command runs as a part of DBCC CHECKDB. If the user executes the DBCC CHECKDB command, then there is no requirement to use all these commands separately. In case, when the database DBCC check reports any error, just take the help of following solutions in order to save the important database table.

Methods Use to Repair Corrupted Database in SQL Server

In the following section, three different workarounds will be described to restore data objects from a damaged or corrupted SQL database. A user can go through all these methods and select that is appropriate.

Approach #1: Use REPAIR_ALLOW_DATA_LOSS Command
When the DBCC CHECKDB reports corruption error, then you can use Repair command on the specified database. It is devised to fix all the reported error. However, the biggest disadvantage of this method is it leads to data loss. Even, when the user uses this command to extract the database from last clean backup, it results in more data loss. In fact, Microsoft does not recommend this method suitable to recover data from the good backup. It is marked as an emergency option that can be used when the data cannot be recovered from the last backup file.

Approach #2: Restore Database from Backup
If the last good backup of database is available, then it is better to recover the database from it with the help of RESTORE DATABASE command. With the help of Restore statement, one can perform multiple operations.

  • Retrieve only a part of SQL database.
  • Recover specific pages of a database.
  • Extract complete database from full backup.
  • Capable to get back a transaction log onto a database.
  • Provide an option to restore only desired file or filegroups.

In case, when the user does not have a last clean backup of the database, then the Restore command does not work.

Approach #3: Use Expert Solution SQL Database Recovery
The above two solutions have some consequences, so it is better to show your trust in a reliable solution because data loss cannot be ignored. That is why many SQL administrators uses MDF Repair Tool when DBCC CHECKDB shows any data corruption error. This utility has an advanced scanning technique that scans the highly corrupted primary (MDF) or secondary (NDF) file. And, recover the data object such as triggers, tables from it.

The great part of this software is user can export the recovered file in SQL Server Database or SQL Server Compatible SQL Scripts. Moreover, SQL Database Recovery Tool supports to repair corrupted Database of version 2017, 2016, 2014, 2012, 2008, 2008 R2, 2005, 2000.

Final Note

It is a head-scratching situation when the SQL database got corrupted and no relevant solution is not in the hand. So, after considering this painful situation, in this article, we have discussed about DBCC CHECKDB that reports error in the database. Along with this, three different solutions have illustrated to recover data from corrupted SQL database.

Advertisements
Categories: Disaster Recovery

How to Recover SQL Database Without Log File: An Ultimate Guide

November 1, 2018 Leave a comment

“Recently I was trying to recover a database that contains MDF and LDF data files. All of a sudden, I got .mdf file from a standard backup tape. However, sp_detach_db was not run on database before the backup of MDF file, so I do not have an LDF file. I know the stored procedure sp_attach_single_file_db can recreate a log file in most cases, and I have tried it to reattach the database, but I receive the following error. If anyone know the solution on how to recover SQL database without log file, please help me out.”

Are you also getting the same error message? Searching for an instant or reliable method to fix it? Do not worry, you are landed on the perfect page. There are multiple users who are facing this problem. Therefore, after understanding the above scenario we have come up with a manual solution. Before that, let us discuss all possible reasons for repairing SQL database without log file.

Reasons Behind Recovery of SQL Database Without LDF file

Go through the following reasons due to which users need to recover database .mdf file without .ldf. Some of them are listed below:

  • Most of the people do not know that log (.ldf) file contains precious transaction data and it is required.
  • There are cases where log file too bit and users have never cared for its size and content. When they want to transfer database to another server they only want the .mdf file to be moved to a new instance.
  • The log file gets corrupted due to hardware failure.

Ways to Recover SQL Database Without Log File

There are different methods through which uses can recover SQL database without using transaction log file. Follow the set of instructions that are listed below:

Way #1: Using T-SQL Method

You can run sp_detach_db on the database to reattach a database with sp_attach_db or sp_attach_single_file_db. Using sp_detach_db ensures the transactional consistency within a database and retains the data integrity. However, if the data integrity is not required or no data has been changed, you will be able to use undocumented Database Consistency Checker (DBCC) REBUILD_LOG cmdlt that Listing 1 shows to attach a database. REBUILD_LOG will re-create the another log file and reattach a particular database even if a valid log file does not exist. But, the data might not eventually consistent because you could have thrown away active and uncommitted transactions. Use this script only for emergency recovery when you need to move data to another database.

Way #2: Using SQL Server Management Studio (SSMS)

Be sure that the following steps will work only if the database has been clean shutdown and primary file (.mdf) is available. Please have a look:

  • Under Object Explorer window, right click on Database and go to Attach option

  • On the Attach Databases, a dialog box appears and click on Add

  • The dialog box Locate Database Files appears, browse the location where MDF file is located, hit on file to select and then press OK button to exit. A new log file is created by SQL Server while repairing the database. Now, the database will appear in Databases

  • Now, back to the Attach Database box. In the database details, you will observe that SQL Server is unable to find log files (.ldf).
  • To attach the MDF file without LDF, choose the transaction log file and after that, click on Remove

Expert Solution to Repair SQL Database File Without LDF File

As we look at the different methods to recover SQL database without log file. It is more clear that all these manual ways have their respective drawbacks. These methods do not provide the satisfactory results. Thus, we have come with a remarkable tool named as SQL Database Repair Tool. It is the best solution that helps to repair both primary (.mdf) and secondary (.ndf) SQL database files. In addition, it is capable enough to restore deleted SQL database table’s without any hassle. It has simple, easy-to-use and user-friendly interface which makes easy for users to accomplish the task effectively.

Final Thoughts

While working with Microsoft SQL Server, the situation arises in which database got corrupted. Now, it is necessary to recover all the corrupt database files in SQL Server. Thus, in this blog, we have discussed both manual and professional solutions to let users understand how to recover SQL database without log file. Also, we have covered the most important features of the tool in an absolute way.

Categories: Disaster Recovery

Why backup testing is important?

October 21, 2009 Leave a comment

How do you do backup testing? In normal cases we backup data from SQL Server (either using SQL Server Agent, or another 3rd party backup agent) to Disk and then to Tape or Directly to tape. We setup alerts on backups to make sure the backup job completed successfully or failed? What else we can do, we can implement verification test after backups to make sure backups are valid. Is that really enough?

What other things should be considered? Every part of the backup and restore process needs to be tested to confirm you have good backups. It should be part of policy to test this as often as possible with minimum at quarterly.

** Assuming using SQL Server Agent Backups **.

1) Confirm backups (Email alerts, manual checks).
2) Confirm backups are going to Tape directly or are being picked up from Operating System backup (Email alerts, manual checks).
3) Request a restore from Tape (make sure it is from off-site location and not Virtual Tape Library (VTL)).
4) Run Verification on the backup to make sure no issues with tape drive and backup file is still valid).
5) Restore the database in a testing environment.
6) Check security and GET END user to try it out.
7) Verify ALL infrastructure items are there, configured, and working (tape backup, tape agent, sql backup, security, disk space, disk availability, etc.)

So Step 1-2 are normal; but that is not where backup confirmation ends. You should occasionally request restore, verify it and restore it in testing environment. Step 6 is seem a bit strange but it just as much part of restore process, I restore the database I am done but if no one can still connect? Then have I really recovered from disaster? Step 7 is explained later….

So why am I writing about this? This is basic DBA duties that should be hard-coded into our brains. Well I thought it was hard coded in my brain but sometimes because of other project constraints, time, money, etc. Process is skipped well in one such case this happened to me.

I configured SQL Server, all standard jobs; disk backups and email alerts. Everything works nice I did not get any errors. Backups were going off server that has been production for a while setup before my time on the team. So I assume everything was correct as we have been using this dump location for a while. This was new server so I didn’t get chance to run verification steps listed above after the server was up, assuming things are going well, I got busy on other tasks. Well that was bad decision on my part, because I ASSUMED! things were configured when they were not. This is were #7 comes into play, I assumed and was left in an awe when I was told this dump location doesn’t have any tape agent installed on it.

I got contacted by client, because of a bug in the application it allowed the client to delete their ENTIRE WEBSITE (Content Managed System) with SQL Server Backend. I found out 1 day after the fact so I did not have backups on disk any more. I contact the Storage guys to ask for restore and found out they DON’T HAVE ANY tape backups for my dump location. So I quickly made copy of what Transaction Log Backups, Data backups I hand on hand to see if I can get some kind of restores. But found the transaction log backups needed the full backup that has been over written already.

Since this was a complete data lose I had to turn to 3rd party vendor tools for data-recovery in non-standard manner. I used the ApexSQL Log tool to read the transaction log to recovery engineer all the records that were deleted. However using this tool found few annoyances that you’ll have to watch out for:

1) The tool will no generate any recovery script for binary type objects. In file format or other wise.
2) If you have large varchar columns or such the system will try to be creative and change the Enter/Carriage Return to Char(10) + Char(13) string concatenation. Problem with that is for very large varchar columns SQL Server could not prase the SQL String successfully any more, for more please read here.
3) In default settings the code does not generate the GUIDS, so if your database heavily relies on this this setting has be turned off before reverse engineering.
4) While the tool is reading transaction log it chugs through alot of memory, for example reverse engineering, transaction logs with over 1M transactions with 1.5GB ram program crashed on me every time. So I processed the transaction logs in small chucks.
5) I found the tool to be extremely slow when selecting/unselecting and the filter functionality to be limiting.

So aside from these limitations this tool still saved me, it couldn’t recovery enough to get site working. But it recovered enough that CMS product vendor was able to recreate the pages because of the redundant nature of the data.

%d bloggers like this: