|
|
I’ve got a lot of changed altered procedures for a project I’m working on. I’ve been altering a lot of data in my test database, so I needed to restore my database and apply the changes I’ve made. The easy way to do would be a powershell script. Here’s what I found.
From Martin Bell for the post, “Executing all .SQL files in a directory with Powershell” Thank you Martin!
I also used twitter and asked #SQLHELP and SQLSoldier responded with this in about 3 minutes. Very impressive!
Get-ChildItem -Path "C:\SQLScripts" -Filter "*.sql" | % {invoke-sqlcmd -InputFile $_.FullName}
Here’s what I altered Martin’s script to:
foreach ($f in Get-ChildItem -path "C:\SQLScripts\" -Filter *.sql | sort-object)
{
$out = "C:\SQLScripts\OUTPUT\" + $f.name.split(".")[0] + ".txt" ;
invoke-sqlcmd –ServerInstance <<ServerName>> -Database <<DBName>> -InputFile $f.fullname | format-table | out-file -filePath $out
}
Here’s all the info on the Invoke-SQLCMD from MSDN.
I’ve got a new step in a job that I’m concerned about the duration. Here’s a script to get the duration of a specific step of a job. Note I found that whole conversion of the Run_DateTime column from Ramblings of a DBA – Tara Kizer – kudos, Tara.
Declare @JobName varchar(255),
@StepName varchar(255)
Set @JobName = '<<Enter Job Name Here>>'
Set @StepName = '<<Enter Job Step Name Here>>'
Select top 1000 run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +
(run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
run_duration = Left(RIGHT('000000' + CONVERT(varchar(6), run_duration), 6), 2)+':'
+ Substring(RIGHT('000000' + CONVERT(varchar(6), run_duration), 6), 3, 2)+':'
+ Right('00' + CONVERT(varchar(6), run_duration), 2),
SJ.name,
SJH.step_name
from msdb.dbo.sysjobs SJ
Join msdb.dbo.sysjobhistory SJH on SJH.job_id = SJ.job_id
where SJ.name = @JobName
and SJH.step_name = @StepName
Order by run_datetime desc --Use this to find the most recent
--Order by run_duration desc --Use this to find the slowest
Every once in a while, you need to loop through a bunch of records in TSQL. It’s not the fastest way to do things, but sometimes it’s necessary. I’ve been working on a process that requires this and I needed to know the progress of the loop, so I came up with this model. The output will give you a bunch of statistics including a predicted finishing time every 1000 rows (note you can adjust this). This should save me from getting some grey hairs when a looping process is necessary.
Declare @counter int,
@currentRow int,
@rowsFound int,
@start datetime,
@SecSoFar bigint,
@PredictedTotalSec int,
@PercentComplete varchar(10),
@CompleteTime datetime
Set @rowsFound = 200000 -- or set this equal to the count of a table you are working with.
Set @currentRow = 1
Set @start = GETDATE()
while @currentRow <= @rowsFound
Begin
--<<DO YOUR WORK HERE>>--
Set @currentRow = @currentRow + 1 ---Update the counter....
If (@currentRow % 1000) = 0 --display every 1000 rows or adjust...
Begin
Set @SecSoFar = Datediff(s, @start, Getdate())
Set @PercentComplete = Cast(ROUND(@currentRow/Cast(@rowsFound as float), 3)*100 as varchar(20)) + '%'
Set @PredictedTotalSec = (@SecSoFar/Cast(@currentRow as float))*@rowsFound
Set @CompleteTime = Dateadd(s, @PredictedTotalSec, @start)
Select
@currentRow as 'Current_Row',
@rowsFound as 'Total_Rows',
@start as 'Start_Time',
GETDATE() as 'Current_Time',
@SecSoFar as 'SecondsSoFar',
@PercentComplete as 'Percent_Complete',
@PredictedTotalSec as 'Predicted_Total_Seconds',
@CompleteTime as 'Predicted_Complete_Time'
End
End
Here’s a script I came up with to move my central management server’s Groups and Server from one server to another. I know it doesn’t make a ton of sense to do this, but I started testing the CMS setup on a test server and it’s worked so well, we wanted to put it on a production server. There are two scripts that are run on the source server that create scripts to run on the destination server.
/*
--Verify you've got the correct groups.
Select * from dbo.sysmanagement_shared_server_groups_internal
where is_system_object = 0 --ignore the builtin groups.
*/
Select '
Declare @server_group_id int
EXEC msdb.dbo.sp_sysmanagement_add_shared_server_group @parent_id=1, @name=N''' + name
+ ''', @description=N''' + description + ''', @server_type=0, @server_group_id=@server_group_id OUTPUT'
+ CHAR(13) + CHAR(10)
+ 'Go'
from dbo.sysmanagement_shared_server_groups_internal
where is_system_object = 0 --ignore the builtin groups.
Again, run the output of this on the destination server.
Here’s the script to move the servers with their groups – note the group id is dynamic, so even if the destination server has more groups, it will figure out the correct one.
Select
'Declare @SGID int, @SGName nvarchar(255)
Set @SGName = ''' + SGI.name + '''
Select @SGID = server_group_id from dbo.sysmanagement_shared_server_groups_internal
where name = @SGName
Declare @server_id int
EXEC msdb.dbo.sp_sysmanagement_add_shared_registered_server @server_group_id=@SGID, @name=N''' +
RSI.name + ''', @server_name=N''' + RSI.server_name + ''', @description=N''' + RSI.description + ''', @server_type=0, @server_id=@server_id OUTPUT'
+ CHAR(13) + CHAR(10)
+ 'Go'
from dbo.sysmanagement_shared_registered_servers_internal RSI
Join dbo.sysmanagement_shared_server_groups_internal SGI on RSI.server_group_id = SGI.server_group_id
Finally, run the output of this on the destination server.
Here’s a couple of resources I used to figure this stuff out:
There’s been many times I’ve tried to find the backup history of a database. Questions like when was the last backup taken? Where was it backed up to? etc. I googled a script for this and I used MSSQLTips as a baseline – found here – then altered it to this:
Declare @DBName varchar(255)
Set @DBName = '<<Database Name Here>>'
SELECT top 10
@@ServerName AS Server,
BUS.database_name,
BUS.backup_finish_date,
CASE BUS.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
BUS.backup_size,
BUMF.physical_device_name
FROM msdb.dbo.backupmediafamily BUMF
INNER JOIN msdb.dbo.backupset BUS ON BUMF.media_set_id = BUS.media_set_id
WHERE BUS.database_name = @DBName
And BUS.type = 'D'
ORDER BY BUS.backup_finish_date desc
There are quite a few times I need to number rows of data based on some grouping column. I often forget how to do it. Well, here’s a couple of examples:
Create table TestTable1
(ID int,
InsertDate datetime)
Go
Insert TestTable1 (ID, InsertDate)
Values(1,'8/1/10'),
(1,'7/1/10'),
(1,'6/1/10'),
(2,'3/15/10'),
(2,'3/16/10'),
(2,'3/13/10'),
(2,'3/11/10')
--Note the values are out of order for ID 2
Select *,
(select count(*) from TestTable1 TT2
where TT1.ID = TT2.ID
and TT1.InsertDate < TT2.InsertDate) as 'rank'
from TestTable1 TT1
Order by ID, rank
Or a simpler way…
SELECT *,
ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY insertdate ) as 'rank'
from TestTable1 TT1
Order by ID, rank
Here are some examples and explanations:
There’s been many times I’ve tried to find the restore history of a database. Questions like when was the backup taken? Can we re-restore and re-do this? Can we create a copy of the original database? etc. Most of the times I use jobs to restore, but sometimes I’ve got to use LiteSpeed, etc. I had to find the source of a backup this morning – here’s what I came up with for a script:
Declare @DBName varchar(255)
Set @DBName = '<<Database Name Here>>'
Select top 10
destination_database_name,
RH.restore_date,
BUS.server_name as 'SourceServer',
BUS.database_name as 'SourceDB',
BUS.backup_start_date,
BUS.type,
BMF.physical_device_name
from msdb.dbo.restorehistory RH
Join msdb.dbo.backupset BUS on BUS.backup_set_id = RH.backup_set_id
Join msdb.dbo.backupmediafamily BMF on BMF.media_set_id = BUS.media_set_id
where RH.destination_database_name = @DBName
Order by RH.restore_date desc
Note – Here’s Technet’s documents on the Backup and Restore Tables in MSDB.
Recently I got an email from a tester claiming that the IDs of values in static data tables are different in one database than they are in another database. To check this out, I used a combo of the  BINARY_CHECKSUM and CHECKSUM_AGG. Here’s a breakdown of each:
- The BINARY_CHECKSUM function returns a checksum value for a row of a table or over a list of expressions.
- A value is computed for each row, this makes it easy to find rows that are different.
- This function returns a group of values.
- Will return a different value for most, but not all, changes to the row, and can be used to detect most row modifications.
- The CHECKSUM_AGG function returns a checksum of the values in a group.
- If one of the values in the expression list changes, the checksum of the list also generally changes. However, there is a small chance that the checksum will not change.
So, I figured that I could first find the BINARY_CHECKSUM of each row and then find the CHECKSUM_AGG of all the BINARY_CHECKSUMs. If the tables were different, most of the time (as noted in bold above) these values would be different.
There are two situations that I find this very useful for (I’ll post about these later):
- Static data – data that needs to be the same from one environment to another
- Data migration – verifying all the columns get migrated correctly.
Note – there are a few data types that BINARY_CHECKSUM does not support. They are text, ntext, image, cursor, xml, and noncomparable CLR user-defined types. In my experience, some of the time update dates and insert dates are not important as well as timestamp data types, so I usually ignore these columns.
So to investigate the tester’s claims, I ran the following on each database:
Select CHECKSUM_AGG(BINARY_CHECKSUM(Col1, Col2, Col3)) from Table1
The values were different, so I removed Col1 from the criteria and ran the following on each database and the values were the same.
Select CHECKSUM_AGG(BINARY_CHECKSUM(Col2, Col3)) from Table1
Therefore, Col2 and Col3 had the same values for both Col2 and Col3 **most likely** but Col1 definitely had different values. The tester was right.
I then removed the CHECKSUM_AGG to find and then compare the values of each row to find the ones that didn’t match.
Select Col1,
BINARY_CHECKSUM(Col1, Col2, Col3)
from Table1
Order by Col1
Here’s an example that shows that this method doesn’t always work – note that the same distinct values are in each table, but each row is not the same.
CREATE TABLE [dbo].[table1](
[col1] [int],
[col2] [varchar](200))
CREATE TABLE [dbo].[table2](
[col1] [int],
[col2] [varchar](200))
Insert table1 Values
(1, 'Row #1 in Table1, Row #2 in Table2'),
(2, 'Row #2 in Table1, Row #1 in Table2')
Insert table2 Values
(2, 'Row #1 in Table1, Row #2 in Table2'),
(1, 'Row #2 in Table1, Row #1 in Table2')
---These are equal
Select CHECKSUM_AGG(BINARY_CHECKSUM(col1, col2)) from table1
Select CHECKSUM_AGG(BINARY_CHECKSUM(col1, col2)) from table2
---These are NOT equal
Select BINARY_CHECKSUM(col1, col2) from table1
Select BINARY_CHECKSUM(col1, col2) from table2
Despite the small chance of a checksum being equal, this method makes it very easy to quickly identify data inconsistencies in different databases or environments. If you’re ever need to quickly find inconsistent data, give it a try.
So apparently I like scripts that create random data. I’ve written a couple of posts that create random numbers and random characters. This week I was working on a clean up job that would delete data that was over 50 days old. To test my clean up routine, I had to combine the random number script with the DATEADD function – here’s the basis for it:
Select Dateadd(D, (-1)* ROUND((RAND() * (100)),0), Getdate())
Breaking it down, the ROUND((RAND() * (100)),0) section randomly chooses an integer from 0 to 100, then multiplies that by –1 as we want to go back in time. Finally the DATEADD function adds the negative random integer number of days to today’s date.
Here’s an example that populates a test table with 1000 rows of data:
SET NOCOUNT ON
Go
Declare @RandomDates Table (DateTest datetime)
Declare @Counter int
Set @Counter = 0
While @Counter < 1000
Begin
Insert @RandomDates (DateTest)
Select Dateadd(D, (-1)* ROUND((RAND() * (100)),0), Getdate()) --Random Date 0 to 100 days old
Set @Counter = @Counter + 1
End
Select Datediff(d,DateTest, getdate()) as DaysOld,
Count(Datediff(d,DateTest, getdate())) as Count
from @RandomDates
Group by Datediff(d,DateTest, getdate())
Order by DaysOld Desc
I’m a runner as well as a DBA. I’ve got a marathon this weekend, so I started thinking about how SQL and running are similar.
- Small issues in your mechanics will show up later when performance is needed.
- You have your good days and your bad days.
- Sometimes you’ve got to grin and bear it and grind it out.
- Sometimes you’ve got to get up earlier than you want.
- You need goals and objectives to improve.
- Hard work is subjective to what you’re used to.
- You have to keep testing yourself to reach your highest potential.
- Sometimes you want friends to join you, sometimes you need some alone time.
- Someone is always better than you.
- Changing what you’re working on improves overall performance.
I’ve updated the script for Running Events to use the time data type for SQL2008.
Also here’s a little script I used to track my goal of running 200 miles in May. It gives a visual graph of actuals vs goal as well as percentage of month used.
Set nocount on
Declare @miles float
Declare @Goalmiles int
Declare @divisor int
--Change these based on your milage/goal
Set @miles = (8+10+11+16+12+10+13.5+22+11+11+11)
Set @Goalmiles = 200
Set @divisor = 2
Select Replicate('@', @Goalmiles/@divisor)
+ Char(13) + Char(10) +
Replicate('@', @miles/@divisor) + ' -- ' + convert(varchar(10), @miles/@Goalmiles) + '%'
+ Char(13) + Char(10) +
convert(varchar(10), @miles) + ' of ' + convert(varchar(10), @Goalmiles) as 'Progress'
--Find the percentage of the month gone
Select round(datepart(D, GETDATE())/convert
(float, datepart(D, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))), 4)
as '%ofMonth'
|
|
