#SQL102

[shannonlowder]

SQL 201 – CREATE TABLE

CREATE TABLE

I've showed you how to get data out of a table, put data in, change it, and delete it.  But I haven't showed you how to create your own table....until now.

CREATE TABLE tableName ( columnName )

This statement is pretty straightforward, you have to choose the name of the table, and the names of your columns.  You also have to choose what data type you want to use for each column.  You can also list several columns rather than just one, just remember you have to separate the columns with a comma.  I haven't talked about this before, but you have to be careful what words you choose when you get to decide what to call a column, or a table, or any other object you create.

You can't use reserved words.

A reserved word is a word SQL uses.  For example, you can't create a table called SELECT.  How confused would you be if someone asked you what to do with:

SELECT * FROM SELECT

The interpreter wouldn't know what to do with this either.  And for that reason, you need to be aware that certain words are reserved in SQL.  Basically, if you see me type it in all caps, it's reserved.  If you have colorizing turned on in your SQL editor, and the word changes to a color different than your regular text, it's probably reserved.  If you can search on Microsoft's Books Online, and find it... it's reserved.  The moral; be careful.

There is another way you can create a table.  It comes up when you already have a SELECT statement, and you want to write that data out to a "temporary" table.  I put temporary in quotes, since that phrase actually has a deeper meaning.  I'll cover that more in another post.  For now, let me show you how to make a table from a SELECT statement.

SELECT * INTO tableName2 FROM tableName

This will create a table using the column names and datatypes already defined in tableName.  You can then use tableName2 to do work, and when you are through with it, you can get rid of it.  I'll show you how to delete database objects in another post too, since there are a few more concepts you'll need to be familiar with first.

The two queries I've shared with you today will let you create any number of tables you wish.  You won't be able to create two tables in the same database with the same name, but otherwise you can create any table you wish.  Just keep in mind reserved words, and you'll be alright.  As usual, if you have any questions, send them in.  I'm here to help!

Original Article

[shannonlowder]

World Backup Day

This past Saturday was World Backup Day, It's not an actual government recognized holiday, but as a DBA you should start celebrating it.  Maybe celebrate isn't the right word for what you might want to do on WBD.  If you have a copy of the  SQL PASS Summit 2011 DVDs, check out Grant Fritchey's Lightning talk rant: Backup Testing.  It's a quick 5 minute rant on some of the things you're going to want to do to celebrate the day.

Backup databases you don't want to lose

It's as simple as that.  Pick the restore strategy that matches how much data you're willing to lose.  If you're ok only getting back to a specific point you made your backup, choose simple.  If you want a little more control, and want to be able to recover to a point in time, choose full.  If you want to get really fancy, and set up faster backups and restores, let's talk.  We can get really fancy with these backups.

Now that you have your backups...

Store them some place safe

Yeah, you're probably backing up to a disc drive or attached to your server, right? That's alright to use to create the backups, but you need to get them off the machine used to create the backups.  Your goal in creating backups has to be to protect against failure.  Are you protected from a machine failure if your backups are stored on the machine you're trying to protect?

The two ways I protect my backups is shipping the backups off to another machine on my network, or shipping them off to a cloud storage solution.  The cloud solution will cost you based on the amount of storage you use, but if you were to lose the data center that houses your SQL server and the backup server, you will still lose your backups.  That's why I like the cloud solution.  Your backup files are generally stored on several servers, rather than just one.

You do have the trade-off of restore time.  You'll have to download those files from the cloud service provider in order to restore them.  You'll have to consider the costs when deciding where to store your backups.

Test your backups

Like Grant mentioned in his Lightning rant: You can apply all sorts of settings to your backups to make sure they're written properly, but the only way you can be sure your backups are successful is to test them.  The best way to do that is to run a restore of your backups to another server, and make sure the databases come back online with all the right data on the other server.

So, let's make world backup day, world backup testing day too.  Pull your latest backups and try to restore them.  That way, if any fail to restore, you've got a chance to fix the problem before you need those backups.

It could mean the difference between being able to recover from a failure, and not recovering from that failure.

If you need help, or have any questions about planning for disaster recovery, or testing your backup strategy, let me know!  I'm here to help!

    Original Article