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.

What are DBCC commands in SQL Server?

DBCC (Database consistency checker) are used to check the consistency of the databases. The DBCC commands are most useful for performance and trouble shooting exercises.

I have listed down and explained all the DBCC commands available in SQL Server 2005, with examples.

The DBCC Commands broadly falls into four categories:
A. Maintenance

B. Informational

C. Validation

D. Miscellaneous

A. Maintenance Commands

Performs maintenance tasks on a database, index, or file-group.

1. CLEANTABLE - Reclaims space from the dropped variable-length columns in tables or index views.

Example

DBCC CLEANTABLE ('AdventureWorks','Person.Contact',0)

2. DBREINDEX - Builds one or more indexes for the table in the specified database. (Will be removed in the future version, use ALTER INDEX instead) USE AdventureWorks

Example

DBCC DBREINDEX ('Person.Contact','PK_Contact_ContactID',80)

3. DROPCLEANBUFFERS - Removes all clean buffers from buffer pool.

Example

DBCC DROPCLEANBUFFERS

4. FREEPROCCACHE - Removes all elements from the procedure cache

Example

DBCC FREEPROCCACHE

5. INDEXDEFRAG - Defragments indexes of the specified table or view.

Example

DBCC INDEXDEFRAG ('AdventureWorks', 'Person.Address', PK_Address_AddressID)

6. SHRINKDATABASE - Shrinks the size of the data and log files in the specified database.

Example

DBCC SHRINKDATABASE ('AdventureWorks', 10)

7. SHRINKFILE - Shrinks the size of the specified data or log file for the current database or empties a file by moving the data from the specified file to other files in the same filegroup, allowing the file to be removed from the database.

Example

USE AdventureWorks;

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (AdventureWorks_Log, 1)

8. UPDATEUSAGE - Reports and corrects pages and row count inaccuracies in the catalog views.

Example

DBCC UPDATEUSAGE (AdventureWorks)

B. Informational Commands

Performs tasks that gather and display various types of information.

1. CONCURRENCYVIOLATION - is maintained for backward compatibility. It runs but returns no data.

Example

DBCC CONCURRENCYVIOLATION

2. INPUTBUFFER - Displays the last statement sent from a client to an instance of Microsoft SQL Server 2005.

DBCC INPUTBUFFER (52)

3. OPENTRAN - Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database.

DBCC OPENTRAN;

4. OUTPUTBUFFER - Returns the current output buffer in hexadecimal and ASCII format for the specified session_id.

DBCC OUTPUTBUFFER (52)

5. PROCCACHE - Displays information in a table format about the procedure cache.

DBCC PROCCACHE

6. SHOW_STATISTICS - Displays the current distribution statistics for the specified target on the specified table USE AdventureWorks

DBCC SHOW_STATISTICS ('Person.Address', AK_Address_rowguid)

7. SHOWCONTIG - Displays fragmentation information for the data and indexes of the specified table or view.

USE AdventureWorks

DBCC SHOWCONTIG ('HumanResources.Employee');

8. SQLPERF - Provides transaction log space usage statistics for all databases. It can also be used to reset wait and latch Statistics.

DBCC SQLPERF(LOGSPACE)

9. TRACESTATUS - Displays the status of trace flags.

DBCC TRACESTATUS(-1)

10. USEROPTIONS - Returns the SET options active(set) for the current connection.

DBCC USEROPTIONS

 

C. Validation Commands 

Performs validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.

1. CHECKALLOC - Checks the consistency of disk space allocation structures for a specified database.

DBCC CHECKALLOC (AdventureWorks)

2. CHECKCATALOG - Checks for catalog consistency within the specified database.

DBCC CHECKCATALOG (AdventureWorks)

3. CHECKCONSTRAINTS - Checks the integrity of a specified constraint or all constraints on a specified table in the current database.

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS

4. CHECKDB - Checks the logical and physical integrity of all the objects in the specified database.

DBCC CHECKDB (AdventureWorks)

5. CHECKFILEGROUP - Checks the allocation and structural integrity of all tables and indexed views in the specified filegroup of the current database.

USE AdventureWorks

DBCC CHECKFILEGROUP

6. CHECKIDENT - Checks the current identity value for the specified table and, if it is needed, changes the identity value.

USE AdventureWorks;

DBCC CHECKIDENT ('HumanResources.Employee')

7. CHECKTABLE - Checks the integrity of all the pages and structures that make up the table or indexed view.

USE AdventureWorks;

DBCC CHECKTABLE ('HumanResources.Employee')

D. Miscellaneous Commands

Performs miscellaneous tasks such as enabling trace flags or removing a DLL from memory.

1. dllname (FREE) - Unloads the specified extended stored procedure DLL from memory.

DBCC xp_sample (FREE)

2. TRACEOFF - Disables the specified trace flags.

DBCC TRACEOFF (3205)

3. HELP - Returns syntax information for the specified DBCC command.

- List all the DBCC commands

DBCC HELP ('?')

Show the Syntax for a given DBCC commnad

DBCC HELP ('checkcatalog')

4. TRACEON - Enables the specified trace flags.

DBCC TRACEON (3205)

How to rename a stored procedure in SQL Server?

Using following ways by which you can rename a stored procedure in SQL Server

(1) Using SSMS 'SQL Server Management Studio'

Using object explorer navigate to database > Programability > Stored Procedures > Right click on SP you want to rename > click rename.

(2) Using 'sp_rename'

Using system procedure 'sp_rename' in the query window of SSMS

sp_rename 'oldname','newname'

How to change the default port of SQL server ?

SQL Server listens on default port number 1433. Following table will provide details of other default ports being used by SQL Server

Port	Protocol	Used By
1433	TCP	Default port for SQL Server connections
1434	UDP	Port for the SQL Browser service. In essence, the browser service returns a list of instances and the ports they are listening on that reside on the host. It is mostly used to identify where named instances are listening
5022	TCP	The default port for Mirroring

To change the default port follows the step below in contrast to SQL Server 2008

1. Click on Start button in Windows. 

2. Go to All Programs

3. Click Microsoft SQL Server 2008

4. Click Configuration Tools

4. Click SQL Server Configuration Mangaer

5. Click on SQL Native Client 10.0 Configuration 

6. Expand on Client Protocols

7. Double Click TCP/IP (Right click Select Properties) on TCP/IP

You will find Default Port 1433, change it accordingly.

After changing the port number it is necessary to restart the SQL Services to make the change take effect.

Ways to know the port being used by SQL Servers

(1) This CMD command give the ports with the corresponding application that are using it 

netstat -abn 

(2) In the SQL ERROR LOG  look for a line like below.

Server is listening on [ 192.128.3.2 <ipv4> 1433].