Archive

Archive for the ‘PASS’ Category

sp_AutomateDBRestore – Now With Table-Valued Parameters!

After several weeks of procrastination, I have finished the latest version of sp_AutomateDBRestore. The previous versions of the procedure built the restore statements using sys.database_files for the restored database, msdb.dbp.backupset, and msdb.dbo.backupmediafamily. This process works great if the instance is up and running, but if the instance is unavailable it won't work.

To handle this circumstance, I have added a table-valued parameter to the procedure allowing a list of backup files and backup file types to be passed in. This way, you can generate a restore statement as long as you have the backup files available. The proc uses RESTORE FILELISTONLY and RESTORE HEADERONLY statements to retrieve the physical and logical file names. Additionally, I've added parameters @backupfilepath and @logfilepath to allow a restore of the database to a different physical file than the source database. Here is the code to create a user-defined table type, followed by a call using the new parameters:

USE [master]
GO

CREATE TYPE [dbo].[backupfiletype] AS TABLE(
	[backupfilename] [varchar](255) NULL,
	[backupfiletype] [varchar](30) NULL
)
GO
DECLARE @backuptvp AS backupfiletype

INSERT INTO @backuptvp
VALUES
('C:\Backup\WIN-MK86UPCRGFF\multifile\FULL\WIN-MK86UPCRGFF_multifile_FULL_20140513_091120.bak','FULL'),
('C:\Backup\WIN-MK86UPCRGFF\multifile\LOG\WIN-MK86UPCRGFF_multifile_LOG_20140513_091135.trn','LOG'),
('C:\Backup\WIN-MK86UPCRGFF\multifile\LOG\WIN-MK86UPCRGFF_multifile_LOG_20140513_091203.trn','LOG'),
('C:\Backup\WIN-MK86UPCRGFF\multifile\LOG\WIN-MK86UPCRGFF_multifile_LOG_20140513_091354.trn','LOG')


DECLARE	@return_value int

EXEC	@return_value = [dbo].[sp_automateDBRestores]
		@sourcedbname = multifile,
		@restoredbname = multifile,
		@droprestoredb = 0,
		@noexec = 1,
		@datafilepath = 'C:\testrestore\',
		@logfilepath = 'C:\testrestore\',
		@TVP = @backuptvp

SELECT	'Return Value' = @return_value

GO

You can find the code for the updated procedure at my Presentation Slides and Scripts page under Wisconsin SQL Server User Group. As usual, feel free to leave any questions or comments in the comments section for the post.

Categories: PASS, SQL Server, T-SQL Tags: , ,

SQL Saturday #291

Speaker dinner at the Schnitzel Platz (MarkV is an instigator) -Thanks to Dave Mattingly for the photo

Speaker dinner at the Schnitzel Platz (MarkV is an instigator) -Thanks to Dave Mattingly for the photo


SQL Saturday #291 took place this past Saturday at DeVry University in Addison, Illinois. It was my eighth SQL Saturday, and it was the first one I helped run. I want to thank Bill Lescher (t), Wendy Pastrick (b|t), Bob Pusateri (b|t), Jamie Samsel, and Andy Yun (b|t) for all of their hard work. I had a great time and have heard positive feedback from all of the attendees I've spoken with.

The hard work these folks put in wouldn't mean anything without the speakers who volunteer their time and pay their own way to attend these events. I especially want to thank Jim Dorame (b|t), Merrill Aldrich (b|t), Eric Boyd (b|t), and Grant Fritchey (b|t). With only 15 minutes' notice, Jim stepped in to replace a speaker who did not arrive, moving from his scheduled noon slot to 9:15. Then Jim, Merrill, Eric, and Grant held a PowerShell panel in Jim's original slot. Their willingness to take on extra responsibility epitomizes the SQL community for me, and went a long way to making the event such a success.

Finally, I want to thank everyone who attended SQL Saturday #291. Without you, the SQL community would not exist. I spoke to a number of first-time attendees, and I hope they all come back again. The SQL community has given me more than I could ever give back. I've made great friends, learned from world-renowned experts, and grown personally and professionally. I met a current co-worker at SQL Saturday Chicago last year and my work in the community had a lot to do with my getting that job.

If you attended SQL Saturday and got something out of it, consider giving back. Presenting, blogging, volunteering, and answering questions on forums are all great ways to give back. If you haven't already, register for your local PASS user group. If you don't have a local user group, consider starting one. I've run the Chicago SQL Server User Group since July of 2012 and have had a blast. Feel free to contact me if you have any questions.

Strange Results from sys.fn_physlocformatter

October 17, 2013 2 comments

I'm writing from Charlotte, North Carolina where I'm attending my fifth PASS Summit. I hope to write a series of posts recapping my experiences, but I wanted to write about something I learned in Paul White's (b|t) pre-conference session, Understanding the Optimizer and Interpreting Execution Plans. The session was amazing and I think I'll spend the next year going through his scripts.

If you've read my blog you know I love internals. Paul introduced me to a function called sys.fn_physlocformatter. In SQL Server 2008 and beyond, %%physloc%% is a virtual column that returns the file, page and slot of each row of a result set in byte-reversed, hex format. That means it is hard to read. If you don't believe me, here is an example of the query and its result:

USE MYTEST
GO

SELECT %%physloc%% AS [physloc],*
FROM MYTestTable

 

physloc results

 

fn_physlocformatter takes that hex value and formats it into a colon-delimited value that is much more readable. Here's the query above, replacing %%physloc%% with fn_physlocformatter:

SELECT sys.fn_PhysLocFormatter(%%physloc%%),*
FROM MYTestTable

 

fn_physlocformatter results

 

After Paul showed us the function, I did a search to get more information and came across this post. Using the example provided, I wrote the following query to return a count of the number of rows written to each page:

SELECT SUBSTRING(sys.fn_PhysLocFormatter(%%physloc%%),4,(CHARINDEX(':',sys.fn_PhysLocFormatter(%%physloc%%),4) - 4)) [Page ID], COUNT(*) AS [Row Count]
FROM MYTestTable
GROUP BY SUBSTRING(sys.fn_PhysLocFormatter(%%physloc%%),4,(CHARINDEX(':',sys.fn_PhysLocFormatter(%%physloc%%),4) - 4))
ORDER BY [Row Count]

The sample code from the link above contains code to insert nine rows to MYTestTable2 at a time. I've listed the table definition and insert code below.

USE MYTEST
GO

CREATE TABLE MYTestTable2
(MYID INT IDENTITY, 
NAME CHAR(500), 
LNAME CHAR(500))
GO

INSERT INTO MYTestTable2 VALUES ('NAME-1','LNAME-1');
GO
INSERT INTO MYTestTable2 VALUES ('NAME-2','LNAME-2');
GO
INSERT INTO MYTestTable2 VALUES ('NAME-3','LNAME-3');
GO
INSERT INTO MYTestTable2 VALUES ('NAME-4','LNAME-4');
GO
INSERT INTO MYTestTable2 VALUES ('NAME-5','LNAME-5');
GO
INSERT INTO MYTestTable2 VALUES ('NAME-6','LNAME-6');
GO
INSERT INTO MYTestTable2 VALUES ('NAME-7','LNAME-7');
GO
INSERT INTO MYTestTable2 VALUES ('NAME-8','LNAME-8');
GO
INSERT INTO MYTestTable2 VALUES ('NAME-9','LNAME-9');
GO

Because the rows are 1008 bytes long, seven rows will fit on one 8kb data page. After the initial insert of nine rows, I expected to see a page containing seven rows followed by a page with two rows. Here are the results after the initial insert:

 

Initial Insert Results

 

I continued running the nine inserts, to confirm that my code was working as expected. And for the next five runs, it did. The strange results didn't show up until the seventh run, where I saw this:

 

strange results

 

After the seventh insert, something was causing seven pages to be allocated each with a single row inserted. This didn't make any sense to me, so I continued investigating. I'll show you the results of those investigations in my next post, sys.fn_physlocformatter - The Mystery Deepens!