SQL Server DBA Tutorial 41-How to Recover Suspect or Recovery Pending Databases in SQL Server

welcome to tech brothers in this video we're going to learn how to recover suspect or recovery pending databases in sequel server in this demo we'll be learning how to check database databases status how to check sequence in resolve to find out why the database went into suspect or recovery pending status how to find reason using dbcc command that why our database went into suspect or recovery pending status and last option if we can't find real resolution then we have to restore the databases so let's go ahead and first check how the how to check the status of the databases there are a couple options we could do we can go ahead and use sequel server management studio and connect with our sequel server instance and you can expand the databases right here and it will tell you in my case there's one database that is in suspect mode or in 2000 and 2005 it used to be called suspect mode and in 2012 and 2014 I believe it's in 2008 as well that is called recovery pending so for some reason the database cannot come online that's why I saying recovery pending so we need to find out the reason why this database went into recovery but so far we are just on status so we can look at the status right here the other way to find the status we have a script right here it's really easy you need to go into system databases view right here and you can check whether the database what is the status of the databases so let's go ahead and run this as you can see that I'm just basically selecting two columns one is name and other is state underscore descending that means the status of the database basically up here you can put it as status and it'll show the column has status right here so all our databases are online and right here there is one database that is recovery pending also known as in previous version suspect mode so we'll be using this method in a little bit how to find the reason why this database went into suspect mode so first method we're going to use is look into sequel server error log because pretty much you can get an idea of why this database did not come online or why this database is still in recovery or suspect mode so let's go ahead and in order to go in sequence server error log you can expand sequence every management sequel server agent and go into the current law so if just so that you know if the database is pending or in suspect mode from last two days and you just notice just right now then you can go into the particular date in error log but since this is recent basically in production obviously it's going to be right away but if your development server has a database down and you've been busy and you didn't have a chance to look at and if it's a like previous dates then it's going to be that date you need to look into the air log of that date so I'm going to go ahead and look at the reason let's see that if we can find the reason why this database went in to suspect mode so as you can see right here we're going to go and find you can filter it up here the message just the demo and you can apply the filter right here and it'll tell you right here all the it will bring the messages where the your database name is demo so up here if you notice that is setting up option on line four demo database starting up demo database and then next thing it could not bring the database online the database name is demo so let's go ahead and look at why it didn't come online as you can see this is operating system error system cannot find the file specified so let's go and take a look and it says that it cannot find the LDF the log file of this particular database so we need to find out whether this LDF is missing or whether it resides to another location so you can go ahead and basically get provide that location and bring the database online and put in this particular right here is the LDF of the location so you need to put LDF right here in order to bring the database online so this is one way to find out that LDF is missing so let's go ahead and run this right here dbcc check DB demo that is my database name and with no one to score info messages what it means when you say with no underscore info messages that means that it's going to only return error messages whatever the error that would be so we're going to go ahead and run dbcc check DB up here let's say what dbcc says so the database demo cannot be open due to inaccessible files or insufficient memory or disk space so basically it didn't really it gave us few clues that maybe the files are inaccessible or insufficient memory or disk space this is are not a whole lot help but at least we know that the file is basically missing or maybe our disk space is you know the log file with log file resides if this space if if the disk is filled up then it cannot write more on to the disk or the sequence I've already started and cannot basically bring the database online so we get some clues from dbcc as well so I would recommend that do use this if your database went in suspect mode or in recovery mode it sometimes it brings much more you know close to our error message but the best thing to do is look in sequence of our log and you can look also in Event Viewer if you double-click on the error you can this a sequel server error this is agent and if you go in Windows NT you can look in application log and you can look in system log application log it usually has that kind of errors as well so I would recommend that you if you look in application log right here it will tell you the same hair right here that what we looked at in sequel server log so let's go ahead and recover the database and now we know from sequel server error log that it cannot find basically the LDF so in my case in your case it might be that you're migrating LDF or MDF the databases from one location to another location and the LDF location is misplaced what the location basically appeared for the sequel server or sequel server you're trying to restart the sequel server and m/l DF is been misplaced but this is looking for LDF at a specific place so let's go in my we know that it's right here if you look into see and look into data right here is my data file so if I go back into log I know that it's looking for demo underscore log I just reproduce this error I rename this love so let's say that it is residing at D you may need to copy that a log file and bring it right here so I'm going to go ahead and rename this so I renamed the correct file and it's looking for this demo underscore log file so we're going to go ahead and restart our sequel server so let's refresh and go in the databases as you can see that our database is recovered so it's online now the other option is that if you can't find such as you know file related issues or anything like that if you can't find it right away then you only option that you have is restore the database so when it comes to restore the database first you need to basically go ahead and restore the recent backup or if you have logged backups and if you don't know how to restore a point-in-time please watch my video that how to restore it abase to a point in time and it should help you a great deal helps

Channel: TechBrothersIT

Views: 67,217

Rating: 4.5333333 out of 5

Keywords: How to Recover Suspect or Recovery Pending Databases in SQL Server, SQL, Server, Developer, TSQL, DBA, Interview, Scripts, DBA Tips, SQL Developer, SQL Server Interview Questions, transact sql, SQL Server 2014, SQL Server Advance Tutorial, SQL Server Tutorial for beginners, SQL Server 2014 DBA, SQL Server DBA tutorial for Beginners, SQL Data Administration Tutorial Step by Step, Database Administrator (Job Title), TechBrothersIt

Id: mE9bdgj8JwQ

Length: 9min 27sec (567 seconds)

Published: Fri Apr 10 2015