Backup Database Error SQL Server 2012: This BACKUP or RESTORE command is not supported on a database mirror or secondary replica. BACKUP DATABASE is terminating abnormally

Microsoft SQL Server 2012 has introduced a new feature called AlwaysOn High Availability. There are numerous resources online to get information on AlwaysOn High Availability. So here i am not going in depth with features of this.

Will be coming up with some interesting post on this topic later. But for those you have already configured this feature, you might run into below error while running a backup command.

"This BACKUP or RESTORE command is not supported on a database mirror or secondary replica. BACKUP DATABASE is terminating abnormally"

This is because AlwaysOn is configured to only allow backups on the primary replica.

So avoid this if you are running backup from Some SSIS Package, make sure you include below phrase in your backup command

sys.fn_hadr_backup_is_preferred_replica ( name ) = 1

So this will run backup on databases which are only Primary replica.

Technorati Tags: AlwaysOn High Availability,database mirror,secondary replica,primary replica,Microsoft SQL Server 2012

Hope this helps.

TSQL to Get all details of who created the SQL Server Login on a SQL server Instance

As a DBA, One of the most important task is to audit the security.

Very Often, we may come across situation where logins are created on SQL server Instance but we are not aware of any details on who created it, when it was created etc.

So here a small simple query to all such details.

Firstly we need to get the path of default Trace which is running on SQL Server Instance,
The below query will assist you in getting that path.

select value from ::fn_trace_getinfo(0)

Now you will get values something like below. The Path may vary how you have configured it to be and it will show you the default trace which are currently running.

Now copy the path which you have found out in above query and replace "PlacetheTraceFilePathwhichyouhavegotfromabovequeryhere" with the path which you have found in the below query to get the required details.

SELECT  ste.name
        ,sftgt.DatabaseName 
        ,sftgt.NTDomainName 
        ,sftgt.ApplicationName 
        ,sftgt.LoginName 
        ,sftgt.StartTime 
        ,sftgt.TargetLoginName 
        ,sftgt.SessionLoginName
FROM    sys.fn_trace_gettable('PlacetheTraceFilePathwhichyouhavegotfromabovequeryhere', DEFAULT) sftgt
        JOIN sys.trace_events ste ON sftgt.EventClass = ste.trace_event_id
        JOIN sys.trace_subclass_values stsv ON stsv.trace_event_id = ste.trace_event_id
                                            AND stsv.subclass_value = sftgt.EventSubClass
WHERE   ste.name = 'Audit Addlogin Event'
        AND stsv.subclass_name = 'add'
GO

You can customize this query.
Note: This feature is available from SQL Server 2005 onwards only.

You get many more information from default trace. Will cover more in further posts.

T-SQL to backup all databases or selected databases to specific location

Very often we come across situation where we need to backup database in bulk. Sometime when you are migrating a server or upgrading to different version or applying patch and to be on safer side you tend to take backup of all database. imagine you have more than 100 databases on server and backing up each database would be tedious task

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name 

--Provide the path where all the databases needs to be backed up
SET @path = 'MyBackupFilePath'  

--used to suffix the current date at the end of backup filename
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

DECLARE db_cursor CURSOR FOR  

--Use this for all database except the system databases and any exclusion you can make
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')  

--Uncomment and use this for only specific databases.
--Those database names you can provide under IN clause
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name IN ('MyDB1','MyDB2') 


OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName WITH STATS = 1  

       FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor 

T-SQL to get the SQL Server Service Start time and Up time of SQL Server service

There are number of occasions where we need to get to know the SQL Server service start date time and also from what time the SQL Services are up and running generally termed as Uptime. There are many ways to do it like check for temp DB Creation time, using DMF sys.dm_io_virtual_file_stats etc. Below written is small T-SQL script but very useful which provides you this information. Hope this is useful. Comments and suggestions are always welcome. .

USE MASTER

GO

SET NOCOUNT ON

GO

SELECT 'SQL server started at ' +

CAST((CONVERT(DATETIME, sqlserver_start_time, 126)) AS VARCHAR(20))

+' and is up and running from '+

CAST((DATEDIFF(MINUTE,sqlserver_start_time,GETDATE()))/60 AS VARCHAR(5))

+ ' hours and ' +

RIGHT('0' + CAST(((DATEDIFF(MINUTE,sqlserver_start_time,GETDATE()))%60) AS VARCHAR(2)),2)

+ ' minutes' AS [Start_Time_Up_Time] FROM sys.dm_os_sys_info

GO

SET NOCOUNT OFF

GO