Pages

Monday, 24 May 2021

How to Repair a Database in SQL Server Management Studio

As a SQL database administrator, it’s your responsibility to maintain and secure the databases. However, you may still experience database corruption. If not handled correctly and on time, you may lose potential data. This post will discuss how you can repair database in SQL Server Management Studio (SSMS) when there is no backup. Also, we will discuss an alternative solution to repair a SQL database without data loss risk. But let’s first discuss the possible reasons that can result in database corruption.

Possible Reasons for SQL Database Corruption

  • Server crashes or the system shuts down abruptly in the middle of processing data
  • I/O subsystem failure
  • Bug in the SQL software

You can call your hardware vendor for support to fix issues with the hardware. If the problem is with your SQL software, check out for updates released by Microsoft to fix the bugs in SQL Server. However, in case of a system crash or abrupt system shutdown, you will need to repair the corrupted database to recover its data. 

Before We Proceed

Make sure that your system meets these prerequisites:

  • SSMS must be installed on your machine. If it is not installed, you can download and install it from here.
  • SQL must be installed on your PC.

Steps to Repair Database in SQL Server Management Studio

  • Launch SQL Server Management Studio and connect to a server instance.
  • In the Object Explorer window, click the ‘+’ sign to expand databases.
  • Right-click on a database that you want to repair, and then select Properties. 
  • In the ‘Database Properties’ dialog box, click the Options tab from the left pane. In the Options screen, scroll down to the bottom of the screen.
  • To repair a SQL database, you will need to set it to single-user mode. For this, Restrict Access to SINGLE_USER from MULTI_USER. Click OK. 
You can also set the database to single-user mode by executing the following query:

ALTER DATABASE Test SET SINGLE_USER


Here ‘Test’ is the name of the database that needs to be repaired, replace it with your corrupted database.

Now, to repair the SQL database, you need to run the DBCC CHECKDB command with any of these repair options: REPAIR_FAST, REPAIR_REBUILD, and REPAIR_ALLOW_DATA_LOSS.

REPAIR OPTIONS

Description

REPAIR_FAST

It only maintains the syntax for backward compatibility and does not perform any repair actions.

REPAIR_REBUILD

It helps perform quick repair tasks without any data loss. It can be used for repairing missing rows in nonclustered indexes and rebuilding an index. It cannot repair errors associated with FILESTREAM data.

REPAIR_ALLOW_DATA_LOSS

It can repair all the errors reported by DBCC CHECKDB. But, Microsoft advises on using this option as a last resort to repair the SQL database, resulting in data loss.


Note: You can try using a SQL Recovery Software, such as Stellar Repair for MS SQL to fix all types of corruption errors. It can help repair a SQL database without any data loss risk. 

Click ‘New Query’ from the main menu. 


In the query window that opens, copy and paste the command below:

DBCC CHECKDB ('DB_Name’, REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD) WITH ALL_ERRORMSGS, NO_INFOMSGS;

GO

In the above command, replace ‘DB_Name’ with the name of your corrupted SQL database. And choose one of the repair options. Here’s an example of using the DBCC CHECKDB with ‘REPAIR_ALLOW_DATA_LOSS’ option to repair the database.


  • Click the Execute button to run the query.
  • This will repair the database. Change the database mode from ‘SINGLE_USER’ to ‘MULTI-USER’ by performing steps 4–6 above. Make sure to close and re-launch the SQL Server Management Studio for the changes to take effect.

How Can I Recover Missing Data after Running DBCC CHECKDB?

If you find missing data in the repaired database after running the DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS command, consider using a SQL repair tool. Recommended by DBAs and MVPs worldwide, Stellar Repair for MS SQL is a reliable software you can use to handle corruption errors in a SQL database. It helps repair a severely corrupted MDF/NDF file on Windows as well as Linux systems.

The software recovers all the database objects, like tables, views, keys, stored procedures, etc., without making any changes to the original database structure. It also helps recover deleted records.

Conclusion

This article outlined the most common reasons leading to SQL database corruption. While restoring from backup is the most recommended and obvious choice to deal with database corruption, there are times when the backup is not updated or has turned corrupt. In that case, you need to try repairing the database. This article covered the step-wise instructions following which you can repair the database in SQL Server Management Studio. Also, it advised using a SQL recovery tool as an alternative to DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS command. 

50 comments:

  1. I am grateful for this blog to distribute knowledge about this significant topic. Here I found different segments and now I am going to use these new instructions with new enthusiasm. Server Management Marco Island

    ReplyDelete
  2. Get Packers and Movers Jaipur List of Top Reliable, 100% Affordable, Verified and Secured Service Provider. Get Free ###Packers and Movers Jaipur Price Quotation instantly and Save Cost and Time. Packers and Movers Jaipur ✔✔✔Reviews and Compare Charges for household Shifting, Home/Office Relocation, ***Car Transportation, Pet Relocation, Bike SHifting @ Packers And Movers Jaipur

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. Packers and Movers Hyderabad Give Certified and Verified Service Providers, Cheap and Best ###Office Relocation Charges, ***Home Shifting, ✔✔✔Goods Insurance worth Rs. 10,000, Assurance for Local and Domestic House Shifting. Safe and Reliable Household Shifting Services in Hyderabad with Reasonable Packers and Movers Price Quotation @ Packers And Movers Hyderabad

    ReplyDelete
  5. I like this post because we can get some useful information from your blog. This blog is very good. Street View Online

    ReplyDelete
  6. If you looking for how to repair deleted photo must visit
    Repair Corrupted Image Files

    ReplyDelete
  7. Thanks a lot for giving us such a helpful information. You can also visit our website for imt cdl project report sample

    ReplyDelete
  8. Hello , lovers of cricket welcome to our our fantasy cricket application means fantasypower11 which is provide you online cricket information
    any time to know all cricket match online ball to ball and over to over play cricket online.
    play cricket online

    ReplyDelete
  9. Your blog is great. I read a lot of interesting things from it. Thank you very much for sharing. Hope you will update more news in the future. for More Information Click:- Epson Scanner Error 000041

    ReplyDelete
  10. Are you looking for Leolist Calgary? We have the best alternative of Leolist Calgary here on https://calgary.xgirl.ca/ .
    Visit calgary.xgirl.ca/ today and find the best results related to Leolist Calgary.

    ReplyDelete
  11. Are you looking for Leolist Edmonton? We have the best alternative of Leolist Edmonton here on https://edmonton.xgirl.ca/ .
    Visit edmonton.xgirl.ca/ today and find the best results related to Leolist Edmonton.

    ReplyDelete

  12. Are you looking for Leolist Fort McMurray? We have the best alternative of Leolist Fort McMurray here on https://ftmcmurray.xgirl.ca/
    Visit ftmcmurray.xgirl.ca/ today and find the best results related to Leolist Fort McMurray.

    ReplyDelete
  13. better blog
    “http://vrlpackerandmoverss.co.in/packers-and-movers-in-indera-nagar/ “Packers and movers in Indira Nagar At present, there could be any reason that you might require to shift to another place. You might need to shift to a location where you have got a new job or you might need to shift to your own house from a rented place.
    “http://vrlpackerandmoverss.co.in/packers-and-movers-in-bellandur/ “Packers and movers in Bellandur Packing and Moving households takes effort and is very time consuming. But the best packers and movers in Bellandur is here to make this process easier. We provide relocation services not only within the city but also domestic and international shifting services as well, as per the requirements of our clients.
    “http://vrlpackerandmoverss.co.in/packers-and-movers-in-jp-nagar/ “best packers and movers in JP Nagar always ensure safe and smooth delivery of the goods to our customer’s new residence. There are lots of reasons that why mostly people choose to shift with the help of a shifting company because household shifting is a task that is very difficult to be achieved on one’s own as it is full of complexities.
    “http://vrlpackerandmoverss.co.in/packers-and-movers-in-yelahanka/ “Packers and Movers in Yelahanka firstly you have to pack all of your household items in boxes or other packs,Packers and Movers in Yelahanka then you have to load them on a vehicle for transit and then you have to unpack all that stuff at your new place after unloading them first from the vehicle after the move.
    “http://vrlpackerandmoverss.co.in/packers-and-movers-in-old-airport-road/ “Packers and movers in Old Airport Road at any time in your life, one can feel the need to shift to a new place. And unless you have done shifting a lot of times in the past, this shifting process is not as smooth as it sounds. Packers and movers in Old Airport Road the shifting process is very energizing process.
    “http://vrlpackerandmoverss.co.in/packers-and-movers-in-tumkur/ “Packers and Movers in Tumkur is an industrialized city in Karnataka, so its not very unusual there for people to relocate to a new place, as one wants to live near to one’s current job location.
    “http://vrlpackerandmoverss.co.in/packers-and-movers-in-electronic-city/ “packers and movers in electronic city will not only help you in transporting your goods and furniture , but also will properly pack them in the packs or boxes they bring themselves and seal them with the seal or locks so they do not get lostor damaged, before the transit and then will unload and assemble the items in the new place that you have shifted to.
    “http://vrlpackerandmoverss.co.in/packers-and-movers-in-btm-layout/ “ Packers and movers in BTM Layout We always focus on the point that we can make the relocation process comfortable for our clients so that they can associate with us for the years to come. We provide shifting services to various parts of India and ensure the well-timed and secure services to make the whole shifting experience pleasant for our clients.

    ReplyDelete
  14. You have given a very helpful information to all of us. I really loved it. Appreciate your hard work behind it! Please check my blog on DSLR Lenses and please give your kind review on it - bestdslrlens.com

    ReplyDelete
  15. I was thinking to send Rakhi gifts to my brother in Gurgaon and was quite a late to choose one. Then one of my friend suggested gifts-to-india site and it made my day. These people were so awesome and professional I could deliver the gift right on time to my brother in Gurgaon. Since then you guys have become my partner to Send Gifts to Gurgaon and I am grateful to you guys. Source: www.gifts-to-india.com/gifts/gurgaon.asp

    ReplyDelete
  16. Always used to get confuse about SQL management but this article solved my confusion! Also check my blog on Selfie Instagram Captions

    ReplyDelete
  17. Fashionable Boutique Shop - Shaksboutique . At discount prices we have variety of party dresses, shoes, bodysuits, skirts, coats, fitness products, swimwear, glasses, candles and much more. Best Quality online women's Fashionable clothing & boutique. Everything you need at unbeatable prices.

    Fashionable Boutique - Dress | Swim | Fitness -Shaksboutique

    ReplyDelete
  18. good keep it up very.... good post i have ever see,keep itup guys... i will follow you for more posts.
    Online romance Series Shop UAE

    ReplyDelete
  19. Annual ROC filing for Companies nice and informative post thanks for the update

    ReplyDelete
  20. Luxury car rental Jaipur is best for wedding functions and events and for baratis.Our top luxury cars models for rent in Jaipur are: Fortuner (Toyota) Jaguar F pace & XF Audi A5, A6 & Q7 Mercedes-Benz S Class & E Class And Limo. Book Luxury Car rental for same day tour and local sightseeing, hire luxury car in Jaipur

    ReplyDelete
  21. https://dataservermarket.com/coupons/hostwinds-advanced-business-web-hosting-6-58-mo/

    ReplyDelete
  22. professional mediator in london,
    top mediator services in midlands uk,
    famous mediator services in london,
    family mediator service in north of england

    ReplyDelete
  23. Why Is My Epson Printer Not Printing Black? Solution (817)587-2017
    Epson printers are of the best printers used globally. The design and features are the reason that makes them so popular. However, like every device, they might encounter issues. Epson printer not printing black is one such issue. If you face this issue and need resolution then you are at the right place. Epson printer troubleshooting You can follow the steps given in the blog or you could also reach out to the Epson printer customer support number.
    If your Epson printer is not printing black, it could be due to the following reasons:
    • An issue with the print head
    • Erroneous settings
    • Problem with drivers
    Epson printer communication error
    • The trouble with cartridges, etc.

    ReplyDelete
  24. How to Make Epson Printer Print Without Black Ink?
    Epson printers may encounter issues. However, if you are in a tough spot and need to print documents even if you are out of black ink.Epson Printer Firmware Reset. We hope you find them useful. In case, you face any issue you can directly reach out to the Epson printer customer support number (817) 442-6650.Epson printer skipping lines error

    ReplyDelete
  25. https://dataservermarket.com/coupons/domain-india-cms-hosting-joomla-%e2%82%b9597-year/

    ReplyDelete
  26. The details on this blog are very impressive. You have worked effectively. I also have info about Real estate that is an imperishable asset, ever increasing in value.
    ATS Kingston Heath

    ATS Pristine Phase-2

    ATS Pious Hideaways

    ATS Le Grandiose

    ATS Picturesque Reprieves

    ATS Pristine Golf Villas

    ATS Marigold Gurugram

    ATS Grandstand Gurgaon

    ReplyDelete
  27. Very good quality. I am surprised how many attempts you set to make such an excellent informative website.

    buy online books in UAE

    ReplyDelete
  28. Very good quality. I am surprised how many attempts you set to make such an excellent informative website.
    Best Online fiction stories for kids

    ReplyDelete
  29. Govt job notification 2021,free job alert govt jobs, rojgar samachar
    Get free latest govt jobs across all UP, Bihar, MP, Rajasthan, Uttrakhand, Delhi,rojgar samachar All India Recruitment with fully vacancy detail at job place, free job alert 2021-2022 ✔ central govt job notfication ✔ Frequently Updated" />

    ReplyDelete
  30. Usually I never comment on blogs but your article is so convincing that I never stop myself to say something about it. You’re doing a great job Man,Keep it up. Meanwhile visit our website for nmims solved assignments december 2022

    ReplyDelete
  31. https://dataservermarket.com/coupons/domainindia-cms-hosting-prestashop-597-year/

    ReplyDelete
  32. Hi there,I enjoy reading through your article post, I wanted to write a little comment to support you and wish you a good continuationAll the best for all your blogging efforts.
    Appreciate the recommendation! Let me try it out.
    Keep working ,great job!


    Best UK visa consultants in hyderabad

    ReplyDelete
  33. The details on this blog are very impressive for real estate investors. I also have info about
    ATS Kingston Heath Noida Birla Niyaara Worli Mumbai
    DLF One Midtown Project By DLF in Moti Nagar Delhi
    Real estate that is an imperishable asset, ever increasing in value. Best to buy because here you will get all the facilities near to the location

    ReplyDelete
  34. Best post i have ever Seen,keep it up guys … I will follow you for more new posts.
    Buy best mystery romance books UAE

    ReplyDelete
  35. Check https://www.kowskin.com/shop/kleenowipe/kleenowipe-multipurpose-sanitizing-wipe/ to buy multipurpose hand Sanitizing wipes Online in India.

    ReplyDelete
  36. Nice and valuable information, Thanks for vist amzon.com for buy a product.

    ReplyDelete
  37. I really like your blog it is very intresrting and helpful for me.
    ILO DEVELOPMENT

    ReplyDelete
  38. Thank you for this post, Very helpful.
    Truck Bazi is the India's leading portal for used commercial vehicle,Second hand truck, buying, selling & renting,with no commission.

    Do you want to Sell Rent & buy used commercial vehicles or/and used construction equipment?
    Download the app now : http://tiny.cc/glzhuz
    For any queries, reach us at 9975941114
    You can also whatsapp us at 7559291280
    For further details, log on to https://truckbazi.com/

    ReplyDelete