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
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
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.
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;
