Author: Jugal Shah

Steps to recover access/reset SA Password to SQL Server Instance without downtime
Problem: There may be situation where you are unable to access the SQL Server. In this article I will guide you how you regain the access to SQL Server instance.
Below are the possible reasons why you wouldn’t be able to access the SQL Server instance
– Built in Administrator is removed
– Built in Administrator is not available
– Forgot SA password
– Forgot add the DBA account on the SQ Server instance

Most of the time to resolve this kind of issue we are restarting the SQL Server in single user mode, which requires down time, here we will follow the approach where we don’t need any downtime to get the access to SQL Server instance.

Solution:
You can follow below steps to get the access to SQL Server instance without downtime.
Step 1: Make sure you have administrator level access on windows
Step 2: Download the PSExec, PSExec utility allows you to connect the SQL Server instance under the context of NT AUTHORTY\system account, which has default sysAdmin access on SQL Server.
Step 3: Open the command prompt as administrator and type the below command, it will open SSMS and under NT AUTHORITY\SYSTEM account context and you can change password or add account etc.
```
PsExec -s -i "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"
```
Step 4: Open new query window from SSMS and either you can change SA account password or add new account.
```
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'password'
GO
```
January 6, 2014

Stripping SQL Server Database Backup to Multiple Files

Stripping Database backup to multiple files and on different drives will make the backup speed faster and will reduce the backup duration.

Check the below sample script for the backup and restore. You can perform the same task using SSMS GUI as well.
Backup Script

BACKUP DATABASE [SQLDBPool] TO  
DISK = N'C:\JSpace\Backup\SQLDBPool1.bak',  
DISK = N'C:\JSpace\Backup\SQLDBPool2.bak',  
DISK = N'C:\JSpace\Backup\SQLDBPool3.bak'
WITH NOFORMAT, 
NOINIT,  
NAME = N'SQLDBPool-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Restore Script

RESTORE DATABASE [SQLDBPool] FROM  
DISK = N'C:\JSpace\Backup\SQLDBPool1.bak',  
DISK = N'C:\JSpace\Backup\SQLDBPool2.bak',  
DISK = N'C:\JSpace\Backup\SQLDBPool3.bak'
WITH  FILE = 1,  NOUNLOAD,  STATS = 10,replace
GO

January 2, 2014

Script to find out the traces running on SQL Server instance

You can execute the below script on SQL Server instance to find out the traces, trace type, trace file path and trace status.

select
      TraceType =
       case trace.is_default
            when 1 THEN 'Default/System Trace'
            when 0 THEN 'User Trace'
       end,
      Trace_Status =
      case trace.status
            when 1 THEN 'Running'
            when 0 THEN 'Stopped'
      end,
       ssion.session_id as SessionID,
       [loginName] = coalesce(ssion.login_name,ssion.login_name,'Reader SPID Not mentioned'),
       [Trace_File_Path] = coalesce(trace.[Path],trace.[Path],'OLEDB Client Trace')
      from sys.traces trace
            left join sys.dm_exec_sessions ssion on trace.reader_spid = ssion.session_id

January 2, 2014

Script to get the SQL Server Installation Date and Authentication Mode

Recently I got a request where I have to check the SQL Server installation date and SQL Server authentication mode. I have write the below script and execute it against all the servers registered in CMS.

Here is the script

select createdate as InstallationDate ,
CASE SERVERPROPERTY('IsIntegratedSecurityOnly')   
WHEN 1 THEN 'Windows Authentication'   
WHEN 0 THEN 'Windows and SQL Server Authentication'   
END as [AuthenticationMode],
SERVERPROPERTY('servername') as svrName 
from master..syslogins where name like 'NT AUTHORITY\SYSTEM'

January 2, 2014

Wish You All Very Happy New Year 2014

January 1, 2014

Author: Jugal Shah

Steps to recover access/reset SA Password to SQL Server Instance without downtime

Stripping SQL Server Database Backup to Multiple Files

Script to find out the traces running on SQL Server instance

Script to get the SQL Server Installation Date and Authentication Mode

Wish You All Very Happy New Year 2014