T-SQL Tuesday #40 Roundup: Files and Filegroups

Good day all, and happy T-SQL Tuesday #40! Yes, this event has officially hit middle age now. I fully expect it’ll go get itself a fast car in the upcoming months.

We had a great turnout for this month. “Files and Filegroups” is an open enough topic that I got exactly what I wanted: A nice variety of blog posts, all over the spectrum. We got introductions and issues, solutions, Powershell, mysteries, and more. Dig it! There’s not a bad blog in the lot!

Rob Farley: Filegroups and Non-Clustered Indexes

Rob wrote an absolutely fantastic primer for filegroups, with useful code, that I’ll be pointing people to regularly from here on out.

Jason Brimhall: T-SQL Tuesday #040: File and Filegroup Wisdom

Jason had a “filegroup not online” issue just in time for T-SQL Tuesday! He walks us through his solution here.

Merrill Aldrich: T-SQL Tuesday #040: Files, Filegroups and Visualizing Interleaved Objects

Merrill gives us an early lesson in storage internals, demonstrating the file proportional fill algorithm. He’s made us a demo, so you can follow along at home! (Bonus points for his Lord of The Rings reference.)

Patrick Keisler: T-SQL Tuesday #40 – Proportional Fill within a Filegroup

Patrick gives us another take on the proportional fill algorithm, with an emphasis on space used.  I like having the two perspectives on this topic!

Robert Davis: Day 30 of 31 Days of Disaster Recovery (T-SQL Tuesday #40): Using Partial Availability and Initialize from Backup to Replicate a Partial Database

Robert used T-SQL Tuesday as day 30 of his “31 days of Disaster Recovery”…awesomesauce!

He discusses the pros and cons of a scenario: we want to replicate only part of a very large database (without initializing from snapshot, or copying and restoring the whole database backup).

Bob Pusateri -: Moving A Database to New Storage With No Downtime

Bob tells us his story about migrating a large number of files to new storage, with no downtime. He considers his options in detail, and goes with a hybrid solution. Bob demonstrates this with scripts and diagrams, with “circles and arrows and a paragraph on the back of each one…”  This is a great blog in a group of great blogs here, dig into it!

Chris Yates: T-SQL Tuesday #040: File and Filegroup Wisdom

Chris talks about one simple but lovely premise: “You can achieve performance gains by created non clustered indexes on a different filegroup if the filegroups are using different physical drives.”

Chris Fradenburg: T-SQL Tuesday #040: File and Filegroup Wisdom [Powershell alert!!]

Chris has an interesting take on this month’s topic:  How Powershell treats filegroups and files!

Thomas Stringer: T-SQL Tuesday #40: Get Filegroup Count and Default Configuration with PowerShell

Another Powershell blog! I wasn’t expecting that. Thomas explores a method to find out how many filegroups a database has, and whether the PRIMARY filegroup is default.

Hemanth Damecharla: T-SQL Tuesday #040: File and Filegroup Wisdom (Quick Introduction to FILESTREAM)

Hemanth takes this opportunity to give us an introduction to FILESTREAMs (it counts, because it needs its own file group).

(By the way, if you’d like an extension on this topic, take a look at my blog “FileTable: SQL Server 2012′s little gasp-maker“.)

Steve Jones: T-SQL Tuesday #40– File and Filegroups

Steve gives us another introductory Filegroups and Filestream piece that dovetails nicely with Hemanth’s. It’s got a nice walkthrough, and links to Steve’s talks on the subject.

Sean McCown: T-SQL Tuesday: A file delete exercise

Wrapping up this month’s T-SQL Tuesday is our very own fellow MidnightDBA, Sean….who gives us homework! Well, not exactly, but we hear about assignments he’s given over the years, with surprising conculsions. And of course, the repro.

SQL Server 2012 CU2 – Have a little fun (#AppliedSQL)

SQL Server 2012 Cumulative Update (CU) 2 is out! If you’re like Me-from-10-years-ago, you couldn’t care less!

But there are actually some good reasons for paying attention to new CUs.  For example: it’s one good way to get to know the new version of SQL Server: play with the bugs before you install the updates! (You DO have the new version of 2012 on a test box somewhere, right? If no, download a trial version from MS).

A lot of these bugs are pretty out there, though…how do we play around with “Access violation when you run a DAX or MDX query against a tabular model database through a BI semantic model connection in SQL Server 2012 “?  Yeah, that sounds easy to set up. 

Ooh, here’s a simple one! “Cannot show requested dialog” error message when you try to view properties of a database in a SQL Server 2012 instance.  Perfect.  So let’s just do what it says!  

1. Create a new SQL user, “Test”.
2. Create a new database, “TestDB”.
3. Assign the user Test as the TestDB owner.
4. Now, log into SSMS as Test, right click TestDB, and try to view properties.

Voila! You get the error! 

Then apply CU2 and try it again.

No, seriously…go do all this yourself. If you’re not in the habit of playing around with new versions of SQL, of the updates, of reproducing issues, this is the ideal first step.  Get in there and DO it, man.  Start to pay attention to and get passionate about the product you work with.

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen

Applied SQL: Demonstrate Simple Backup and Restore

Let’s apply a little backup and restore knowledge, eh wot?

I introduce this whole concept in Applied SQL: You Have Homework. Find all assignments on the Applied SQL page.

Prerequisites: basic T-SQL, create objects/data, basic understanding of recovery models and backup types.

Reading – SQL Server Books Online articles:

• Backup (Transact-SQL)
• Restore (Transact-SQL) 
• ALTER DATABASE SET options (for FULL mode)
• Choosing the Recovery Model for a Database
• Backup Under the Full Recovery Model

Setup: We’re going to build a script that demonstrates different backups, and the effect of their restores.  The setup and assignments should all contribute to this one script, so save your work!  First, script a new test database and table simply:

• Name the database “testing”
• Set it to FULL recovery mode.
• Create a simple table (T1) in it with at least one varchar(100) column.
• Insert a few rows into your table.
• Add a “drop database; GO ” at the beginning of your script, so you’ll have a clean start each time.

Level 1 Assignments: Backups – after the Setup section in your script, add the following:

[1-1] Full backup 

• Backup the database to disk, filename testing.bak
• Insert a few more rows into your table.

[1-2] TLOG backup

• Backup the LOG to disk, filename testingLOG1.bak
• Insert a few more rows into your table.

[1-3] DIFF backup, one more TLOG backup

• Take a DIFFERENTIAL backup of the database, filename testingDIFF2.bak
• Insert a few more rows into your table.
• Backup the LOG to disk, filename testingLOG3.bak
• Insert a few more rows into your table.

For the purposes of this exercise, we’re naming the backup files a little oddly (full, log1, diff2, log3). We’re doing this to help us keep track in the next assignment section – restores.

Setup2: Speaking of the next section…we have the option of using the same backup files, or of rerunning then entire script. Rerunning the whole script is kind of fun, so go back to all of your BACKUP statements and add options to initialize and format your backup file.

Level 2 Assignment: Restores – after the Backups section in your script, add and run a “drop database; GO ” to make way for the restores, then add the following:

[2-1] Restore FULL

• Restore the FULL backup of the database (from step 1-1 above).
• Select all data from your table; note how data entered later (after the FULL backup) isn’t here. Exactly as we planned it…
• Drop the database & comment out this section when you’re ready to move on.

[2-2] Restore FULL, DIFF

• Restore the FULL and DIFFERENTIAL backups of the database (from steps 1-1 and 1-3 above). If you haev problems restoring the differential, pay attention to the error you get.
• Select all data from your table; note how data entered later (after the DIFF backup) isn’t here. Get the tlog1/diff relationship straight in your head…do you know why we skipped right over the tlog1 backup?
• Drop the database & comment out this section when you’re ready to move on.

[2-3] Restore FULL, DIFF, TLOG

• Restore the FULL, DIFFERENTIAL, and TLOG3 backups of the database (from steps 1-1, 1-3, 1-4 above). Again, pay attention to any errors you get to figure out the problem.
• Select all data from your table to see where you stand.

Bonus points: Post your scripts (or a link to them) in the comments below, just to show off that you did it.

Double bonus points: Do you think we should’ve done something more here? Post a related assignment of your own in the comments, and I’ll see how I do.

Recommended optional videos, by MidnightDBA:

• Basic Backup
• Restore With Move
• Backup With Init
• Backup File Extensions
• See also Best Backup Class Evar series on the Admin page.

 -Jen McCown
www.MidnightDBA.com/Jen