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

Enterprise Conference 2015 for Modernizing Application and Optimizing IT Operations

I will be speaking “Cross-platform Continuous Integration and Continuous Deployment Workflows” at “Enterprise Conference 2015 for Modernizing Application and Optimizing IT Operations”.

Session Synopsis:
Team Foundation Server 2015 brings a major overhaul to the Team Foundation Server build system that not only addresses numerous points of customer feedback – like a web UI, change auditing, better agent pool management, and high availability but also unlocks whole new worlds by creating a dramatically simpler extensibility model, providing tasks for building almost anything you want – Java, Android, IOS, Node.js and more, and a cross platform agent that enables you to build on any platform you choose. In this session, you will see an end-to-end demonstrations of these capabilities within Team Foundation Server 2015.

Date:
22 September 2015, Tuesday

Time:
12:00 pm – 5:00 pm
Venue:
Microsoft Auditorium, Level 26, Menara 3 Petronas

Please register at: http://www.microsoft.com/click/services/Redirect2.ashx?CR_EAC=30031416111996909_989234431097390_2004985359587605137_n

DevCom Penang

I will be speaking “Cross-platform continuous integration and continuous deployment workflows” at “DevCom Penang”. Many thanks to the event organizer for inviting me as a speaker at the event. The event will be held at Microsoft Penang Office on September 9, 2015 and September 10, 2015.

Session Synopsis:
Team Foundation Server 2015 brings a major overhaul to the Team Foundation Server build system that not only addresses numerous points of customer feedback – like a web UI, change auditing, better agent pool management, and high availability but also unlocks whole new worlds by creating a dramatically simpler extensibility model, providing tasks for building almost anything you want – Java, Android, IOS, Node.js and more, and a cross platform agent that enables you to build on any platform you choose. In this session, you will see an end-to-end demonstrations of these capabilities within Team Foundation Server 2015.DX FY16Q1 Event Agenda-Partners (1)

Change Windows hostname from command-line interface

You can use the following command to change computer’s hostname through the command-line interface:

netdom renamecomputer %COMPUTERNAME% /Newname “NEW-NAME”

Rebuild Team Foundation Server client’s computer cache

You can force a rebuild of the cache on each client computer the next time the client computer connects to a team project collection by using the witadmin rebuildcache command.

To prevent workspace errors from occurring during version control or build operations in Team Foundation, the data cache on client computers must be updated after certain maintenance operations. After you move, restore, rename, or fail over a data-tier or application-tier server, you must refresh the cache for tracking work items and users must refresh the version control cache on client computers.

The following is the syntax of the witadmin rebuildcache command:

witadmin rebuildcache /collection:CollectionURL [/noprompt]

Example:
The following command invalidates the metadata cache for all clients that connect to Tfs_DefaultCollection that is defined on the server that is named TfsServer. The client caches are updated the next time they connect to the project collection.

witadmin rebuildcache /collection:http://TfsServer:8080/tfs/Tfs_DefaultCollection

Configure Team Foundation Server Proxy <Servers> nodes

The <Servers> node contains one or more nodes that correspond to one or more instances of Visual Studio Team Foundation Server or team project collections from which the proxy server caches files.

Each <Server> node contains a <Uri> node that specifies either a computer that is running Team Foundation Server or a single team project collection on Team Foundation Server.

1) If you specify a computer that is running Team Foundation Server, the proxy can cache files from all collections on that server.

2) If you specify a single team project collection, the proxy can cache files only from that collection.

Example 1:
The <Servers> node might resemble the following example if the proxy were caching version control files from every collection on a server that is named TfsServer, using the default values for port and virtual directory:


<Servers>
 <Server>
  <Uri>
  http://tfsserver:8080/Tfs
  </Uri>
 </Server>
</Servers>

Example 2:
The <Servers> node might resemble the following example if the proxy were caching version control files from Tfs_DefaultCollection on a server that is named TfsServer, using the default values for port and virtual directory:


<Servers>
 <Server>
  <Uri>
  http://tfsserver:8080/Tfs/Tfs_DefaultCollection
  </Uri>
 </Server>
</Servers>

How to clear the Team Foundation Server 2010/2012/2013 cache on client machines?

You just need to save the following content to a .bat file and execute on the saved .bat file on the client machine.

REM **********************************************************
REM Script for clearing Team Explorer 2010/2012/2013 cache
REM **********************************************************

REM **********************************************************
REM Change directory to the Team Foundation Server 2010 client cache
REM **********************************************************
cd “%localappdata%\Microsoft\Team Foundation\3.0\Cache\”
for /d %%a in (*_http) do rmdir /s /q %%a

REM **********************************************************
REM Change directory to the Team Foundation Server 2012 client cache (ignore if it does not exist)
REM **********************************************************
cd “%localappdata%\Microsoft\Team Foundation\4.0\Cache\”
for /d %%a in (*_http) do rmdir /s /q %%a

REM **********************************************************
REM Change directory to the Team Foundation Server 2013 client cache (ignore if it does not exist)
REM **********************************************************
cd “%localappdata%\Microsoft\Team Foundation\5.0\Cache\”
for /d %%a in (*_http) do rmdir /s /q %%a

ECHO “All Done!”

Follow

Get every new post delivered to your Inbox.