Using point in time recovery in SQL Server

Sometimes, you might want to restore your SQL Server database at a specific point of time. I will guide you through to use point in time recovery in SQL Server.

Prerequisites:
1. Your database recovery model must be FULL or BULK-LOGGED.
2. You have enabled Transactions Log Backup’s.

Problem:
You have a SQL Server database that is being used by many users, one user has deleted some records by mistake and you have to restore them back.Deletions has been done at 5.00 PM.

Procedures:
1. Restore the latest Full backup with no recovery option.
2. Now Restore your Transaction logs with no recovery option, except the last one transaction
log back.Also all your transaction backup must be in sequence at restoration time.
3. Restore Last Transaction Log File with recovery option and STOPAT=’Date, Time’. The following is a sample script:

Restore database testing from disk='<DRIVE_LETTER>:\<BACKUP_LOCATION>\<FULL_BACKUP_FILENAME>.bak’ with norecovery
Go
Restore log testing from disk='<DRIVE_LETTER>:\<BACKUP_LOCATION>\<TRANSACTION_LOG_BACKUP_FILENAME1>.trn’ with norecovery
Go
Restore log testing from disk='<DRIVE_LETTER>:\<BACKUP_LOCATION>\<TRANSACTION_LOG_BACKUP_FILENAME2>.trn’ with recovery, stopat=’Sep 4, 2012 7:53:00 PM’
Go

Using ADO with SQL Server Native Client

To enable the usage of SQL Server Native Client, ADO applications will need to implement the following keywords in their connection strings:

Provider=SQLNCLI10
DataTypeCompatibility=80

The following is an example of establishing an ADO connection string that is fully enabled to work with SQL Server Native Client (SQL Server 2008 R2):

Dim connection As New ADODB.Connection
connection.ConnectionString = “Provider=SQLNCLI10;” _
& “SERVER=(local);” _
& “Database=Northwind;” _
& “DataTypeCompatibility=80;” _
& “User Id=sa;” _
& “Password=Pa$$w0rd;”
connection.Open

The following is an example of establishing an ADO connection string that is fully enabled to work with SQL Server Native Client (SQL Server 2012 and SQL Server 2014):

Dim connection As New ADODB.Connection
connection.ConnectionString = “Provider=SQLNCLI11;” _
& “SERVER=(local);” _
& “Database=Northwind;” _
& “DataTypeCompatibility=80;” _
& “User Id=sa;” _
& “Password=Pa$$w0rd;”
connection.Open

What is REDO queue in database mirroring?

The REDO queue is the set of log records that have been received on the mirror from the principal, but have not yet been replayed in the mirror database. Once a failure is initiated, the mirror database will not come online as the principal until the REDO queue has been processed. It is very important to monitor the REDO queue on the mirror. The amount of REDO queue correlates to the amount of downtime you’ll experience during a mirroring failover. This is due to the fact that the amount of unrestored log waiting in the redo queue is an indicator of the time required to fail over to the mirror database.

Operating modes in database mirroring

Operating modes in database mirroring is one of the common questions usually posted to me during the DBA trainings that I have delivered over the years.

In summary, there are two operating modes in database mirroring:

  • High-performance mode. The database mirroring session operates asynchronously and uses only the principal server and mirror server. The only form of role switching is forced service (with possible data loss). In high-performance mode, as soon as the principal server sends the log for a transaction to the mirror server, the principal server sends a confirmation to the client, without waiting for an acknowledgement from the mirror server. Transactions commit without waiting for the mirror server to write the log to disk. Asynchronous operation permits the principal server to run with minimum transaction latency.
  • High-safety mode. The database mirroring session operates synchronously and, optionally, uses a witness, as well as the principal server and mirror server. In high-safety mode,  the mirror server must synchronize the mirror database with the principal database. When the session begins, the principal server begins sending its active log to the mirror server. The mirror server writes all of the incoming log records to disk as quickly as possible. As soon as all of the received log records have been written to disk, the databases are synchronized. As long as the partners remain in communication, the databases remain synchronized.

Cannot drop server ‘repl_distributor’ because it is used as a Distributor in replication

Recently I encountered the following error:

Cannot drop server ‘repl_distributor’ because it is used as a Distributor in replication

during an attempt to remove the linked server that is created as part of a replication. The following is the fix to this error:

EXEC master.dbo.sp_serveroption @server=N'<SERVER_NAME>’, @optname=N’dist’, @optvalue=N’true’
GO

EXEC sp_dropdistributor @no_checks = 1, @ignore_distributor = 1
GO

You need to replace <SERVER_NAME> with the computer name of the distributor.

Server ‘<SERVERNAME' is not configured for RPC error

Recently, I was doing a proof of concept on database mirroring and needed to execute a stored procedure on the principal server through linked server. I ran into the following error when I attempt to execute the stored procedure from another server:

Server ‘<SERVERNAME’ is not configured for RPC

Upon further troubleshooting, I discovered that there is an option for ‘RPC’ and ‘RPC Out’. To resolve the issue, ‘RPC Out’ needs to be set to ‘True’ in order to successfully execute the stored procedure. ‘RPC’ allows stored procedure call from the linked server whereas ‘RPC Out’ allows stored procedure calls to go out to the linked server.

Fix for Agent XPs’ component is turned off as part of the security configuration for this server

Today, I was working on preparing a series of demonstration scripts for an upcoming training. One of scripts is to create a maintenance plan, I encountered the following error message:

‘Agent XPs’ component is turned off as part of the security configuration for
this server. A system administrator can enable the use of ‘Agent XPs’ by using   sp_configure. For more information about enabling ‘Agent XPs’, see “Surface Area   Configuration” in SQL Server Books Online. (ObjectExplorer)

To fix the above mentioned error, you need to use the Agent XPs option to enable the SQL Server Agent extended stored procedures on this server. When this option is not enabled, the SQL Server Agent node is not available in SQL Server Management Studio Object Explorer.

You can enable the SQL Server Agent extended stored procedures by running the following scripts:

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Agent XPs’, 1;
GO
RECONFIGURE
GO