Monthly Archives: August 2012

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

Three things make a post?

  1. I’ll be reading at the Broad Universe Rapid Fire Reading at Dragoncon on Sunday, September 2, at 5:30pm in Greenbriar.
  2. Woe, my current story is 23,000+ words long and conflict-poor.  It needs some serious, serious editing.  But I was not writing at $previous_job, so this is an improvement.  Right?
  3. I’m running 3-4 days a week and about to start running outside.  I’m also in the market for a 5K, if you know of one.

Comments Off on Three things make a post?

Filed under cons

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