SQLandSai: April 2016

Tuesday, 26 April 2016

Script to find backup history , location , servername and backup size details

To find backup history details along with location we need to use backupset and backupmediafamily system tables available in msdb database.

These system tables are available from SQL Server 2000 version on wards so the below script will run from SQL Server 2000 and above versions.

SELECT TOP 5 a.server_name
 ,a.database_name
 ,backup_finish_date
 ,a.backup_size
 ,CASE a.[type] -- Let's decode the three main types of backup here
  WHEN 'D'
   THEN 'Full'
  WHEN 'I'
   THEN 'Differential'
  WHEN 'L'
   THEN 'Transaction Log'
  ELSE a.[type]
  END AS BackupType
 ,b.physical_device_name
FROM msdb.dbo.backupset a
INNER JOIN msdb.dbo.backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE a.database_name LIKE 'master%'
ORDER BY a.backup_finish_date DESC

SQL DBA : Script to find backup , restore estimation timings in SQL Server

To find out estimation completion time for backup as well as restore database we will use 2 dmv's i.e sys.dm_exec_sql_text and sys.dm_exec_requests .

SELECT r.session_id
 ,r.command
 ,CONVERT(NUMERIC(6, 2), r.percent_complete) AS [Percent Complete]
 ,CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20) AS [ETA Completion Time]
 ,CONVERT(NUMERIC(10, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min]
 ,CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [ETA Min]
 ,CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [ETA Hours]
 ,CONVERT(VARCHAR(1000),

(
   SELECT SUBSTRING(TEXT, r.statement_start_offset / 2, CASE 
      WHEN r.statement_end_offset = - 1
       THEN 1000
      ELSE (r.statement_end_offset - r.statement_start_offset) / 2
      END)
   FROM sys.dm_exec_sql_text(sql_handle)
   )

FROM sys.dm_exec_requests r
WHERE command IN (
  'RESTORE DATABASE'
  ,'BACKUP DATABASE'
  )

Monday, 25 April 2016

SQL Server backup commands

Full Backup :

BACKUP DATABASE <DatabaseName> TO DISK = 'DriveName:\PathName\DatabaseName.bak'

Differential Backup :

BACKUP DATABASE <DatabaseName> TO DISK = 'DriveName:\PathName\DatabaseName.diff' WITH DIFFERENTIAL

Transaction Log Backup :

BACKUP LOG <DatabaseName> TO DISK = 'DriveName:\PathName\DatabaseName.trn'

Partial Backup : Taking backup of specific file on a group from the available file groups is called as partial backup.

BACKUP DATABASE <DatabaseName> <FileGroupName>
TO DISK = 'DriveName:\PathName\DatabaseName.bak'

BACKUP DATABASE AdventureWorks READ_WRITE_FILEGROUPS
TO DISK = 'C:\TestBackup_Partial.bak'

File Backup :

BACKUP DATABASE TestBackup FILE = 'TestBackup'
TO DISK = C:\TestBackup_TestBackup.fil'

File Group Backup :

BACKUP DATABASE TestBackup FILEGROUP = 'ReadOnly'
TO DISK = 'C:\TestBackup_ReadOnly.flg'

SQL Server security basic commands

Creating SQL Login for the users registered in Local Users / Groups (Windows ):

USE [master]
GO
CREATE LOGIN [SQL\snreddy] FROM WINDOWS
WITH DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english]

Create SQL login :

USE [master]
GO
CREATE LOGIN [snreddy] WITH PASSWORD=N'lDÇ°ü†ÉUÛ^¼h nðÿ$©9 ke—z£˜ Ã`øM', DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=ON

Creating user and mapping to Login :

Create user <User Name > for Login <Login_Name> -- For SQL Login

Create user <User Name > from login <Login_Name> -- For Windows Login

Note :

One Login can be associated with many users (one per database ).

Wednesday, 6 April 2016

Step by Step SQL Server Cluster Installation

To set up SQL Server cluster , We need to select the s/w where SQL Server is located.
Click on VM --> Settings .

Select the media file path.

Now click on Windows Explorer drive path where SQL Server mirror copy presents as shown below.

After few seconds the SQL Server Installation Center Wizard will display . Now click on Installation and choose New SQL Server failover cluster installation.