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.