Jen McCown

Content rating: Beginner/review material.

The most common database restore I do is the WITH MOVE format.  I get DB backups from friends, book or presenter sites, and restore them on my box to play with.  The RESTORE syntax WITH MOVE, from BOL, is:

RESTORE DATABASE TestDB

FROM DISK = 'C:\AdventureWorks.bak'

WITH MOVE 'AdventureWorks_Data' TO 'C:\testdb.mdf',

MOVE 'AdventureWorks_Log' TO 'C:\testdb.ldf'

GO

See, database backups store information about the database they came from…namely, the logical file names, the source file locations, and so on.  When I restore a backup on my box, I'm very likely going to restore to a different filepath – I want to save my files in a folder on C:\, not F:\ (I don't even HAVE an F drive).  

Let's take a look.  First, locate the backup file itself – in this case, Brent Ozar's Twittercache database – and get the full filepath (e.g., is it saved in "c:\temp"?).  Then you can run this in SSMS:

RESTORE FILELISTONLY FROM DISK='C:\temp\TwitterCache.bak'

That'll get you the backup information:

TwitterCache F:\MSSQL\DATA\TwitterCache.mdf D PRIMARY 134217728 35184372080640 1 0 0 6DC5C93F-811F-40D3-9C8F-56692E098701 0 0 57147392 512 1 NULL 26000000004400037 B9297268-D556-44FE-9940-13A7B8550289 0 1
TwitterCache_log F:\MSSQL\DATA\TwitterCache_log.ldf L NULL 201138176 2199023255552 2 0 0 B5C0DCCF-9EBA-4A62-8B8A-6B09844B953C 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 1

All you really need are the LogicalNames. In this example, we get these logical names:

TwitterCache
TwitterCache_log

Now build your restore statement, using the Logical Names (in bold) and the filepath to your SQL data and log folder(s):

RESTORE DATABASE TwitterCache

FROM DISK='C:\temp\TwitterCache.bak'

WITH MOVE 'TwitterCache' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TwitterCache.mdf',

MOVE 'TwitterCache_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TwitterCache_log.ldf' 

The easiest part to mess up is the comma between the MOVE statements, so don't miss it.

The end.
-Jen McCown
http://www.MidnightDBA.com

Last night, with all the pomp, circumstance, and formailty due its gravity, we held the drawing for our first EVAR web contest. And the winner is….

Todd Holmes!

Thank you all for playing, and we'll have more giveaways in the weeks & months to come.

-Jen McCown, http://www.MidnightDBA.com

In the course of our interviews at the PASS Summit 2009 this month, we managed to snag four top-level SQL authors and educators at once, in the same room. (Actually, in the same 8'x8' space, but who's counting.) 

In Part 2 of our series "Buck, Paul, Joe, and Louis", we have an absolutely wonderful talk about agile development with Paul Nielsen, Joe Celko, and Louis Davidson (right before Buck Woody joined the party).  Louis started it out, talking about managers being the real problem, because they "want it done immediately, they don't want it designed first." This is one of my favorite topics, so I was absolutely thrilled to be in agreement with so many SQL development gurus.

Here's a (nearly word for word) transcript of the talk that followed:

Jen: "In a gig I was at recently, they got the business requirements, they handed it to the coders, and it was sort of "design as you write it". Which seems like a BAD idea to me."

Paul: "And because they don't do design – they THINK they're doing rapid development, rapid development tools, agile – but because they don't do design it actually takes them longer to develop, they don't have extensibility, they don't have data integrity, and they have to redo it again sooner."

Joe: "Agile is usually an excuse to do what we used to call 'cowboy coding'. There's a book on the first agile project – Chrysler. It was a year 2000 thing. It failed. Chrysler eventually had to throw the whole damn thing out and redo their payroll package in COBOL, from scratch.  Agile's based on a failed project, and it's become an excuse. Yeah, it's gradually developing into something that's got some merit, but, 'I'm not sloppy, I'm agile!'"

Louis: "The point I was making about playing chess [really quickly] is, people who do agile seem to play one move ahead.  And to do it really right, you need to be playing ten moves ahead. Obviously the idea behind doing monthly iterations and having some shippable code is awesome, but if you don't know what's coming up down the road, you may do something really horrible here, and then down here you gotta redo it."

Jen: "And it's driven by this panic to give the users exactly what they're asking for at that moment, as opposed to what they really need and taking the time to go, 'You know that's a GREAT idea, but we should really consider doing it this way because…"

Paul: "There is some truth in there. The whole idea of, let's spend a whole year gathering requirements, specify it out…"

At this point (about 4:45 in the video) Buck shows up, and we continue the talk. It's well worth going to see the video for a number of reasons, but certainly to catch the tail end of our talk on agile development.  I'm inspired, so watch this space for more on the failings of "rapid development".

In the meantime, I'd love to hear your stories on failed or successful agile or rapid development projects. Comment, tweet, or email me.

-Jen McCown, http://www.MidnightDBA.com