Don’t forget about DTC when migrating your vRealize Automation IaaS database

20161114-1HobbitCloud has been enjoying somewhat of an infrastructure refresh recently, with the entire estate being renamed and Windows Servers migrating to 2016 and 2019. Part of this project was to increase resilience in my on-premise data warehouse by implementing a Microsoft SQL Server 2016 AlwaysOn Availability Group (AAG) running on Windows Server Core. Continue reading

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.