Point-In-Time Restoration Issue

Hello,

I'm testing "Point In Time" restoration for my system using both Database & Log backup files. (Database backup once a day; Log backup every 4 hours)

When I use T-SQL to perfrom the restoration, I can specify one .BAK file with numerous .TRN files and restore to any 'point of time' with no issue.

However, if I use EM to perform the same restoration, I can only specify one .BAK file with a maximum of two .TRN files (although I can see all the .TRN files) in order to restore the database properly. If I specify more .TRN files, after restoration, my DB will be in 'LOADING' status and can't be used.

Does anyone encounter the same problem before and know what is going on?

Thank You.When restoring a database with Enterprise Manager, make sure the 'Leave Database Operational' is checked.|||you can also always switch the database from "Loading" to "operational" modes with simple tsql command

restore database <Dbname> with recovery

simas|||hi guys,

thanks for the replies. I'll look into it.

btw, I did found out some documentation that closely describe my problem, it's "Microsoft Knowledge Base Article - 319697, FIX: SQL Enterprise Manager Restore to Point in Time Does Not Stop at Requested Time and the Database is Left in a Loading State"

Seems like it's not something new...

Thank You.

Point-in-time backup of database & filesystem

We're considering purchasing an application,
which stores some data in the filesystem, and
some data (meta data and links to files) in
MS-SQL.

We need to be able to create a backup wherein
the database and files are "in synch" (in other words,
in a consistent state). We need to maintain the
"referential integrity" between database and files.

What I'm thinking of is something like this:
1. stop the application server
2. set database to readonly (flush updates
from cache to disk)
3. backup database to disk files
4. back up application files and backup files.
(We could use "snapshot" capabilities of our disk
hardware, so that the backup could run against the
snapshot; that would minimize downtime.)
4. set database to read_write; restart app server

Recovery scenario would be:
1. Stop application server.
2. restore application files and database backup files
3. restore database from disk backup
4. restart application server

Is there a simpler way to get a "point in time" backup
of application files and the database?"L. Bertolini" <bertolini.1@.osu.edu> wrote in message news:<c25aq0$59b$1@.charm.magnus.acs.ohio-state.edu>...
> We're considering purchasing an application,
> which stores some data in the filesystem, and
> some data (meta data and links to files) in
> MS-SQL.
> We need to be able to create a backup wherein
> the database and files are "in synch" (in other words,
> in a consistent state). We need to maintain the
> "referential integrity" between database and files.
> What I'm thinking of is something like this:
> 1. stop the application server
> 2. set database to readonly (flush updates
> from cache to disk)
> 3. backup database to disk files
> 4. back up application files and backup files.
> (We could use "snapshot" capabilities of our disk
> hardware, so that the backup could run against the
> snapshot; that would minimize downtime.)
> 4. set database to read_write; restart app server
> Recovery scenario would be:
> 1. Stop application server.
> 2. restore application files and database backup files
> 3. restore database from disk backup
> 4. restart application server
> Is there a simpler way to get a "point in time" backup
> of application files and the database?

The first place to ask would be the vendor - find out what their
recommended backup strategy is, and how they say you should recover in
case of failure. If they can't give you a good answer, then you should
make that fact part of your decision.

The snapshot backup might be a good option if you already have the
hardware to support it - you probably wouldn't need to stop the server
at all in that case.

Simonsql