0

How do I restore database only with .trn (transaction log backup) file?

I did below two steps Microsoft SQL Server Management studio shows "Restoring".

1)

RESTORE DATABASE [database_name]
  FROM DISK = N'path\file_name.bak'
  WITH MOVE N'logical_filename' TO N'path\filename.mdf',
       MOVE N'logical_filename_log to N'path\filename.ldf' REPLACE,
       NORECOVERY;
GO

2)

RESTORE LOG [database_name]
  FROM DISK = N'path\filename.trn'
  WITH NORECOVERY;
GO
  • remember that restore ... with RECOVERY is default .. so if you just use restore log db_name .. from disk .. - a recovery clause is dafault. So you dont have to specify (but its a good practice to write it, so you know that you are bringing the database online. – Kin Shah Oct 27 '15 at 21:33
3

Run this:

RESTORE LOG [database_name] FROM DISK = N'path\filename.trn' WITH RECOVERY; GO

Notice I've changed it to WITH RECOVERY - this runs the recovery process and puts the database in a usable state.

Running WITH NORECOVERY leaves the database in a state where further log files can be restored. This state prevents you using the database until you do a RESTORE LOG ... WITH RECOVERY

0

Yes, as per Tim Chapman sql blog. But be sure that before restore your database recovery mode was set to 'FULL'. And also set your database recovery mode is set to 'FULL' , for a point in time to recover a data table in future perspective. If you don't have then follow these steps

Step 1

From TSQL Commad to change Full recovery Model

ALTER DATABASE databaseName SET RECOVERY FULL

Step 2

To set the full recovery model for your database via the GUI, right click on the database name and select Properties. Then go to option tab and change the recovery model.

A transaction log backup keeps track of all transactions that have occurred since the last transaction log backup; it also allows you to restore your database to a point in time before a database error occurred.

I would like to say that here if you are storing the (.trn) transaction log backup. You can restore your transaction log file with two ways.

Step1

As Mr. Max Vernon already said

RESTORE LOG [database_name] FROM DISK = N'path\filename.trn' WITH RECOVERY; 
GO

it recover the database as part of the last RESTORE LOG.

Step2

If you Wait to recover the database by using a separate RESTORE DATABASE then do this below statement:

RESTORE LOG [database_name] FROM DISK = N'path\filename.trn' WITH NORECOVERY; 
GO
RESTORE DATABASE [database_name] FROM DISK = N'path' WITH RECOVERY;
GO

Waiting to recover the database gives you the opportunity to verify that you have restored all of the necessary log backups. This approach is often advisable when you are performing a point-in-time restore.

for ref http://www.techrepublic.com/blog/the-enterprise-cloud/restore-your-sql-server-database-using-transaction-logs/ and https://msdn.microsoft.com/en-us/library/ms177446.aspx

NB:- As per MSDN BOL, you always explicitly specify either 'WITH NORECOVERY' or 'WITH RECOVERY' in every 'RESTORE' statement to eliminate ambiguity. This is particularly important when writing scripts.

0

If you have restored all of the log backups and forgotten to use WITH RECOVERY on the last one then run the following.

RESTORE LOG [database_name]
WITH RECOVERY;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.