Recent Articles:

From the Vault: Tip: RESTORE FILELISTONLY / WITH MOVE

June 9, 2014 sql server No Comments

I’ll be in France and largely AFK for a few weeks this summer, so I’m posting some golden oldies. You can find the original article and comments here

As we’ve seen in recent DBARant-able tales, not everyone is completely familiar with methods of restoring SQL backup files to new filepaths. The answer, in short, is: RESTORE FILELISTONLY/ RESTORE WITH MOVE.

Let’s say that you backed up the database ImportantDB from your production server, and you want to restore it to a test server. The only problem is, the data and log files on prod reside on the D: and E: drives , while the test box doesn’t HAVE D: and E: drives…only F: and G: drives.  Here’s what you do:

  • Use RESTORE FILELISTONLY to get the logical names of the data files in the backup. This is especially useful when you’re working with an unfamiliar backup file.

Example:
RESTORE FILELISTONLY
FROM DISK = \\srv1\sql\ImportantDB.bak’

  • Use RESTORE WITH MOVE to move and/or rename database files to a new path.

Example:
RESTORE DATABASE ImportantDB
FROM DISK = \\srv1\sql\ImportantDB.bak’
WITH MOVE ‘ImportantDB’ TO ‘F:\SQL\ImportantDB.mdf’,
MOVE ‘ImportantDB_log’ TO ‘G:\SQL\ImportantDB_log.LDF’

As they say on TV, it’s just that easy.

And yes, I realize that I wrote about this last year…but I’m gonna take the hit on this one. It’s useful, it’s important, and it’s overlooked.

Happy days,

Jen McCown

http://www.MidnightDBA.com/Jen

This Week in Awesome – International Version

June 6, 2014 Fluffy Stuff No Comments

Even though we’re abroad, this week still had awesome to share. So, this week in awesome:
awesome

How do you get people out of the way if you’re running in a crowded area? Apparently, you support this Kickstarter to get a little brass bell and go DING! DING! DING!

Brace yourselves: hoverbikes are coming. (Via @MythBusters)

From @healthcare_dev: “Classic developer double speak pic.twitter.com/aK4KTZxY4T” Has things like “Horrible hack = Horrible hack that I didn’t write; Temporary workaround = Horrible hack that I wrote”.

The world is full of wonder, especially if you never do any research into anything.
-@DemetriMartin 

Happy days,

Jen
http://www.MidnightDBA.com/Jen

 

From the Vault: Detangling Nested Views

June 2, 2014 Uncategorized No Comments

I’ll be in France and largely AFK for a few weeks this summer, so I’m posting some golden oldies. You can find the original article and comments here

I hate nested views. I hate them, I hate them, I hate them. This is the Nth shop I have been in that makes use of one view that pulls FROM and JOINs other views, which in turn pull from other views, and it’s foot-stompingly frustrating to work with.

I’ve talked before about how inappropriate views hurt performance, and I will talk in the future specifically about how nested views hurt performance, but today I just want to give you the start of a solution.  In time, I hope to develop this code into something beautiful, into a tool that will rewrite nested views into their simplest components, whilst simultaneously washing your dog and paying your overdue utility bills. But for now, this is what we have.

Note: This script is workable, but not complete. It doesn’t currently take different schemas into account, and there’s a lot more functionality I want to add to it.  Consider this a draft, or an early beta, and use it as intended.

A nested view is like a tree: the top node (the parent view) references one or more child nodes (child tables and views), which in turn may reference another level of child nodes, and so on.  Here we have a script that takes a given view and works through each level of nodes, to ferret out all the referenced objects:

----------------------------------------------------------------------------
-- Create temp table, variables
----------------------------------------------------------------------------
-- Create a temp table to hold the view/table hierarchy
CREATE TABLE #viewHierarchy
( id INT IDENTITY(1,1)
, parent_view_id INT
, referenced_schema_name NVARCHAR(255)
, referenced_entity_name NVARCHAR(255)
, join_clause NVARCHAR(MAX)
, [LEVEL] TINYINT
, lineage NVARCHAR(MAX)
)
DECLARE @viewname NVARCHAR(1000),
@count INT,        -- Current ID
@maxCount INT    -- Max ID of the temp table

-- Set the name of the top level view you want to detangle
SELECT @viewName = N'vw_stadium_tickets_current_football',
@count = 1
----------------------------------------------------------------------------
-- Seed the table with the root view, and the root view's referenced tables.
----------------------------------------------------------------------------
INSERT INTO #viewHierarchy
SELECT NULL parent_view_id
, 'dbo' referenced_schema_name
, @viewName referenced_entity_name
, NULL join_clause
, 0 [LEVEL]
, '/' lineage

INSERT INTO #viewHierarchy
SELECT DISTINCT @count parent_view_id
, referenced_schema_name
, referenced_entity_name
, '' join_clause
, 1 [LEVEL]
, '/1/' lineage
FROM sys.dm_sql_referenced_entities(N'dbo.' + @viewName,'OBJECT')

SELECT @maxCount = MAX(id)
FROM #viewHierarchy
----------------------------------------------------------------------------
-- Loop through the nested views.
----------------------------------------------------------------------------
WHILE (@count < @maxCount) -- While there are still rows to process...
BEGIN
SELECT @count = @count + 1

    -- Get the name of the current view (that we'd like references for)
SELECT @viewName = referenced_entity_name
FROM #viewHierarchy
WHERE id = @count

    -- If it's a view (not a table), insert referenced objects into temp table.
IF EXISTS (SELECT name FROM sys.objects WHERE name = @viewName AND TYPE = 'v')
BEGIN
INSERT INTO #viewHierarchy
SELECT DISTINCT @count parent_view_id
, referenced_schema_name
, referenced_entity_name
, '' join_clause
, NULL [LEVEL]
, '' lineage
FROM sys.dm_sql_referenced_entities(N'dbo.' + @viewName,'OBJECT')
SELECT @maxCount = MAX(id)
FROM #viewHierarchy
END
END
--------------------------------------
--------------------------------------
WHILE EXISTS (SELECT 1 FROM #viewHierarchy WHERE [LEVEL] IS NULL)
UPDATE T
SET T.[Level] = P.[Level] + 1,
T.Lineage = P.Lineage + LTRIM(STR(T.parent_view_id,6,0)) + '/'
FROM #viewHierarchy AS T
INNER JOIN #viewHierarchy AS P ON (T.parent_view_id=P.ID)
WHERE P.[Level]>=0
AND P.Lineage IS NOT NULL
AND T.[Level] IS NULL
SELECT       parent.*
,child.id
,child.referenced_entity_name ChildName
FROM #viewHierarchy parent
RIGHT OUTER JOIN #viewHierarchy child ON child.parent_view_id = parent.id
ORDER BY parent.id, child.id

This generates a table that we join to itself to get a hierarchical view of parent and child nodes:

So far, this is useful in quickly getting an overall picture of how complicated a nested view is, how many times it hits a particular table, etc.  Like I said, this is the basics, and I’d like to do more. If you do more with it, drop a comment or email and let me know what you did! I’m always interested in seeing improvements and expansions.

Feel free to use this work in any way you like, but be kind and slap a link back here in the comments.

Happy days,

Jen

And as always, thanks to the Simple-Talk SQL Prettifier for its help today.

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/