Mini Post: Adding a Column to an Existing Table With a Default Value

Hey again everyone and welcome to SQL Something.

Just thought I'd throw this in as well because I needed to do this earlier this week: Adding a column with a default value.

Initial query was found here (click this, it's a very cool blog).

See my slight variant below and explaination:

ALTER TABLE YourTable
ADD YourNewColumn INT NOT NULL DEFAULT(42)
GO

So as far as an explanation goes, what the above does is alter your existing table (named 'YourTable') by adding a new column (named 'YourNewColumn') of type INT with a default value of 42. Each row in the table will then have a new column filled with a value of 42.

You can change the column type and the default value in the query to suit your needs.

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: 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. :-)