World Search

Tuesday, November 29, 2011

HELP! - PF Details

REGIONAL OFFICE [KARNATAKA (Bangalore)] Bhavishyanidhi Bhavan, No. 13, Raja Ram Mohan Roy Road,
P.B. No. 25146, Bangalore – 560 025 (KN).
General – EPBAX : 22216566, 22216596, 22214901, 22214961
E-mail: rpfcbglr@dataone.in / rpfcbglr@vsnl.net
Designation Phone Fax R.P.F.C 080-22277700 080-22273991 P.R.O 080-22279130        22384722
- SUB REGIONAL OFFICE  [KARNATAKA (Bangalore)]
BOMMASANDRA 2nd Floor, CMC Building, Begur Road, Bommanahally, Hosur Road, Bommasandra, Bangalore – 560 068.
E-mail: srobmsbglr@dataone.in
Designation Phone Fax A.P.F.C (OIC) 080-25734492 080-25733817
K.R PURAM 36, “Lakshmi Complex”, NH-4 (Opposite Syndicate Bank), K.R. Puram,
Bangalore – 560 036. E-mail: epfokrpbglr@hotmail.com
Designation Phone Fax A.P.F.C (OIC) 080-25611955        25613382
080-25610209 P.R.O 080-25617629 -
PEENYA Plot No. S(1), 1st Cross, 1st Phase, Peenya Industrial Estate, Bangalore – 560 058.
E-mail: rpfcpeenya@dataone.in
Designation Phone Fax R.P.F.C 080-23721377 080-23721377 General 080-23721355         23721355


More on :- http://www.sarkaritel.com/ministries/min_labour/epfo_index.htm

To Check status and other info :- http://www.epfindia.com/

max degree of parallelism

 If the computer has only one processor, the max degree of parallelism value is ignored.

Value
Edition of SQL Server
1
SQL Server Express
2
SQL Server Workgroup
4
SQL Server 2008 Web
4
SQL Server 2008 Standard
32
SQL Server 2008 Enterprise running on x86 operating systems.

Full Text Indexing

All user-created databases in SQL Server 2008 and later versions are always enabled for full-text indexing.


For previous versions we can find using below method,



SELECT DATABASEPROPERTYEX('DBNAME', 'IsFulltextEnabled');
Result :- 
1 = TRUE
0 = FALSE
NULL = Input not valid

Friday, November 25, 2011

SQL Server AlwaysOn - Part 2 - Availability Groups Setup

Create Sample Database and Create Backups

First thing we need to do is connect to the primary server using SSMS and create two databases and back them up to the network share we created. In this example I'll create database RammerJammer and RollTide and then create database backups.
ssms object explorer

Specify Name

In SSMS go to Management, right click Availability Groups and click New Availability Group Wizard. Once the wizard appears click Next on the main screen and create a unique Availability Group name on the Specify Availability Group Name screen. I’ll name my group AG-Bama and then click Next.

Select Databases

On the next screen we will need to select our databases that we want added to our availability groups. This screen also has a status column that will let us know ahead of time if our databases meet the prerequisites. I’ll select both databases and click Next.
new availability group in sql

Specify Replicas

On the next screen click Add Replica… and connect to the other server (Denali2) Replica Mode can be set to Automatic Failover, High Performance, or High Safety.
  • Automatic Failover: This replica will use synchronous-commit availability mode and support both automatic failover and manual failover.
  • High Performance: This replica will use asynchronous-commit availability mode and support only forced failover (with possible data loss).
  • High Safety: This replica will use synchronous-commit availability mode and support only manual failover.
Connection Mode in Secondary Role can be set to Disallow connections, Allow only read-intent connections, or Allow all connections.
  • Disallow connections: This availability replica will not allow any connections.
  • Allow only read-intent connections: This availability replica will only allow read-intent connections.
  • Allow all connections: This availability replica will allow all connections for read access, including connections running with older clients. For this example, I’ll choose Automatic Failover and Disallow connections to my secondary role and click Next.
sql server replicas

Specify Availability Group Listener

On the next screen we’ll setup the listener. I’ll take defaults and choose Next.
specify availability group listner

Select Data Synchronization

The next screen we will need to specify the Perform initial data synchronization option and type the UNC path of the network share we created earlier and click the Next. Hopefully we’ll see SUCCESS for our validations:
select initial data synchronization

Validation

validate sql server alwayson setup

Summary

Click Next to view the summary and Finish to configure or you can click the Script button if you’d like to perform this action at a later time:

verify always setup choices

Results

During the configuration you can see the wizard complete the following: Configures endpoints Create Availability Group Create Availability Group Listener Join secondary replica to the Availability Group Create a full backup of DB1 Restore DB1 to secondary server Backup log of DB1 Restore DB1 log to secondary server Join DB1 to Availability Group on secondary server Create a full backup of DB2 Restore DB2 to secondary server Backup log of DB2 Restore DB2 log to secondary server Join DB2 to Availability Group on secondary server.  When done click Close to close the wizard. To
alwayson installation results

View the Availability Group in SSMS

In SSMS, drill down to Management and Availability Groups. Here you will see your Availability Group. Drill down one farther and you’ll see Availability Replicas, Databases, and Group Listeners.
ssms availability groups
To add a database, replica or view the dashboard right click on the Availability Group Name.
add database replica
The dashboard will help you determine if your databases are Synchronized and Healthy.
alwayson dashboard
For demo purposes I’ll change my Connection mode in Secondary Role from Disallow all connections to Allow all connections (In SSMS, right click Availability Group and go to Properties) This will allow me to connect to my secondary server and open the databases.
ssms alwayson databases
If we had left this set to disallow all connections when we try to open the secondary servers databases we would have received a database is inaccessible error. I am going to create a table called NatlChamps on my primary server in the RammerJammer database.
ssms always on sql server databases
Within seconds if I switch over to my secondary server, the NatlChamps table is already created.
sql server alwayson database list
AlwaysOn is an excellent new feature of SQL Server and I can't wait to use this in my production environments.

SQL Server AlwaysOn Feature - DENALI

Environment:  I have setup two separate VM’s running in VM Workstation with Windows 2008 R2 Enterprise Edition and SQL Server 2008 R2 Enterprise named Denali and Denali2.
First thing we need to do is make sure both servers have .NET Framework 3.0 Features and Failover Clustering installed. To do this, go to Server Manager, Features, Add Feature. Check .NET Framework 3.0 and Failover Clustering and Install as shown below.
alwayson features
Once .NET and Failover Clustering are installed we can configure the cluster. Go to Control Panel, Administrative Tools, Failover Cluster Manager and click Validate a Configuration.
cluster validate configuration
On the “Validate A Configuration Wizard” page click Next, then enter the names of the SQL Servers you want to configure. In this example we are using Denali and Denali2.
cluster configuration wizard
Click Next to run through some validation tests. If you receive any errors click View Report to view the errors.
cluster validation report
As you can see from above I received errors on my validation, because I am only using one network card. This means I have a single point of failure. In a production environment you would want to fix this, but this is only a warning and does not stop us from configuring clustering. From the main screen click “Create a Cluster
failover cluster management
After clicking "Create a Cluster" and clicking Next on the main page you should see the "Access Point for Administering the Cluster". Here is where you will type in the name of your cluster. This doesn’t need to be your server name. Use a name to distinguish this cluster from other clusters. In this example, I’ll use DenaliCluster.
cluster access point configuration
Click Next to install the cluster.
create new cluster
After the creation, you can see the install report as shown below.
cluster created
From the Failover Cluster Management screen you can see I have an error regarding my storage solution (no quorum drive) by the yellow exclamation point over my Cluster Name. No worries in a test environment, but you would want to address this in production.
FCMTree
After creating the cluster, we’ll need to install SQL Server Denali on both servers (in this example, I'm using Denali CTP3). On the installation screen we could click "New SQL Server failover cluster installation", but since we're only using Availability Groups we can just use the standard installation, "New SQL Server stand-alone installation…"
sql server installation center
Accept all defaults, choose SQL Server Feature Installation and select Database Engine Services and Management Tools – Complete.
setup sql server role
sql server features
Accept defaults, specify a domain user on the Server Configuration screen and click Next. On the Database Engine Configuration screen specify Mixed Mode Authentication and enter a password for the sa account.
sql server configuration setup
sql denali server setuo
Click Next, Next, Next, Install. In a few minutes you should have a working copy of SQL Denali. Once Denali is installed on both servers we’ll need to do a few things to configure it.
Enable TCP/IP: Click Start, Programs, Microsoft SQL Server Denali CTP3, Configuration Tools, SQL Server Configuration Manager. Click SQL Server Network Configuration, Protocols for MSSQLSERVER, and right click TCP/IP and enable.
sql configuration manager
Enable AlwaysON: In SQL Server Configuration Manager, click SQL Server Services, right click SQL Server (MSSQLSERVER) and choose Properties. Click the AlwaysOn High Availability tab and Enable AlwaysOn Availability Groups.
alwayson high availability
Add Startup Parameter: In SQL Server Configuration Manager, click SQL Server Services, right click SQL Server (MSSQLSERVER) and choose Properties. Click the Startup Parameters tab, type –T9532 in the startup parameter box and click Add and Restart Services.
sql startup parameters
Setup Logins: Open SSMS and connect to one of your instances. Click Security, right click Logins and click New Login. Create logins for the accounts you used for your SQL Services. For example, I used SQLBrady/SQLUser for my service accounts so I'm going to make a login for this user and give this login sysadmin rights from the Server Roles page. Do this for both instances.
sql server logins creation
Create file share for backups and replicas: If you've ever setup log shipping you know you have to have a file share on a server and this is the same for this new feature. Create a file share on one of the servers and give read/write access to all your service accounts. Once clustering is setup, Denali is installed and configured, we can create our first Availability Group for AlwaysOn.

Certification Path

Wednesday, November 23, 2011

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

Tuesday, November 22, 2011

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

Thursday, November 17, 2011

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

Script to identify tables without Primary Key

SELECT name
  FROM sysobjects
 WHERE xtype='U'
   AND objectproperty(object_id(name),'TableHasPrimaryKey')= 0
 ORDER BY 1


or

USE DatabaseName;
GO
SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
ORDER BY SchemaName, TableName;
GO

Move Temp DB

use master
go
Alter database tempdb modify file (name = tempdev, filename = 'C:\Workarea\MDFLDF\2005\MDF\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'C:\Workarea\MDFLDF\2005\MDF\templog.ldf')
go

Who Used SQL Server WHEN

SELECT
   I.NTUserName,
   I.loginname,
   I.SessionLoginName,
   I.databasename,
   Min(I.StartTime) as first_used,
   Max(I.StartTime) as last_used,
   S.principal_id,
   S.sid,
   S.type_desc,
   S.name
FROM
   sys.traces T CROSS Apply
   ::fn_trace_gettable(CASE
                          WHEN CHARINDEX( '_',T.[path]) <> 0 THEN
                               SUBSTRING(T.PATH, 1, CHARINDEX( '_',T.[path])-1) + '.trc'
                          ELSE T.[path]
                       End, T.max_files) I LEFT JOIN
   sys.server_principals S ON
       CONVERT(VARBINARY(MAX), I.loginsid) = S.sid
WHERE
    T.id = 1 And
    I.LoginSid is not null
Group By
   I.NTUserName,
   I.loginname,
   I.SessionLoginName,
   I.databasename,
   S.principal_id,
   S.sid,
   S.type_desc,
   S.name

Search all columns of all tables in a database for the keyword

 CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT


CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
      ) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results
END


--To search all columns of all tables in Pubs database for the keyword "Computer"
EXEC SearchAllTables 'Computer'
GO

Rebuild and Reorganize Script

--Start of variables

DECLARE @dbName sysname
DECLARE @tableName sysname
DECLARE @databaseID INT
DECLARE @objectID INT
DECLARE @indexID INT
DECLARE @fragPercent INT
DECLARE @cmd VARCHAR(5000)
DECLARE @objectName sysname
DECLARE @indexName sysname
-- End of Variables

-- The databaseCursor cursor selects the user databases

DECLARE databaseCursor CURSOR FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('master','msdb','model','tempdb','AdventureWorks2008','AdventureWorksDW','AdventureWorksDW2008','AdventureWorksLT','AdventureWorksLT2008','SQL2008SBS', 'SQL2008SBSFS')

OPEN databaseCursor

FETCH NEXT FROM databaseCursor INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

/* Selecting the required columns in the statsInfoTable table
         for storing the selected values for the database. The table will be dropped
and recreated for each database*/


SELECT database_id, object_id, index_id, avg_fragmentation_in_percent
INTO statsInfoTable
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10 and index_id > 0

-- Declaring cursor for selecting each value from the statsInfoTable

DECLARE physicalStatsCursor CURSOR FOR
SELECT database_id, object_id, index_id, avg_fragmentation_in_percent
FROM statsInfoTable

OPEN physicalStatsCursor

FETCH NEXT FROM physicalStatsCursor INTO @databaseID, @objectID,@indexID,@fragPercent

WHILE @@FETCH_STATUS = 0

BEGIN

-- Select the Table name and Index name. The selected values will be
-- used for building the dynamic query for the Reorganize or Rebuild.
-- Reorganize is the online operation and Rebuild is the offline operation

SELECT @objectName = s.name + '.' + t.name FROM sys.objects t
JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE t.object_id = @objectID

SELECT @indexName = i.name
FROM sys.indexes i
JOIN sys.objects o
ON i.object_id = o.object_id
WHERE o.object_id = @objectID AND i.index_id = @indexID

IF (@fragPercent > 10 AND @fragPercent < 30)

BEGIN

PRINT 'Rorganizing Index' + @indexName + 'ON' +  @objectName
SET @cmd = 'ALTER INDEX' + ' ' + @indexName + ' ' + 'ON' + ' ' +  @objectName + ' ' + 'REORGANIZE'
PRINT @cmd
EXECUTE (@cmd)

END

IF (@fragPercent = 30 OR @fragPercent > 30)

BEGIN

PRINT 'Rebuilding Index' + @indexName + ' ' + 'ON' + ' ' +  @objectName
SET @cmd = 'ALTER INDEX' + ' ' + @indexName + ' ' + 'ON' + ' ' + @objectName + ' ' + 'REBUILD'
PRINT @cmd
EXECUTE (@cmd)

END

FETCH NEXT FROM physicalStatsCursor INTO @databaseID, @objectID,@indexID,@fragPercent

END

CLOSE physicalStatsCursor

DEALLOCATE physicalStatsCursor

FETCH NEXT FROM databaseCursor INTO @dbName

DROP TABLE statsInfoTable

END

CLOSE databaseCursor

DEALLOCATE databaseCursor

--select * from statsInfoTable

Inventory Base LIne Script

--Step 1: Setting NULLs and quoted identifiers to ON and checking the version of SQL Server
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'prodver') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)                      
drop table prodver
create table prodver ([index] int, Name nvarchar(50),Internal_value int, Charcater_Value nvarchar(50))
insert into prodver exec xp_msver 'ProductVersion'
if (select substring(Charcater_Value,1,1)from prodver)!=8
begin

                 
-- Step 2: This code will be used if the instance is Not SQL Server 2000

Declare @image_path nvarchar(100)                      
Declare @startup_type int                      
Declare @startuptype nvarchar(100)                      
Declare @start_username nvarchar(100)                      
Declare @instance_name nvarchar(100)                      
Declare @system_instance_name nvarchar(100)                      
Declare @log_directory nvarchar(100)                      
Declare @key nvarchar(1000)                      
Declare @registry_key nvarchar(100)                      
Declare @registry_key1 nvarchar(300)                      
Declare @registry_key2 nvarchar(300)                      
Declare @IpAddress nvarchar(20)                      
Declare @domain nvarchar(50)                      
Declare @cluster int                      
Declare @instance_name1 nvarchar(100)                      
-- Step 3: Reading registry keys for IP,Binaries,Startup type ,startup username, errorlogs location and domain.
SET @instance_name = coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');                      
If @instance_name!='MSSQLSERVER'                      
Set @instance_name=@instance_name                    

    Set @instance_name1= coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');                      
If @instance_name1!='MSSQLSERVER'                      
Set @instance_name1='MSSQL$'+@instance_name1                      
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;                      
                     
Set @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name1;                      
SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';                      
If @registry_key is NULL                      
set @instance_name=coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');                      
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;                      

SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';                      
SET @registry_key1 = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\supersocketnetlib\TCP\IP1';                      
SET @registry_key2 = N'SYSTEM\ControlSet001\Services\Tcpip\Parameters\';                      
                     
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ImagePath',@value=@image_path OUTPUT                      
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='Start',@value=@startup_type OUTPUT                      
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ObjectName',@value=@start_username OUTPUT                      
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key,@value_name='SQLArg1',@value=@log_directory OUTPUT                      
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='IpAddress',@value=@IpAddress OUTPUT                      
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key2,@value_name='Domain',@value=@domain OUTPUT                      
                     
Set @startuptype= (select 'Start Up Mode' =                      
CASE                      
WHEN @startup_type=2 then 'AUTOMATIC'                      
WHEN @startup_type=3 then 'MANUAL'                      
WHEN @startup_type=4 then 'Disabled'                      
END)                      
                     
--Step 4: Getting the cluster node names if the server is on cluster .else this value will be NULL.

declare @Out nvarchar(400)                      
SELECT @Out = COALESCE(@Out+'' ,'') + Nodename                      
from sys.dm_os_cluster_nodes                      
                     
-- Step 5: printing Server details
                     
SELECT                    
@domain as 'Domain',                    
serverproperty('ComputerNamePhysicalNetBIOS') as 'MachineName',                    
CPU_COUNT as 'CPUCount',
(physical_memory_in_bytes/1048576) as 'PhysicalMemoryMB',                    
@Ipaddress as 'IP_Address',                    
@instance_name1 as 'InstanceName',
@image_path as 'BinariesPath',                    
@log_directory as 'ErrorLogsLocation',                    
@start_username as 'StartupUser',                    
@Startuptype as 'StartupType',                    
serverproperty('Productlevel') as 'ServicePack',                    
serverproperty('edition') as 'Edition',                    
serverproperty('productversion') as 'Version',                    
serverproperty('collation') as 'Collation',                    
serverproperty('Isclustered') as 'ISClustered',                    
@out as 'ClusterNodes',                    
serverproperty('IsFullTextInstalled') as 'ISFullText'                    
From sys.dm_os_sys_info                      
                   

-- Step 6: Printing database details

SELECT                    
serverproperty ('ComputerNamePhysicalNetBIOS') as 'Machine'                    
,@instance_name1 as InstanceName,                    
(SELECT 'file_type' =                    
CASE                    
WHEN s.groupid <> 0 THEN 'data'                    
WHEN s.groupid = 0 THEN 'log'                    
END) AS 'fileType'                    
, d.dbid as 'DBID'                    
, d.name AS 'DBName'                    
, s.name AS 'LogicalFileName'                    
, s.filename AS 'PhysicalFileName'                    
  , (s.size * 8 / 1024) AS 'FileSizeMB' -- file size in MB                    
  , d.cmptlevel as 'CompatibilityLevel'                    
  , DATABASEPROPERTYEX (d.name,'Recovery') as 'RecoveryModel'                    
  , DATABASEPROPERTYEX (d.name,'Status') as 'DatabaseStatus' ,                  
  --, d.is_published as 'Publisher'                    
  --, d.is_subscribed as 'Subscriber'                    
  --, d.is_distributor as 'Distributor'
  (SELECT 'is_replication' =                    
CASE                    
WHEN d.category = 1 THEN 'Published'                    
WHEN d.category = 2 THEN 'subscribed'                    
WHEN d.category = 4 THEN 'Merge published'
WHEN d.category = 8 THEN 'merge subscribed'
Else 'NO replication'
END) AS 'Is_replication'                    
  , m.mirroring_state as 'MirroringState'                    
--INTO master.[dbo].[databasedetails]                    
FROM                    
sys.sysdatabases d INNER JOIN sys.sysaltfiles s                    
ON                    
d.dbid=s.dbid                    
INNER JOIN sys.database_mirroring m                    
ON                    
d.dbid=m.database_id                    
ORDER BY                    
d.name                    
       
       
       


--Step 7 :printing Backup details                    

Select distinct                          
b.machine_name as 'ServerName',                      
b.server_name as 'InstanceName',                      
b.database_name as 'DatabaseName',                          
d.database_id 'DBID',                          
CASE b.[type]                                
WHEN 'D' THEN 'Full'                                
WHEN 'I' THEN 'Differential'                                
WHEN 'L' THEN 'Transaction Log'                                
END as 'BackupType'                              
--INTO [dbo].[backupdetails]                      
from sys.databases d inner join msdb.dbo.backupset b                          
On b.database_name =d.name                      


End
else

begin



--Step 8: If the instance is 2000 this code will be used.

declare @registry_key4 nvarchar(100)                      
declare @Host_Name varchar(100)
declare @CPU varchar(3)
declare @nodes nvarchar(400)
set @nodes =null /* We are not able to trap the node names for SQL Server 2000 so far*/
declare @mirroring varchar(15)
set @mirroring ='NOT APPLICABLE' /*Mirroring does not exist in SQL Server 2000*/
Declare @reg_node1 varchar(100)
Declare @reg_node2 varchar(100)
Declare @reg_node3 varchar(100)
Declare @reg_node4 varchar(100)

SET @reg_node1 = N'Cluster\Nodes\1'
SET @reg_node2 = N'Cluster\Nodes\2'
SET @reg_node3 = N'Cluster\Nodes\3'
SET @reg_node4 = N'Cluster\Nodes\4'

Declare @image_path1 varchar(100)
Declare @image_path2 varchar(100)
Declare @image_path3 varchar(100)
Declare @image_path4 varchar(100)

set @image_path1=null
set @image_path2=null
set @image_path3=null
set @image_path4=null


Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node1, @value_name='NodeName',@value=@image_path1 OUTPUT
Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node2, @value_name='NodeName',@value=@image_path2 OUTPUT
Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node3, @value_name='NodeName',@value=@image_path3 OUTPUT
Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node4, @value_name='NodeName',@value=@image_path4 OUTPUT

    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'nodes') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)                      
drop table nodes
Create table nodes (name varchar (20))
 insert into nodes values (@image_path1)
 insert into nodes values (@image_path2)
 insert into nodes values (@image_path3)
 insert into nodes values (@image_path4)
 --declare @Out nvarchar(400)                      
 --declare @value nvarchar (20)
 SELECT @Out = COALESCE(@Out+'/' ,'') + name from nodes where name is not null
 
-- Step 9: Reading registry keys for Number of CPUs,Binaries,Startup type ,startup username, errorlogs location and domain.

SET @instance_name = coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
IF @instance_name!='MSSQLSERVER'

BEGIN
set @system_instance_name=@instance_name
set @instance_name='MSSQL$'+@instance_name

SET @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name;
SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';
SET @registry_key1 = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\Setup';
SET @registry_key2 = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\';
SET @registry_key4 = N'SYSTEM\CurrentControlSet\Control\Session Manager\Environment'


EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='SQLPath',@value=@image_path OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='Start',@value=@startup_type OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ObjectName',@value=@start_username OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key,@value_name='SQLArg1',@value=@log_directory OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key2,@value_name='Domain',@value=@domain OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key4,@value_name='NUMBER_OF_PROCESSORS',@value=@CPU OUTPUT                      


END

IF @instance_name='MSSQLSERVER'
BEGIN
SET @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name;
SET @registry_key = N'Software\Microsoft\MSSQLSERVER\MSSQLServer\Parameters';
SET @registry_key1 = N'Software\Microsoft\MSSQLSERVER\Setup';
SET @registry_key2 = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\';
SET @registry_key4 = N'SYSTEM\CurrentControlSet\Control\Session Manager\Environment'                                            



EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='SQLPath',@value=@image_path OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='Start',@value=@startup_type OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ObjectName',@value=@start_username OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key,@value_name='SQLArg1',@value=@log_directory OUTPUT
--EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='IpAddress',@value=@IpAddress OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key2,@value_name='Domain',@value=@domain OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key4,@value_name='NUMBER_OF_PROCESSORS',@value=@CPU OUTPUT                        

END
set @startuptype= (select 'Start Up Mode' =
CASE
WHEN @startup_type=2 then 'AUTOMATIC'
WHEN @startup_type=3 then 'MANUAL'
WHEN @startup_type=4 then 'Disabled'
END)

--Step 10 : Using ipconfig and xp_msver to get physical memory and IP

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'tmp') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)                    
DROP TABLE tmp
create table tmp (server varchar(100)default cast( serverproperty ('Machinename') as varchar),[index] int, name sysname,internal_value int,character_value varchar(30))
insert into tmp([index],name,internal_value,character_value) exec xp_msver PhysicalMemory

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'ipadd') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)                    
drop table ipadd
create table ipadd (server varchar(100)default cast( serverproperty ('Machinename') as varchar),IP varchar (100))
insert into ipadd (IP)exec xp_cmdshell 'ipconfig'
delete from ipadd where ip not like '%IP Address.%' or IP is null


-- Step 11 : Getting the Server details

SELECT  top 1            
@domain as 'Domain',                    
serverproperty('Machinename') as 'MachineName',                    
@CPU as 'CPUCount',
cast (t.internal_value as bigint) as PhysicalMemoryMB,
cast(substring ( I.IP , 44,41) as nvarchar(20))as IP_Address,
serverproperty('Instancename') as 'InstanceName',                    
@image_path as 'BinariesPath',                    
@log_directory as 'ErrorLogsLocation',                    
@start_username as 'StartupUser',                    
@Startuptype as 'StartupType',                    
serverproperty('Productlevel') as 'ServicePack',                    
serverproperty('edition') as 'Edition',                    
serverproperty('productversion') as 'Version',                    
serverproperty('collation') as 'Collation',                    
serverproperty('Isclustered') as 'ISClustered',                    
@Out as 'ClustreNodes',
serverproperty('IsFullTextInstalled') as 'ISFullText'                    
From tmp t inner join IPAdd I
on t.server = I.server

-- Step 12 : Getting the instance details

SELECT                    
serverproperty ('Machinename') as 'Machine',                    
serverproperty ('Instancename') as 'InstanceName',                    
(SELECT 'file_type' =                    
CASE                    
WHEN s.groupid <> 0 THEN 'data'                    
WHEN s.groupid = 0 THEN 'log'                    
END) AS 'fileType'                    
, d.dbid as 'DBID'                    
, d.name AS 'DBName'                    
, s.name AS 'LogicalFileName'                    
, s.filename AS 'PhysicalFileName'                    
, (s.size * 8 / 1024) AS 'FileSizeMB' -- file size in MB                    
,d.cmptlevel as 'CompatibilityLevel'                    
, DATABASEPROPERTYEX (d.name,'Recovery') as 'RecoveryModel'                    
, DATABASEPROPERTYEX (d.name,'Status') as 'DatabaseStatus' ,                  
(SELECT 'is_replication' =                    
CASE                    
WHEN d.category = 1 THEN 'Published'                    
WHEN d.category = 2 THEN 'subscribed'                    
WHEN d.category = 4 THEN 'Merge published'
WHEN d.category = 8 THEN 'merge subscribed'
Else 'NO replication'
 END) AS 'Is_replication',
 @Mirroring as 'MirroringState'
FROM                    
sysdatabases d INNER JOIN sysaltfiles s                    
ON                    
d.dbid=s.dbid                    
ORDER BY                    
d.name                    

-- Step 13 : Getting backup details

Select distinct                          
b.machine_name as 'ServerName',                      
b.server_name as 'InstanceName',                      
b.database_name as 'DatabaseName',                          
d.dbid 'DBID',                          
CASE b.[type]                                
WHEN 'D' THEN 'Full'                                
WHEN 'I' THEN 'Differential'                                
WHEN 'L' THEN 'Transaction Log'                                
END as 'BackupType'                              
from sysdatabases d inner join msdb.dbo.backupset b                          
On b.database_name =d.name


-- Step 14: Dropping the table we created for IP and Physical memory

Drop Table TMP
Drop Table IPADD
drop table Nodes

end
go

-- Step 15 : Setting Nulls and Quoted identifier back to Off

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO