Restore Database from .mdf, .ldf, and .trn, but no .bak file

[TWillard]

We are testing out a new software application that creates sql server clones and snapshots. The application copies the data files at the block level from the SAN. We are thinking about using the clones and snapshots as our database backup solution. Therefore, we will stop the routine creation of bak files and continue to create trn files. (Please provide feedback, if our strategy does not make sense.)

Once a clone or snapshot is restored to disk, the database can be attached in seconds. The software restores the mdf and ldf files from the point in time of the clone/snapshot creation. The problem I am currently facing is re-applying trn files to the database clone/snapshot.

I understand that normal recovery steps of a database and transaction logs includes the following steps and use a .bak and .trn files.
1. Restore a full backup and use the WITH NORECOVERY option
2. Then each transaction log made after that full backup has to be restored in order. For ALL but the LAST one, you need to use the WITH NORECOVER. On the last one, use the WITH RECOVERY option.

How can I restore a database from .mdf, .ldf and .trn files. Ideally, I would like to perform the following steps:
1. Attach the database from .mdf and .ldf.
2. Place the database in NORECOVERY state.
3. Apply trn log files as needed.
4. Place the database back into RECOVERY state.

Thanks in advance.

 

[yelworcm]

I don't know of any way to put a database into recovery mode, except by restoring a full backup. Can you do a full backup of the snapshot?

 

[mrdenny]

In order to roll forward the transaction log backups you would still need to take a full backup. Even if you back a snapshot backup of the database and then did a full backup of it, you wouldn't be able to roll the logs forward as the LSNs wouldn't line up correctly.

Keep doing your full backups as normal.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLBill]

How can I restore a database from .mdf, .ldf and .trn files

You can't. You cannot 'restore' a database from the .mdf and .ldf files. You are 'attaching' a database when you use those files. You restore from a backup file. So you have two choices:

1. attach the .mdf and .ldf and forget about the .trn files.
2. do backups and restore them to the second server.

-SQLBill

Posting advice: FAQ481-4875

 

[TWillard]

Everyone,

Thank you for the responses. You have answered my question. I was hoping that there would be a way to restore from mdf,ldf and trn. That does not appear to be the case.

Tim