Scripting all Agent Jobs By Using SQL Server Management Studio

I recently came across a way to script out all agent jobs in one go within SQL Server Management Studio (SSMS). Previously I used to right click on each job and select ‘Script Job as’ to script up each one individually, but this can be very laborious
if there are a lot of jobs to do.

To script all jobs, just open the ‘Object Explorer Details’ from the View menu in SSMS, or press the F7 key. Click on any job in the Object Explorer window and a list of all the agent jobs appears in the ‘Object Explorer Details’ window.

Select all the jobs you want to script (press the Ctrl button while clicking to select individual jobs) and then right click and select the scripting option you want. This will then create all the selected jobs as a single query. You can script to a query window, file etc.

Find out the backup locations/paths for multiple SQL Instances

If you are trying to consolidate backup locations, we first need to analyse where the databases are being backed up to. Below query will give you the location of where the databases were backed up to. Run this in Central Management server and you can get a report of all the SQL Instances in one shot

SET NOCOUNT ON
SELECT CONVERT(CHAR(30), SERVERPROPERTY('Servername')) AS instance_name,
CONVERT(CHAR(30),bs.database_name) AS database_name,
CONVERT(CHAR(125),bf.physical_device_name) backup_path
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bf
ON bs.media_set_id = bf.media_set_id
JOIN master.dbo.sysdatabases db
ON bs.database_name = db.name
WHERE bs.backup_finish_date = (
      SELECT MAX(backup_finish_date)
      FROM msdb.dbo.backupset
      WHERE database_name = bs.database_name
            AND type = 'D'
      )
      AND (DATABASEPROPERTYEX(bs.database_name,'Status') = 'ONLINE'
      AND DATABASEPROPERTYEX(bs.database_name,'IsInStandby') = 0)

Script to generate database Role membership and also to generate commands to add the users to database roles

During the migration of a database from one SQL Instance to another, we might have to re-create the permissions on the target SQL Instance. This is not required in all the cases, but only in some cases where the user permissions are not synced for some reason. So an easy way to script them out before performing the database refresh is to use the below script to capture the existing setup of database role membership:

-- List Database Roles and Members with Server Login
SELECT ROL.name AS RoleName
      ,MEM.name AS MemberName
      ,MEM.type_desc AS MemberType
      ,MEM.default_schema_name AS DefaultSchema
      ,SP.name AS ServerLogin
FROM sys.database_role_members AS DRM
     INNER JOIN sys.database_principals AS ROL
         ON DRM.role_principal_id = ROL.principal_id
     INNER JOIN sys.database_principals AS MEM
         ON DRM.member_principal_id = MEM.principal_id
     INNER JOIN sys.server_principals AS SP
         ON MEM.[sid] = SP.[sid]
ORDER BY RoleName
        ,MemberName;

Now after this is captured, you still have to manually run the below commands for each of the users captured:

sp_addrolemember 'Rolename','UserName'

It takes a lot of time to generate a script manually if there are more than 5 or 10 users. Below is a script to generate those commands as well by leveraging on the above script:

SET QUOTED_IDENTIFIER OFF
IF EXISTS (SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME="##USER_PERMISSIONS")
DROP TABLE ##USER_PERMISSIONS
CREATE TABLE ##USER_PERMISSIONS
(ROLENAME VARCHAR (100),
MEMBERNAME VARCHAR(100))
INSERT INTO ##USER_PERMISSIONS
SELECT ROL.name AS RoleName
      ,MEM.name AS MemberName
      FROM sys.database_role_members AS DRM
     INNER JOIN sys.database_principals AS ROL
         ON DRM.role_principal_id = ROL.principal_id
     INNER JOIN sys.database_principals AS MEM
         ON DRM.member_principal_id = MEM.principal_id
     INNER JOIN sys.server_principals AS SP
         ON MEM.[sid] = SP.[sid]
ORDER BY MemberName, Rolename;
SELECT ("sp_addrolemember '"+ ROLENAME +"','"+MEMBERNAME+"'") AS CMD FROM ##user_permissions
go

How To Find How Many Instances Installed In The System By Using T-SQL

Create Table #SQLInstances
( Value nvarchar(100),
InstanceName nvarchar(100),
Data nvarchar(100))

Insert into #SQLInstances
EXECUTE xp_regread
@rootkey ='HKEY_LOCAL_MACHINE',
@key ='SOFTWARE\Microsoft\Microsoft SQL Server',
@value_name ='InstalledInstances'


select instancename,Data from #SQLInstances

Important New DMV's Features

There are couple of new DMV’s. But I would like to share with you all.Some Useful new DMV’s in SQL Server 2012 and SQL Server 2008 R2 SP1

sys.dm_os_windows_info: This DMV will get you OS related information of the Server.

sys.dm_server_registry: This DMV will get you information on registry keys which are used by the server.

sys.dm_server_memory_dumps : This DMV will get you information on dumps which are generated by the server.

sys.dm_server_services: This DMV will get you information on services and startup’s

Splitting comma separated column’s values in rows in SQL Server And Vice Versa

I have seen many Developer/DBA uses user defined function or stored procedure to split a comma separated ( or any delimiter to separate values like |,;-.& or any character/special character) column into rows using various coding logic like while/for loop or using cursor etc.

Let see an example, below is a sample data,

     dbaraja.blogspot.in                  

                  

And my requirement is to split the every SkillSet column values in a single row according EMPID and EMPNAME like

          dbaraja.blogspot.in                                                                   

                                                                           

To get above desired result without using any Function, Stored Procedure or any loop, simple flat SQL select statement will be like this,

SELECT EmpID,EmpName,Split.Data.value('.', 'VARCHAR(100)') AS Skill

FROM (Select EmpID,EmpName,CAST('<M>' + REPLACE(SkillSet,',', '</M><M>')+ '</M>' AS XML) AS Skill FROM Rajendra_Test) AS Data 

CROSS APPLY Skill.nodes ('/M') AS Split(Data)

                           ------------------------------------------------
The below Query is used to retrieve the opposite of above process.

;WITH CTE AS
(
SELECT DISTINCT EmpId
FROM Employee
)
SELECT EmpId, STUFF((SELECT ', ' + CAST(E2.Skill AS NVARCHAR(MAX))
     FROM Employee E2 WHERE E1.EmpId = E2.EmpId
     FOR XML PATH('')),1,1,'') skills
  into Employee1
FROM CTE E1

Select * from Employee1 --Getting Result--

              

Script to list currently executing queries in SQL Server

 
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')

List all users of all databases with all roles in SQL Server

Create procedure [dbo].[List_DBRoles]
(
@database nvarchar(128)=null,
@user varchar(20)=null,
@dbo char(1)=null,
@access char(1)=null,
@security char(1)=null,
@ddl char(1)=null,
@datareader char(1)=null,
@datawriter char(1)=null,
@denyread char(1)=null,
@denywrite char(1)=null
)

as
declare @dbname nvarchar(1000)
declare @mSql1 varchar(8000)
CREATE TABLE #DBROLES
( DBName sysname not null,
UserName sysname not null,
db_owner varchar(3) not null,
db_accessadmin varchar(3) not null,
db_securityadmin varchar(3) not null,
db_ddladmin varchar(3) not null,
db_datareader varchar(3) not null,
db_datawriter varchar(3) not null,
db_denydatareader varchar(3) not null,
db_denydatawriter varchar(3) not null,
Cur_Date datetime not null default getdate()
)

DECLARE DBName_Cursor CURSOR FOR
select name
from master.dbo.sysdatabases
where name not in ('mssecurity','tempdb')
Order by name
OPEN DBName_Cursor
FETCH NEXT FROM DBName_Cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
Print @dbname
Set @mSQL1 = ' Insert into #DBROLES ( DBName, UserName, db_owner, db_accessadmin,
db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
db_denydatareader, db_denydatawriter )
SELECT '+''''+@dbName +''''+ ' as DBName ,UserName, '+char(13)+ '
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
from (
select b.name as USERName, c.name as RoleName
from ' + @dbName+'.dbo.sysmembers a '+char(13)+
' join '+ @dbName+'.dbo.sysusers b '+char(13)+
' on a.memberuid = b.uid join '+@dbName +'.dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'
--Print @mSql1
Execute (@mSql1)
FETCH NEXT FROM DBName_Cursor INTO @dbname
END
CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor

Select * from #DBRoles
where ((@database is null) OR (DBName LIKE '%'+@database+'%')) AND
((@user is null) OR (UserName LIKE '%'+@user+'%')) AND
((@dbo is null) OR (db_owner = 'Yes')) AND
((@access is null) OR (db_accessadmin = 'Yes')) AND
((@security is null) OR (db_securityadmin = 'Yes')) AND
((@ddl is null) OR (db_ddladmin = 'Yes')) AND
((@datareader is null) OR (db_datareader = 'Yes')) AND
((@datawriter is null) OR (db_datawriter = 'Yes')) AND
((@denyread is null) OR (db_denydatareader = 'Yes')) AND
((@denywrite is null) OR (db_denydatawriter = 'Yes')) 

SQL Server set compatibility level for all server databases

use master;
go


DECLARE UserDatabases_CTE_Cursor Cursor
FOR

-- Selecting user database names.
select name as DatabaseName
from sys.sysdatabases
where ([dbid] > 6) and ([name] not like '$')

OPEN UserDatabases_CTE_Cursor
DECLARE @dbName varchar(100);

DECLARE @compatQuery varchar(500);



Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName
While (@@FETCH_STATUS <> -1)

BEGIN
-- set database compatibility level
set @compatQuery =  'ALTER DATABASE ' + @dbName + ' SET COMPATIBILITY_LEVEL = 100;'

-- Print SQL statement
print @compatQuery

-- Execute compatability script
EXEC (@compatQuery)

-- Get next database
Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName
END

CLOSE UserDatabases_CTE_Cursor
DEALLOCATE UserDatabases_CTE_Cursor
GO

How to transfer logins and passwords between instances of SQL Server

Transferring Logins from one instance to another

First 2 steps should be executed in primary server and copy 2nd step output and run in standby or second server

--step1
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL


  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR


      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

--step2
EXEC sp_help_revlogin

--step3:
The above s.p generates some output as follows

/* sp_help_revlogin script

-- Login: BUILTIN\Administrators
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE = [master]

-- Login: NT AUTHORITY\SYSTEM
CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE = [master]

Copy, paste above output in Stand by server instance and run for required logins.

Important Scripts

Backup All User and System Databases

If you want to backup all system and user databases together you can do the same with below SQL script.

DECLARE @DB VARCHAR(20)
DECLARE @BkpFName VARCHAR(100)
DECLARE @BkpFDate VARCHAR(50)

SELECT @BkpFDate = REPLACE(CONVERT(VARCHAR(50), GETDATE(), 102),'.','') + REPLACE(CONVERT(VARCHAR(50), GETDATE(), 108),':','')

DECLARE Cursor_DBs CURSOR FOR

SELECT name
FROM master.sys.DATABASES
WHERE name != 'tempdb'

OPEN Cursor_DBs
FETCH NEXT FROM Cursor_DBs INTO @DB

WHILE @@FETCH_STATUS = 0

BEGIN
SET @BkpFName = 'F:\Rajendra\BackupFolder\' + @DB + '_FullBackup_' + @BkpFDate + '.bak'
BACKUP DATABASE @DB TO DISK =  @BkpFName  WITH COMPRESSION
FETCH NEXT FROM Cursor_DBs INTO @DB
END

CLOSE Cursor_DBs
DEALLOCATE Cursor_DBs

Backup All User Databases

If you want to backup only user databases and don’t want to include system databases then you can use below SQL script.

DECLARE @DB VARCHAR(20)
DECLARE @BkpFName VARCHAR(100)
DECLARE @BkpFDate VARCHAR(50)

SELECT @BkpFDate = REPLACE(CONVERT(VARCHAR(50), GETDATE(), 102),'.','') + REPLACE(CONVERT(VARCHAR(50), GETDATE(), 108),':','')

DECLARE Cursor_DBs CURSOR FOR

SELECT name
FROM master.sys.DATABASES
WHERE name NOT IN ('master','model','msdb', 'tempdb')

OPEN Cursor_DBs
FETCH NEXT FROM Cursor_DBs INTO @DB

WHILE @@FETCH_STATUS = 0

BEGIN
SET @BkpFName = 'F:\Rajendra\BackupFolder\' + @DB + '_FullBackup_' + @BkpFDate + '.bak'
BACKUP DATABASE @DB TO DISK =  @BkpFName  WITH COMPRESSION
FETCH NEXT FROM Cursor_DBs INTO @DB
END

CLOSE Cursor_DBs
DEALLOCATE Cursor_DBs

Backup All System Databases

If you want to backup all system databases master, model and msdb then use below SQL script.

DECLARE @DB VARCHAR(20)
DECLARE @BkpFName VARCHAR(100)
DECLARE @BkpFDate VARCHAR(50)

SELECT @BkpFDate = REPLACE(CONVERT(VARCHAR(50), GETDATE(), 102),'.','') + REPLACE(CONVERT(VARCHAR(50), GETDATE(), 108),':','')

DECLARE Cursor_DBs CURSOR FOR

SELECT name
FROM master.sys.DATABASES
WHERE name IN ('master','model','msdb')

OPEN Cursor_DBs
FETCH NEXT FROM Cursor_DBs INTO @DB

WHILE @@FETCH_STATUS = 0

BEGIN
SET @BkpFName = 'F:\Rajendra\BackupFolder\' + @DB + '_FullBackup_' + @BkpFDate + '.bak'
BACKUP DATABASE @DB TO DISK = @BkpFName WITH COMPRESSION
FETCH NEXT FROM Cursor_DBs INTO @DB
END

CLOSE Cursor_DBs
DEALLOCATE Cursor_DBs

How To DELETE Records Batch Wise By Using Cursor ?

DECLARE @TblName Varchar(Max), @MSQL Nvarchar(Max)
DECLARE TopTableDelete Cursor
FOR
SELECT TOP 5 Name FROM Sysobjects WHERE xtype = 'U' order by name
OPEN TopTableDelete
FETCH NEXT FROM TopTableDelete INTO @TblName
WHILE @@FETCH_STATUS = 0
BEGIN      
 SET @MSQL = 'DELETE RDYadav FROM
     (SELECT SrlNo = ROW_NUMBER() OVER(ORDER BY GETDATE()),*
       FROM [dbo].['+@TblName +']) RDYadav
       WHERE SrlNo >100000'
 EXEC(@MSQL)    
 --Print @MSQL
 FETCH NEXT FROM TopTableDelete INTO @TblName
END
CLOSE TopTableDelete
DEALLOCATE TopTableDelete

How To Find How Many Tables Are There & How Many Records Are There Each Table Having ?

CREATE TABLE #COUNTS( TABLE_NAME VARCHAR(255), ROW_COUNT INT)
EXEC SP_MSFOREACHTABLE @COMMAND1='INSERT #COUNTS (TABLE_NAME, ROW_COUNT) SELECT ''?'', COUNT(*) FROM ?'
SELECT TABLE_NAME, ROW_COUNT FROM #COUNTS ORDER BY TABLE_NAME, ROW_COUNT DESC
DROP TABLE #COUNTS

dbaraja.blogspot.com

Query to find particular object belonging to what database in SQL Server

Suppose, if your SQL Server having more than 10000 databases and you got a challenge to find a particular object (proc/function/table/view..etc) is in which database?

Either we have to login into each Database and check the sysobjects
or we can run a single query in master db which can query all the DB's, search what you want and bring that you...later one is the better option because it`s save the timing also...)
Use below query to find the result

Exec sp_MSforeachdb 'if EXISTS (select * from ?..sysobjects where name like ''Prods'')
Begin
Select name from sysdatabases where name like ''?''
END'
---You just need to replace "ProdsTable" with your required object name and you can very well add more conditions to that query...

How To INSERT No.Of Records By Using T-SQL

Just Create  One Table Like
Create Table Prods1 (Pid int,PName varchar(20),Qty Int)

After complete table creation, Next following below code.

declare @pid int=1
while @pid<=1000000000000000
begin
insert into prods1 values(@pid ,'Sweet'+CONVERT(varchar(20),@pid),40)
select @pid=@pid+1
end

General

How to Get the Week Number from a Date in SQL Server?
To get the week number from a date in SQL Server, you can use DATENAME Built-in Function:
SELECT DATENAME(ww, GETDATE())

How to Get the Day of the Week from a Date in SQL Server?
To get the date of the week from a date in SQL Server, you can use DATENAME Built-in Function:
SELECT DATENAME(dw, GETDATE())

How to Determine a Leap Year or not in SQL Server by using User Defined Function (UDF)?

A leap year is a year containing one additional day and lasts 366 days instead of the usual 365.
Rule to determine if a year is a leap year:
– leap year is any year that is divisible by 400 (2000, 2400 are leap years…) or by 4 (2004, 2008 are leap years…) but it must not be divisible by 100 (2100, 2200 are NOT leap years…)

We will create a User Defined Function (UDF) that accepts a DATETIME value and checks if a year is a leap year:

CREATE FUNCTION [dbo].[Udf_Rajendra_LeapYearCheck] (@MyDate DATETIME)
RETURNS varchar(50)
AS
BEGIN
DECLARE @ReturnString varchar(50)
SET @ReturnString = CASE WHEN (YEAR(@MyDate) % 4 = 0 AND YEAR(@MyDate) % 100 != 0)
OR YEAR(@MyDate) % 400 = 0
THEN 'The year is a leap year'
ELSE 'The year is NOT a leap year'
END
RETURN @ReturnString
END
GO

To use this user-defined function:
SELECT dbo.[Udf_Rajendra_LeapYearCheck] ('2015-06-30')

How to select only date or time part from DateTime in SQL Server

When you run following command:

SELECT GETDATE()

you will get a result similar to this:

2012-05-25 20:24:17.590

with time included along with date.

To select only date part from DateTime in SQL Server run one of these commands, depending of the output format needed (for date example May 25th 2012):

To select only time part from DateTime in SQL Server run one of these commands, depending of the output format needed:

Query to find the users and their permissions in SQL Server Database ?

SELECT u.name as user_name, p.state_desc, p.permission_name
FROM sys.server_permissions AS p JOIN sys.server_principals AS u
ON p.grantee_principal_id = u.principal_id

How to give Grant permissions to users to create jobs in SQL Server ?

There are roles in MSDB database that help database administrators to have better granular control over job creation, execution, and browsing:
SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole.

To grant users permission to create and schedule their own jobs, use this script:

USE [msdb]
GO
CREATE USER [UserName] FOR LOGIN [LoginName]
GO
USE [msdb]
GO
EXEC sp_addrolemember 'SQLAgentUserRole', 'UserName'
GO

To grant a domain group permission to create and schedule their own jobs, use this script:

USE [msdb]
exec sp_addrolemember 'SQLAgentUserRole', 'DomainName\GroupName'

Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own.

How to list all database files in SQL Server ?

SELECT name, physical_name AS current_file_location,*
FROM master.sys.master_files
                       (OR)
SELECT DB_NAME([dbid])AS [Database Name], fileid, [filename]
FROM sys.sysaltfiles
WHERE [dbid] > 0 AND [dbid] <> 32767
OR [dbid] = 2;

How to Disable and Enable Index in SQL Server

To disable an index in SQL Server use the following command:
USE Database_Name
GO
ALTER INDEX Index_Name ON Table_Name DISABLE;
GO

To enable a disabled index, use the following command (REBUILD enables a disabled index):
USE Database_Name
GO
ALTER INDEX Index_Name ON Table_Name REBUILD;
GO

How to list all Disabled indexes in a SQL Server database

USE Rajendra --Database Name
GO
SELECT i.name AS Index_Name, i.index_id, i.type_desc, s.name AS 'Schema_Name', o.name AS Table_Name
FROM sys.indexes i
JOIN sys.objects o on o.object_id = i.object_id
JOIN sys.schemas s on s.schema_id = o.schema_id
WHERE i.is_disabled = 1
ORDER BY
i.name
GO

How to enable, disable and check if Service Broker is enabled on a database?

SELECT name, is_broker_enabled FROM sys.databases

ALTER DATABASE test SET ENABLE_BROKER;

ALTER DATABASE test SET DISABLE_BROKER;

How to Check Failed Jobs in SQL Server?

Using Below SQL query to get failed job history in SQL Server

SELECT  'Job: ' + Job.[name] as failedjobs, Hst.[sql_message_id], Hst.[message] , Hst.[run_date], Hst.[run_time],'Hist', hst.*
FROM [msdb].dbo.sysjobhistory Hst
INNER JOIN [msdb].dbo.sysjobs Job ON Hst.[job_id] = Job.[job_id]
where hst.run_status = '0'   -- 0 = FAILED
and  convert(varchar(8), GETDATE(),112) = Hst.[run_date]
ORDER BY Job.[name],Hst.[run_date] DESC, Hst.[run_time] DESC

Important SQL Queries

1) The following SQL query will show the HostName , SQL Instance Name , SQL edition ,SQL Product Level, Standalone or Clustered type and SQL version info
SELECT
SERVERPROPERTY('MachineName') as HostName,
SERVERPROPERTY('InstanceName') as InstanceName,
SERVERPROPERTY('Edition') as Edition,
SERVERPROPERTY('ProductLevel') as ProductLevel
Case SERVERPROPERTY('IsClustered') when 1 then 'CLUSTERED' else
'STANDALONE' end as ServerType,
@@VERSION as SQLVersionDetails
2  The below SQL  query will show all of the information related to Server level configuration and sql performance related information .
SELECT * from sys.configurations order by NAME
3  Below SQL query will show the info of sql security .
SELECT sl.name, sl.denylogin, sl.isntname,sl.isntgroup, sl.isntuser
  FROM master.dbo.syslogins sl
WHERE sl.sysadmin = 1 OR sl.securityadmin = 1
4  Below SQL query will show the information of database name , compatibility level ,database recovery model and database state , is it online or offline .
SELECT name,compatibility_level,recovery_model_desc,state_desc FROM sys.databases
5. This sql query will provides the logical name and the physical location of the data/log files of all the databases available in the current SQL Server instance
SELECT db_name(database_id) as DatabaseName,name,type_desc,physical_name FROM sys.master_files
6) The database may contain filegroups other than the primary file group. The following sql query  gets executed in each database on the server and displays the file groups related results
EXEC master.dbo.sp_MSforeachdb @command1 = 'USE [?] SELECT * FROM sys.filegroups'
7) The following SQL query lists all of the databases in the server and the last day the backup happened. This will help the database administrators to check the backup jobs and also to make sure backups are happening for all the databases
SELECT db.name, case when MAX(b.backup_finish_date) is NULL then 'No Backup' else convert(varchar(100), MAX(b.backup_finish_date)) end AS last_backup_finish_date FROM sys.databases db LEFT OUTER JOIN msdb.dbo.backupset b ON db.name = b.database_name AND b.type = 'D' WHERE db.database_id NOT IN (2) GROUP BY db.name ORDER BY 2 DESC
8)The below sql query gets all the information related to the current backup location from the msdb database.
SELECT Distinct physical_device_name FROM msdb.dbo.backupmediafamily
9) SQL Server database administrators used sp_who and sp_who2 to check the current users, process and session information. These statements also provided information related to cpu, memory and blocking information related to the sessions
sp_who
Sp_who2
10)  Using below query to find out the all databases file location on sql instance.
SELECT DB_NAME([dbid])AS [Database Name], fileid, [filename]
FROM sys.sysaltfiles
WHERE [dbid] > 0 AND [dbid] <> 32767
OR [dbid] = 2;

How To Find Particular .Extension File in Any Drive Or Folder By Using SQL Stored Procedure

IF OBJECT_ID (N'dbo.RDYadav') IS NOT NULL
   DROP PROCEDURE dbo.RDYadav
GO
CREATE PROCEDURE dbo.RDYadav
@FileSpec VARCHAR(2000),
@order VARCHAR (80) = '/O-D'
AS
DECLARE @myfiles TABLE (MyID INT IDENTITY(1,1) PRIMARY KEY, FullPath VARCHAR(2000))
DECLARE @CommandLine VARCHAR(4000)
IF @order IS NOT NULL
   BEGIN
   SELECT @CommandLine =LEFT('dir "' + @FileSpec + '" /A-D /B /S '+@order,4000)
   INSERT INTO @MyFiles (FullPath)
       EXECUTE xp_cmdshell @CommandLine
   DELETE FROM @MyFiles WHERE fullpath IS NULL OR fullpath='File Not Found'
   END
SELECT fullpath FROM @MyFiles
-------------------------------------------------------------
GO
EXECUTE RDYadav 'D:\*.txt'

Usage of DBCC And DMV`s

1)    What is the use of DBCC commands?
DBCC stands for database consistency checker. There are many DBCC command in SQL Server. We generally use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.

They are grouped as:

 Maintenance: Maintenance tasks on Db, filegroup, index etc. Commands include DBCC CLEANTABLE, DBCC INDEXDEFRAG, DBCC DBREINDEX, DBCC SHRINKDATABASE, DBCC DROPCLEANBUFFERS, DBCC SHRINKFILE, DBCC FREEPROCCACHE, and DBCC UPDATEUSAGE.

Miscellaneous: Tasks such as enabling tracing, removing dll from memory. Commands include DBCC dllname, DBCC HELP, DBCC FREESESSIONCACHE, DBCC TRACEOFF, DBCC FREESYSTEMCACHE, and DBCC TRACEON.

Informational: Tasks which gather and display various types of information. Commands include DBCC INPUTBUFFER, DBCC SHOWCONTIG, DBCC OPENTRAN, DBCC SQLPERF, DBCC OUTPUTBUFFER, DBCC TRACESTATUS, DBCC PROCCACHE, DBCC USEROPTIONS, and DBCC SHOW_STATISTICS.

Validation: Operations for validating on Db, index, table etc. Commands include DBCC CHECKALLOC, DBCC CHECKFILEGROUP, DBCC CHECKCATALOG, DBCC CHECKIDENT, DBCC CHECKCONSTRAINTS, DBCC CHECKTABLE, and DBCC CHECKDB. 

2)    What are the DMV’s in SQL Server ?
Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.
Important:
Dynamic management views and functions return internal, implementation-specific state data. Their schemas and the data they return may change in future releases of SQL Server. Therefore, dynamic management views and functions in future releases may not be compatible with the dynamic management views and functions in this release. For example, in future releases of SQL Server, Microsoft may augment the definition of any dynamic management view by adding columns to the end of the column list. We recommend against using the syntax SELECT * FROM dynamic_management_view_name in production code because the number of columns returned might change and break your application.
3)     What is back process for the DBCC?
When you execute one of these DBCC commands, the Database Engine creates a database snapshot and brings it to a transactionally consistent state. The DBCC command then runs the checks against this snapshot. After the DBCC command is completed, this snapshot is dropped.
Sometimes an internal database snapshot is not required or cannot be created. When this occurs, the DBCC command executes against the actual database. If the database is online, the DBCC command uses table-locking to ensure the consistency of the objects that it is checking. This behavior is the same as if the WITH TABLOCK option were specified.
An internal database snapshot is not created when a DBCC command is executed:
•    Against master, and the instance of SQL Server is running in single-user mode.
•    Against a database other than master, but the database has been put in single-user mode by using the ALTER DATABASE statement.
•    Against a read-only database.
•    Against a database that has been set in emergency mode by using the ALTER DATABASE statement.
•    Against tempdb. In this case, a database snapshot cannot be created because of internal restrictions.
4)    Can you explain DBCC CheckDB?
DBCC CHECKDB is a Algorithm  which at  backend checks that:
1. Object Integrity
2. Linkages for text, ntext, and image pages
3. Index and data pages are correctly linked.
4. Indexes are in their proper sort order.
5. Pointers are consistent.
6. The data on each page is reasonable (Allocation Checks).
7. Page offsets are reasonable.
5)    what is the exact use of DMVs?
DMVs can be used in the gathering of baseline information and for diagnosing performance problems. Few important dmvs are:
1. sys.dm_os_performance_counters
2. sys.dm_db_index_physical_stats
3. sys.dm_db_index_usage_stats
6)    Please explain DMV Categories?

Category	Prefix
Common Language Runtime (CLR)	Sys.dm_clr_*
Database	Sys.dm_db_*
Indexing	Sys.dm_db_index_*
Database Mirroring	Sys.dm_db_mirroring_*
Execution	Sys.dm_exec_*
Full-Text Search	Sys.dm_fts_*
I/O	Sys.dm_io_*
Query Notifications	Sys.dm_qn_*
Replication	Sys.dm_repl_*
Service Broker	Sys.dm_broker_*
SQL Server Operating System	Sys.dm_os_*
Transactions	Sys.dm_tran_*
Change Data Capture	Sys.dm_cdc_*
Object	Sys.dm_sql_*
Resource Governor	Sys.dm_resource_governor_*
SQL Server Extended Events	Sys.dm_xe_* Sys.dm_cryptographic_*
Security	Sys.dm_provider_* Sys.dm_audit_*

7)    What are all the SQL Server  Dynamic Management Views(DMV) and Dynamic management functions(DMF)  available in SQL Server?
Use below query to list out all available DMVs present in a SQL Installation :-
SELECT name, type, type_desc FROM sys.system_objects WHERE name LIKE ‘dm[_]%’
ORDER BY name
8)    How many types of DMVs are there?
There are two types of dynamic management views:
a.    Server-scoped DMV: Stored in Master Database
b.    Database-scoped DMV: Specific to each database
9)    Explain DBCC inputbuffer()?
DBCC INPUTBUFFER  returns the last sql statement  issued by a client. The command requires the SPID
DBCC INPUTBUFFER (SPID)
10)    List few DMVs for space usage related information ?
sys.dm_db_file_space_usage –  Lists space usage information for each file in the database. Reports on unallocated extent page count.
sys.dm_db_session_space_usage – Broken down by each session. Lists the number of pages allocated and deallocated
sys.dm_db_task_space_usage – Broken down by each task. Lists page allocation and deallocation activity
11)    While viewing activity on SQL Server , for example, sp_who2 – the status column displays different states – RUNNABLE – SUSPENDED – RUNNING.   Could you explain the difference?
Some background information on the SQL Schedulers , will make understanding the RUNNABLE – SUSPENDED – RUNNING  model clearer.
Schedulers are made up of three parts . A thread cycles though these three parts
1) Processor
2) Waiter list – threads waiting for resources. Use Sys.dm_os_waiting_tasks to view resource waits for the resources
3) Runnable – thread has all the resources and waiting for the processor. Explore runnable status with the  sys.dm_os_schedulers and sys.dm_exec_requests  DMVs
This leads us into the RUNNABLE – SUSPENDED – RUNNING
1)      RUNNING – thread is executing on the server
2)      SUSPENDED – thread is waiting for resources to become available.
3)      RUNNABLE – the thread is waiting to execute on the processor
12)    Why does RUNNING transition to SUSPENDED ?
Thread is executing and if waiting for a resource moves to SUSPENDED into the waiter list
13)    Why does SUSPENDED  transition into RUNNABLE?
The resource is now available and moves to the bottom of the RUNNABLE queue.
14)    Why does RUNNABLE transition into RUNNING?
Top spid at head of RUNNABLE queue moves to processor
15)    List 5 inportant DMVs for Index analysis.
sys.dm_db_index_usage_stats :- Maintains counts for the range of index activity and the last performed time. Also displays statistics ob how an index is used against a query.
sys.dm_db_missing_index_details :- Returns detailed information about each missing index on a table. Information is lost at SQL Server recycle.
sys.dm_db_missing_index_columns :- Returns information about database table columns that are missing an index, excluding spatial indexes.
sys.dm_exec_query_stats :- Performance statistics for cached plans. The information is only available while the plan remains in the cache.
sys.dm_db_index_operational_stats :- Returning IO , locking , latching and access activity. Useful for identifying index hotspots , waits for read\writes to a table. Will give information about insert,update, and delete
16)    What is use of DBCC DBREINDEX?
This command will reindex your table. If the indexname is left out then all indexes are rebuilt. If the fillfactor is set to 0 then this will use the original fillfactor when the table was created.
17)    Which DBCC command is used to shrink database files?
DBCC SHRINKFILE :- This will allow you to shrink one of the database files. This is equivalent to doing a database shrink, but you can specify what file and the size to shrink it to. Use the sp_helpdb command along with the database name to see the actual file names used.
18)    Which DBCC command is used to store the Procedure cache related information?
DBCC PROCCACHE – This command will show you information about the procedure cache and how much is being used.
19)    Explain DBCC TRACEON & DBCC TRACEOFF?
DBCC TRACEON – This command will turn on a trace flag to capture events in the error log. DBCC TRACEOFF – This command turns off a trace flag.
20)    How can you check if any transaction is running on a database or not?
We can use DBCC OPENTRAN to check any running transaction on the database. It is one of the most commonly used DBCC command along with DBCC CHECKDB, DBCC SHRINKFILE, DBCC SQLPERF(logspace) etc.
21)    Can anyone predict how long DBCC Checkdb will run on any database?
As far as estimating how long DBCC CHECKDB will take to run on a given database, it’s very difficult to tell because there are so many variables involved. The following are some factors that affect DBCC CHECKDB’s run time:
•    The size of the database. This one’s not so obvious—it’s not the size of the database that matters, it’s the amount of data that’s in it.
•    The load on the system. DBCC CHECKDB is extremely resource hungry—I like to say it’s the most resource-intensive operation you can run on SQL Server. Therefore, if the server is already heavily loaded, DBCC CHECKDB will be competing for resources and will take a lot longer to run.
•    The capabilities of the system. If the database being consistency checked is very large and structurally complicated, but the server and/or I/O subsystem are heavily underpowered, this will have a knock-on effect on the ability of the server to provide the resources DBCC CHECKDB needs, slowing it down.
•    The options specified. If the WITH PHYSICAL_ONLY option is specified, the amount of processing that DBCC CHECKDB does is drastically cut down, which usually leads to a significant reduction in run time.
•    The complexity of the database schema. The more features that you use in the database, the more structures there are to be consistency checked, so DBCC CHECKDB will take longer to run.
•    The corruptions that are found. Some corruptions require deeper reprocessing of data to figure out exactly where the corruption is. This can lead to a much longer run time for DBCC CHECKDB.
•    The tempdb configuration. DBCC CHECKDB uses a lot of memory to store intermediate consistency checking data, and that storage usually spills out to the tempdb database. If tempdb isn’t configured well, it can be a bottleneck for DBCC CHECKDB and slow it down.
As you can see, there are too many factors involved to be able to make a good guess. The best way to know how long DBCC CHECKDB will take is to run it.
22)    What is the effect of DBCC CHECKDB and DBCC DBREINDEX on the Transaction log?
DBCC DBREINDEX is an offline operation is used to rebuild the indexes of a table dynamically. This operation requires enough space in the data files. If the space is not enough DBCC DBREINDEX may be unable to rebuild the indexes.
DBCC CHECKDB is used to produce a consistent view of the data by performing a physical consistency check on indexed views, validating integrity of the indexes, objects etc. in earlier versions of SQL, this required locking. Newer versions involve reading the transaction log of the oldest active transaction. REDO and UNDO of the transactions affect the volatile changes to available free space.
23)   How can DMVs help with performance tuning?
Helps to find out the queries that are causing memory or CPU pressure on your system
Helps to investigate caching, and query plan reuse
Helps to identify index usage patterns
Helps to track fragmentation in clustered indexes and heaps
Gives full details on blocking and blocked transactions
24)    What permission does a user need to access the DMV’s?
There are two types of dynamic management views and functions:
Server-scoped dynamic management views and functions (e.g OS, IO, Threads, tasks etc). These require VIEW SERVER STATE permission on the server.
Database-scoped dynamic management views and functions (e.g Index, Tables, partition, file etc). These require VIEW DATABASE STATE permission on the database.
25)    How are DMV’s and DMF’s changing the memory consumptions of SQL Server? 
consider the dm_exec_* which store the results of the current workload.
DMV’s are in-memory structures and are anyway’s used by SQL Server internally. It is with SQL Server 2005 that we started exposing them in an official manner rather than doing bit-manipulations with some DBCC commands. Hence there is nothing to be worried about the load or memory consumptions. It is not as alarming as you think.
26)    Which DMV give me query plan or I will use old method to find query plan?
Below DMVs can be used to provide query plan related information :-
sys.dm_exec_query_stats
sys.dm_exec_sql_text
sys.dm_exec_query_plan
27)    Name some Security related DMVs\DMFs.?
sys.dm_audit_actions
sys.dm_audit_class_type_map
sys.dm_cryptographic_provider_properties
sys.dm_database_encryption_keys
sys.dm_server_audit_status
28)    Mention some SQL OS related DMVs\DMFs.
sys.dm_os_buffer_descriptors
sys.dm_os_child_instances
sys.dm_os_cluster_nodes
sys.dm_os_hosts
sys.dm_os_nodes
sys.dm_os_memory_pools
sys.dm_os_performance_counters
sys.dm_os_process_memory
sys.dm_os_schedulers
sys.dm_os_memory_objects
sys.dm_os_workers
29)    Name few database related DMVs :-
sys.dm_db_file_space_usage
sys.dm_db_partition_stats
sys.dm_db_session_space_usage
sys.dm_db_task_space_usage
30)    Which DMVs are useful to gather information about database mirroring :-
sys.dm_db_mirroring_connections
sys.dm_db_mirroring_auto_page_repair
31)    What are the most important DMVs\DMFs from a DBA perspective.
Execution Related
•    sys.dm_exec_connections
•    sys.dm_exec_sessions
•    sys.dm_exec_requests
•    sys.dm_exec_cached_plans
•    sys.dm_exec_query_plans
•    sys.dm_exec_sql_text
•    sys.dm_exec_query_stats
Index Related
•    sys.dm_db_index_physical_stats
•    sys.dm_db_index_usage_stats
SQL Server Operating System
•    sys.dm_os_performance_counters
•    sys.dm_os_schedulers
•    sys.dm_os_nodes
•    sys.dm_os_waiting_tasks
•    sys.dm_os_wait_stats
I/O Related
•    sys.dm_io_virtual_file_stats
32)How to resolve Allocation and consistency Errors?
Run DBCC checkdb DBName
Repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (SomeDB).

What is purpose of database Schema?

Ø  What is Schema ?

      A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects.
                                      (OR)
We can simply telling , Schema is nothing but Collection of  Database Objects under a database pricipal.

You can assign a user login permissions to a single schema, so that the user can only access the objects they are authorized to access.

Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.

Ø  Advantages of Database Schema?
     * To group the similar tables
     * To Grant permissions on more than one table at a time
     * No need to change the owner of table(s) if the user is deleted.
     * Objects are not attached to any specific user account. So if the user account needs to be deleted we don’t have to worry about changing the objects owners.
     * It simplifies managing permissions on Schema objects. If the schema owner’s account is to be removed from the database, the ownership of the schema can be transferred to other user without breaking any code.
     * Use of Schema’s with database roles can simplify managing security. I have tried to explain it in more detail later in this post.
     * Overall, maintenance of database become easier and I will recommend the use of schemas if you’re working with more than 20 tables.
Ø  How to create Schema?
    When you create any objects in SQL Server 2005/2008/2008R2/2012/2014 they are allocated to a default schema which is “dbo” (database owner account) in most of the cases. However it is recommended to explicitly specify the schema name when creating objects. 
Syntax:To create Schema
            Create Schema <SchemaName>
Eg: 
Step-1: Use RDYadav
                Go
                Create Schema Library
Step-2: Create table Library.Books(Bid int,Bname Varchar(40))
                Create Table Library.Students(StudId int,Sname varchar(40) ) 
Ø  How to Transfer one schema into another Schema?   
Syntax: ALTER SCHEMA newschema TRANSFER oldschema.TABLEName
Eg: ALTER SCHEMA Library TRANSFER dbo.Pencils

Syntax:  To Grant schema level permissions
Grant ....on schema::[schemaName] to <username>/<rolename>

Eg: Granting permission on Library schema to Madhuri Dixit
       Use RDYadav
       Go
       GRANT SELECT on Schema::[Library] to Madhuri Dixit
       
DENY INSERT ON SCHEMA::Library TO Madhuri Dixit      


Ø  How to know the what permission having a particular user ?

      SELECT state_desc, permission_name, 'ON', class_desc,SCHEMA_NAME(major_id),'TO',USER_NAME(grantee_principal_id)  FROM sys.database_permissions AS Perm  JOIN sys.database_principals AS Prin  ON Perm.major_ID = Prin.principal_id AND class_desc = 'SCHEMA' WHERE major_id = SCHEMA_ID('Library')    AND grantee_principal_id = user_id('TestUser')

      --AND    permission_name = 'SELECT'

Database Backups and Restore-1

1) What are database backups? 

A Database backup is a copy of SQL Server data that can be used to restore and recover the data in case of any failure. A backup of SQL Server data is created at the level of a database or one or more of its files or filegroups. There is another option to take Transaction Log backups when the database recovery model of a database is Full. 

2) Types of Database backups? 

We have below type of backup available in SQL Server 2012. 

Full Backup

Differential Backup

Transaction Log Backup

Copy-Only Backup

File or Filegroup Backup

3) What is Full Database backup?

 A full backup is a backup of the entire database that contains all the data and log file records needed to recover the database to the point in time when backup completed. Full backup should be a part of backup strategy for all the business-critical databases. 

Full database backup contains the complete set of data needed to restore and recover a database to a consistent state. It serves as a baseline for all other backups.

 --Back up the AdventureWorks as full backup

BACKUP DATABASE AdventureWorks TO DISK = N'D:\AdventureWorks.bak'

4) What is Differential Backup? 

Differential backup backups up only the data that has changed since the last full backup. A differential backup is not a stand-alone backup it needs a full backup to act as a baseline. For larger databases differential backups is common in order to save space and reduce the backup time.

 In addition to being smaller and faster than full backup, a differential backup makes the restore process simpler. When you restore using differentials you must first restore the full backup followed by the most recent differential backup that was taken.

--Back up the AdventureWorks as differential backup

BACKUP DATABASE AdventureWorks TO DISK = N'c:\AdventureWorksDiff.bak' WITH DIFFERENTIAL

5) What is Transaction Log Backup?

Log backups can be taken only if the recovery model of the database is Full recovery or Bulk-logged recovery. Simple recovery model does not allow transaction log backup because the log file is truncated automatically upon database checkpoints. 

Log backups are taken between full backups to allow point-in-time recovery with the exception of log backups containing bulk-logged records. Without Transaction log backups you can restore data only till the time when the full or differential backup was taken.

--Back up the AdventureWorks transaction log

BACKUP LOG AdventureWorks TO DISK = N'c:\AdventureWorksLog.trn'

 6) What is File or File Group backup? 

Using the file or filegroup backup you can backup an entire filegroup or files within the filegroup. These backups are essential when the database size is so large that backups must be done in parts because it takes too long to backup the entire database. Another potential benefit of having filegroup backups is that if the disk on which a particular file resides fails and is replaced, just the file can be restored instead of the entire database.

BACKUP DATABASE AdventureWorks FILEGROUP='PRIMARY', FILEGROUP ='Secondary'

TO DISK ='D:\AdventureWorks_FileGroup.bak'

7) What is COPY ONLY Backup?

 Copy-only backups are introduced in SQL Server 2005 and are used to create a full database or transaction log backup without breaking the log chain. A copy-only full backup can’t be used as a basis for a differential backup, nor can you create a differential copy only backup.

 --Back up the AdventureWorks database as copy only

BACKUP DATABASE AdventureWorks TO DISK = N'c:\AdventureWorks.bak' WITH COPY_ONLY

--Back up the AdventureWorks transaction log as copy only

BACKUP LOG AdventureWorks TO DISK = N'c:\AdventureWorksLog.trn' WITH COPY_ONLY

8) What are Split Backups?

 SQL Server have one more feature to database backups can split to multiple files. Using this way SQL Server run the multiple thread of database backups for each files and can be completed faster comparatively with less time and IO. 

BACKUP DATABASE AdventureWorks

TO DISK = ‘C:\AdventureWorks_1.bak’

DISK = ‘D:\AdventureWorks_2.bak’,

DISK = ‘E:\AdventureWorks_3.bak’

GO

9) What is Mirrored backup?

 Mirrored database backups can be used to create multiple copies of the database backups on different locations. 

BACKUP DATABASE AdventureWorks

TO DISK = ‘C:\AdventureWorks.bak’

MIRROR TO DISK =  ‘D:\AdventureWorks_mirror.bak’

GO

10) What is Tail log backup? 

A tail-log backup captures any log records that have not yet been backed up (the tail of the log) to prevent work loss and to keep the log chain intact. Before you can recover a SQL Server database to its latest point in time, you must back up the tail of its transaction log. The tail-log backup will be the last backup of interest in the recovery plan for the database.

 Tail log backup is taken in below ways:

 If the database is online follow below syntax:

BACKUP LOG [database name] TO [backup device] WITH NORECOVERY

If the database is offline (example a corrupted database which does not start]

BACKUP LOG [database name] TO [backup device]  WITH CONTINUE_AFTER_ERROR

11) What is Native Backup Compression? 

Database backup compression helps in creating a database backup in a compressed format (Supported SQL Server 2008 onwards based on the Edition).  Enabling database backups to use compression can be done in one of two ways. You can either configure all backups for the instance to be compressed (it is disabled by default), or you can issue the WITH COMPRESSION command for a specific database backup. 

12) How can you enable Database compression on all the native backups? 

Backup compression can be enabled at the SQL Server instance level as below.

 USE master;

 GO

 EXEC sp_configure 'show advanced option', '1';

 RECONFIGURE

 GO

 EXEC sp_configure 'backup compression default', '1';

 RECONFIGURE WITH OVERRIDE;

 GO

 EXEC sp_configure 'show advanced option', '0';

 RECONFIGURE

 GO

13) Is it possible to add password to a backup file in SQL Server 2012 version?

WITH password option is not available any more with SQL Server 2012 onwards.

14) In which recovery model, Transaction Log backups are possible?

Transaction Log backups are possible in Full and Bulk Logged recovery model.

15) What all operations are minimally logged when the database is in Bulk Logged Recovery mode? 

Bulk import operations (bcp, BULK INSERT, and INSERT… SELECT). For more information about when bulk import into a table is minimally logged.

SELECT INTO operations.

Partial updates to large value data types, using the .WRITE clause in the UPDATE statement when inserting or appending new data. Note that minimal logging is not used when existing values are updated.

WRITETEXT and UPDATETEXT statements when inserting or appending new data into the text, ntext, and image data type columns. Note that minimal logging is not used when existing values are updated.

CREATE INDEX operations (including indexed views).

ALTER INDEX REBUILD or DBCC DBREINDEX operations.

DROP INDEX new heap rebuild (if applicable).

 16) How do you know if your database backups are restorable? 

We can use RESTORE VERIFY ONLY command to make sure that the Database backups are restorable.

 17) What is the database that has the backup and restores system tables?

 MSDB database contains information about the backup restore.

 18) What are the backup and restore system tables?  What do each of the tables do? 

Here are the backup and restore system tables and their purpose:

 backupfile – contains one row for each data file or log file backed up

backupmediafamily – contains one row for each media family

backupmediaset – contains one row for each backup media set

backupset – contains one row for each backup set

restorefile – contains one row for each restored file

restorefilegroup – contains one row for each restored filegroup

restorehistory – contains one row for each restore operation

19) For differential backups, how is the data determined for those backups? 

DCM page contains information about the extent which are changed after the Full backup. Diff. backup process reads information about the changed extents and those extents are added in the differential backup. 

20) In a situation with full, differential and transaction log backups being issued for a database, how can an out of sequence full backup be issued without interrupting the LSN’s?

 Backup with COPY ONLY option can be used in such a situation.

 21) How can I verify that backups are occurring on a daily basis? 

We can verify the backup history of the database that backups are happening or not. 

backupset table in msdb 

22) What is the meaning of the values in Type column in backupset table. 

This column tells us about the backup type.

 Backup type. Can be: 

D = Database

I = Differential database

L = Log

F = File or filegroup

G =Differential file

P = Partial

Q = Differential partial

23) What are the permissions required to perform backup? 

The user must be a member of either of the below roles 

Backup:

sysadmin – fixed server role

db_owner –  fixed database role

db_backupoperator – fixed database role

24) Is there any option to prevent All successful SQL Server backup entries from writing to the SQL Server Error Log?

 Yes – We can enable the trace flag 3226.

 25) Assume that we have to take a backup of the database with a backup size of 90 GB. There is no space available in a single disk drive instead there are 4 different drives where we have 25 GB free space on each drive. How can you perform the backup to three different drives? 

We can take backup in split backups.

 BACKUP DATABASE AdventureWorks

TO DISK = ‘D:\Backup\AdventureWorks1.bak’,

DISK = ‘E:\Backup\AdventureWorks2.bak’,

DISK = ‘F:\Backup\AdventureWorks3.bak’,

DISK = ‘G:\Backup\AdventureWorks4.bak’

 26) Explain the below Backup script?

 USE master

GO

BACKUP DATABASE [Test] TO

DISK = N'D:\ Backups\ test_full_native_1.bak'

WITH FORMAT, INIT,

NAME = N'test- Full Database Backup',

SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

 FORMAT – This option tells SQL Server whether or not to overwrite the media header information. The FORMAT option will erase any information in a backup set that already exists when the backup is initialized (NOFORMAT will preserve it).

INIT – By default, when scripting a backup generated by the Backup wizard, this parameter will be set to NOINIT, which lets SQL Server know not to initialize a media set when taking the backup and instead append any new backup data to the existing backup set. However, since we adopt the rule of one backup per backup set, it’s useful to use INIT instead, to make sure that, if a command gets run twice, we overwrite the existing set and still end up with only one backup in the set.

NAME – The NAME parameter is simply used to identify the backup set. If it is not supplied, the set will not record a name.

SKIP – Using the SKIP parameter will cause SQL Server to skip the expiration check that it normally does on the backup set. It doesn’t care if any backups existing in the backup set have been marked for availability to be overwritten.

NOREWIND – This parameter will cause SQL Server to keep a tape device open and ready for use when the backup operation is complete. This is a performance boost to users of tape drives since the tape is already at the next writing point instead of having to search for the correct position. This is obviously a tape-only option.

NOUNLOAD – When backing up to a tape drive, this parameter instructs SQL Server not to unload the tape from the drive when the backup operation is completed.

27) What are the Backup and Restore Enhancements?

 An enhancement introduced in SQL Server 2012 SP1 Cumulative Update 2 is enable backup and restore from the Windows Azure Blob storage service from SQL Server using TSQL

 28) What are the limitations with Windows Azure Blob storage service? 

The following are limitations specific to this release: 

The maximum backup size supported is 1 TB.

In this implementation, you can issue backup or restore statements by using TSQL or SMO. A backup to or restoring from the Windows Azure Blob storage service by using SQL Server Management Studio Backup or Restore wizard is not currently enabled.

29) What are the restrictions on the Database backups operations?

 Some typical examples include the following: 

You request a file backup of specific files, but one of the files is not online. The operation fails. To back up the online files, you can omit the offline file from the file list and repeat the operation.

You request a partial backup, but a read/write filegroup is offline. Because all read/write filegroups are required for a partial backup, the operation fails.

We request a full database backup, but one filegroup of the database is offline. Because all filegroups are implicitly included in a full database backup, this operation fails.To back up this database, you can use a file backup and specify only the filegroups that are online.

30) What all operations are prohibited when the database backups are running? 

Operations that cannot run during a database backup or transaction log backup include the following:

 File-management operations such as the ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options.

Shrink database or shrink file operations. This includes auto-shrink operations.

If you try to create or delete a database file while a backup operation is in progress, the create or delete operation fails.

31) What is Back up WITH CHECKSUM? 

SQL Server supports three types of checksums: a checksum on pages, a checksum in log blocks, and a backup checksum. When generating a backup checksum, BACKUP verifies that the data read from the database is consistent with any checksum or torn-page indication that is present in the database. 

The BACKUP statement optionally computes a backup checksum on the backup stream; if page-checksum or torn-page information is present on a given page, when backing up the page, BACKUP also verifies the checksum and torn-page status and the page ID, of the page. When creating a backup checksum, a backup operation does not add any checksums to pages. Pages are backed up as they exist in the database, and the pages are unmodified by backup.