Wednesday Tidbit: Adding the SQL Server Agent to a Windows Failover Cluster

20150916 - 1Yesterday I decided it was time to patch my VMware vCenter 5.5 hosts to the recently released Update 3. As I make use of properly configured SSL certificates, each component (SSO, Web Client, Inventory and vCenter Server) has to be installed separately. However when I came to install the last one, I ran into an issue. Continue reading

Create a Microsoft SQL 2014 Failover Cluster on Windows Server 2012 R2 Server Core

20150813 - 1When it first came out last year, I installed Microsoft SQL 2014 and configured an Availability Group to host my vCenter database (despite it not being supported).

Unfortunately it has never worked perfectly, and today I decided it was time to do the job right. I still need high availability for my SQL Server, and Microsoft Clustering Services would be ideal. Continue reading

Backing-up multiple SQL databases using T-SQL

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>;
BACKUP DATABASE <em>databaseName</em>
TO DISK = 'I:\backups\<em>currentDate</em>_<em>currentTime</em>_<em>serverName</em>_<em>databaseName</em>.bak'
MEDIANAME = 'I:\backups',
NAME = <em>'currentDate</em>_<em>currentTime</em>_<em>serverName</em>_<em>databaseName</em>.bak';

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.