Tag Archives: batch

Windows Internal Database Maintenance

“Windows Internal Database,”  is basically SQL Server with network access and the maintenance tools turned off.  You might want to do maintenance on whatever’s in there, anyway.  I use Windows Task Scheduler for that, at least until I move the database to one of our full SQL installs, but you might be stuck with the free stuff.  If so, or if you’re a perfectionist like me, here’s a maintenance script for WSUS (as an example) running on Windows Internal Database:

sqlcmd -E -S\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -i “C:\[path]\sseemaint.sql”
sqlcmd -E -S\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -i “C:\[path]\wsusbackfull.sql”

(Download.)

You can probably tell that’s pulling SQL query files and passing them to sqlcmd, the SQL command line utility.  (You can download sqlcmd here.)  Here’s the maintenance script, which does an integrity check and reindexes every database that isn’t tempdb or model:

Declare @DBname varchar(255),
@RowsToProcess int,
@CurrentRow int,
@SelectCol1 varchar(max),
@SQL nvarchar(max)

CREATE TABLE #maintenancetemp (RowID int not null primary key identity(1,1), dbname nvarchar(max) )
INSERT into #maintenancetemp (dbname) select name from sys.databases where name <> ‘tempdb’ and name <> ‘model’
SET @RowsToProcess=@@ROWCOUNT

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
SET @CurrentRow=@CurrentRow+1
SELECT
@SelectCol1=dbname
FROM #maintenancetemp
WHERE RowID=@CurrentRow
set @DBname = @SelectCol1
set @SQL=’DBCC CHECKDB(N”’ + @DBName+ ”’)  WITH NO_INFOMSGS’
EXEC SP_EXECUTESQL @SQL
set @SQL=’USE [‘+@DBname+’]; exec sp_MSforeachtable @command1=”print ”?” DBCC DBREINDEX (”?”, ” ”, 80)”; EXEC sp_updatestats’
EXEC SP_EXECUTESQL @SQL
END

drop table #maintenancetemp

(Download.)

And here’s the SUSDB backup script.  It’s a full back up of SUSDB, but you could, obviously, use it for any DB:

Declare @today nvarchar(50),
@SQL nvarchar(4000)

set @today = convert(nvarchar,GETDATE(),12)

set @SQL=’BACKUP DATABASE [SUSDB] TO  DISK = N”F:\SQL\SUSDB-‘+ @today +’.bak” WITH FORMAT, INIT,  NAME = N”SUSDB-Full Database Backup”, SKIP, NOREWIND, NOUNLOAD,  STATS = 10′
EXEC SP_EXECUTESQL @SQL

(Download.)

You’ll also want a separate script to back up the logs:

Declare @today nvarchar(50),
@SQL nvarchar(4000)

set @today = convert(nvarchar,GETDATE(),12) + ‘-‘ + replace(CONVERT(VARCHAR(26), GETDATE(), 108),’:’,”)

set @SQL=’BACKUP LOG [SUSDB] TO  DISK = N”F:\Transaction Logs\susdb-‘+ @today +’.bak” WITH FORMAT, INIT,  MEDIANAME = N”SUSDB-Transaction Log Backup”,  NAME = N”SUSDB-Transaction Log  Backup”, SKIP, NOREWIND, NOUNLOAD,  STATS = 10′
EXEC SP_EXECUTESQL @SQL

(Download.)

These spit out date or datetime labelled backups, so you can get rid of the old ones as your storage fills up.  You can schedule these in task scheduler to run daily, weekly, hourly, whatever your pain point is for what’s in Windows Internal Database.  Good luck!

Speaking of WSUS, you can migrate that from Windows Internal Database to a full SQL install using these instructions, and can move the content to a new drive using these instructions.

3 Comments

Filed under scripting

A Script for Migrating SQL Data (and Log) Files

Suppose that you’ve been asked to migrate 166 databases’ .mdf files from one drive to another.  How would you do that?

I’d generate a list based on the .mdf files (the databases are all named the same thing as the .mdf.  If this wasn’t the case, we’d have to query SQL for it–“select name from sys.databases”–and get the file name out of the database’s sys.database_files table) and do this:

@ECHO OFF

set controlfile=test.txt

SETLOCAL DISABLEDELAYEDEXPANSION
FOR /F %%L IN (%controlfile%%) DO (
SET “line=%%L”
SETLOCAL ENABLEDELAYEDEXPANSION
ECHO !line:.mdf=!
sqlcmd -E -S.\prod1 -Q”EXEC master.dbo.sp_detach_db @dbname = N’!line:.mdf=!'”
copy “C:\SQL Server Data\Prod1\Data\!line!” “D:\SQL Server Data\Prod1\Data”
sqlcmd -E -S.\prod1 -Q”CREATE DATABASE [!line:.mdf=!] ON ( FILENAME = N’D:\SQL Server Data\Prod1\Data\!line!’ ),( FILENAME = N’C:\SQL Server Data\Prod1\Log\!line:.mdf=!_log.ldf’ ) FOR ATTACH”
move “C:\SQL Server Data\Prod1\Data\!line!” “C:\SQL Server Data\Prod1\Data\moved\”
ENDLOCAL
)
ENDLOCAL

(Download.)

In this example, the logs were then moved, too.

@ECHO OFF

set controlfile=test.txt

SETLOCAL DISABLEDELAYEDEXPANSION
FOR /F %%L IN (%controlfile%%) DO (
SET “line=%%L”
SETLOCAL ENABLEDELAYEDEXPANSION
ECHO !line:.mdf=!
sqlcmd -E -S.\prod1 -Q”EXEC master.dbo.sp_detach_db @dbname = N’!line:.mdf=!'”
copy “C:\SQL Server Data\Prod1\Log\!line:.mdf=!_log.ldf” “F:\SQL Server Data\Prod1\Log\”
sqlcmd -E -S.\prod1 -Q”CREATE DATABASE [!line:.mdf=!] ON ( FILENAME = N’D:\SQL Server Data\Prod1\Data\!line!’ ),( FILENAME = N’F:\SQL Server Data\Prod1\Log\!line:.mdf=!_log.ldf’ ) FOR ATTACH”
move “C:\SQL Server Data\Prod1\Log\!line:.mdf=!_log.ldf” “C:\SQL Server Data\Prod1\Log\moved\”
ENDLOCAL
)
ENDLOCAL

(Download.)

You could always combine both actions in the same script, too.  There was a reason to move the files separately, but we could move them together and add a backup to the script:

@ECHO OFF

set controlfile=test.txt

SETLOCAL DISABLEDELAYEDEXPANSION
FOR /F %%L IN (%controlfile%%) DO (
SET “line=%%L”
SETLOCAL ENABLEDELAYEDEXPANSION
ECHO !line:.mdf=!
sqlcmd -E -S.\prod1 -Q”BACKUP DATABASE [!line:.mdf=!] TO DISK = N”F:\SQL backups\!line:.mdf=!.bak” WITH COPY_ONLY, WITH FORMAT, INIT, NAME = N”!line:.mdf=!-Full Database Backup”, SKIP, NOREWIND, NOUNLOAD, STATS = 10″
sqlcmd -E -S.\prod1 -Q”EXEC master.dbo.sp_detach_db @dbname = N’!line:.mdf=!'”
copy “C:\SQL Server Data\Prod1\Data\!line!” “D:\SQL Server Data\Prod1\Data”
copy “C:\SQL Server Data\Prod1\Log\!line:.mdf=!_log.ldf” “F:\SQL Server Data\Prod1\Log\”
sqlcmd -E -S.\prod1 -Q”CREATE DATABASE [!line:.mdf=!] ON ( FILENAME = N’D:\SQL Server Data\Prod1\Data\!line!’ ),( FILENAME = N’F:\SQL Server Data\Prod1\Log\!line:.mdf=!_log.ldf’ ) FOR ATTACH”
move “C:\SQL Server Data\Prod1\Data\!line!” “C:\SQL Server Data\Prod1\Data\moved\”
move “C:\SQL Server Data\Prod1\Log\!line:.mdf=!_log.ldf” “C:\SQL Server Data\Prod1\Log\moved\”
ENDLOCAL
)
ENDLOCAL

(Download.)

How would you do it?

Comments Off on A Script for Migrating SQL Data (and Log) Files

Filed under scripting

A one-time use script.

This isn’t the entire thing, it’s bits and pieces, but it’s enough for you to bend, fold, spindle, and mutilate for your own purposes.

I was given 70,000+ tab delimited lines that were output dumped from a program and asked to find out what version of a specific piece of software was running on the servers in position 9 of those lines.  I was also told that there were duplicates and that some of them were Unix or Linux and I was only responsible for the Windows servers. I used batch because it was there and available.

First of all, batch choked on the tabs so I replaced all tabs with spaces, then picked out the server names and sorted them:

SetLocal EnableDelayedExpansion

for /f “usebackq delims=” %%g in (%controlfile%) do (
set content=%%g
echo !content:  = ! >> process.txt
)

for /f “tokens=9 delims= ” %%A in (process.txt) do (
echo %%A >> servers.txt
)

sort < servers.txt > sorted.txt

I then had 70,000 server names with a lot of duplicates in them.  Next up:  Unduplicating them.

@echo on > unduped.txt

for /f “tokens=* delims= ” %%a in (sorted.txt) do (
find “%%a” < unduped.txt > nul
if errorlevel 1 echo %%a >> unduped.txt
)

That gave me 110 (!) server names.  Next, to see which ones are behind a firewall or otherwise unreachable:

@echo on > unreachable.txt

for /f “usebackq delims=” %%g in (unduped.txt) do (
for /f “tokens=11 delims= ” %%A in (‘ping %%g ^| FIND “loss)”‘) do (
if “%%A” NEQ “(0%%” (
echo %%g >> unreachable.txt
)
)
)

for /f “tokens=* delims= ” %%a in (unduped.txt) do (
find “%%a” < unreachable.txt > nul
if errorlevel 1 echo %%a >> checklist.txt
)

Okay.  Now to try to pick out the Windows machines, and make sure I have the proper permissions for the task:

for /f “tokens=* delims= ” %%s in (checklist.txt) do (
sc \\%%s query workstation | findstr /c:”unavailable”
if errorlevel 1 echo %%s >> win.txt
)

for /f “tokens=* delims= ” %%a in (checklist.txt) do (
find “%%a” < win.txt > nul
if errorlevel 1 echo %%a >> nonwin.txt
)

for /f “tokens=* delims= ” %%s in (win.txt) do (
sc \\%%s query “Workstation” | findstr /c:”Access is denied”
if errorlevel 1 echo %%s >> access.txt
)

for /f “tokens=* delims= ” %%a in (win.txt) do (
find “%%a” < access.txt > nul
if errorlevel 1 echo %%a >> accessdenied.txt
)

And, finally, to check for the actual service.  I needed to find a specific service name related to a version, and, for some reason, checking for fails was more reliable than checking for the existence of the service:

for /f “tokens=* delims= ” %%s in (access.txt) do (
sc \\%%s query “Your Super Fab Service version 8.2″ | findstr /c:”RUNNING”
if errorlevel 1 echo %%s >> notv82.txt
)

It was actually a fun project, and made a day when I was worried about a family member in the hospital go by a lot faster.  I also saved it in case I ever needed it again, but I haven’t.  Yet.

Comments Off on A one-time use script.

Filed under scripting

Nagios Plugin – SQL Job Status

I checked out Nagios Exchange, and didn’t see anything that checked the status of a job and used Windows/AD credentials/trusted connection.  So I wrote this.

It’s intended to run as an NRPE script, and doesn’t require anything that doesn’t come with Windows and SQL Server.  There’s a stored procedure and a batch file, both of which can be modified to suit your purposes.

You also need to enable nrpe and external scripts in nsc.ini, and define the checks in the [External Scripts] section.  Run the script with the job name as an argument (“scripts\last_sql_job_run_status.cmd some_job_name”).  An optional second argument is an integer representing how many days ago the job ran before Something Is Wrong [TM] (“scripts\last_sql_job_run_status.cmd some_job_name 3”).  If no time period is specified, it assumes a week.  The plugin returns critical if the job failed and warning if it succeeded but is late.

For trusted connections, you can run nsclient++ as a service account and give that account read access to sysjobs and sysjobactivity in msdb and execute on the procedure itself, although it should work out of the box for a default nsclient++ install checking localhost.  You can also use sql server authentication if you edit the line invoking sqlcmd accordingly.

Enjoy.

Comments Off on Nagios Plugin – SQL Job Status

Filed under monitoring, scripting