HobbitCloud 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
Configuring DTC access between vRealize Automation IaaS and Microsoft SQL Server
Recently I had an issue with vRealize Automation communicating with my SQL Server. Unfortunately, the IaaS database plays a fundamental part in any vRA installation, so any problem with it will severely limit your deployment. Continue reading
Wednesday Tidbit: Adding the SQL Server Agent to a Windows Failover Cluster
Yesterday 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
When 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>; 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.