Automatically restore a list of databases

I recently had a situation at my client where we were troubleshooting a SQL server hanging and one candidate was a faulty RAID array or SCSI controller. One option to explore was to restore all the databases to another server and run an Index rebuild and Update statistics jobs against these restored databases. This was because these jobs caused the server to hang. So we had 20 odd databases to restore. The same requirement, that of restoring a list of databases to another server was also required by another DBA team at another client, so my quest for automation would be used by my team members at another client.

The databases in both scenario's are backed up by a maintenance plan. As such there is a backup folder with a subfolder for each database, with the sub folder name being the name of the database being restored. This is quite a common scenario. So I could copy the whole backup folder and then iterate through the sub folders and restore each database contained in each backup file. 2 minor challenges, the restore location may be, and in both cases, is different, the 2nd challenge is that each database may have more than one mdf and ldf. My script caters for both of these scenarios.

The strategy is to write a function (encapsulated single unit of work) that restores one database from one backup file. It takes the backupfile name and the database name as input parameters. I've hard coded the restore location in the function, but this could be passed in as a 3rd parameter.

Once I have this function working, I can iterate through the folder structure and for each sub directory, grab the folder name, which would be the database name, and restore the database in the backup file in that sub folder.

Herewith the code:

FUNCTION Restore-Db ($BackupFile, $DbName)
{
$RestP = "K:\MSSQL\MSSQL10.MSSQLSERVER\MSSQL\DATA"
$moveStr = ""
$q = "RESTORE FILELISTONLY FROM DISK = "+ $BackupFile
$b = Invoke-Sqlcmd -Query $q
$ln = $b | select logicalName, PhysicalName
foreach ($lnn in $ln) {$e = $lnn.PhysicalName.LastIndexOf("\")+1;$l =$lnn.PhysicalName.length; $ll = $l-$e; $phys = $lnn.PhysicalName.Substring($e,$ll); $moveStr = $moveStr+"MOVE '"+ $lnn.LogicalName + "' TO '"+$RestP+"\"+$phys+"', "}
$RestStr = "Restore Database "+$DbName+" FROM DISK = "+$BackupFile+" WITH "+$moveStr+ " STATS = 5"
Invoke-Sqlcmd -Query $RestStr -querytimeout 3600
}

$BackupsFiles = "F:\"
$dirs = dir $BackupsFiles  | Where {$_.psIsContainer -eq $true}
$dbs = $dirs | select name, fullname

foreach ($dbx in $dbs) {$BackupFile = gci -path $dbx.fullname -include *.bak -recurse; $DbName = $dbx.name; write-host "Restoring database $DbName from file $BackupFile";$BackupFile = "'"+$BackupFile+"'"; Restore-Db $BackupFile $DbName}

One point to take note of, the code does not cater for cases where there is more than one backup file per sub folder. If this is the case then you'll have to edit the function accordingly. Another point is I've put in a -querytimeout value of 3600, or 1 hour. If a very big database restore takes longer than this, adjust this value accordingly.

Its a beautiful thing when you can fire off a script and watch a list of 30 databases being restored, automatically, while you do other stuff. :-)

Check that all databases on all servers have been backed up

At the client that I work at, there are over 1500 databases on more than 200 SQL servers, ranging from SQL 2000 to 2008. For me to have a good nights sleep, I want to know that each and every database is being and has been backed up. I could check each databases properties, I could go through 200 emails, and other such tedious methods?! Heck no, read the title of this Blog!!!

Prior to SQL 2008 and the Powershell provider it ships with (sqlps.exe) you could either write your own cmdlets or using SQLPSX from Codeplex, thanks Chad. The beauty with Powershell and SMO and .Net is that a database is an object and the lastbackupdate is a property of that object. Grab the databases collection of a SQL server and for each database get the last backup date. Using T-SQL you'd join a few system tables and write some code. Thats for 1 server, for multiple servers you could use any one of a number of methods to get this information. All this is quite a bit more tedious than the PS approach.

The high level PS approach is I have a list of SQL servers, for each server go to the databases collection and return the database name and the last backup date. That's as simple as it gets and the code is not much more complicated. Well sort of.

To automate the above I can use the SQL Agent on my DBA Management server that has SQL 2008 client tools installed. I can create a SQL Agent Job that runs a Powershell step. How cool is that?!! So in the command window I type vanilla PS code. Whoop!! Thing is, and this is what to me a while to figure out, the PS shell that SQL agent runs is the vanilla shell, not the sqlps shell with the SQL snapins. So to get around this I created a profile file that applies to the native powershell shell that is used by all users.

I create the file and add the two entries using powershell:

ni C:\Windows\system32\WindowsPowerShell\v1.0\Microsoft.PowerShell_profile.ps1 -type "file"
ac -path C:\Windows\system32\WindowsPowerShell\v1.0\Microsoft.PowerShell_profile.ps1 -value "Add-PSSnapin SqlServerCmdletSnapin100"
ac -path C:\Windows\system32\WindowsPowerShell\v1.0\Microsoft.PowerShell_profile.ps1 -value "Add-PSSnapin SqlServerProviderSnapin100"

The great thing now is that, besides from SQL Agent, I can browse SQL like a directory. What I do from the SQL Agent Job is just that. The code:

$svl = 'SQLsvr1', 'SQL-Dev01', 'SQL2k-03'
foreach ($s in $svl) {gci sqlserver:sql\$s\default\databases | select parent, name, lastbackupdate| where {$_.lastbackupdate -lt $(Get-Date).AddDays(-7)}}

Thats it! The above queries my server list ($svl) and gets the properties (gci or get-childitme) of each database in the databases collection for each server. The specific property is the LastBackupDate and where its older than 7 days ago.

Work smart, sleep easy. ;-)

Viewing SQL Agent Job History and Duration visually using floating bars

Viewing SQL Agent Job History and Duration visually using floating bars. The title only tells half the story. What this article will describe is this and then applying this view across a number of inter-related servers. The gem though, is the last bit, reverse engineering the Job schedule based on the new found view that was previously not visible. So you may see that there is a peak period between 4am and 7am where a number of jobs are running and some jobs in this period may be moved earlier or later. I can click on the job and change the schedule, all from reporting services. ;-).

The intro kinda gives you a good idea of the contents of this blog. Basically, when viewing performance and what happens when on which server an important place to look is at the SQL Agent and to see what jobs are running, what they do, how long they run for and if they could be contributing to performance issues. This is particularly relevant when multiple jobs doing different tasks are run on a number of servers.

So first we need a T-SQL Query that shows us the job history and the job duration. This is found in the msdb database in the sysjobhistory table and the sysjobs table. I could use the sp_help_jobhistory system stored procedure to return most, but not all the information that I need. The query I've written returns exactly the information that I need to get the required view in Excel. Prior to the actual query I need 2 user defined functions, these are to format the run_duration and run_date columns in the sysjobhistory tables into a DATETIME Value.

Herewith the two ufn's:

USE MSDB
GO

CREATE function ufn_Run_time (@Run_Date INT, @Run_time INT)
RETURNS VARCHAR(30)
AS
BEGIN
DECLARE @TL INT
DECLARE @T VARCHAR(30)
DECLARE @RTC VARCHAR(10)
DECLARE @DS VARCHAR(15)

SET @TL = LEN(@Run_time)
SET @RTC = CAST(@Run_time AS VARCHAR(10))

SET @DS = CAST(@Run_Date AS VARCHAR(15))
SET @DS = SUBSTRING(@DS, 1, 4)+'/'+SUBSTRING(@DS, 5, 2)+'/'+SUBSTRING(@DS, 7, 2)

IF (@TL = 5)
 BEGIN
 SET @T = '0'+SUBSTRING(@RTC,1,1)+':'+SUBSTRING(@RTC,2,2)+':'+SUBSTRING(@RTC,4,2)
 END
ELSE
 IF (@TL = 1)
 BEGIN
 SET @T = '00:00:0'+@RTC
 END
ELSE
 IF (@TL = 2)
 BEGIN
 SET @T = '00:00:'+@RTC
 END
ELSE
 IF (@TL = 3)
 BEGIN
 SET @T = '00:0'+SUBSTRING(@RTC,1,1)+':'+SUBSTRING(@RTC,2,2)
 END
ELSE
 IF (@TL = 4)
 BEGIN
 SET @T = '00:'+SUBSTRING(@RTC,1,2)+':'+SUBSTRING(@RTC,3,2)
 END
ELSE
 SET @T = SUBSTRING(@RTC,1,2)+':'+SUBSTRING(@RTC,3,2)+':'+SUBSTRING(@RTC,5,2)

SET @T = @DS + ' ' + @T
RETURN(@T)
END


--select (dbo.ufn_Run_time(20090815, 1234))

USE MSDB
GO

CREATE function ufn_Run_Duration (@Run_time INT)
RETURNS VARCHAR(30)
AS
BEGIN
DECLARE @TL INT
DECLARE @T VARCHAR(30)
DECLARE @RTC VARCHAR(10)
DECLARE @DS VARCHAR(30)
SET @TL = LEN(@Run_time)

SET @RTC = CAST(@Run_time AS VARCHAR(10))


SET @DS = '19000101'
SET @DS = SUBSTRING(@DS, 1, 4)+'/'+SUBSTRING(@DS, 5, 2)+'/'+SUBSTRING(@DS, 7, 2)

IF (@TL = 5)
 BEGIN
 SET @T = '0'+SUBSTRING(@RTC,1,1)+':'+SUBSTRING(@RTC,2,2)+':'+SUBSTRING(@RTC,4,2)
 END
ELSE
 IF (@TL = 1)
 BEGIN
 SET @T = '00:00:0'+@RTC
 END
ELSE
 IF (@TL = 2)
 BEGIN
 SET @T = '00:00:'+@RTC
 END
ELSE
 IF (@TL = 3)
 BEGIN
 SET @T = '00:0'+SUBSTRING(@RTC,1,1)+':'+SUBSTRING(@RTC,2,2)
 END
ELSE
 IF (@TL = 4)
 BEGIN
 SET @T = '00:'+SUBSTRING(@RTC,1,2)+':'+SUBSTRING(@RTC,3,2)
 END
ELSE
 SET @T = SUBSTRING(@RTC,1,2)+':'+SUBSTRING(@RTC,3,2)+':'+SUBSTRING(@RTC,5,2)

--SET @T = @DS + ' ' + @T
RETURN(@T)
END


--select dbo.ufn_Run_Duration(1234)

Right, so now for the T-SQL query:

select jh.Server + ' - ' + j.Name AS ServerJob,
CONVERT(VARCHAR(30),CAST(dbo.ufn_Run_time(Run_Date, Run_Time) AS DATETIME), 120) AS StartTime,
CONVERT(VARCHAR(30), CAST(dbo.ufn_Run_time(Run_Date, Run_Time) AS DATETIME) + CAST(dbo.ufn_Run_Duration(Run_Duration) AS DATETIME), 120) AS EndTime
from sysjobhistory jh inner join sysjobs j
on jh.job_id = j.job_id
where run_date = '20100308' AND Name NOT LIKE '%Biz%' AND Name NOT LIKE 'ASP%' AND Name NOT LIKE '%Mail%' 
AND Name NOT LIKE 'Update DbIo' AND Name NOT LIKE '%distribution' AND Name NOT LIKE '%Capture DB%' 
AND Name NOT LIKE '%Replication%' AND Name NOT LIKE '%Mobile - Process%' AND NAME NOT LIKE 'MSCRM_CONFIG.HardDelete'
and step_name = '(Job outcome)'
--AND Run_Duration > 30

This shows me the Server and Job name and the StartTime and EndTime of the Job. This is what I require in Excel to show floating bars, across a time scale. And in SQL 2008 Reporting Services, which has Dundas chart controls built in, you can do it to! :-). So the above query is the Dataset query.

I then create a range chart with the floating bars:




The Series data is configured as the two date fields, see below image:



I drop the Server - Job field in as the category field:



If everything is set up correctly, when you preview the report you should have a view of something you could never visualize so succinctly before, all your SQL Agent jobs and their durations across a time scale. Son, its a beautiful thing.... :-).




Now for the final bit, I'm gonna write some pseudo code. ;-). Basically I want to be able to click on a Job bar and be able to change its start time. So I'll need a second report that does the work of changing the schedule. A report that changes code?! Yip, it is possible. :-). This 2nd report takes the job name as an input parameter. On the 1st report I set a click action to navigate to the second report and populates the job name parameter. The only other input needed is the new start time, in the format of hhmmss, i.e. 031500 for a start time of 03:15am. I'll post the stored procedure code below to actually affect the schedule change. The report bit you'll have to figure out. If you don't come right, I can email you the rdl. ;-).

Code to change job schedule from Reporting Services:

ALTER PROC usp_Change_JobStartTime @JobName VARCHAR(255), @NewTime VARCHAR(50)
AS
BEGIN
DECLARE @ScheduleID INT
DECLARE @ReturnString VARCHAR(500)
DECLARE @ReturnTime VARCHAR(50)
--SET @JobName = 'Toets32'
--SET @NewTime = '122920'

SET @ScheduleID = (SELECT s.schedule_id FROM sysjobschedules s INNER JOIN sysjobs j
on s.job_id = j.job_id WHERE  j.Name = @JobName)

exec sp_update_schedule @schedule_id = @ScheduleID, @active_start_time = @NewTime

SET @ReturnTime = (select active_start_time from sysschedules WHERE schedule_id = @ScheduleID)
SET @ReturnString = 'Job: ' + @JobName + ' Start time has been changed to ' + (dbo.ufn_Run_Duration(@ReturnTime))
SELECT @ReturnString 
END

usp_Change_JobStartTime 'Toets32', '124935'

Rarring files, Maintenance Plans and powershell

Hi Peeps. Been a while. Happy 2010 and all that.

Right, automation, powershell and maintenance plans. All meant to make our lives easier. Throw in some WinRar (licensed of course ;-)) and maybe some multi server administration (SQL) and you have a seriously powerful mix of stuffs!

Ok, so, first the problem statement: I want to be able to backup up all my SQL databases and then subsequent to that, compress all these backups and remove rar files older than say, 2 days.

Now the solution overview: Create a maintenance plan in SQL Management Studio (SSMS). The 1st task would be a Backup database task to back up all your databases. Then the 2nd maint plan task would be a T-SQL task. This would code would start a SQL Agent job that would run a Powershell script. This PS script would trawl through the SQL backup directory and rar all the backup files. The 3rd maint plan task would also kick off a SQL Agent Job to remove rar files older than say 2 days. Groovy.

This all seems simple enough. The funky bit in the code (below) is to tell Powershell to wait for just enough time for Winrar to run to compress the current file. Thats because winrar is running outside of the PS process. I've divised a very simple strategy here. Take the size of the current file being rarred, divide it by a number that will return an integer value that equates to a wait time just long enough for the rar process to complete. This number will depend on your server hardware, so you may want to tweak it a bit. It works, beeee-yute-ifully!

Here is the PS script:

$WinRAR = 'C:\Program Files\WinRAR\Winrar.exe'
$path = 'D:\MSSQL\MSSQL\BACKUP\'
sl $path
$FullBackup = gci $path -recurse -include *.bak
foreach ($File in $FullBackup) {$FileName = $File.Fullname
if ($FileName -ne $Null) {#echo "Compressing $FileName
&$WinRar a -v50000 m "$FileName.rar" $FileName; $ss = $File.Length/2500000; write-host "Rarring $filename with wait time of $ss seconds"; start-sleep $ss}
}

The above basically gets (Get-ChildItem gci) all the backup files (*.bak) in a specified path ($path). Preceeding this it creates a Winrar object ($Winrar). Now we create a foreach loop to rar each file in our $FullBackup list of files. The funky bit is to take the File size ($File.Length) and divide it by 2500000. This gives us our wait time (start-sleep $ss).

Putting it all together:

I save the above PS script on the SQL Server. I then create a SQL Agent Job that runs this script. I don't set a schedule because it will be called by the Maintenance Plan Job. I then create my Maintenance Plan with the 1st step being my backup database task and my 2nd step being a custom T-SQL Task that executes the below T-SQL code to start a SQL Agent Job. T-SQL Code:

exec msdb..sp_start_job 'Rarfiles'

The last step is to create a 2nd SQL Agent Job that calls a PS script that deletes rar files in the SQL Backup directory that are older than, say, 2 days. This is a one liner:

gci '\\devss165\D$\MSSQL\MSSQL.1\MSSQL\Backup\' -recurse -include *.bak | where {($_.CreationTime -le $(Get-Date).AddDays(-2))} | ri -force

This 2nd SQL Agent will be called from the 3rd task in the Maintenance plan, also a custom T-SQL task, that would run a sp_start_job as well.

Now if you roll this out using a multi server maintenance plan to all your SQL servers you could just save your corporate a heap of disk space and money. :-).