Being on the cutting edge of technology is something that we need to be prepared as IT professionals. Newer capabilities get into the software almost every single day and it is difficult to keep track of what is getting added into the software’s we work with. I am a firm believer that we need to innovate and keep learning every single day. That is the best way we can be competitive and make our lives easier.
I get an opportunity to speak at multiple conferences. Though the presentations range from short tips to full-fledged presentations for hours. More than the presentation time, it is the process of preparing and adding a new concept in every session is something I cherish a lot. Most of my writings are based on what people ask me from time-to-time.
In a recent conversation I had with a junior DBA, it taught me something interesting that I thought was worth a share. The DBA had emailed me the below screen shot:
He was complaining that he was not able to take the Transaction Log backup, but it was encountering an error. I said, most of the times it is important to watch the error message because in the recent past, I have seen the error messages give us the solution too. These errors are simple and a little search on MSDN can give the answer. Having said that, I did talk about other things that involve around the error. To get started, I would like to list all the backup types we can do with SQL Server:
- Full backups
- Differential backups
- File backups
- Filegroup backups
- Partial backups
- Copy-Only backups
- Mirror backups
- Transaction log backups
We are not going to look at each of the types in this blog, but this was important to mention all of them for a future reference.
Find when the last backup done
I wrote back to the DBA stating there is something bigger problem in hand. I always recommend as a seasoned DBA, you need to know when the last backup was taken on the server. This becomes useful to restore the same incase of any disaster:
SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus
ON bus.database_name = sdb.name
GROUP BY sdb.Name
As a contingency plan, we need to be always aware when a backup was taken. It is critical that as a DBA, all critical production databases are monitored from time to time.
In the above error, even though we are on a FULL recovery model – if we try to take a TLog backup without a FULL backup, the error is encountered. So to mitigate this, we need to be taking a FULL backup before the TLog backups can be initiated. The concept is simple right.
SQL Server Dashboard with Spotlight
Ask a naval officer about how a command center looks and how critical it is for them? A better view of the battle field and the strategies for the field is always decided in the command center. In a similar way, a DBA can use Spotlight Dashboard to get a high level understanding of what happens on his servers.
In our example, you can see that our servers don’t have a backup being taken till date. Incase the FULL backup is not taken from our databases over, this alert is being raised. The same thing is shown in a visual form to in the tiles view.
If you ever get a chance to play around with the Spotlight dashboard, I would highly recommend watching some of the out-of-box warnings they arise and take corrective actions immediately.
Note: A database even in the FULL recovery model will act like SIMPLE recovery model till the first FULL backup is taken.
So this error / behavior can get any seasoned DBA into trouble. The Dashboard is a big time savior and can save your job if gone un-noticed. If you want to lead a stress free DBA life, then such early warnings can save you big time.
Final Words
As we wrap up, sometimes we are likely to miss some of the basic principles that we assume from the system. Having something that can remind us of these basics can get us out of trouble in many ways. Most the tools we work give us early warnings and recommendations. It is up to a DBA to look at them and take necessary actions. If you would like to try out these tools, then you can use the SQL Server Evaluation Version to try them out.
Click here for a free 30 day trial of Spotlight on SQL Server
Reference: Pinal Dave (https://blog.sqlauthority.com)
