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