SQL, Code, Coffee, Etc.

05/09/2012
by Marlon Ribunal
3 Comments

Your SQL Server database just failed, can you restore the backup?

It’s one thing to be able to backup a database, it’s another thing to be able to restore it into an operational database in case of failure. We can run a simple TSQL to check the validity of a backup file. We can do something like this:

[sql] RESTORE VERIFYONLY FROM DISK = ‘C:\TestDB.bak'[/sql]

It’s easy, right? You don’t have to have some ninja skills to run that statement. Run the verification, and if SQL Server validates the backup, your job here is done. Yes to that, only if you can rely on VERIFYONLY.

Valid doesn’t mean you can restore it

The fact is you can’t rely on VERIFYONLY for real backup verification. It doesn’t guarantee that you can RESTORE the backup although it might report your database backup as VALID. If it reports validity, it’s only guaranteeing that it can READ the backup file, but not necessarily for restoration. “Validity” in this case is a test for whether the backup file is readable or not.

Let’s run an example.

For this example let’s borrow Paul Randal’s corrupt database backup. Let’s pick “Sample 2008 R2 Fatal Corruption Databases”. And that is because I am running a SQL Server 2008 R2 on my laptop.

Let’s verify the CorruptDemoFatalCorruption1.bak backup file and see if SQL Server validates it:

[sql]
RESTORE VERIFYONLY FROM DISK = ‘C:\BackupTest\CorruptDemoFatalCorruption1.bak’
[/sql]

This is what SQL Server found:

(For the sake of this example, let’s just ignore the fact that you should be monitoring the health of your databases.)

The best you could do to make sure that you can restore your backup is to actually restore it in a test server. I’m sure you’re going to have some fun doing that on hundred’s of databases.

Third-party backup and restore tool

This is where third-party backup and restore tools can easily help. Let’s take a look at the latest version of Red Gate’s SQL Backup Pro (v.7).

Since we’re talking about validity of a backup for restoration, let’s take a look particularly at one of the new features of SQL Backup Pro v.7 – Automated Backup Verification and Restore. I’ll provide a couple of links at the end of this post if you want to check the demo of the new capabilities of the application. Let’s zoom in to the verification of a backup.

Let’s restore the backup CorruptDemoFatalCorruption1.bak. To check for the data and log files needed for restoration, just do RESTORE FILELISTONLY first.

[sql]
RESTORE FILELISTONLY
FROM DISK = ‘C:\BackupTest\CorruptDemoFatalCorruption1.bak’
[/sql]

Then restore:

[sql]
RESTORE DATABASE CorruptDemoFatalCorruption1
FROM DISK = ‘C:\BackupTest\CorruptDemoFatalCorruption1.bak’
WITH MOVE ‘DemoFatalCorruption1’
TO ‘C:\BackupTest\RestoreTest\Corruption1.mdf’,
MOVE ‘DemoFatalCorruption1_log’
TO ‘C:\BackupTest\RestoreTest\Corruption1.ldf’
[/sql]

We should get a message similar to this:

Let’s cut to the chase and go straight to the verification process. Like I said, I’ll provide links at the end of this post if you want to check all the features of SQL Backup Pro v7.

For this demonstration, I’ve already created a backup of CorruptDemoFatalCorruption1 in advance using SQL Backup Pro. Suppose we’re not aware of the health status of the database (we know that it’s a corrupt database).

Now that we have the backup, the next thing we want to do is verify that backup and make sure that we can restore it. We can do the verification by creating a Schedule Restore Job and running it. At step 4 of 7 of the process, we specify verification options for the restored database as shown below:

When run, SQL Backup Pro v7 provides the Outcome status of the restore. In our case, we know that our database restore will fail, and SQL Backup Pro accurately tells us that:

Of course, you can check where the Database Consistency Check failed:

Grant Fritchey (Blog | @GFritchey) have produced the following resources to demo the new features of SQL Backup Pro v7:

SQL Backup Pro for the Accidental DBA (article)

Introduction To SQL Backup Pro v7 (video)

05/03/2012
by Marlon Ribunal
2 Comments

DBA Roadmap Seminar review

Our usual step when seeking for advice is to search the internet for relevant topics. There is no shortage of what we can get if we know where to find them.

The internet has a vast, rich body of information that is readily available for our instant gratification.

But I feel that the goals I want to achieve are too important for me to rely on the John and Jane Doe’s that dominate my Google search.

(Of course, I’m making exception for the likes of Brent Ozar, Thomas LaRock, Brad McGehee, etc. who never fail to offer great pieces of technical advice.)

I am not saying that I cannot put these free information to good use – I can, in more ways than one.

There’s more ‘why’ to that.

I feel that if I invest some of my hard earned money to my goals I can probably get more value than just by consuming free information on my spare time.

I need to invest in myself. I need a concrete way to get my body and soul commit to the journey!

Thus, I paid to get the DBA Roadmap. A thing like this is the least I can do for my career. Some are spending out of pocket upwards of $5,000 dollars each year towards their career (training, conferences, etc).

The DBA Roadmap Seminar is a business venture by the SQL Server couple Sean and Jen McCown (@MidnightDBA), or popularly known as the Midnight DBA’s. The recorded seminar costs $99.00 as of this writing. Update: The couple is now operating as Minion Ware LLC.

The seminar has 7 tracks, including the Introduction and Conclusion. Except for those two, you can listen to any tracks in any order you want (not in sequence).

The main tracks are: How To Study, What To Study, Resumes, Job Hunting, and Interviews. Each track comes with a bonus Companion Guide that gives you a breakdown of what has been discussed on the track. The guide also provides additional links to useful sites and resources. The tracks are described on the DBA Roadmap site.

You’re probably thinking if these tracks can actually offer you new things. It’s true that these topics are often discussed. But do you think the McCown’s will ask you for money just to rehearse things you already know? Yes, I know, right? There was hesitation at first on my part because of the way the seminar was being marketed (newbie’s seminar). But I found out it’s exactly what I needed.

Although a newbie to SQL Server can reap the most benefit from this seminar, I don’t see why a current SQL Server pro can’t learn and pick up resume and interview skills from it.

For 99 bucks, the seminar is a steal. Listening to 5 hours of solid, meaningful material is like attending a whole day of seminar. Plus, you can play the tracks over and over again, as I have, if you cannot digest the content on one pass.

If you are a SQL Server DBA with 2-3 years worth of experience or even a mid-level SQL pro looking to advance his career and thinking that he could use an advice or two, you owe it to yourself to purchase this seminar.

04/23/2012
by Marlon Ribunal
11 Comments

Who are the sysadmins in this sql server?

Sysadmins are pretty much powerful in the SQL Server environment. They can put the whole SQL Server infrastructure under their control. That said, it is important to know who else has this fixed server role.

Here’s a quick query that you can run to find out the users with sysadmin fixed server role. Sysadmins have a complete control of your server. So, it is very important to know who these people are.

Just to give you an example on why it is very important to check who’s who in your server from time to time, Team Shatter has recently published an advisory on Privilege Escalation Via Internal SQL Injection In RESTORE DATABASE Command.

According to the advisory, there is a vulnerability in the RESTORE DATABASE command that allows malicious users to run SQL codes via internal sql injection. This vulnerability can be exploited by users with CREATE DATABASE permission.

A rogue user can find his way to taking control over your databases by using a backdoor such as the vulnerability described above. Imagine the same user was able to add himself to a server-wide role such as the sysadmin.

Find Sysadmins with TSQL Query

So, going back to the query, here is what it looks like:

USE master
GO

SELECT p.name AS [loginname] ,
p.type ,
p.type_desc ,
p.is_disabled,
CONVERT(VARCHAR(10),p.create_date ,101) AS [created],
CONVERT(VARCHAR(10),p.modify_date , 101) AS [update]
FROM sys.server_principals p
JOIN sys.syslogins s ON p.sid = s.sid
WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
-- Logins that are not process logins
AND p.name NOT LIKE '##%'
-- Logins that are sysadmins
AND s.sysadmin = 1
GO

And that query should give us something like this:

Find Sysadmins with System Stored Procedure

There is a quicker way to do that, though. You’ll achieve a similar result by executing the system stored procedure sp_helpsrvrolemember:

 EXEC sp_helpsrvrolemember 'sysadmin'

And, you’ll get a result that looks like this:

Of course, you can use any of the other server roles with sp_helpsrvrolemember as parameters. For example, if you want to look for users with the database creator role, you use the variable 'dbcreator' instead of 'sysadmin'.

Server roles are listed somewhere in one of the system tables in the master database. But there’s already a system stored procedure for that as well – sp_helpsrvrole. You don’t have to dig in and search for them yourself. SQL Server gives you most of the stored procedures you’ll likely need out of the box.

EXEC sp_helpsrvrole

Executing that will give you the following list:

Update from the comments:

Here’s a good one from Eric Russell (see comment below): to return what accounts are members of a domain group, we do this:

xp_logininfo 'DEVCOMPUTER\SQLAdmins', 'members';

*suppose SQLAdmins is a domain group

And, it does exactly what it is meant to do – returns the members of the group like this: