Backup Meta-Data

BuckWoody — Tue, 30 Mar 2010 14:46:00 GMT

I'm working on a PowerShell script to show me the trending durations of my backup activities. The first thing I need is the data, so I looked at the Standard Reports in SQL Server Management Studio, and found a report that suited my needs, so I pulled out the script that it runs and modified it to this T-SQL Script.

A few words here - you need to be in the MSDB database for this to run, and you can add a WHERE clause to limit to a database, timeframe, type of backup, whatever. For that matter, I won't use all of the data in this query in my PowerShell script, but it gives me lots of avenues to graph:

SELECT distinct t1.name AS 'DatabaseName'

,(datediff( ss, t3.backup_start_date, t3.backup_finish_date)) AS 'DurationInSeconds'

,t3.user_name AS 'UserResponsible'

,t3.name AS backup_name

,t3.description

,t3.backup_start_date

,t3.backup_finish_date

,CASE WHEN t3.type = 'D' THEN 'Database'

WHEN t3.type = 'L' THEN 'Log'

WHEN t3.type = 'F' THEN 'FileOrFilegroup'

WHEN t3.type = 'G' THEN 'DifferentialFile'

WHEN t3.type = 'P' THEN 'Partial'

WHEN t3.type = 'Q' THEN 'DifferentialPartial'

END AS 'BackupType'

,t3.backup_size AS 'BackupSizeKB'

,t6.physical_device_name

,CASE WHEN t6.device_type = 2 THEN 'Disk'

WHEN t6.device_type = 102 THEN 'Disk'

WHEN t6.device_type = 5 THEN 'Tape'

WHEN t6.device_type = 105 THEN 'Tape'

END AS 'DeviceType'

,t3.recovery_model

FROM sys.databases t1

INNER JOIN backupset t3

ON (t3.database_name = t1.name )

LEFT OUTER JOIN backupmediaset t5

ON ( t3.media_set_id = t5.media_set_id )

LEFT OUTER JOIN backupmediafamily t6

ON ( t6.media_set_id = t5.media_set_id )

ORDER BY backup_start_date DESC

I'll munge this into my Excel PowerShell chart script tomorrow.

Script Disclaimer, for people who need to be told this sort of thing:

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. Yes, there are always multiple ways to do things, and this script may not work in every situation, for everything. It’s just a script, people. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.

Code that Writes Code

BuckWoody — Wed, 25 Nov 2009 14:01:40 GMT

I have scripts that re-create my databases for testing and development purposes. But sometimes I want to take the data from a set of tables and move it as well – I could use SSIS, or a SELECT INTO statement, but what if I want to “re-set” the data to a point in time? In other words, load it with some “base data”?

I thought this might be a good place to demonstrate “code that writes code”. No, it isn’t that big of a deal – most DBAs know how to do this, but in the interest of those who don’t, I thought I would share.

So the end result I want in this case is to have a bunch of INSERT statements that contain my base data from a table where that data already exists. I could script out the table from SSMS and use the replacement parameter feature to fill out each record individually:

I have a table in one of my databases that I right-clicked to show the INSERT statement:

-- Person

INSERT

INTO [WAVS].[dbo].[Person]

([PersonName]

,[Street]

,[CityStateZip]

,[PersonType])

VALUES

(<PersonName, varchar(150),>

,<PersonStreet, varchar(100),>

,<PersonCityStateZip, varchar(255),>

,<PersonPersonType, varchar(100),>)

I can use the CTRL-SHIFT-M keys to replace the VALUES part with the values I want.

But I already had a copy of that “base” data before I turned the database over to the testers. Before I give it to them, I run code like this on each table – this one is for the Person table above (note that there should only be two lines here):

SELECT

'INSERT INTO [WAVS].[dbo].[Person] ([PersonID], [PersonName], [Street], [CityStateZip], [PersonType]) VALUES (' + CAST([PersonID] AS VARCHAR(5))+ ', ''' + [PersonName]+ ''', ''' + [Street]+ ''', ''' + [CityStateZip] + ''', ''' + [PersonType] + ''')'

FROM

[WAVS].[dbo].[Person]

When I run this code, I get these statements:

INSERT INTO [WAVS].[dbo].[Person] ([PersonID], [PersonName], [Street], [CityStateZip], [PersonType]) VALUES (1, 'Buck Woody', '123 Here Street', 'Covington, WA 98042', 'Vet')
INSERT INTO [WAVS].[dbo].[Person] ([PersonID], [PersonName], [Street], [CityStateZip], [PersonType]) VALUES (2, 'Jane Doe', '231 Function Ave', 'Redmond, WA 98052', 'Vet')
INSERT INTO [WAVS].[dbo].[Person] ([PersonID], [PersonName], [Street], [CityStateZip], [PersonType]) VALUES (3, 'Diane Wilson', '34251 Appt 3 7th Street', 'Seattle, WA 98061', 'Vet')
INSERT INTO [WAVS].[dbo].[Person] ([PersonID], [PersonName], [Street], [CityStateZip], [PersonType]) VALUES (4, 'John Kelso', '89734 Country Lane', 'Covington, WA 98042', 'Farmer')
INSERT INTO [WAVS].[dbo].[Person] ([PersonID], [PersonName], [Street], [CityStateZip], [PersonType]) VALUES (5, 'Marjorie Christian', '9893452 Changein Lane', 'Maple Valley, WA 98072', 'Farmer')
INSERT INTO [WAVS].[dbo].[Person] ([PersonID], [PersonName], [Street], [CityStateZip], [PersonType]) VALUES (6, 'Joanne Lister', '98904 Mapelwood Drive', 'Spokanne, WA 98045', 'Zoo Worker')

All I have to do is copy those statements into my “initialization” scripts just after the table creations, and then I can run them at will to create my new database (I know I can take a backup – I have reasons for not doing that).

There are two keys for writing code that writes code: The first is to simply use the single tick (') to write out what the end code should say (INSERT INTO, in my case) and then to use multiple ticks (''') when you want the code to contain the ticks themselves. This “escapes” the characters so that you can embed them. If you use SSMS to write your queries, the color coding will look as mine does above. You’ll also need to remember to CAST or CONVERT any data types that don’t concatenate (+) together well. In my case I had to CAST an integer value – but that’s OK, since it is only printing to the screen and I don’t include the ticks, the INSERT works fine.

You can use this method not only with INSERTS, but with lots of operations. And you can also even fire off the code once you create it – but that’s another post :)

Search results matching tags 'DBA', 'T-SQL', 'SQL Server', and 'SQL Server Management Studio'

Backup Meta-Data

Code that Writes Code