Mini Post: Error Specified Cast is Not Valid

Hey everybody! Me again with another Mini Post.

Today we are going to look at a SQL restore error: Specified Cast is Not Valid.

You may see this error when you are attempting to restore a SQL Server Backup via the SQL Server Management Studio. This error could be the result of a couple of different things:

• Corrupt backup
• Attempting to restore a backup of a newer version of SQL Server on a older version of SQL Server
• SQL Server Management Studio GUI bug

Solutions (in order of the problems above):

• Redo the backup and then try to restore it (if this still doesn't work it may indicate a corrupt DB; check the DB with DBCC CHECKDB)
• Verify that you are not attempting to restore a backup on a older version of SQL Server (example don't try to restore a 2008 DB backup on a 2005 instance)
• Try restoring from the command line instead of the GUI

DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Mini Post: The Operating System Returned Error 21

Hey everybody and welcome back to SQL Something!

Today we're gonna take a quick look at a very generic error: Error 21.

You will usually get a message along the lines of "The operating system returned error 21" along with something like "The device is not ready". The second part of the error is the important bit as it specifies that, for whatever reason, the 'device' (usually your drive or the actual files on it) is not ready.

This could be due to a number of reasons (hence the generic nature of the error):

• Drive disk space issue
• Hard drive failure
• SAN failure
• Corrupt database files (MDF/LDF etc)

I would say after you check out what might be causing the issue as well as rectify it, you run a DBCC CHECKDB to ensure that you do not have any consistency issues with you database(s).


DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Mini Post: Remote Procedure Call Failed. [0x800706be] (SQL Server 2008 / 2008 R2)

Hey everyone! Yes, it's Mini Post time.

After you have installed SQL Server 2008 or 2008 R2 (on possibly a Windows 7 or 8 machine) and you attempt to go into the Configuration Manager you may see the following:

Fig. 1: The error in question.

This seems to be a bug that a few people run into (including myself recently). The solution(s) are as follows:

SQL Server 2008

• Upgrade to Service Pack 3. Found here (worked for me!).
• If the above does not work, use the steps to recreate the WMI Repository found here.

SQL Server 2008 R2

• Upgrade to Service Pack 1 or 2. Found here and here respectively. 
• If the above does not work, use the steps to recreate the WMI Repository found here.

If anyone knows the exact cause of this bug, please feel free to share in the comments below. It will benefit everyone. :-)


DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Mini Post: Reseed Identity Column

Hey all, when in doubt: Mini Post!

In this post we look at reseeding the identity column via T-SQL.

Please note that the rules change depending on if there are already values in your table when you attempt the reseeding.

1) With Values in Table

Suppose you want the next value in your table (Table1) to be 12. We will use the following:

DBCC CHECKIDENT (Table1, reseed, 11)

Note that your reseed value will be one less than the value you want to appear (e.g. 11 is one less than 12).

2) Without Values in Table (Empty Table)

Suppose you have an empty table and you want the next value to be 12 (for whatever reason). We will use the following:

DBCC CHECKIDENT (Table1, reseed, 12)

Note that your reseed value will be exactly the value you want to appear (e.g. we use 12).

3) Reseed via TRUNCATE

If you are going to delete all the values in a table anyway before you reseed, you can use the TRUNCATE statement instead of DELETE, and the table will automatically reseed from whatever it's initial starting value was.

TRUNCATE TABLE Table1

(Please read up on TRUNCATE before you decide that it's a viable solution for your needs)


DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Mini Post: Counting Duplicate Records

Hey all, just trying to sneak in a couple posts before the month ends (can't have a month without posts; just feels wrong).

Alright this was a useful something that I used way back whenever and recently had to reuse:

SELECT Col1, COUNT(*) AS Total
FROM Table1
--WHERE SomeCondition
GROUP BY Col1
HAVING COUNT(*) > 1
ORDER BY COUNT(*) ASC

This query will list the number of times a value in Col1 is duplicated (i.e. if appears more than one time).

Handy for finding copies of a value that is only supposed to appear once in a table.
You can, of course, adjust the "HAVING" section to suit your needs (e.g. HAVING COUNT > 3, HAVING COUNT = 1, etc) thereby creating a more generalized search query.


DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Mini Post: Useful - Colour Coding Your SQL Instances/Queries

Hey everyone just a quick useful something that my colleague reminded me of.

So if you're like me, you may have many queries windows open but they could be from multiple instances of SQL Server, over multiple servers.

It may become a little confusing as to what query is being run on what instance.

Luckily SQL Server gives you the ability to colour code your query windows! You can assign a colour to a particular instance and then all queries that run against that instance will have a band of colour, indicating the instance. Lets take a look after the jump.

Recreate a 2008 DB on a 2005 Instance

Hey everyone, just a quick note on one way to get data from a 2008 DB to a 2005 DB. (EDIT: This initial "Mini Post" turn out a bit longer than I expected...)

As most of you would know you cannot restore a backup from a newer version of SQL Server on an older instance, so this limits the options you have if you do end up having to this for whatever reason.

Your best option is to generate a script of the DB on the newer instance and execute the script on the older instance.

You can choose to either script the data as well, or you can export the data to either a text file or Excel document and import the data into the 2005 DB afterwards.