For over twenty years I’ve run my own servers at home. For slightly less time I also ran my lab from there, so these servers that ran my email, DNS, web services etc I referred to as “production”. Recently I decided that my home was no longer the place to keep my lab, as heating and noise were becoming increasingly more irritating. Shortly after I decided Continue reading
Today is a sad day. After just over fifteen years of having a computer lab environment at home, I have decided the time has come to decommission it. The bandwidth and energy requirements have become too much, and I can no longer justify having one in my current role. Continue reading
Recently I made the decision to temporarily move away from VMware VSAN. Whilst there was a lot of things about VSAN that I liked (close integration with the vSphere platform for example), my home lab exists to enable me to evaluate a number of different solutions. Continue reading
Last week I was watching the highlights from Tech Field Day 10 and saw some details emerge of the Dell VRTX.
The VRTX is an integrated solution that brings together compute, storage and (fast) networking all under one chassis. It comes as both a tower or rack configuration.
Recently I was inspired by a post Scott Lowe wrote regarding his 2016 projects. At the beginning of each year I always have a vision about what I’d like to achieve, but if I don’t write it down and set benchmarks against it I have no way of measuring success.
So here goes… Continue reading
Recently I revamped my lab storage with the addition of a HP Microserver Gen8 and a dual-port Emulex OCe10102 10GbE card. After some initial struggles with Windows Server 2012 R2 and Windows Server 2016 Technical Preview 3, I decided to go back to FreeNAS 9.3. Continue reading
This post is a quick update to let anyone who’s following my blog (other than my mother) that I’m taking a few days off this week to digest all the interesting stuff that comes out of VMworld 2015 US.
Today as part of a migration, a client asked me to backup all thirty three SQL databases on a specific server to a local drive. Normally they only ask for a handful, and I use the following code:
USE <em>databaseName</em>; GO BACKUP DATABASE <em>databaseName</em> TO DISK = 'I:\backups\<em>currentDate</em>_<em>currentTime</em>_<em>serverName</em>_<em>databaseName</em>.bak' WITH FORMAT,13 MEDIANAME = 'I:\backups', NAME = <em>'currentDate</em>_<em>currentTime</em>_<em>serverName</em>_<em>databaseName</em>.bak'; GO
However with thirty three databases to do, I needed something a little smarter.
The store procedure sp_databases gives me a list of all databases, but this also includes the temp, model and master databases which I don’t need. What I needed was a script to iterate through all the databases, and back them up.
The first attempt went like this:
DECLARE @backupCommand varchar(1000) SELECT @backupCommand = 'IF ''?'' IN(''dbaUtils'') BEGIN USE ? BACKUP DATABASE ? TO DISK = ''I:\backups\20150526_<em>serverName</em>_?.bak'' WITH FORMAT, MEDIANAME = ''I:\backups', NAME = ''20150526_<em>serverName</em>_?.bak'' END' EXEC sp_MSforeachdb @backupCommand
Whilst that was great, it didn’t give me the current date and time for each database as it backed up. After a lot of debugging, this came about:
DECLARE @backupCommand varchar(2000) SELECT @backupCommand = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ?; DECLARE @filename varchar(100) SET @filename = ''I:\backups\'' + left(replace(replace(replace(convert(varchar(25), getdate(), 120),''-'',''''),'' '',''_''),'':'',''''),13) + ''_'' + @@SERVERNAME + ''_?.bak''; BACKUP DATABASE ? TO DISK=@filename END' EXEC sp_MSforeachdb @backupCommand
A big thanks to my friend and resident SQL Jedi Master Chris for his help (read: he did it all) on this.