Database in Suspect Mode… Frowny Face.

The other day I received a call from a developer telling me that they could not access a particular database in one of our lower environments. After a bit of investigation, I found the database causing the problem, it was in ‘Suspect’ Mode.  Here is what I did to correct the issue.

ALTER DATABASE DatabaseName SET EMERGENCY
GO
ALTER DATABASE DatabaseName SET SINGLE_USER
GO
DBCC CheckDB (DatabaseName , REPAIR)
GO
ALTER DATABASE DatabaseName SET MULTI_USER
GO

Presto, the database was now fully functional and accessible. A very quick fix, to what had the potential to really ruining my Tuesday morning.

Cheerio!

I need results! Via E-mail, please.

I like the idea of taking several issues, ideas, or problems, and combining what I need to get results.
I was recently poking around Red-Gates site and found a nifty little *FREE* app called SQL Scripts Manager. SQL Scripts Manager is a great application for not only SQL Rooks, but for SQL Vets too. It contains over 25 scripts designed to help simplify the everyday life of a SQL DBA, including automating troubleshooting, diagnostic, and maintenance tasks. It features scripts from people like Dan McClain, Gail Shaw, Glenn Berry [twitter|blog], Louis Davidson [twitter|blog], Ola Hallengren, Phil Factor, Rodney Landrum, Tim Ford [twitter|blog] and Tracy Hamlin [twitter].
Specifically I found a sweet little script by Rodney Landrum, perfectly named “Last good backup”.

Lists the date and time of the last good backup of a particular type, for each database on an instance
This script is useful for checking the progress of backups for all databases on an instance. You can quickly see whether a particular type of backup is missing or delayed, and then investigate further if necessary.
If you need to run an ad hoc backup, you can use the Back up database script included with SQL Scripts Manager.

Here is the exact script:

SELECT sd.name AS [Database],

CASE WHEN bs.type = 'D' THEN 'Full backup'
WHEN bs.type = 'I' THEN 'Differential'
WHEN bs.type = 'L' THEN 'Log'
WHEN bs.type = 'F' THEN 'File/Filegroup'
WHEN bs.type = 'G' THEN 'Differential file'
WHEN bs.type = 'P' THEN 'Partial'
WHEN bs.type = 'Q' THEN 'Differential partial'
WHEN bs.type IS NULL THEN 'No backups'
ELSE 'Unknown (' + bs.type + ')'
END AS [Backup Type],
max(bs.backup_start_date) AS [Last Backup of Type]
FROM master..sysdatabases sd
LEFT OUTER JOIN msdb..backupset bs ON rtrim(bs.database_name) = rtrim(sd.name)
LEFT OUTER JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE sd.name 'tempdb'
GROUP BY sd.name,
bs.type,
bs.database_name
ORDER BY sd.name, [Last Backup of Type]

Wow, that’s a pretty powerful, and useful script, I thought to myself “hmmmm, I’d like to have this script run nightly and email me the results, but how?” This would give me (and my boss) a valuable report every morning by receiving an email, detailing the results of the previous nights backups, in a quick, precise, easy to read method.

Enter Twitter and the wonderful hash tag  #SQLHelp.  I posted a question…

Email the results of a query #sqlhelp

Within the matter of 15 minutes I had a conversation taking place with Marcos Freccia [twitter] starting out on twitter, and ending via Gmail.

Marcos gave me this script:

execute sp_send_dbmail
@profile_name = ‘MCITP Profile’, -- DbMail Profile
@recipients = ‘your@email’, -- Your Email
@subject = ‘Product`s list’,
@body = ‘Some Message’,
@body_format = ‘Text’, -- You can use HTML also, but you have to create some html code before.
@importance = ‘High’, -- Importance Level.
@query = ‘select idProduct, name, quantity from dbo.Products’, -- This is the query, also can be a stored procedure
@execute_query_database = ‘MCITP’, — The database that query going to be executed
@attach_query_result_as_file = 0 , -- If you put 0 the query will be showed in the body, 1 will be a file attached in email
@query_result_header = 1 -- if you put 1 you see the colum name, 0 no.

Perfect, with the combination of the script Marcos had provided and Rodney’s script, I could perform exactly what I set out to do.

Here is the script:

execute sp_send_dbmail
@profile_name = 'your servers mail profile',
@recipients = 'who is going to get the email',
@subject = 'SQL Database Backup Report',
@body = 'important',
@body_format = 'Text',
@importance = 'High',
@query = 'SELECT sd.name AS [Database],
CASE WHEN bs.type = ''D'' THEN ''Full backup''
WHEN bs.type = ''I'' THEN ''Differential''
WHEN bs.type = ''L'' THEN ''Log''
WHEN bs.type = ''F'' THEN ''File/Filegroup''
WHEN bs.type = ''G'' THEN ''Differential file''
WHEN bs.type = ''P'' THEN ''Partial''
WHEN bs.type = ''Q'' THEN ''Differential partial''
WHEN bs.type IS NULL THEN ''No backups''
ELSE ''UNKNOWN (''+ bs.TYPE +'')''
END AS [Backup Type],
max(bs.backup_start_date) AS [Last Backup of Type]
FROM master..sysdatabases sd
LEFT OUTER JOIN msdb..backupset bs ON rtrim(bs.database_name) = rtrim(sd.name)
LEFT OUTER JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE sd.name <> ''tempdb''
GROUP BY sd.name,
bs.type,
bs.database_name
ORDER BY sd.name',
@execute_query_database = 'MASTER',
@attach_query_result_as_file = '1',
@query_result_header = '1'

NOTE: The formation change in Rodney’s script where there were single quotes we now have double quotes

If anyone is actually reading this, leave a comment 🙂

A special thanks to Marcos Freccia for his help. And an indirect thanks to Red Gate and Rodney Landrum.

Out of Cycle Backups?…Yes Please!

I wasn’t really planning on writing anything on this topic, but then I thought about my readers…uh wait…I’m the only one reading this blog, LOL! Here we go!
The setup…
A few management folks at my organization, you know the type, the ones above your pay grade. Thought it would be a wonderful idea to have one of our most important databases backed up to a laptop off site each night. So in the event of a disaster, (Hurricane) the laptop could be scooped up, taken to a hotel, and work could still be processed. So they come to me, and tell me to make it happen….ASAP! After all, hurricane season had just started.

So we will call the source server, “Point A” and the destination laptop “Point B”. I already knew this situation might get a bit complicated. The database on Point A is set to Full recovery mode, a full backup every night, two diffs during the day, followed by transaction logs every 15 minutes, nothing special there. The good news I only had to restore a full backup down to Point B, so its recovery model is simple. I knew once I got the backup to Point B, the rest would be easy. Because, of the way SQL Server backups work, I didn’t want to take an additional Full backup to copy over to Point B, making my backups out of sequence. It’s like being Marty McFly and altering time, we all remember how that turned out! I first thought I was going to have to write a powershell script that would copy my latest backup, and move it via UNC path to Point B. Then I ran across this little jewel. Enter Copy Only Backups, simple, and easy to prove they are working!

So I must admit, as a SQL Rook, I need a few more tools in my tool belt then the seasoned vets out there, and one little helper I really enjoy using is Ola Hallengren’s backup strategy, I consider it my MacGyver Knife at this stage in my career.

Here is a sample script that I am working with using Ola’s method with Copy Only:

EXECUTE dbo.DatabaseBackup <——Download the code here
@Databases = ‘AdventureWorks’,
@Directory = ‘\\LaptopName\ShareName’,
@BackupType = ‘Full’,
@Verify = ‘Y’,
@Checksum = ‘Y’,
@CopyOnly =’Y’

That's it, run this script as a SQL Job within SSMS nightly and now my "Out of Sequence" backup isn't effecting my production backup sequence.
So at this point, now that I have my Full Backup on Point B, I just need to restore it nightly.

RESTORE DATABASE Adventureworks
FROM DISK = ‘C:\ShareName\AdventureWorks.bak’;

This too will be ran from a SQL job on Point B, nightly. Additionally, I do not exactly want to start collecting these out of sequence backups on Point B, so I will run a Cleanup SQL Job, nightly as well. And the final step would be to create a new Full backup sequence of the "Out of Sequence" backup...make sense? lol.

BACKUP DATABASE AdventureWorks
TO DISK = ‘C:\Backups\AdventureWorks.BAK

Now, earlier in the post, I mentioned that it is easy to prove this out of sequence method. Refer back to this link...the jewel The Author uses LSN (log sequence number) information to track the backups, to compare and contrast the difference the LSN numbers when taking a normal backup and then taking a backup with Copy_Only (out of sequence).

So there you have it folks...Please leave your comments, better ideas are always well appreciated, from the SQL Vets out there. Cheerio!