Generate a Change Script to Move System Databases

I set this up to be modular and reusable, because I like reusing things.  This doesn’t actually move your system databases.  It just outputs a change script and a revert script.  You still have to run the generated script, then log on to your server and stop the services and move the files yourself.  In my example, I’m moving TempDB from C (ew) onto better storage.
declare @dbname sysname,
@oldpath varchar(255),
@newpath varchar(255)

set @dbname='tempdb'
set @oldpath='C:\Program Files\Microsoft SQL Server\MSSQLSERVER\MSSQL\DATA' --no trailing slash
set @newpath='H:\HappyStorage' --no trailing slash

/*  Generates your change script */
SELECT 'alter database ' + @dbname + ' modify file (name=' + name + ', filename = ''' + replace(physical_name,@oldpath,@newpath) + ''');'
FROM sys.master_files
WHERE database_id = DB_ID(@dbname);

/* Generates a revert script */
SELECT 'alter database ' + @dbname + ' modify file (name=' + name + ', filename = ''' + physical_name + ''');'
FROM sys.master_files
WHERE database_id = DB_ID(@dbname);

/* Tells you where the files are now */
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(@dbname);
GO

Sample output would  include, in the top window:

alter database tempdb modify file (name=tempdev, filename = 'H:\HappyStorage\Data\tempdb.mdf');
alter database tempdb modify file (name=templog, filename = 'H:\HappyStorage\Log\templog.ldf');

Copy and paste that into a new window, and run it if you’re sure it’s right.

You can, of course, use this for MSDB or any other database you want to move.

Leave a Comment

Filed under Uncategorized

De-duplicating files in batch

So, I have a drive full of files.  Sometimes, I get a new one–okay, sometimes I get over 1000 new ones–and want to keep the latest and pitch the duplicates without throwing away old files that don’t have a duplicate.  These files are of the form DATABASENAME_Attach_number_7777mmdd_hhmmss.sqb, but they could be anything, really.

(Why batch?  Because I had most of it lying around already and I’m lazy.)

set destination="i:\directory\with\old\files"
set source="f:\directory\with\new\files"
cd %destination%
mkdir old
mkdir new
move *.sqb old
robocopy %source% %destination%\new /mt:2 /mov
del early.txt
del files.txt
del names.txt
for %%F in ("old\*.sqb") do echo %%F >> early.txt
for /f "tokens=2 delims=\" %%A in (early.txt) do (
echo %%A >> files.txt
)
for /f "tokens=1-3 delims=_" %%A in (files.txt) do (
echo %%A_%%B_%%C >> names.txt
)
for /f %%F in (names.txt) do @if exist new\%%F* del old\%%F_FULL*
del early.txt
del files.txt
move names.txt dedupe
cd old
move * ..
cd ..\new
move * ..
cd ..
rd new
rd old

Leave a Comment

Filed under scripting

Pull the restore chain from MSDB

What files do I need to restore this database to a point in time?

You might know this off the top of your head, especially if you set up the backups or if you don’t have a lot of backups, but just in case you’re panicking, ask MSDB.  MSDB knows all… unless that data has been purged.  (Sorry.)

declare @dbname varchar(80),
@lastfull datetime,
@lastdiff datetime,
@fullback varchar(1024),
@diffback varchar(1024),
@RowsToProcess int,
@CurrentRow int,
@restorediff varchar(max),
@logback varchar(1024),
@restoreday datetime

set @dbname = ‘YourDB’
set @restoreday = ‘1/1/2016 8:05am’ –Make sure the time zone is your server’s time zone.

select @lastfull = max(backup_finish_date)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type = ‘D’
WHERE d.database_id NOT IN (2, 3) and d.name=@dbname
and description like ‘Backup on%’
and backup_finish_date < @restoreday

SELECT    @fullback = m.physical_device_name
FROM         msdb.dbo.backupmediafamily AS m INNER JOIN
msdb.dbo.backupset AS b ON m.media_set_id = b.media_set_id
and b.type=’D’ and b.database_name=@dbname
AND b.backup_finish_date=@lastfull

print @fullback

SELECT @lastdiff = b.backup_finish_date FROM msdb.dbo.backupmediafamily AS m INNER JOIN msdb.dbo.backupset AS b ON m.media_set_id = b.media_set_id and b.type=’I’ and b.database_name=@dbname AND b.backup_finish_date>@lastfull AND b.backup_finish_date < @restoreday

If @@ROWCOUNT = 0
begin
set @lastdiff = @lastfull
end

select @diffback = m.physical_device_name FROM msdb.dbo.backupmediafamily AS m INNER JOIN msdb.dbo.backupset AS b ON m.media_set_id = b.media_set_id and b.type=’I’ and b.database_name=@dbname AND b.backup_finish_date=@lastdiff

print @diffback

CREATE TABLE #logs (RowID int not null primary key identity(1,1), logback nvarchar(255),finishdate datetime)
insert into #logs SELECT distinct m.physical_device_name, b.backup_finish_date FROM msdb.dbo.backupmediafamily AS m INNER JOIN msdb.dbo.backupset AS b ON m.media_set_id = b.media_set_id and b.type=’L’ and b.database_name=@dbname AND b.backup_finish_date>@lastdiff AND b.backup_finish_date<@restoreday order by b.backup_finish_date

SELECT @RowsToProcess=COUNT(logback) from #logs

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
SET @CurrentRow=@CurrentRow+1
SELECT
@logback=logback
FROM #logs
WHERE RowID=@CurrentRow
print @logback
END

drop table #logs

Leave a Comment

Filed under Uncategorized

Windows Internal Database/SQL Express backups and maintenance

Google informs me that my post on Windows Internal Database Maintenance is popular.  It’s not what I’m currently using, however.

I’ve switched over to Ola Hallengren’s scripts.  If you download and run the installer script, it creates a series of agent jobs, which, well.  Neither Windows Internal Database nor SQL Express has SQL Server Agent.  However, the jobs run as cmdexec scripts that look like

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'f:\sql_backups', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 672, @CheckSum = 'Y', @LogToTable = 'Y'" -b

It’s easy to run these through Task Scheduler.  You’ll want to replace “$(ESCAPE_SQUOTE(SRVR))” with your actual server.

(The connection string for the version of Windows Internal Database based on 2008 is \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query.  The connection string for the version of Windows Internal Database based on 2012 is \\.\pipe\MICROSOFT##WID\tsql\query.  SQLExpress on your current host is .\SQLExpress by default.)

Continue reading

Leave a Comment

Filed under scripting, sql

Nagios Checks for Dell OpenManage Disk Health Written in PowerShell

I wrote these in PowerShell to run with nrpe/nsclient.  They query Dell OpenManage’s command line and return a Nagios-readable result.

(There are plugins available on Nagios Exchange, but they all seemed to be… more than I wanted.  I just wanted to know, “Is my RAID okay?”  Because whenever I’ve had an Event, I like to monitor for that event happening again.)

This checks the Physical Disks in the array.  If one or more disks reports anything other than OK, it alerts:

$status = 0; omreport storage pdisk controller=0 | Where-Object {$_ -match "^status"} | %{if($_ -notlike "*OK*"){$status=2}}

If ($status -eq 0) {
Write-Host "OK:  Physical Disks report OK"
} else {
Write-Host "CRITICAL:  Check OpenManage"
}
exit $status

You might have to edit the scripts to check Virtual Disk health.  It could probably be made more elegant, but it suits my purposes.

This script checks the health of my C drive (vdisk 0):

omreport storage vdisk controller=0 vdisk=0 | ?{$_ -match "^status"} | %{$status=0}{if($_ -notlike "*OK*"){$status=2}}

If ($status -eq 0) {
Write-Host "OK:  Virtual Disk (OS) reports OK"
} else {
Write-Host "CRITICAL:  Check OpenManage"
}
exit $status

This script checks the health of my data drive (E, vdisk 1):

omreport storage vdisk controller=0 vdisk=1 | ?{$_ -match "^status"} | %{$status=0}{if($_ -notlike "*OK*"){$status=2}}

If ($status -eq 0) {
Write-Host "OK:  Virtual Disk (data) reports OK"
} else {
Write-Host "CRITICAL:  Check OpenManage"
}
exit $status

Running these involves adding lines like this to the external script section of nsclient.ini or equivalent:

check_physicaldisk = cmd /c echo scripts\\pdiskcheck.ps1; exit($lastexitcode) | powershell.exe -command -

check_virtualdisk = cmd /c echo scripts\\vdiskcheck.ps1; exit($lastexitcode) | powershell.exe -command -

And lines like this to command.cfg in Nagios:

define command {
command_name    check_physicaldisk
command_line    /usr/lib/nagios/plugins/check_nrpe -H $HOSTADDRESS$ -c check_physicaldisk
}
define command {
command_name    check_CRaid
command_line    /usr/lib/nagios/plugins/check_nrpe -H $HOSTADDRESS$ -c check_virtualdisk
}

Leave a Comment

Filed under Uncategorized

Katherine’s Excellent Log-Shipping Adventure

Or, Log-Shipping over 1200 databases automagically.

Picture1

Continue reading

Leave a Comment

Filed under monitoring, powershell, sql

Log-Shipping SQL Express with PowerShell

Someone on Server Fault asked a question about which versions of SQL Server support log shipping.  He uses Express.

Log shipping uses SQL Agent, and Express doesn’t come with SQL Agent, but you can still manually log ship with PowerShell and Task Scheduler.  Use the log backup script of your choice (I like Ola Hallengren’s, which can also be used with Task Scheduler), either back up to a share or copy the files to a share (perhaps with robocopy /mir?), and then run this or something like this (for a read-only copy):

$restoreserver=”YourServer”
$logbackpath=”C:\path\to\logs”
$standby=”C:\path\to\ROLLBACK_UNDO_YourDB.BAK”
$dbname=”YourDB”
$dbsearch = “SomeSearchString*”

$logbacks = Get-ChildItem $logbackpath | Where-Object {$_.Name -like $dbsearch} | Sort-Object LastAccessTime

foreach ($logback in $logbacks)
{
$query = “RESTORE LOG [” + $dbname + “] FROM  DISK = ‘” + $logback.FullName + “‘ WITH  FILE = 1,  STANDBY = N'” + $standby + “‘,  NOUNLOAD,  STATS = 10”
sqlcmd -E -S $restoreserver -Q $query
}

Or this (for an offline warm standby):

$restoreserver=”YourServer”
$logbackpath=”C:\path\to\logs”
$dbname=”YourDB”
$dbsearch = “SomeSearchString*”

$logbacks = Get-ChildItem $logbackpath | Where-Object {$_.Name -like $dbsearch} | Sort-Object LastAccessTime

foreach ($logback in $logbacks)
{
$query = “RESTORE LOG [” + $dbname + “] FROM  DISK = ‘” + $logback.FullName + “‘ WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10”
sqlcmd -E -S $restoreserver -Q $query
}

(If you’re copying and pasting, don’t forget to fix the “smart quotes,” which aren’t smart.)

Leave a Comment

Filed under powershell

Peachtree Road Race

For those of you not in the know, the Peachtree Road Race is a ginormous (60,000 runners) 10K held every July 4th in Atlanta. This was my third Peachtree.

Anyway, this is what I did for the 4th of July.

326343_201415781_Medium 326343_201188324_Medium

Leave a Comment

Filed under running

Squee!!!

As of yesterday, I’m a Microsoft MVP in Enterprise Security. 😀

Leave a Comment

Filed under Uncategorized

Rename a lot of databases with PowerShell

I was given the task of renaming a bunch of databases named something like “name,” “name_1,” “name_2,” etc., to “nameold,” “nameold_1,” “nameold_2,” etc.  Our convention is for the database name to match the file names.  So…

$controlfile=”path\to\listofdatabases.txt”
$server=”servername\instance”
$mdfpath=”c:\path\to\Data”
$ldfpath=”c:\path\to\Log”
$dbnameappend=”old”
$dbnameappendarchive = $dbnameappend + “_”

foreach ($dbname in get-content $controlfile)
{
$newdbname = $dbname + $dbnameappend
if ($dbname -match ‘_[0-9]’) {$newdbname = $dbname -replace ‘_’, $dbnameappendarchive}
Write-Host $newdbname

$mdf=$dbname + “.mdf”
$ldf=$dbname + “_log.ldf”
$newmdf=$newdbname + “.mdf”
$newldf=$newdbname + “_log.ldf”

$query = “ALTER DATABASE ” + $dbname + ” SET SINGLE_USER WITH ROLLBACK IMMEDIATE;”
sqlcmd -E -S $server -Q $query

$query = “ALTER DATABASE ” + $dbname + ” MODIFY NAME = ” + $newdbname
sqlcmd -E -S $server -Q $query

$query = “ALTER DATABASE ” + $newdbname + ” SET offline”
sqlcmd -E -S $server -Q $query

$query = “ALTER DATABASE ” + $newdbname + ” MODIFY FILE (NAME = ‘[namingconvention]_log’, FILENAME = ‘” + $ldfpath + “\” + $newldf + “‘)”
sqlcmd -E -S $server -Q $query

$query = “ALTER DATABASE ” + $newdbname + ” MODIFY FILE (NAME = ‘[namingconvention]’, FILENAME = ‘” + $mdfpath + “\” + $newmdf + “‘)”
sqlcmd -E -S $server -Q $query

Move $ldfpath\$ldf $ldfpath\$newldf -Force
Move $mdfpath\$mdf $mdfpath\$newmdf -Force

$query = “ALTER DATABASE ” + $newdbname + ” SET online”
sqlcmd -E -S $server -Q $query

$query = “ALTER DATABASE ” + $newdbname + ” SET MULTI_USER;”
sqlcmd -E -S $server -Q $query

}

This was so fast that I decided to share the joy. 🙂

(I plan to use this script if I ever need to bring my log-shipped databases online, hence the append.  I might need to do thing_oldprod, stuff_oldprod, whatsit_oldprod, etc.)

Leave a Comment

Filed under powershell, scripting