I’m A Square! SQLPass B-I-N-G-O

Here’s something fun going on at the SQLPass Conference in Seattle November 2-6:

SQLPass BINGO!
(rules and playing cards can be found here)

I’m personally looking forward to meeting people I ‘see’ on Twitter every day. Even though I’m not eligble to play (and win PRIZES), my goal will still be to meet those I haven’t spoken face-to-face with.

Here is a list of the people playing. I’ve marked those whom I’ve met and those who I know from being online and then the remainder is anyone I haven’t ever encountered. They will definitely be at the top of my list. If you find me and need help with anyone I know already, I'll point you in the right direction!

Square	SquareUserName	My Status
Andy Leonard	AndyLeonard	Need to meet
Aaron Bertrand	AaronBertrand	Need to meet
Aaron Nelson	SQLvariant	Need to meet
Adam Machanic	AdamMachanic	Need to meet
Allen Kinsel	sqlinsaneo	I know Allen!
Andy Warren	sqlAndy	Need to meet
Arlene Gray	whimsql	Need to meet
Bill Fellows	billinkc	Need to meet
Bill Graziano	billgraziano	Hey! I know Bill!
Blythe Morrow	blythemorrow	Hey! I know Blythe!
Brent Ozar	brento	Hey! I know Brent!
Brian Kelley	kbriankelley	Need to meet
Colin Stasiuk	BenchmarkIT	Need to meet
Denny Cherry	mrdenny	Need to meet
Eric Humphrey	lotsahelp	Need to meet
Geoff Hiten	SQLCraftsman	Need to meet
Grant Fritchey	GFritchey	I know Grant!
Jeff Rush	JeffRush	Need to meet
Jeremiah Peschka	peschkaj	Need to meet
Joe Webb	JoeWebb	I know Joe!
Ken Simmons	kensimmons	Need to meet
Kendal Van Dyke	SQLDBA	Need to meet
Lee Anne Pedersen	leeannepedersen	Need to meet - my roomie!
Lori Edwards	loriedwards	Need to meet
Mike Walsh	Mike_Walsh	Need to meet
Mike Wells	SarasotaSQL	It will be a discovery
Pat Wright	SQLAsylum	I know Pat!
Peter Schott	paschott	It will be a discovery
Peter Shire	Peter_Shire	Need to meet
Ross Mistry	RossMistry	Need to meet
Rushabh Mehta	rushabhmehta	I know Rushabh!
Steve Jones	way0utwest	Need to meet
Stuart Ainsworth	stuarta	Need to meet
Tim Benninghoff	bugboi	Need to meet
Tim Ford	sqlagentman	I know this Tim!
Tim Mitchell	Tim_Mitchell	Need to meet this Tim
TJay Belt	tjaybelt	I know TJ!
Todd McDermid	Todd_McDermid	Need to meet
Tom LaRock	SQLRockstar	I know Tom!
Trevor Barkhouse	SQLServerSleuth	It will be a discovery
Wendy Pastrick	wendy_dance	Well, this is me...
Wesley Brown	WesBrownSQL	Need to meet
William McKnight	williammcknight	It will be a discovery

SQL University: SQL Admin Part 1 – Database Files and Management

We are going to start our week off by taking a look at where the database files are stored and how to work with them.

Two Types of Files
First, there are two types of files which make up your database:
mydatabasename.mdf -- These are the data files (you may also have .ndf files)
mydatabasename.ldf – These are your transaction log files

Data files store all of the, well, data and metadata associated with your database. Things like data structures, stored procedures, views, etc.

Log files are a transactional history of activity in your database. These should not be confused with other available log files for SQL server operations, however (errors, setup, etc.)

Great – where are these files anyway? There is a lot of information out on the web about determining the architecture for your database storage. Instead of rehashing that here, I will show you where to find existing files.

Default Location
In SQL Server Management Studio (SSMS), right-click on the servername and choose “Properties”. In the left hand navigation bar, click “Database Settings”. Near the bottom, you will see “Database default locations”. There will be a listing for Data files and one for Log files. Most companies have these in separate file directories, if not separate hard drives.

This is where any new database created will store its files by default.

Specific Location
To determine where a specific set of database files is located, right-click on the name of any database, choose “Properties” and then click “Files”. You will need to scroll to the right to see the Path. The File Name will also be shown. Note: there may be more than one .mdf and/or .ldf file for your database.

Now, you can also do this without resorting to drilling through GUI screens. Just use the following command:

select * from sys.master_files

Moving Files
On occasion, there is a need to move your files to another location, or perhaps you need to make sure a database has been taken offline. Using the Sp_detach_db and ATTACH commands are a great tool to have in your arsenal.

Sp_detach_db is used to first disassociate the database from the underlying files. This does NOT delete your database, even though it will no longer show up under Databases in SSMS.

sp_detach_db [ @dbname= ] 'database_name'

Once a database is Detached, you can move the .mdf and .ldf files anywhere you need them. This is also great for copying databases.

Once you have the files in the appropriate locations, use the ATTACH command to re-attach the database:

CREATE DATABASE database_name
ON [ ,...n ]
FOR { ATTACH [ WITH ]
| ATTACH_REBUILD_LOG }

Refer to the MSDN page if you would like to get more information about additional parameters or dealing with replicated, mirrored, or other database scenarios before Detaching. http://msdn.microsoft.com/en-us/library/ms190794.aspx

Of course, SSMS also has these features availabe. Right-click any database and choose “Tasks” then “Detach”.

To Attach the database, right-click on the heading “Databases” and choose “Attach”. You can then browse to the file location by clicking the “Add” button. The appropriate .mdf and .ldf files should appear and then you can click “OK”.

Refreshing your list of Databases will confirm if your Attach was successful.

A Word of Caution
SQL Server comes with a set of System Databases which are used internally by SQL Server to keep track of data and processes necessary to make the engine function. Here is a short description of each for you: http://www.mssqltips.com/tip.asp?tip=1420

If you find you need to move any System Databases (master, model, etc), Microsoft’s detailed instructions can be found here:
http://msdn.microsoft.com/en-us/library/ms345408.aspx

Go back to the SQL University home page.

How I Became a Database Administrator

Thanks to my buddy Ted for Tagging me. (See his blog here, or find him on Twitter @onpnt)

My journey to the position of DBA starts off much like those of others: I graduate from college (with my degree in Psychology) and start looking for work. As a new graduate with a piece of paper to prove it, all I have to show for experience are the odd jobs I held while in High School and College. Fortunately, for me anyway, those jobs were always in an administrative role instead of fast food. Taking my diploma and my admin/customer service skills into the workforce I found myself employed – as a ‘secretary’ as they were called back then.

Fortune smiled upon me once again, having more than half a brain, and I ended up being the Go-To Person in the office when someone couldn’t get their computers to behave the way they expected: network issues, printing, using applications, you name it and I fixed it. Evenutally, I had the good fortune to take a class on how to use Microsoft Access. As my first introduction to the word ‘database’ and learning about datastorage and querying, I was hooked! As fate would have it, my Administrative Assistant position was no longer needed and I was in search of another opportunity.

I went back to that company where I took the database class. They were hiring! For the next three years, I worked as a Software Trainer. I began teaching VBA and .net classes and had my first taste of SQL Server teaching T-SQL. As I started to talk more with my students, I realized I wanted to get out there and use the skills I had been teaching everyone else. This leads to my first Real Job™ as a developer for a booming Dot-Com and what I like to call the “Sold My Soul” years. As much work as it was, this opportunity did give me the stepping stone to becoming a DBA, though. About 4 weeks into my first full-fledged .net developer position, the DBA walked out. When management looked around the room and said, “who here knows anything about databases? Ah, yes – Wendy! You’re our new DBA” well, the rest is history.

Formal training has not existed since that first Microsoft Access class. Sometimes it reminds me of the saying “Everything I Need to Know I Learned in Kindergarten”; other times it’s a struggle to make sure I keep up and learn whatever I can whenever the opportunity strikes. Fortunately there is a wealth of information on the interenet and many wonderful resource books. However, I learn best in hands on situations. This makes conferences that much more valuable for me. While I have attended a TechEd here and there, my preferred conference is SQLPass (www.sqlpass.org). It’s refreshing to be a contributing member or a professional community.

I still haven’t reached my personal professional goal, and while I can’t reveal what that is, I promise to share it when I do!