Where to start performance tuning in SQLServer

There is no special area to start debugging the issue in SQL Server.

Refer the below screens shots for quick idea.

How to restore database in AG

ü  Backup the exisitng  dataabse

ü  Take the security backup

ü  Remove the databases from availability group : AlwaysOn High Availability --> Availability Groups è Group Name Ex:AG_Test -->Availability Databases --> DatabaseName --> Remove Database Name from Availability Group

ü  Restore the databases

ü  Execute the security permissions ( which you have taken before restore) on newly restored database.

ü  Remove the availability databases on secondary

ü  Add the databases to the Availability group

Can We delete the database pariticipating in AG ?

You cannot delete the database which participating in AG . If you try to delete the database then you will get the error like -

The operation cannot be performed on database "DB Name" because it is involved in a database mirroring session or an availability group

For more details about errors

TITLE: Microsoft SQL Server Management Studio
------------------------------

Alter failed for Database 'DBNAME'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.5000.0+((SQL14_PCU_main).160617-1804)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The operation cannot be performed on database "DB Name" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 1468)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.5000&EvtSrc=MSSQLServer&EvtID=1468&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Can we restore database pariticipating in AG Group .

you cannot restore the database directly participating on AG. Please refer the below error for more details.

Msg 3104, Level 16, State 1, Line 2 RESTORE cannot operate on database 'DBNAME 'because it is configured for database mirroring or has joined an availability group. If you intend to restore the database, use ALTER DATABASE to remove mirroring or to remove the database from its availability group. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally.
In order to proceed with restore, first you need to remove the database from the AG and restore the db.Make sure you took database level permissions before restore and apply the permissions after restore.

Script to find when backup started and finished in SQL Server

In order to get the backup start date and backup end date we need to use backupfile,backupmediafamily and backupset tables with dbo schema available in msdb system database .

SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SERVER
 ,msdb.dbo.backupset.database_name
 ,msdb.dbo.backupset.backup_start_date
 ,msdb.dbo.backupset.backup_finish_date
 ,msdb.dbo.backupset.expiration_date
 ,CASE msdb..backupset.type
  WHEN 'D'
   THEN 'Database'
  WHEN 'L'
   THEN 'Log'
  END AS backup_type
 ,msdb.dbo.backupset.backup_size
 ,msdb.dbo.backupmediafamily.logical_device_name
 ,msdb.dbo.backupmediafamily.physical_device_name
 ,msdb.dbo.backupset.NAME AS backupset_name
 ,msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON
ORDER BY msdb.dbo.backupset.database_name
 ,msdb.dbo.backupset.backup_finish_date