T-SQL to find Server and SQL restart date time

This script comes handy when you would like to find out the Server and SQL services restart times.

DECLARE @SQLServiceRestartDateTime AS VARCHAR(50),
		@ServerRestartDateTime AS VARCHAR(50)

SELECT @SQLServiceRestartDateTime = create_date FROM sys.databases WHERE database_id=2
SELECT @ServerRestartDateTime = CAST(DATEADD(SECOND, (ms_ticks/1000) * -1, GETDATE()) AS datetime) FROM sys.dm_os_sys_info

SELECT @SQLServiceRestartDateTime AS [SQL Service Start DateTime], @ServerRestartDateTime AS [Server Start DateTime]

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

T-SQL to find Navigation path in CMS

In large enterprises there will be huge number of SQL servers which will be registered within a Central Management Server (CMS) and times it may become difficult to find out where the server is registered atleast for new team members in the Huge pile of servers and folder structure. This query give you the path where the specified servers is registered within the CMS.

DECLARE @ServerName nvarchar(50)
SET @ServerName='SQL01';

WITH CMSCTE
AS
(
--Anchor
SELECT server_group_id, name, description, parent_id, 1 AS [Level], CAST((name) AS VARCHAR(MAX)) AS CMSPath
FROM msdb.dbo.sysmanagement_shared_server_groups AS A
WHERE parent_id IS NULL
UNION ALL
--Recursive Member
SELECT B.server_group_id, B.name, B.description, B.parent_id, C.[level] + 1 AS [Level], CAST((C.CMSPath + '->' + B.Name) AS VARCHAR(MAX)) AS CMSPath
FROM msdb.dbo.sysmanagement_shared_server_groups AS B
JOIN CMSCTE AS C 
ON B.parent_id = C.server_group_id  
)

SELECT TOP 1 CMSPath AS 'Path in CMS' , B.name as 'Server Name', B.description AS 'Server Description', A.name AS 'Group Name', A.description AS 'Group Description'
FROM CMSCTE AS A
INNER JOIN msdb.dbo.sysmanagement_shared_registered_servers AS B
ON A.server_group_id=B.server_group_id
WHERE B.name = @ServerName
ORDER BY [Level] DESC

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

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)

When trying to load an excel spread sheet into SQL Server you may be presented with the below error.

This occurs because the Office System Driver and Data Connectivity Components are not installed on the server and SQL Server is unable to interact with the MS Office document. For the SQL Server to interact, open and load the data from excel spreadsheet, it requires the Office System Driver and Data Connectivity Components to be installed.

To fix this issue, download and install the Office System Driver and Data Connectivity Components from the below link.
http://www.microsoft.com/en-us/download/details.aspx?id=23734

This will install a set of components that can be used to facilitate transfer of data between Microsoft Office System files and non-Microsoft Office applications.

Page Level Restoration now has GUI

In one of my previous posts "Page Level Restoration", I had explained how to perform a page level restoration for a database. The whole procedure was using the T-SQL commands.
From SQL Server 2012 onward, we have an GUI to perform Page level restoration which makes life easy.

Different ways to make a table read-only

There may be many cases where a tables needs to be made as read-only in order to maintain the existing data. This can be achieved using many ways and out of which the common methods are

• Deny Permission
• Put the table on read only file group
• Create trigger instead of insert, update, delete
• Make the database read-only
• Create a Column Store Index

Upgrade Error - Valid Database compatibility level and successful connection rule

Recently when I was trying to upgrade an SQL Server 2008 R2 instance to SQL Server 2012, I was presented with an error during the upgrade rules validation.
The error message was, "The report server database is not a supported compatibility level or a connection cannot be established."

When I checked the reporting server databases, the databases had the right compatibility level.

Then I found that the SQL Server Name defined in the Reporting services configuration manager was not existing. This caused the validation rule to fail.

After changing the SQL Server Name to right server name, all the validations passed and it let me upgrade the SQL Server Instance. 

Error while installing SQL Server 2012 - Access to the path 'C:\Program Files\Microsoft SQL Server\110\License Terms\SQLServerDenaliCTP_License_EVAL_1033.rtf' is denied.

When installing SQL Server 2012 you might run into an error which says "Access to the path 'C:\Program Files\Microsoft SQL Server\110\License Terms\SQLServerDenaliCTP_License_EVAL_1033.rtf' is denied."

To workaround this error, browse to the path "C:\Program Files\Microsoft SQL Server\110\License Terms\" and change the property of file "SQLServerDenaliCTP_License_EVAL_1033.rtf" to remove "Read-Only" and then click "Retry" and the installation will continue.

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

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'

Uninstalling SQL Server Service Pack or Hotfix

Prior to SQL Server 2008, if you had to rollback a Service pack or hotfix installation, it was a long and hectic process as it involves rebuilding the SQL Server and restoring the user and system databases and setting up jobs, logins,etc... And in the meantime if you have missed backing up something then you are gone!
Starting from SQL Server 2008, Service Pack 1, microsoft has introduced an option to uninstall the service pack or an hotfix. It is as simple as uninstalling an application from your computer.
To uninstall an SQL Server service pack or hotfix,
Go to "Control panel" --> "Uninstall a Program" --> Click on "view installed updates" on left hand side
Choose the update you want to uninstall and click "Uninstall" option.

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

Backup databases with COPY_ONLY using Maintenance Plans - SQL Server 2012

Prior to SQL Server 2012, we were not allowed to take COPY_ONLY backups using the Maintenance Plans. We could use this option only with the Backup Database GUI or using T-SQL scripts.
In SQL Server 2012, this is one of the new option that allows us to take COPY_ONLY backups using the Maintenance Plans.

Unable to create restore plan due to break in the LSN chain

Recently one of my colleague was working on restoring a database in SQL Server 2012 and the process had one Full database backup and one Differential Backup.
At first everything worked when they try to restore the full database backup with "No Recovery" and when they are trying to restore the differential backup, they were facing an issue which states "Unable to create restore plan due to break in the LSN chain."

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.