Stellar Repair for MS SQL | Recover Corrupt SQL Database


– What’s up, everyone? Welcome to Josh Burns Tech. So one thing that you never want to see is a corrupt SQL server database, unless you’re a freelancer and
you’re here to save the day. I’ve personally resolved numerous SQL server database corruption issues. So if you’re experiencing
corruption, I’m here to help you. I’m gonna show you my
favorite recovery tool for repairing SQL server
database corruption, which is Stellar’s Repair for MS SQL. I’m first gonna discuss some use cases for Stellar Repair for MS SQL. Then, I’m gonna give you
some real-life examples, some real clients that I’ve
personally helped repair their database corruption. Then, we’re gonna dive into a live demo. I’m going to corrupt a
local SQL server database. It’s on a test instance, don’t worry. I’m going to corrupt a database, and then we’re gonna use
Stellar Repair for MS SQL to resolve the corruption
and get rid of it. So be sure to stick around because you don’t want to
miss the SQL server video which is coming right up. (upbeat music) So if you get value from this video and you enjoy my content,
I do ask one thing of you. Please like the video. It helps out a ton with
the YouTube algorithm. And also consider subscribing
to the channel as well so you don’t miss out on
any of my future content. So let’s first discuss some use cases in which you would want to
use Stellar Repair for MS SQL. So right now, I’m on Stellar’s website. This is specifically their page for Stellar Repair for MS SQL. They have a lot of other products, but this is the one specific to MS SQL. As you can see, it’s capable of repairing corrupt SQL server
database MDF and NDF files. Now, this type of corruption could be at the table level, index level, or even down to the data page level. Now, for an example of this. The most recent type of corruption that I worked on was
for one of my clients. Their entire data center went down, and when it came back up, their
main database was corrupted. And I was able to use
Stellar Repair for MS SQL to recover that entire database. And as you can also see on their website, I’m gonna throw a link to this website down in the description of this video. It’ll take you directly to
Stellar Repair for MS SQL. What you can also see is that it recovers database components, tables, triggers, indexes, keys,
rules, store procedures. And something that’s really cool, you can even recover deleted data records from SQL server databases as well. We all know that we
try to be really strict with the type of access that’s put into place for SQL server. We don’t want users
having too much access. However, in the case that
data deletion does occur in a SQL server database, you could use Stellar Repair for MS SQL to actually recover that data. And then something that’s
really cool about this product, you can download it for free, run it against your corrupted database, and when it’s able to
successfully repair it, then you can pay for the product. You just have to pay for the license, which will then allow you
to save the repaired file as an MS SQL server
database file, MDF or NDF, HTML, XLS, or even CSV. And it also supports all
the main supported versions of MS SQL server. If you’re running
something older than 2008, it’s time to upgrade. And to give you an example,
for my professional career, the main use case in which I
use Stellar Repair for MS SQL is to repair a corrupted database that’s usually at the page level. So as many of you know, I’m a freelancer. I’ve been a freelancer on
Upwork for over three years. I’ve worked with a ton of clients and made almost $500,000 on the platform. Now in that time I’ve personally
helped clients resolve their database corruption issues. And what I’m gonna do now
is show you the review that the client left for me both on Upwork and they allowed me to
put it on my website, JoshBurnsTech.com. I’m gonna show you that review where I was able to successfully use Stellar Repair for MS SQL to resolve their database
corruption issues. So here we are on my Upwork profile, and what I’m gonna do now is scroll down to my portfolio section
and show you the project for database corruption. And here we go, database corruption. So as you can see in
the project description, the client reached out to me indicating that a customer of theirs could not work in their application due to a database corruption error message. When analyzing the database
running DBCC CHECKDB, I found corruption in various pages of the database’s MDF file. This was specific to the page file, which we discussed earlier
in one of the use cases, so removing an index would not be possible to resolve the corruption. Now, this client did have
database backups in place. However, every single database
backup had corruption in it, so they couldn’t use that
as a recovery option. So after running DBCC CHECKDB
and finding the corruption, I was able to download
Stellar Repair for MS SQL, run it against the corrupted database, and then recover their entire database. The customer did not lose any data, and they were back up and running in less than 24 hours with
minimal business impact. Now again, this client
was amazing to work with. They even let me put the
review on my website. And if we scroll down
in the review section, we can find what they had to say. And here we are at the review. When a new customer of
mine reported problems with their line of business application, it was discovered that
the problems were due to massive corruption
in their SQL database. After examination, it was determined that any of normal repair options would most likely result in data loss. This would be due to running DBCC CHECKDB with repair allowed data loss. As you know, you can lose data doing that. So no reliable database
backups existed, as well, which I mentioned earlier. I handed the project off to
Josh to see what he could do, and it was just 24 hours later that the database was up and running. The rest of it is just great things that he had to say about me. Again, he was an amazing client. I really enjoyed working with him. Okay, so now for the most important part of the entire video, the moment
you’ve all been waiting for. Let’s go corrupt a SQL server database and then use Stellar Repair
for MS SQL to recover it. Now, if you would like to follow along and do this test as well in
your own local test environment, check out the description of this video. I’m gonna put a link to a blog
post on JoshBurnsTech.com. You can follow the blog
post to get the code that I’m gonna use to
corrupt the database. So we first need to install
Stellar Repair for MS SQL. Now, I’ve already downloaded it. We’re gonna go through the installation. If you haven’t downloaded it yet, check out the description
in this video again. There’s gonna be a link in it
to Stellar Repair for MS SQL. Go ahead and download it. And then once you have it downloaded and you run the executable, this is the screen that you’re gonna see. So we’re gonna go ahead and click next. On the license agreement section, go ahead and click I accept
and click next again. Next, choose where you want to install Stellar Repair for MS SQL. I’m gonna leave it as
the default location. Select start menu folder. You can change this if you desire. I’m gonna go ahead and click next. You can have it create
a desktop icon, as well. I’m gonna go ahead and let it do that. And now we’re ready to install
Stellar Repair for MS SQL. Okay, so now it’s installed, and we can go ahead and run it. Now, before we use
Stellar Repair for MS SQL, we actually need to corrupt our database. As you can see, I’m using the
AdventureWorks2017 database. You can download this
directly from Microsoft. I’ll throw a link to it in the description of the video, as well. So we’re gonna go ahead
and corrupt this database. I’m gonna open a new query window. Okay, so now we’re gonna run DBCC IND against the AdventureWorks2017 database and the Person.Person table to identify a specific page to corrupt. So I’m gonna go ahead and click execute. And now we can see all
the pages in this table. And as I forgot to mention, we’re also gonna use a
number one here at the end, and what this does is it tells it to look at the clustered
index on the table, all the pages within that index. Okay, so you ready for the fun part? Let’s corrupt this database. We’re gonna focus specifically on row three for page ID 1176. We’re gonna alter database
AdventureWorks2017. Set it to single user
with rollback immediate. And now to corrupt it we’re
gonna write specifically to page ID 1176. And because at the end of the statement we’re providing a value of one as our direct or buffer pool value, our change was made directly
to the file on disk, which means that the checksum on the page is almost certainly wrong. Now, after we run the statement, we’re just gonna do a select star against the Person.Person table to verify that it’s corrupted. So now I’m gonna highlight
this alter database statement and click execute. Okay, so we successfully
wrote to page ID 1176. Now, let’s go ahead and
verify the corruption. And we are just simply going to select everything from Person.Person. Click execute. Query is executing. And boom, there we go. SQL server detected a logical
consistency-based IO error. Incorrect checksum. It gives you the expected
value, the actual value, and it gives you the
page ID of 1176, as well. Now, the next step would
be to run DBCC CHECKDB. If it can’t make the repairs, what you would have to do
then using DBCC CHECKDB is to run it with repair allow data loss. Now, the key word with
that is allow data loss. You can lose data by running that. So the alternative and
the one that I recommend that you try out is using
Stellar Repair for MS SQL. So I’m gonna go ahead and run
Stellar Repair for MS SQL. Now, when you first run
Stellar Repair for MS SQL, it tells you to stop the MS SQL services, and then copy the MDF file
to a different location. And this is a best practice thing to do. You don’t want to run it directly against your main MDF file. If something happens to that file, you don’t want to jeopardize
losing all your data, not being able to recover anything. So make sure you stop your services. Copy that MDF file to a different
drive, different location. So I’m gonna stop my MS SQL service. (computer dings) As you know, if you do this, it will take your SQL
server instance down, so just keep that in mind
when you’re doing this against a test instance. If anyone else is connected,
it will disconnect them. Okay, so now that the service is stopped, we can go ahead and copy the MDF file. And here’s my AdventureWorks2017 database. We’re going to copy it, and now we’re gonna paste it
into a different location. Now, we’re gonna jump back
to Stellar Repair for MS SQL. Select database, browse,
navigate to the location where we just copied the
AdventureWorks2017 MDF file, select it, click open, select
your version of SQL server. I’m running SQL server 2017, so I’m gonna select this option. Click OK. And now, the recovery process is started. And repair complete. Selected MS SQL server
database repaired successfully. Click OK to activate your copy and save the repaired database. Now, I’m gonna click OK. And after you click OK,
it’ll open a web page for Stellar Repair for MS SQL, and this is the part
where you buy the license. You can scroll down and look
at the benefits of each option. As you can see, the main difference is when you get down to
Stellar Backup Extractor and Stellar Password Recovery for MS SQL. If you don’t need either
of those features, just stick with the original
Stellar Database Repair. This comes in at $349 with a
30-day money-back guarantee. If you can’t recover your
database for some reason after purchasing the license, then you can call the number listed above, and they have the 30-day
money-back guarantee where you can get the money
back for your license. However, I’ve personally used this product with a lot of my clients,
and they’ve never had to use the money-back guarantee option. It always works and has always been able to recover their corrupted database. And something that’s really cool, as well, is that you can look at your tables. You can look at all the
tables that are recovered in your entire database, as well. It’ll show up here on the left-hand side. Now, I’m ready to save
this repaired database back to my SQL server 2017 instance. I’m gonna go ahead and click save. Really important thing
that I almost forgot to do is starting the SQL
server instance back up. I’m gonna go ahead and click start. Now my SQL server service and
agent are both up and running. This is the part where you
enter an activation key. I’m gonna go ahead and activate mine. Okay, activation completed successfully. As I mentioned in the
beginning of the video, there’s different options to
save your repaired database. We’re gonna leave MDF selected. Next, you can either
save it as a new database or over your live database. I recommend doing the new database option so you don’t restore anything
over your live database. Enter your SQL server instance name. Mine is just the name of my local PC. I’m gonna go ahead and select it, jump back over to Stellar, and now I’m gonna paste that name in. We use Windows authentication, so it’ll use my local Windows account. For location, I’m gonna
leave default SQL selected. Now, this is going to
save the new database file to the default location
where MDF files get created. If you want to choose
a different location, then select new, and then
browse to that location. I’m gonna go ahead and click save, and now my database is being recovered back to my SQL server 2017 instance. And as the recovery is running, you can see the log, as well. Now, the log is gonna show every
table that it’s recovering, how many fields are present in the table, and the total records, as well. And then you can see the
status bar at the bottom. And now the recovery process is finished. File saved at the desired path. So now, I’m gonna click OK. I’m gonna open my SQL
server 2017 instance, and there’s our new database,
Recovered AdventureWorks2017. So the way that I like to
verify the recovery process is I like to run the SQL statement. This is linked in the blog
post on JoshBurnsTech.com. Just check out the
description in this video. It’ll have a link to it. So I ran this SQL code against
the recovered database, and it returns the
schema name, table name, and record count of every single
table within the database. Now, to verify it, I would
look at the record count for each individual table
in the recovered database and compare it to the old
database that’s corrupted. Let’s take a look at the specific
table that had corruption. So that would be Person.Person. I’m gonna come down to the
where clause, type in person, and I’m gonna add in another
criteria with AND SCHEMA_NAME, schema ID equals person. Click execute. So there’s our schema person, table person, and the record count. Take this same SQL statement, open a new query window
in the corrupted database, and run the SQL statement
in the corrupted database. Okay, so there we go, SchemaName Person, TableName Person, exact same record count. Now, after you verify that the recovery process
was indeed successful, you can change the name
of your recovered database to make it your main database. So before we get rid of
the corrupted database, we need to set it back to multi-user. And we’ll go refresh the databases. and we’re back in multi-user. Again, we already have a
backup of the MDF file. If you want to be really safe and secure, go ahead and backup the
transaction log, as well. All you need to do is stop
your SQL server service again, navigate to where your
transaction log file exists, the LDF file, copy it, and then paste it into that same location where we pasted the MDF file earlier. Now, after you’ve done that, we can go ahead and drop the database. (soft relaxing music) Okay, so the corrupted
database has been deleted. Okay, so now I’m going
to rename the database. I’m just gonna delete Recovered_ off. (computer dings) Yes, we want to save it, and there we go. We’ve successfully renamed our database back to the original database name, and the corruption is gone. And again, to verify it, all we have to do is run that same select
statement that we ran earlier. And there we go, the corruption is gone. Again, links to everything that we did in this video are down in the description. Be sure to check that out, if you want to go through
and run all the code in your environment, as
well, and test it out. Be sure to leave a comment. Let me know, have you used
Stellar Repair for MS SQL to repair corrupted SQL server databases? Or if there’s a different technique that you like to use, please let me know. I would love to hear about it. On this end screen, I recommend
checking out these videos. And be sure to hit that
round subscribe button for weekly tech videos, and
hit that notification bell so you don’t miss anything. Thank you for watching
and until next time. ♪ 98 ♪
♪ Yeah, jungle and the juice ♪ ♪ Man, I could feel the heat ♪ ♪ Yeah, I could feel the heat ♪ ♪ Man, I could feel the heat ♪

, , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

Post navigation

4 thoughts on “Stellar Repair for MS SQL | Recover Corrupt SQL Database

  1. I used on a client's database. It is impressive, but it was too slow for my taste. Took over a day to complete. I don't recall the database size but it was not large. This product is good as a last resort option. It works when CHECKDB doesn't. In any case, as any good DBA would agree, please, please, make sure you have backups. Make sure your production database is not on a desktop. Make sure you have storage level redundancy, etc… Otherwise, you will make a happy customer to Stellar sooner or later.

  2. Want to Learn more about SQL Server? Checkout my Playlist! https://www.youtube.com/playlist?list=PL2RRbYdV_lUTzRCwfY9nTgsiQ7BkVkzoi

  3. Hi Joshua, Great vídeos.

    I recently signed up for upwork as a freelancer, I sent me profile for a review, and I didn’t receive a confirmation mail from upwork, but I can send proposals. Does it mean I’ve been approved?

    Please reply when you see this thank you 🙏

    Love from Nigeria

Leave a Reply

Your email address will not be published. Required fields are marked *