Convert Rows to Column using COALESCE() function

Using the below query we can convert the rows to column sperated by a delimiter.
In the query I am using ';' as the delimiter and you can change the delimiter of your choice by replacing ';'.
Data from Table:

Query:

Use AdventureWorks2008R2
GO
DECLARE @eMailList nvarchar(max) 

SELECT @eMailList = COALESCE(@eMailList + ';', '') + 
   CAST(eMail AS nvarchar(max))
FROM Employees

Select @eMailList as eMailList

Output of the above Query:

Microsoft SQL Server 2012 Release Candidate 0 (RC0) - Available for Download

Microsoft SQL Server 2012 RC0 enables a cloud-ready information platform that will help organizations unlock breakthrough insights across the organization as well as quickly build solutions and extend data across on-premises and public cloud backed by capabilities for mission critical confidence.
To read more and download Microsoft SQL Server 2012 RC0 click here.

Backup Database to multiple locations simultaneously - Mirror Backups

Database backup is one the regular activity a DBA would perform. Some times you might come across a situation where in you need to backup the database to different location. When I say backup database to different locations, it means that a copy of backup file needs to be placed on a different location as well and this is different from the Split Backups.

This is can be achieved by different methods,

1. Take backup and then copy to multiple location
2. Take backup of the same database multiple times pointing to different locations
3. Use "MIRROR TO" Option in the Backup command 

Using the option "MIRROR TO" is very simple, you just need to mention "MIRROR TO" and "WITH FORMAT" options in the normal BACKUP DATABASE Statement and you are done. The backup database statement with these two options will take the backup of the same database to multiple locations at the same time.

This option "MIRROR TO" is introduced in SQL Server 2005 and this works only in SQL Server 2005 Enterprise Edition and later versions.

This can be used for all backup types and the Maximum number of "MIRROR TO" clauses that you can specify is three.

Example: 

BACKUP DATABASE AdventureWorks TO DISK = 'C:\Backup\AdventureWorks_Full.bak' MIRROR TO DISK = 'C:\Mirror\AdventureWorks_Full.bak' WITH STATS=10, FORMAT BACKUP DATABASE AdventureWorks TO DISK = 'C:\Backup\AdventureWorks_Differential.bak' MIRROR TO DISK = 'C:\Mirror\AdventureWorks_Differential.bak' WITH STATS=10, DIFFERENTIAL, FORMAT BACKUP LOG AdventureWorks TO DISK = 'C:\Backup\AdventureWorks_log.trn' MIRROR TO DISK = 'C:\Mirror\AdventureWorks_log.trn' WITH STATS=10, FORMAT

When it comes to restoring the database, we can use either of the backup copies to restore or recover the database.

Hide an Instance of SQL Server Database Engine

In one of my previous post "List of SQL Server instances currently installed in your network", I explained how to get the list of SQL server instances installed in your network.
Sometimes you might require an SQL Server instance to be not exposed to others due to some security reason. This can be done by using the "Hide" option available for an SQL Server instance.
Before Hiding an Instance:

Steps to Hide an Instance of SQL Server Database Engine

1. Go to "SQL Server Configuration Manager"
2. In the Left Pane Expand "SQL Server Network Configuration"
3. Now Right Click on "Protocols for <ServerName>" and go to Properties for the server you need to hide.
4. In the "Flags" Tab, change the option to "Yes" for "Hide Instance"
   
5. Click on "Apply" and "OK"
6. Now re-start the SQL Server Service for this Instance and you are done. This instance from now will be hidden.

After Hiding an Instance:

Get last backup details of all databases in a server

This stored procedure give you the information about latest backups happened on all databases in a server.
This SP works for SQL server 2005 and up.

Create Proc sp_BackupDetails AS DECLARE @BackupDetails table ([Server Name] nvarchar(500), [Database Name] nvarchar(500), [Last Full Backup] nvarchar(500), [Last Differential Backup] nvarchar(500), [Last Log Backup] nvarchar(500), [Last File or filegroup Backup] nvarchar(500), [Last Differential file Backup] nvarchar(500), [Last Partial Backup] nvarchar(500), [Last Differential Partial Backup] nvarchar(500) ) DECLARE @DBName nvarchar(500) Declare DBName Cursor for Select name from sys.databases Open DBName Fetch Next from DBName into @DBName While @@fetch_status = 0 BEGIN Insert into @BackupDetails select @@ServerName as [Server Name]        ,SDB.name AS [Database Name]        ,(select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='D') AS [Last Full Backup]        ,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='I') AS [Last Differential Backup]        ,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='L') AS [Last Log Backup]        ,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='F') AS [Last File or filegroup Backup]        ,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='G') AS [Last Differential file Backup]        ,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='P') AS [Last Partial Backup]        ,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='Q') AS [Last Differential Partial Backup] from sys.databases SDB where SDB.name =@DBName Fetch Next from DBName into @DBName END Close DBName DEALLOCATE DBName Select * from @BackupDetails GO

Usage:

Exec sp_BackupDetails

go

Results: