Category Archives: scripting

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

Comments Off on De-duplicating files in batch

Filed under scripting

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

Comments Off on Windows Internal Database/SQL Express backups and maintenance

Filed under scripting, sql

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…

$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”

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.)

Comments Off on Rename a lot of databases with PowerShell

Filed under powershell, scripting

They’re using HOW MUCH space?

This assumes a convention where databases are named after the responsible party with an underscore and a version number or description.  It also assumes that running this script is faster than remoting to the server and looking at the directory.  😉

declare @dbname varchar(255),
@CurrentRow int,
@RowsToProcess int,
@SQL nvarchar(max),
@size float

RowID int not null primary key identity(1,1),
dbname varchar(255),
owner varchar(80),
size float

insert into #dbs (dbname) select name from sys.databases where name not in ('master','msdb','model','tempdb')
set @RowsToProcess=@@ROWCOUNT

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
SET @CurrentRow=@CurrentRow+1
FROM #dbs
WHERE RowID=@CurrentRow
set @SQL = 'update #dbs set size=(SELECT sum(size) FROM ' + @dbname + '.sys.database_files) where dbname=''' + @dbname + ''''
set @SQL = 'update #dbs set owner=(SELECT CASE WHEN CHARINDEX(''_'', dbname) > 0 THEN LEFT(dbname, CHARINDEX(''_'', dbname)-1) ELSE dbname END AS Owner FROM #dbs where dbname=''' + @dbname + ''') where dbname=''' + @dbname + ''''

select owner, COUNT(owner) as DBCount, ((((SUM(size) * 8192)/1024)/1024)/1024) as TotalGB
from #dbs group by owner

drop table #dbs

Comments Off on They’re using HOW MUCH space?

Filed under scripting

SFTP – copy smallest files first

I needed to SFTP a bunch of files ranging in size from 40 meg to 130 gig.  The default behavior of “mput *” is to copy the files in alphabetical order, but what happens then is that all the smaller files pile up behind the large ones.  This is bad because there’s a time limit and I want to copy as many as possible before the time limit passes.  The solution?

Pre-sort them by size, smallest to largest.

These are encrypted database backups, so the script requires a SQL Server instance name.  You can, of course, edit that behavior.

This depends upon PuTTY and Pageant.  Create an SSH profile with key-based authentication and use Pageant to handle the key as described in the link.  My SSH profile in this case is named “Atlanta” because that’s where the files are being copied.

This also assumes that you have directories in the SFTP server named with the pattern servername-instance because I’m running multiple streams.  Also, the last thing the database backup does is copy a file called “flag.txt” to the backup directory so the script knows when backups are done and can start copying.

set instance=%~1
if %instance%z==z goto :_help

set flag=”f:\full backups\%instance%\flag.txt”


IF EXIST %flag% (
GOTO file_found
) else (
GOTO loop


dir “f:\full backups\%instance%” /os /b /a-d-h > c:\temp\%instance%.txt

echo lcd “f:\full backups\%instance%” >> c:\temp\%instance%-control.txt
echo cd %computername%-%instance% >> c:\temp\%instance%-control.txt

FOR /F %%L IN (c:\temp\%instance%.txt) DO (
echo put %%L >> c:\temp\%instance%-control.txt

echo quit >> c:\temp\%instance%-control.txt

“c:\Program Files (x86)\PuTTY\psftp.exe” Atlanta -b c:\temp\%instance%-control.txt

del c:\temp\%instance%-control.txt
del c:\temp\%instance%.txt

goto :_end

@echo Please specify an instance.


Linked as a separate file because smart quotes are not smart.

Comments Off on SFTP – copy smallest files first

Filed under scripting

A script for migrating to a new server

I stripped out a piece of company-specific logic, but…

List the databases you want to move in a file named “control.txt.”  (To migrate the entire server, paste the output of “select name from sys.databases where name not in (‘master’, ‘msdb’, ‘model’, ‘tempdb’).”)

Replace the example values in the below script with real information:


set controlfile=control.txt

set newipmdf=\\newserver\g$
set newipldf=\\newserver\e$
set oldserver=oldserver\Prod1
set oldmdfpath=d:\prod1
set newmdfpath=g:\data
set copymdfpath=m:\data
set newserver=newserver
set oldlogpath=e:\prod1
set newlogpath=e:\log
set copylogpath=l:\log
set movedmdfpath=%oldmdfpath%\moved
set movedldfpath=%oldlogpath%\moved

mkdir %movedmdfpath%
mkdir %movedldfpath%

net use m: %newipmdf%
net use l: %newipldf%

FOR /F %%L IN (%controlfile%%) DO (
  SET "line=%%L"
  ECHO !line!
  sqlcmd -E -S!oldserver! -Q"EXEC master.dbo.sp_detach_db @dbname = N'!line!'"
  copy "!oldmdfpath!\!line!.mdf" !copymdfpath!
  copy "!oldlogpath!\!line!_log.ldf" !copylogpath!
  sqlcmd -E -S!newserver! -Q"CREATE DATABASE [!line!] ON ( FILENAME = N'!newmdfpath!\!line!.mdf' ),( FILENAME = N'!newlogpath!\!line!_log.ldf' ) FOR ATTACH"
  move "!oldmdfpath!\!line!.mdf" !movedmdfpath!
  move "!oldlogpath!\!line!_log.ldf" !movedldfpath!

net use m: /z
net use l: /z

(The redacted data was a sharding-related item–updating a metadata table with the new location.  You can, of course, add your own logic after the attach, if needed.)

As always, I’ve included a downloadable version here.

Comments Off on A script for migrating to a new server

Filed under scripting

Fun with VLFs

Virtual Log Files.  Your database’s log file is made up of one or more virtual log files.

Our databases have too many. Basically, our LDFs are fragmented.

What to do about this?  Well, Dave Levy has a script to reduce the number of VLFs, but it’s to run against a single database.  I have over 8000 databases.  Yeah.  That’s going to have to be scripted.  I know, job security and all, but that’s crazy.

Also, as a Sysadmin/Production DBA, I like scripts.  Consistent, repeatable.

I’m going to strip out some company-specific code that won’t do you any good, but if there’s a particular set of databases you want to target, list them in a maintenance table and work from that, not sys.databases.  (That’s why the check to see if the database really exists code is there.)  You’ll be able to tell that I shamelessly pasted bits and pieces of Dave Levy’s script.  For control, there’s a @maxlogcount variable that you can set crazy high.  Also, you can comment out the EXEC sp_executesql bits and uncomment the print bits for test/debug/just show me what you were going to do purposes.

declare @RowsToProcess int,
@CurrentRow int = 0,
@DBexists int,
@DBname varchar(80),
@logcount int,
@SQL nvarchar(max),
@file_name sysname,
@file_size int,
@shrink_command nvarchar(max),
@alter_command nvarchar(max),
@maxlogcount int

set @maxlogcount=100

CREATE TABLE #vlfcount(RowID int not null primary key identity(1,1), dbname varchar(80), vlfcount int, file_name varchar(255), file_size int )

INSERT into #vlfcount (dbname) SELECT name FROM sys.databases order by name
SET @RowsToProcess=@@ROWCOUNT

create table #scratch (Field tinyint, FileSize bigint, StartOffset bigint, FSeqNo int, Status tinyint, Parity tinyint, CreateLSN numeric(25,0))

WHILE @CurrentRow<@RowsToProcess
SET @CurrentRow=@CurrentRow+1
FROM #vlfcount
WHERE RowID=@CurrentRow
select @DBexists = @@ROWCOUNT from sys.databases where name=@DBname
if @DBexists = 1
set @SQL=’insert into #scratch exec (”dbcc loginfo (””’+@DBname+””’)”)’
–print @SQL
set @SQL=’update #vlfcount set vlfcount=(select count(field) from #scratch) where DBName=”’ + @DBName + ””
–print @SQL
set @SQL = ‘update #vlfcount set file_name=(select name FROM ‘ + @DBName + ‘.sys.database_files WHERE type_desc = ”log”) where DBName=”’ + @DBName + ””
–print @SQL
set @SQL = ‘update #vlfcount set file_size=(select (size / 128) FROM ‘ + @DBName + ‘.sys.database_files WHERE type_desc = ”log”) where DBName=”’ + @DBName + ””
–print @SQL
delete from #scratch
set @DBexists=0

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
SET @CurrentRow=@CurrentRow+1
FROM #vlfcount
WHERE RowID=@CurrentRow
if @logcount > @maxlogcount
SELECT @shrink_command = ‘Use ‘ + @DBName + ‘;DBCC SHRINKFILE (N”’ + @file_name + ”’ , 0, TRUNCATEONLY)’
–PRINT @shrink_command
EXEC sp_executesql @shrink_command

SELECT @shrink_command = ‘Use ‘ + @DBName + ‘;DBCC SHRINKFILE (N”’ + @file_name + ”’ , 0)’
–PRINT @shrink_command
EXEC sp_executesql @shrink_command

SELECT @alter_command = ‘ALTER DATABASE [‘ + @DBname + ‘] MODIFY FILE (NAME = N”’ + @file_name + ”’, SIZE = ‘ + CAST(@file_size AS nvarchar) + ‘MB)’
–PRINT @alter_command
EXEC sp_executesql @alter_command

select MAX(vlfcount) as worst, AVG(vlfcount) as average from #vlfcount
select dbname, vlfcount from #vlfcount where vlfcount > @maxlogcount

drop table #scratch
drop table #vlfcount

As always, I’ve included a downloadable link here: Download.  Because smart quotes are not smart.

Comments Off on Fun with VLFs

Filed under scripting

I am a winner!

I went to the Atlanta PowerShell Users Group tonight.  The topic was tips and tricks.  My trick was this script, which task scheduler reads to me in the morning. Assuming you use Hiveminder–and you should, because it’s awesome–you only have to edit the RSS locations for your location and for your to do list.

$weatherurl = "[yahoo weather RSS and city code]"
$hiveminderurl = "[hiveminder rss url here]"

You’ll need your yahoo weather city code–for example, this is Alpharetta, GA–and the RSS feed of your hiveminder to-do list for today.

I won a copy of PowerShell in Depth:  An Administrator’s Guide, by Don Jones, Richard Siddaway, and Jeffery Hicks.  Awesome!


Comments Off on I am a winner!

Filed under geekiness, powershell, scripting

So, um, I have this friend… *shifty eyes*

So, I have this friend *cough* who had a domain controller that needed a new motherboard. Due to the excitement of the hardware vendor’s tech forgetting to reattach the RAID, then the server no longer recognizing its network cards, and then the tech realizing that he forgot to replace the fan shroud, um, my… friend… kind of forgot to check the system time. It was a long week (which included parental death).

(The system time was 12 hours fast, so it looked right at a glance.)

This was Saturday. The system time situation was discovered Sunday on my… um… friend’s workstation.  (If the time is wrong on the domain controller, that wrong time propagates across the entire network.  This is to prevent Kerberos issues. If this had happened during the week, people would have randomly lost their ability to log on or connect to network shares until the times on workstations and servers were synchronized.  To the wrong time, since the domain controller is the official source.  Yeah.  And that’s the optimistic scenario.)

Since I’m not fooling anyone, I’ll take responsibility for the fix.

  1. Fixed the time on the domain controller.  (Date/time control panel followed by “w32tm /resync” to get the precise time from an internet time server.)
  2. Did search-and-replace on a list of computers from dsquery to strip it down to nothing but computer names, and saved the list as control.txt.
  3. Ran this simple script.


set controlfile=control.txt

FOR /F %%L IN (%controlfile%%) DO (
w32tm /resync /computer:%%L

I’m a little embarrassed to post my five-liner to my blog, but there you are.  Ten minute fix.

Comments Off on So, um, I have this friend… *shifty eyes*

Filed under scripting

Generic Backups

I support developers.  In our dev/QA environment, people add, delete, and change the recovery model of databases all the time without telling me.  How do I keep track of which databases need to be backed up?

I don’t.  I back up everything.

Master has information on all databases.  That means master can give you a list of what needs to be backed up.  Of course, you don’t want to back up tempdb (because you can’t restore tempdb), and I haven’t customized model, so:

Declare @DBname varchar(255),
@RowsToProcess int,
@CurrentRow int,
@SQL nvarchar(max),
@today nvarchar(50),
@servername nvarchar(50)

set @today = convert(nvarchar,GETDATE(),12)
set @servername = REPLACE(@@SERVERNAME,’\’,’-‘)

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
SET @CurrentRow=@CurrentRow+1
FROM #maintenancetemp
WHERE RowID=@CurrentRow
set @SQL=’BACKUP DATABASE [‘ + @DBName + ‘] TO DISK = N”S:\backups\’ + @servername + ‘-‘ + @DBName + ‘-‘ + @today + ‘.bak” WITH FORMAT, INIT, NAME = N”Full ‘ + @DBName + ‘Database Backup”, SKIP, NOREWIND, NOUNLOAD, STATS = 10’
–print @SQL

drop table #maintenancetemp

Here’s the full backup script that loads that into a temp table and loops through the rows and backs them up.  You can, of course, customize this script to suit your preferred file name convention and backup location.  You can, of course, also plop that into a job or, if you’re running something like Windows Internal Database or SQL Express, run it via sqlcmd and Task Manager.

What about log backups?  If it’s just dev/QA data, is it important?  Is QA going to jump out of a window if they lose data?

I don’t ask.  I just back it up.  Master knows what’s in full recovery, too:

select name from sys.databases where name <> ‘tempdb’ and name <> ‘model’ and recovery_model IN (1, 2)

This log backup script assumes hourly backups, which might be overkill for your environment and might not.  I’ve seen “dev” and “QA” environments that were treated like prod because they were actually for training and the company lost money if training couldn’t take place, so I’d rather err on the side of being backed up.  Again, customize to suit your environment.

I’ve started running these against any new internal server-based SQL Server instance I find out about.  We do have instances of SQL Server 2005 Express and the like, for testing, so sqlcmd continues to be my friend.

Comments Off on Generic Backups

Filed under scripting