Script to schedule Backup All Databases

DECLARE @name VARCHAR(100)  
DECLARE @path VARCHAR(256)
DECLARE @fileName VARCHAR(256)
DECLARE @fileDate VARCHAR(20)

SET @path = 'LOCATION '  ---Mention path

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)+'_1'

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
       SET @fileName = @path + @name + '_PROD'  + '.BAK'
       BACKUP DATABASE @name TO DISK = @fileName WITH NOFORMAT, INIT,SKIP, NOREWIND, COMPRESSION, NOUNLOAD,  STATS = 10

       FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

Which protocal requires specific hardware to use in SQL Server?

Correct answer

VIA

Explanation

VIA is a specialized protocol developed for use with specific hardware.

When you execute DBCC CHECKDB, What are all DBCC commands will be executed?

DBCC CHECKALLOC, DBCC CHECKTABLE, DBCC CHECKCATALOG are executed by DBCC CHECKDB.

what is Heath Normal Form?

A name suggested by Chris Date for Boyce-Codd normal form

Explanation

Although Boyce-Codd Normal Form is called that because it was described in a 1974 paper detailing recent work done at IBM in the USA by Ray Boyce and Ted Codd, it was originally defined in a paper by Ian Heath (of IBM in the UK) in 1971. Because of this, Chris Date wrote "it seems to me that BCNF ought by rights to be called Heath normal form. But it isn't."
Codd, E. F. "Recent Investigations into Relational Data Base Systems." IBM Research Report RJ1385 (1974)
Heath, I. "Unacceptable File Operations in a Relational Database." Proc. 1971 ACM
SIGFIDET Workshop on Data Description, Access, and Control (1971)
Date, C.J. Database in Depth: Relational Theory for Practitioners (2005) (the quoted text is on page 142)

SQL Server - Database Permissions

SELECT
prmssn.permission_name AS [Permission],
sp.type_desc,
sp.name,
grantor_principal.name AS [Grantor],
grantee_principal.name AS [Grantee]
FROM
sys.all_objects AS sp
INNER JOIN sys.database_permissions AS prmssn ON prmssn.major_id=sp.object_id AND prmssn.minor_id=0 AND prmssn.class=1
INNER JOIN sys.database_principals AS grantor_principal ON grantor_principal.principal_id = prmssn.grantor_principal_id
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE
(SCHEMA_NAME(sp.schema_id)='dbo')
ORDER BY sp.type

Humor - Holiday List of 2012

  

Date	Day	Holiday
26-Jan-2012	Thursday	Republic Day
01-May-2012	Tuesday	May Day
15-Aug-2012	Wednesday	Independence Day
02-Oct-2012	Tuesday	Gandhi Jayanthi
26-Oct-2012	Friday	Bakrid
01-Nov-2012	Thursday	Kannada Rajyotsava Day
12-Nov-2012	Monday	Naraka Chaturdashi/ Deepavali
25-Dec-2012	Tuesday	Christmas

Drop all Database Connections

Use Master
Go
Declare @dbname sysname
Set @dbname = 'DBNAME'
Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
        Execute ('Kill ' + @spid)
        Select @spid = min(spid) from master.dbo.sysprocesses
        where dbid = db_id(@dbname) and spid > @spid
End