How to start the SQL instance in Single-User mode?



Sometimes it becomes necessary for DBA to start the SQL Instance in Single-User mode to perform maintenance task like restoring master database upon corruption etc.

One can do this in different ways using
1. SQL Server Configuration Manger
2. Windows Command prompt

1. SQL Server Configuration Manger
 
Go to Start >> All Program >> Microsoft SQL Server 2008 R2 >> Configuration Tool>> SQL Server Configuration Manger>>Click SQL Server Services Tab

Right click on engine service SQL Server (MSSQLSERVER) and click properties tab.


 In properties window click Advanced Tab and type “-m;” in Startup Parameters and click Apply and OK.
  

 Now you can connect the instance through management studio in Single-User mode.

2. Window Command Prompt

To start the SQL Server in single user mode through command prompt follow the step below

1. If SQL instance in running then stop all the services running on the instance.
2. Access the command prompt (C:\>)
3. Type net start MSSQLSERVER /m


For named instance use MSSQL$<instance name>

How to perform Point-in-Time Recovery (PITR)?

Restoring a database to a particular time just before any unwanted data changes using database backups is know as Point-in-Time Recovery of a database.

Point-in-Time Recovery is also know as incomplete database recovery because in PITR we will not restore and recover full database.

In SQL Server Point-in-Time Recovery is only possible under following recovery models:

1. Full recovery model
2. Bulk-Logged recovery model

NOTES:
1. Under Simple recovery model this PITR functionality will not work.
2. Under Bulk-Logged recovery model if we have any bulk logged operations such as create index or bulk insert then Point-in-Time recovery is not possible because these operations are minimally logged.

Real Scenario
A Database 'test' is configured in Full-recovery model and  is scheduled for full database backup every morning 4 AM and differnetial backup for every 3 hours and transactional log backup occurs every 30 minutes. At 10:58 AM a database user unfortuntly executed a TRUNCATE TABLE against this database and at 11:05 AM he ask you to restore and recover the database till 10:57 AM.

Below are the steps to be followed to perform database Point-in-Time Recovery

1. Firstly restore full backup WITH NORECOVERY option
2. Then restore the all differential backups WITH NORECOVERY option
3. Restore the transactional log backup WITH RECOVERY and STOPAT option

In this case we have full backup occur at 4 AM and then differential backups at 7 AM and 10 AM respectively and then transactional log backups at 10:30 AM and 11:00 AM.

RESTORE DATABASE test
FROM DISK = 'D:\Backup\test_full_backup_0400.bak'
WITH NORECOVERY

RESTORE DATABASE test
FROM DISK = 'D:\Backup\test_diff_backup_0700.bak'
WITH NORECOVERY

RESTORE DATABASE test
FROM DISK = 'D:\Backup\test_diff_backup_1000.bak'
WITH NORECOVERY

RESTORE LOG test
FROM DISK = 'D:\Backup\test_log_1030.trn'
WITH NORECOVERY

RESTORE LOG test
FROM DISK = 'D:\Backup\test_log_1100.trn'
WITH RECOVERY,
STOPAT = 'Sep 21, 2013 10:58:00 AM'

The last restore command is always WITH RECOVERY and STOPAT command.

What is Tail-Log backup and how to take?

The tail-log backup is simply a regular transaction log backup that's been generated since the most recent log backup was taken just before when you’re going to restore a database to the point of failure.

Tail-log backups are new to SQL Server 2005. Tial-log backup is relevant to only following recovery models
1. Full Recovery Model
2. Bulk-logged Recovery Model

NOTE: The only exception is when there has been a minimally-logged operation performed in the BULK_LOGGED recovery model since the previous log backup – in that case a tail-log backup is not possible at all, and you'll have lost all transaction log generated since the last log backup.

There are two cases to consider here
1) SQL Server still available
2) SQL Server not available

CASE 1. SQL Server still available
It is simple to take the tial-log backup of database if the server is available.

Create Database DBTestTLog;
GO
Use DBTestTLog;
GO
CREATE TABLE
myTrans 
    (ID INT IDENTITY,
     DATE CHAR(20));
GO

Now backup the database

BACKUP DATABSE DBTestTLog TO DISK = 'C:\DBTestTLog\DBTestTLog_Full backup.bak' WITH INIT;
GO

INSERT INTO myTrans VALUES ('Data1')
INSERT INTO myTrans VALUES ('Data2')
 

BACKUP LOG DBTestTLog TO DISK = 'C:\DBTestTLog\DBTestTLog_Log1.trn' WITH INIT;

INSERT INTO mytrans VALUES ('Data3')
INSERT INTO myTrans VALUES ('Data4')

To create the disaster situation i will do the following:

1) ALTER DATABASE DBTestTLog SET OFFLINE;
2) Delete the data file

ALTER DATABASE DBTestTLog SET ONLINE;

When you try to bring the database online you will get the following error message

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DBTestTLog.mdf". Operating system error 2: "2(The system cannot find the file specified.)".
Msg 945, Level 14, State 2, Line 1
Database 'DBTestTLog' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.


BACKUP LOG DBTestTLog TO DISK = 'C:\DBTestTLog\DBTestTLog_LOG2.trn' WITH INIT;

When to try to take the log backup of database you will encounter the following error

Msg 945, Level 14, State 2, Line 1
Database 'DBTestTLog' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.


It doesn't work – as the data files aren't all accessible.

The trick is to use the NO_TRUNCATE option, which allows the log backup even if the database files aren't there:

BACKUP LOG DBTestTLog TO DISK = 'C:\DBTestTLog\DBTestTLog_LOG2.trn' WITH INIT, NO_TRUNCATE; 

Processed 2 pages for database 'DBTestTLog', file 'DBTestTLog_log' on file 1.
BACKUP LOG successfully processed 2 pages in 0.012 seconds (0.773 MB/sec).

I can then use this tail-log backup as the final backup in the restore sequence, saving transactions 3 and 4 from above.

I will update this document with the CASE 2 : SQL Server not available

How to move TempDB from one drive to another?

Suppose tempdb database files are located on drive C and you want to move it to drive E.

To get the current location of the tempdb database files run the following script.

USE TempDB
GO
EXEC sp_helpfile
GO

The result is like below


Name
FileID
FileName
FileGroup
Size
Maxsize
Growth
usage
tempdev
1
C:\Program Files\Microsoft SQL Server\
MSSQL10_50.MSSQLSERVER\MSSQL\
DATA\tempdb.mdf
PRIMARY
18240 KB
Unlimited
10%
data only
templog
2
C:\Program Files\Microsoft SQL Server\
MSSQL10_50.MSSQLSERVER\MSSQL\
DATA\templog.ldf
NULL
768 KB
Unlimited
10%
log only

The Name field in above result will be used in 'ALTER DATABASE' statement to move mdf and ldf files

USE master
GO
ALTER DATABASE TempDB
MODIFY FILE (NAME = tempdev, FILENAME = 'e:TempDB\tempdb.mdf')
GO
ALTER DATABASE TempDB
MODIFY FILE (NAME = templog, FILENAME = 'e:TempDB\templog.ldf')
GO

The definition of the TempDB is changed. However, no changes are made to TempDB till SQL Server restarts. Please stop and restart SQL Server and it will create TempDB files in new locations.

NOTES:
1. Reasons why tempdb needs to be moved
       a. No Enough space on existing drive for Tempdb to grows big in size.
       b. Moving Tempdb to another drive helps to improve disk I/O.
2. Make sure that TempDB is set to autogrow and do not set a maximum size for TempDB.
3. It is recommended to create the tempdb files equal to the number of CPU cores in the server.

What is TempDB Database in SQL Server?

The tempdb is a system database, which is global resource available to all users.

The tempdb is a temporary workspace for storing temporary tables, worktables that hold intermediate results during the sorting or query processing which intern increase the performance for SQL Server.

The tempdb is re-created each time the SQL Server service starts with clean copy of the database. The tempdb is created from model database and reset to its last configured size.

You can shrink tempdb but shrinking files regularly is not a recommended practice, because these files may probably grow again and also shrink operations causes’ data fragmentation. 

Backup and restoration operations are not possible on tempdb database.

Major Problems with TEMPDB:-
1. tempdb has run out of space.
2. tempdb is experiencing I/O bottleneck
3. tempdb is experiencing contention

In my next post i will document how to move TempDB database from one drive to another.