T-SQL to find Backup or Restore Progress

Here is a script that comes handy while performing a huge database Backup or Restore. This script provides the details on the progress of the Backup or Restore operation including the estimated finish time.

SELECT 
   session_id AS SPID, command AS [Command], a.text AS Query, start_time AS [Start Time], 
   percent_complete AS [Percent Complete],
   dateadd(second,estimated_completion_time/1000, getdate()) AS [Estimated Completion Time]
FROM sys.dm_exec_requests r 
   CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 
WHERE r.command like 'BACKUP%' OR r.command like 'RESTORE%') 

Trouble in Opening MDF File because it Says SQL Error 5171? - A guest Post by Andre Williams

MS SQL Server is the most widely used and deployed database server in organizations. But, there are times when the SQL Server database gets corrupted due to various reasons. Error messages like SQL Error 1571 are also frequent with SQL Server. Let’s learn more about the SQL error 1571, its reasons and solutions.

Symptoms of SQL Error 5171:
With SQL Error 5171, you may face failures while logging in to SQL Server, restoring SQL database files, creating a tempdb database, and attaching MDF files successfully to the SQL Server database. Instead you will receive an error saying – “.mdf is not a primary database file. (Microsoft SQL server 5171)”

Possible Reasons for SQL Error 5171:
MDF file saves data in the form of pages, and each page occupies space of 8KB. The initial first page is the header page containing important database details such as signature, file size, compatibility, and much more. Rest all the other pages stores the actual data.
When the header page or related page of the file does not get recognized by the SQL Server database, it results in the SQL Error 1571 as the entire MDF is not considered to be a valid file.

Solution to Fix SQL Error 5171:
There are multiple reasons due to which SQL Error 5171 occurs. Some scenarios are mentioned below with their possible fix solution.

Scenario 1:
Usage of a mirror database in MS SQL Server by a user encounters the Error 5171, when database is set online by executing ALTER DATABASE command

Scenario 2:
When the SQL Server is upgraded to a latest or newer version, there are possible chances of Error 5171. As, you will have to first detach the database and then upgrade it to the new version. Hence, when you will try to attach it back to the MS SQL Server, it will fail to do so and error 5171 might encounter.

Below mentioned are the possible solutions for this error:

Method 1: For database mirroring
Step 1: First set, the principal database
Step 2: Use ALTER DATABASE MODIFY FILE command to modify the information.
Step 3: Now, stop the SQL server instance.
Step 4: Copy MDF and LDF files in a separate directory
Step 5: Now, restart SQL Server and attach the database files

Method 2: For attaching the database
Step 1: To troubleshoot the error, use the sp_attach_db command
Step 2: The command will attach the detached database files after upgrading is completed.
Please note: This method will work only in the case where you have used the sp_detach_db command to detach the database

Method 3: Automated Method to FIX SQL Server Error 5171
The above methods can easily remove the SQL Error 5171. However, if you still face the error after trying the above workaround methods, then you can opt for the automated solution. You can use Kernel for SQL Database Recovery tool, one of the most recommended methods by the database experts. The tool smoothly repairs and recovers all the database objects of corrupt or inaccessible MDF and NDF files. It flawlessly recovers large-sized MDF and NDF files.

Final Words:

You can always opt to use the manual methods for SQL recovery if you are an experienced database professional. But if you’re a naïve user of SQL database and you are not skilled enough to understand the errors of the database, then we suggest you restore your database files with the help of the automated solution.  

Hope the solutions help you to resolve the issues related to MS SQL Server. If you have any queries, please mention in the comments. We will get back to you with a possible resolution.

About Andre Williams

Andre Williams, with more than three year's experience in SQL related technologies, contributes articles, blogs, and how-to tips regularly.

Social Media Profiles

Facebook: https://www.facebook.com/andre.williams1986

LinkedIn: https://www.linkedin.com/in/andrewilliams594

Kernel for SQL Database - Product Review - A guest Post by Andre Williams

Most of the organizations that handle large amount of data is very much dependent on MS SQL Server for their operations. However, an MS SQL database corruption, can cause a lot of troubles to the organization owing to the criticality of the data contained in the database. Fortunately, many SQL Server recovery tools are available in the market. We will try to understand the features, working, and benefits & drawbacks of a popular, SQL recovery tool—the Kernel for SQL Database.

Product information

• Name - Kernel for SQL Database
• Description – MDF file recovery software (recovers SQL data from corrupt databases)
• Developers – Lepide Software

Company information 

Lepide Software, the developers of Kernel range of products, is known for sophisticated IT security, management, and recovery solutions. Its Kernel range, designed and developed for small and mid-sized organizations offers cost-effective solutions for most of the day-to-day issues faced by IT organizations. These products help organizations in email migration, email recovery, data recovery, file repair, and more. The company has a loyal customer-base spanning across the world owing to its quality products and dedicated customer support.

Notable features of the software

• Automatically detects the SQL version of the database
• Fixes almost all types of SQL corruption issues
• Recovers all type of SQL data—tables, triggers, views, stored procedures, constraints, indexes, rules, and user defined functions/data types
• Works even with large SQL databases
• Recovers data even when the SQL Server is live
• Supports MS SQL 2000, 2005 and 2008, 2008R2 and 2012 versions

Kernel for SQL Database - versions available

• Demo version
  The demo version, meant educate users on SQL recovery process, is completely free. But it is not equipped with the saving facility.
• Licensed version
  Licensed version is the fully featured version of the software and is available on purchase only. It can save all the recovered data.

How to use Kernel for SQL Database to repair corrupt MDF files?

The hallmark of Kernel for SQL Database is the simplicity of its operation. The steps are simple, the interface is intuitive, and recovery is quick. The major steps of the SQL database recovery process are outlined here:

1. Selecting MDF file for recovery
   The Open button (in the main page of the software) opens the Select SQL Database dialogue box. In this dialogue box, one can browse for the corrupt database. If users are sure about the SQL server version, the can select the version. Otherwise, they can use the auto detect option. Finally, the recovery process can be initiated by clicking the Recover button.
   
2. Previewing the recovered data
   Once the software displays the recovered objects, the users can click an object on the left tree to preview its data.
   
3. Saving the recovered data
   Two saving options are available—saving to SQL Server and saving to batch file. The first option allows to save directly to an SQL Server either through server authentication or through Windows authentication. In the Batch File option, the data is stored in script files in the desired location (which can be copied to a new database).
   

Benefits and drawbacks of Kernel for SQL Database

Benefits:

The important benefits of Kernel for SQL Database are:

• Friendly interface
  The software interface itself provides information on how to execute the recovery process. So even non-technical users can perform recovery without any help. 
• Preview facility
  Everything recovered can be viewed on the software’s preview pane before saving. Previewing helps users to know about the recoverability of the data.
• Automatic detection of SQL Version
  If the users know the version of the SQL, they can select it. But if they do not know, the software can automatically detect the SQL version.
• Multiple saving options
  The software saves the recovered MDF file files to SQL Server directly. In addition, it can save files in batch file, which users can copy to a new database when required.

Drawbacks:

The major drawback of Kernel for SQL Database is:

• No saving option in the trial version
  The trial version of Kernel for SQL Database cannot save the data. With this version, users can only preview the data. 

Final verdict

Kernel for SQL Database is a great tool for SQL recovery. As we understand, users are happy with the product. Still the product can be improved in some areas. Also, it is good if Lepide can add some limited saving facility to the trial version. https://www.nucleustechnologies.com/sql-recovery.html 

Performance rating

Overall rating for the software is 4/5

About Andre Williams

Andre Williams, with more than three year's experience in SQL related technologies, contributes articles, blogs, and how-to tips regularly.

Social Media Profiles

Facebook: https://www.facebook.com/andre.williams1986

LinkedIn: https://www.linkedin.com/in/andrewilliams594

Mirrored Databases disconnect and goes in-recovery state after a server restart

Recently, I had a situation where a production server was restarted and soon after the restart we found that the database mirroring was disconnected.
And as a bonus, one of the mirrored database was hung in recovery state.

When I investigated further, I found the below message in the SQL Server Logs.

Bypassing recovery for database '<DB Name>' because it is marked as an inaccessible database mirroring database. A problem exists with the mirroring session. The session either lacks a quorum or the communications links are broken because of problems with links, endpoint configuration, or permissions (for the server account or security certificate). To gain access to the database, figure out what has changed in the session configuration and undo the change.

This clearly says the connection between the principal and  the mirror is not established properly and the combination of a Mirrored Database with a big log file and a server restart caused the "In Recovery State" of the mirrored database.
To fix this error, I had to Restart the Database Mirroring Endpoint.

--To Stop the Endpoint
ALTER ENDPOINT <EndPoint Name> STATE=STOPPED

--To Start the Endpoint
ALTER ENDPOINT <EndPoint Name> STATE=STARTED

Data source name not found and no default driver specified - SSIS Error

Sometimes you might get the below error when you are trying to run a SSIS package which is trying to connect to a 32 bit system from a 64 bit system/driver.

[DataReader Source [320]] Error: System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)

To fix this error:

1. Go to "Solution Explorer"
2. Right-Click on the Project and choose "Properties"
3. Expand "Configuration Properties" and choose "Debugging"
4. Set the "Run64BitRuntime" option to "False"
5. Click "ok" and save the project

T-SQL to get the list of objects modified in x number of days

USE SansSQL; -- Change the Database Name
GO
DECLARE @Days int
SET @Days=300 -- Specify the number of days
SELECT name AS ObjectName
  ,SCHEMA_NAME(schema_id) AS SchemaName
  ,type_desc AS ObjectType
  ,create_date AS ObjectCreatedOn
  ,modify_date As ObjectModifiedOn
FROM sys.objects
WHERE modify_date > GETDATE() - @Days
ORDER BY modify_date;
GO

Connect to SSIS service on machine failed: Error loading type library/DLL

When connecting to an SSIS instance on a newly installed system you might be sometimes presented with the error "Connect to SSIS service on machine <Machine Name> failed: Error loading type library/DLL"

T-SQL Query to display all currently executing user commands

Here is a T-SQL Query to display all currently executing user commands on an SQL Server Instance

SELECT r.session_id
            ,status
            ,SUBSTRING(qt.text,r.statement_start_offset/2, 
                  (CASE WHEN r.statement_end_offset = -1 
                  THEN len(convert(nvarchar(max), qt.text)) * 2 
                  ELSE r.statement_end_offset end - r.statement_start_offset)/2) 
            as query_text
            ,qt.dbid
            ,qt.objectid
            ,r.cpu_time
            ,r.total_elapsed_time
            ,r.reads
            ,r.writes
            ,r.logical_reads
            ,r.scheduler_id
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt
WHERE r.session_id > 50
ORDER BY r.scheduler_id, r.status, r.session_id

T-SQL Query to find all members in server role

Here is T-SQL script to find all the members present in the SQL Server roles.

SELECT SUSER_NAME(members.role_principal_id) AS [ServerRole]
   ,logins.name AS 'RoleMember'
   ,'EXEC sp_addsrvrolemember ''' +logins.name+''', '''+
   SUSER_NAME(members.role_principal_id)+'''' AS [Command to add role members]
FROM sys.server_role_members members, sys.server_principals logins
WHERE members.role_principal_id >=3 AND members.role_principal_id <=10 AND
members.member_principal_id = logins.principal_id
and logins.name <>'sa'

Unable to start SQL Server Service on a cluster instance

When you try to bring online the SQL Server cluster service, it might fail with the error "Login timeout expired".

In my case the SQL Server version was SQL Server 2005 and when I failover to Node1 it was working fine, however when I failback to Node2, it was failing with this error.

This is unusually right?
It works on one node but not on the other node.

How to Restore model and msdb database

Unlike master database, restoring model and msdb is simple and follows the same procedure as restoring any other user database. However we have to be very cautious while restoring system databases as it will have sensitive data which is important for SQL Server to function without any issues.

How to Restore Master database

Before restoring master database, make sure

1. The build versions of the both instances (Current instance and the instance where backup is generated) should be the same.
• More Information at http://www.sanssql.com/2013/09/the-backup-of-system-database-on-device.html

Fix - The SSIS subsystem failed to load

The subsystem failure is a common error which occurs when an SQL Server instance is migrated.
When we do the migration, we usually miss to install the SQL Server binaries on the same location as it was in source. Because of this the SQL Server jobs which uses SSIS, powershell, etc... subsystems will fail.

T-SQL Query to find last run status of scheduled Jobs

Here is a T-SQL query to find the last run status of all the scheduled jobs in SQL Server.
This query will be handy when you are not able to access the "Job Activity Monitor"

USE msdb
GO
SELECT DISTINCT SJ.Name AS JobName, SJ.description AS JobDescription,
SJH.run_date AS LastRunDate, 
CASE SJH.run_status 
WHEN 0 THEN 'Failed' 
WHEN 1 THEN 'Successful' 
WHEN 3 THEN 'Cancelled' 
WHEN 4 THEN 'In Progress' 
END AS LastRunStatus
FROM sysjobhistory SJH, sysjobs SJ
WHERE SJH.job_id = SJ.job_id and SJH.run_date = 
(SELECT MAX(SJH1.run_date) FROM sysjobhistory SJH1 WHERE SJH.job_id = SJH1.job_id)
ORDER BY SJH.run_date desc

T-SQL Query to find currently running jobs

Here is a T-SQL query to find the currently executing jobs.
The output of this query will be the list of jobs that are currently running along with the number of seconds it is been running.

SELECT  J.name as Running_Jobs,  
  JA.Start_execution_date As Starting_time,
        datediff(ss, JA.Start_execution_date,getdate()) as [Has_been_running(in Sec)]
FROM msdb.dbo.sysjobactivity JA

Alert when the Scheduled job state is changed

When you create this trigger on the sysjobs table of msdb, it will send out an email alert whenever someone changes the state of the job from Enabled to Disabled or from Disabled state to Enabled.
For setting up database mail option and to use sp_send_dbmail, refer to my earlier post "Configuring Database Mail"

USE msdb
GO
CREATE Trigger tr_AuditJobEnable
ON sysjobs  
FOR UPDATE 
AS
DECLARE @UserName VARCHAR(50),  
@HostName VARCHAR(50),  
@JobName VARCHAR(100),  
@DeletedJobName VARCHAR(100),  
@Ins_EnabledFlag INT,  
@Del_EnabledFlag INT,  
@Body VARCHAR(200),  
@Subject VARCHAR(200), 
@Servername VARCHAR(50) 

SELECT @UserName = SYSTEM_USER, @HostName = HOST_NAME()  
SELECT @Ins_EnabledFlag = Enabled FROM Inserted  
SELECT @Del_EnabledFlag = Enabled FROM Deleted  
SELECT @JobName = Name FROM Inserted  
SELECT @Servername = @@servername 

IF @Ins_EnabledFlag <> @Del_EnabledFlag  
BEGIN  

  IF @Ins_EnabledFlag = 1  
   BEGIN  
  SET @Body = 'The User "'+@username+'" from "'+@hostname+
   '" ENABLED the Job "'+@jobname+'" on '+CONVERT(VARCHAR(20),GETDATE(),100)  
  SET @Subject = 'SQL Job "'+@jobname+ '" on ' + @Servername+
   ' has been ENABLED at '+CONVERT(VARCHAR(20),GETDATE(),100)  
   END  

  IF @Ins_EnabledFlag = 0  
   BEGIN  
  SET @Body = 'The User "'+@username+'" from "'+@hostname+
   '" DISABLED the Job "'+@jobname+'" on '+CONVERT(VARCHAR(20),GETDATE(),100)  
  SET @Subject = 'SQL Job "'+@jobname+ '" on ' + @Servername+
   ' has been DISABLED at '+CONVERT(VARCHAR(20),GETDATE(),100)   
   END  

-- Send e-Mail 
Exec msdb..sp_send_dbmail
       @profile_name='DBA' -- Change to your Profile
      ,@recipients='segu.sandesh@gmail.com' -- Change Recipients
      ,@Subject=@Subject
      ,@Body=@Body
END

T-SQL query to find "mssqlsystemresource" database ID and database files location

We all know that mssqlsystemresource is a system database that is introduced from SQL Server 2005 onwards.
Here are few links which I posted earlier which gives more information about mssqlsystemresource database.

Monitor SQL Server and related services using T-SQL

As part of the DBA job, it is very important to monitor the SQL Server and its related service and ensure that the services are always up and running.
There are different ways to achieve this, and one among them is by using the custom SQL scripts.
Writing custom SQL scripts play a vital role in few environments where budget is a concern to implement an full fledged monitoring system.

Get SQL Server Database details using T-SQL

Here is a T-SQL script which gives you the details of all the databases in an SQL Server Instance.
This will be very useful when you are gathering SQL Server information from multiple servers

SET NOCOUNT ON
IF OBJECT_ID('tempdb..#DatabaseDetails') IS NOT NULL DROP TABLE #DatabaseDetails
CREATE TABLE #DatabaseDetails (
  DatabaseID int
, DatabaseName varchar(256)
, CreateDate datetime
, Collation varchar(256)
, ComparisonStyle int
, IsAnsiNullDefault bit
, IsAnsiNullsEnabled bit
, IsAnsiPaddingEnabled bit
, IsAnsiWarningsEnabled bit
, IsArithmeticAbortEnabled bit
, IsAutoClose bit
, IsAutoCreateStatistics bit
, IsAutoShrink bit
, IsAutoUpdateStatistics bit
, IsCloseCursorsOnCommitEnabled bit
, IsFulltextEnabled bit
, [IsInStandBy] bit
, IsLocalCursorsDefault bit
, IsMergePublished bit
, IsMergeSubscribed bit
, IsNullConcat bit
, IsNumericRoundAbortEnabled bit
, IsParameterizationForced bit
, [IsQuotedIdentifiersEnabled] bit
, IsPublished bit
, IsRecursiveTriggersEnabled bit
, IsSubscribed bit
, IsSyncWithBackup bit
, IsTornPageDetectionEnabled bit
, LCID int
, [Recovery] varchar(256)
, [SQLSortOrder] tinyint
, [Status] varchar(256)
, Updateability varchar(256)
, UserAccess varchar(256)
, [Version] int
, LastDatabaseBackup datetime
, LastIncremetalBackup datetime
, LastLogBackup datetime
, TotalLogSize bigint
, LogPercentUsed int
, [TotalDBSize_MB] bigint
, [cmptlevel] int
)

INSERT INTO #DatabaseDetails(
  DatabaseID
, [DatabaseName]
, [CreateDate]
, [Collation]
, [ComparisonStyle]
, [IsAnsiNullDefault]
, [IsAnsiNullsEnabled]
, [IsAnsiPaddingEnabled]
, [IsAnsiWarningsEnabled]
, [IsArithmeticAbortEnabled]
, [IsAutoClose]
, [IsAutoCreateStatistics]
, [IsAutoShrink]
, [IsAutoUpdateStatistics]
, [IsCloseCursorsOnCommitEnabled]
, [IsFulltextEnabled]
, [IsInStandBy]
, [IsLocalCursorsDefault]
, [IsMergePublished]
, [IsMergeSubscribed]
, [IsNullConcat]
, [IsNumericRoundAbortEnabled]
, [IsParameterizationForced]
, [IsQuotedIdentifiersEnabled]
, [IsPublished]
, [IsRecursiveTriggersEnabled]
, [IsSubscribed]
, [IsSyncWithBackup]
, [IsTornPageDetectionEnabled]
, [LCID]
, [Recovery]
, [SQLSortOrder]
, [Status]
, [Updateability]
, [UserAccess]
, [Version]
, [cmptlevel])
SELECT sd.dbid as 'DatabaseID'
 , sd.[name] as 'DatabaseName'
 , sd.crdate as 'CreateDate'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'Collation') as varchar(256)) as 'Collation'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'ComparisonStyle') as varchar(256)) as 'ComparisonStyle'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAnsiNullDefault') as bit) as 'IsAnsiNullDefault'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAnsiNullsEnabled') as bit) as 'IsAnsiNullsEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAnsiPaddingEnabled') as bit) as 'IsAnsiPaddingEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAnsiWarningsEnabled') as bit) as 'IsAnsiWarningsEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsArithmeticAbortEnabled') as bit) as 'IsArithmeticAbortEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAutoClose') as bit) as 'IsAutoClose'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAutoCreateStatistics') as bit) as 'IsAutoCreateStatistics'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAutoShrink') as bit) as 'IsAutoShrink'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAutoUpdateStatistics') as bit) as 'IsAutoUpdateStatistics'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsCloseCursorsOnCommitEnabled') as bit) as 'IsCloseCursorsOnCommitEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsFulltextEnabled') as bit) as 'IsFulltextEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsInStandBy') as bit) as 'IsInStandBy'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsLocalCursorsDefault') as bit) as 'IsLocalCursorsDefault'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsMergePublished') as bit) as 'IsMergePublished'
 , CASE WHEN sd.category & 8 = 8 THEN 1 ELSE 0 end as 'IsMergeSubscribed'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsNullConcat') as bit) as 'IsNullConcat'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsNumericRoundAbortEnabled') as bit) as 'IsNumericRoundAbortEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsParameterizationForced') as bit) as 'IsParameterizationForced'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsQuotedIdentifiersEnabled') as bit) as 'IsQuotedIdentifiersEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsPublished') as bit) as 'IsPublished'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsRecursiveTriggersEnabled') as bit) as 'IsRecursiveTriggersEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsSubscribed') as bit) as 'IsSubscribed'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsSyncWithBackup') as bit) as 'IsSyncWithBackup'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsTornPageDetectionEnabled') as bit) as 'IsTornPageDetectionEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'LCID') as int) as 'LCID'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'Recovery') as varchar(256)) as 'Recovery'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'SQLSortOrder') as tinyint) as 'SQLSortOrder'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'Status') as varchar(256)) as 'Status'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'Updateability') as varchar(256)) as 'Updateability'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'UserAccess') as varchar(256)) as 'UserAccess'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'Version') as int) as 'Version'
 , cmptlevel
FROM master.dbo.sysdatabases sd 
ORDER BY sd.dbid



UPDATE dbd
SET   LastDatabaseBackup = fullbak.LastDatabaseBackup
 , LastIncremetalBackup = incbak.LastIncremetalBackup
 , LastLogBackup = logbak.LastLogBackup
FROM #DatabaseDetails dbd
left join (
 SELECT sd.dbid
   , sd.[name] as 'DatabaseName'
   , max(t1.backup_finish_date) as 'LastDatabaseBackup'
 FROM master.dbo.sysdatabases sd 
 join msdb.dbo.backupset t1  on t1.type = 'D' and t1.database_name = sd.[name]
 GROUP BY sd.dbid, sd.[name]
) fullbak ON fullbak.dbid = dbd.DatabaseID
left join (
 SELECT sd.dbid
   , sd.[name] as 'DatabaseName'
   , max(t2.backup_finish_date) as 'LastIncremetalBackup'
 FROM master.dbo.sysdatabases sd 
 join msdb.dbo.backupset t2  on t2.type = 'I' and t2.database_name = sd.[name]
 GROUP BY sd.dbid, sd.[name]
) incbak on incbak.dbid = dbd.DatabaseID
left join (
 SELECT sd.dbid
   , sd.[name] as 'DatabaseName'
   , max(t3.backup_finish_date) as 'LastLogBackup'
 FROM master.dbo.sysdatabases sd 
 join msdb.dbo.backupset t3  on t3.type = 'L' and t3.database_name = sd.[name]
 GROUP BY sd.dbid, sd.[name]
) logbak on logbak.dbid = dbd.DatabaseID


IF OBJECT_ID('tempdb..#logspace') IS NOT NULL DROP TABLE #logspace
CREATE TABLE #logspace(
   DatabaseName varchar(256)
 , TotalLogSize decimal(20,4)
 , PercentUsed decimal(20,4)
 , [Status] varchar(50)
)
INSERT INTO #logspace
EXEC('DBCC sqlperf(logspace)')

UPDATE dbd
SET   TotalLogSize = ls.TotalLogSize
 , LogPercentUsed = Convert(int, ls.PercentUsed)
FROM #DatabaseDetails dbd
join #logspace ls ON dbd.DatabaseID = db_id(ls.DatabaseName)

EXEC master.dbo.sp_MSForEachDB 'update #DatabaseDetails
set TotalDBSize_MB = (select (sum([size]) * 8.0) / 1024.0 
FROM [?].[dbo].[sysfiles] )
where DatabaseID = db_id(''?'')'

SELECT DatabaseID
, DatabaseName
, [TotalDBSize_MB]
, TotalLogSize
, LogPercentUsed
, CreateDate
, [Status]
, LastDatabaseBackup
, LastIncremetalBackup
, LastLogBackup
, [Recovery]
, [Updateability]
, [UserAccess]
, [Collation]
, [ComparisonStyle]
, [LCID]
, [SQLSortOrder]
, [Version]
, [cmptlevel]
, [IsAutoUpdateStatistics]
, [IsAutoCreateStatistics]
, [IsInStandBy]
, [IsAutoShrink]
, [IsNullConcat]
, [IsFulltextEnabled]
, [IsPublished]
, [IsSubscribed]
, [IsMergePublished]
, [IsMergeSubscribed]
, [IsAnsiNullDefault]
, [IsAnsiNullsEnabled]
, [IsAnsiPaddingEnabled]
, [IsAnsiWarningsEnabled]
, [IsArithmeticAbortEnabled]
, [IsAutoClose]
, [IsCloseCursorsOnCommitEnabled]
, [IsLocalCursorsDefault]
, [IsNumericRoundAbortEnabled]
, [IsParameterizationForced]
, [IsQuotedIdentifiersEnabled]
, [IsRecursiveTriggersEnabled]
, [IsSyncWithBackup]
, [IsTornPageDetectionEnabled]
FROM #DatabaseDetails
ORDER BY DatabaseName
SET NOCOUNT OFF

T-SQL Query to find IP Address of SQL Server

Here is a handy T-SQL script to find the IP Address of the SQL Server you are connected to.

SELECT CONNECTIONPROPERTY('local_net_address') AS [IP Address Of SQL Server]

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') [Machine Name]
   ,SERVERPROPERTY('InstanceName') AS [Instance Name]
   ,LOCAL_NET_ADDRESS AS [IP Address Of SQL Server]
   ,CLIENT_NET_ADDRESS AS [IP Address Of Client]
 FROM SYS.DM_EXEC_CONNECTIONS 
 WHERE SESSION_ID = @@SPID