Restore .trn

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris - 30 Jun 2008 17:24 GMT

Hi all,

I would like to test that restorations are working from the .bak that is
created each night and .trn files that are created every 6 hours. How do we
go about it?

I hear we may have to use a 3rd party application, but I'm open to
suggestions. Thanks

Chris

Reply to this Message

Andrew J. Kelly - 30 Jun 2008 18:17 GMT

Why would you have to use a 3rd party app? Just restore the backups to
another instance of sql server or to the same instance with a different name
and file locations. You can do this from the command line or from SSMS. Be
careful of restoring to the same instance if you have never done that before
so you don't hose your original db. By the way you should reconsider why you
are only doing log backups every 6 hours. You risk up to 6 hours worth of
data loss that way.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> Hi all,
>
[quoted text clipped - 7 lines]
>
> Chris

Reply to this Message

Chris - 01 Jul 2008 15:54 GMT

Andrew,

Thanks for the info...the reason I ask is I've only done it with .baks
before...but never .bak and .trn. If you could give me a quick step by step
that would be great please. Thanks!

Chris

> Why would you have to use a 3rd party app? Just restore the backups to
> another instance of sql server or to the same instance with a different name
[quoted text clipped - 15 lines]
> >
> > Chris

Reply to this Message

Tibor Karaszi - 01 Jul 2008 16:06 GMT

Since you are testing restore, I assume that you have some degree of responsibility for restore. I'm
saying this just se we agree on the bar here: You are familiar with both database as well as log
restore. If that isn't the case, I suggest you talk to your employer to shell out for some
education/study time. This I say for the benefit for both you as well as your employer.

Having said above, and assuming you are familiar with log restore in general:
You said you've done this (test restore on other machine) for db backup but not log backups. No
problem. The restore process for log backups is the same when done to some other machine as when
"local". Specify NORECOVERY for all but the last restore command. And you only need to specify MOVE
options for the first (DB) restore.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Andrew,
>
[quoted text clipped - 23 lines]
>> >
>> > Chris

Reply to this Message

Chris - 01 Jul 2008 16:14 GMT

Hi all,

If anyone can elaborate / give an actual step by step guide I'd greatly
appreciate it. Can .trn restores be done through both the management console
and also using scripts?

Thanks

Chris

> Since you are testing restore, I assume that you have some degree of responsibility for restore. I'm
> saying this just se we agree on the bar here: You are familiar with both database as well as log
[quoted text clipped - 34 lines]
> >> >
> >> > Chris

Reply to this Message

Tibor Karaszi - 01 Jul 2008 16:29 GMT

Since you say "management console" I assume we are talking about SQL Server 2000. Yes, the GUI (MMC)
is only a GUI which construct and execute your TSQL commands. Here are bare-bone examples of the
RESTORE commands:

RESTORE DATABASE db FROM DISK = 'C:\db.bak'
WITH
MOVE 'db' TO 'C:\db.mdf'
,MOVE 'db_log' TO 'C:\db_log.ldf'
,NORECOVERY

RESTORE LOG db FROM DISK = 'C:\db_log1.trn'
WITH
,NORECOVERY

RESTORE LOG db FROM DISK = 'C:\db_log2.trn'
WITH
,RECOVERY

To restore from the GUI, right-click the databases folder and select "Restore" in here, select "From
device" and specify the options for your first backup file to restore. Make sure you select
NORECOVERY and new filenames for the database files. Then repeat for the two log restores. I suggest
you use the TSQL commands instead - much easier....

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Hi all,
>
[quoted text clipped - 46 lines]
>> >> >
>> >> > Chris

Reply to this Message

Chris - 01 Jul 2008 16:40 GMT

Tibor,

That's great. I had added the files in like this but wanted to confirm
before I click OK ad go for it. Great stuff!

Chris

> Since you say "management console" I assume we are talking about SQL Server 2000. Yes, the GUI (MMC)
> is only a GUI which construct and execute your TSQL commands. Here are bare-bone examples of the
[quoted text clipped - 69 lines]
> >> >> >
> >> >> > Chris

Reply to this Message