Point in time restore - cannot change date

I am trying to roll back a SQL Server 2000 database to a previous point
in time, but I am not allowed to select the date I want in the 'point
in time' dialog - it always reverts back to the date of the transaction
log backup.
So far, I have done the following, in the following order:
1. Backed up the database.
2. Backed up the transaction log.
3. Restored the database from a previous backup.
4. Do a 'point in time restore' from the transaction log backup
completed in step 2.
At step 4, if I follow through with the restore, choosing the date and
time of the log backup as my 'point in time', I sucessfully get the
data I started with.
Can anyone tell me why I cannot specify a 'point in time' of, say, five
days ago - and, more importantly, *how* to specify another point in
time? If the transaction log has the data to restore my database to the
point at which the log backup was taken, surely it has the data to
restore to a point a few days earlier.
Thanks,
Joejoe
Do you perfom it from EM, right?
There is very good topic ( with examples) about it in the BOL
"joe" <joe.hodsdon@.gmail.com> wrote in message
news:1155098591.530691.44480@.n13g2000cwa.googlegroups.com...
>I am trying to roll back a SQL Server 2000 database to a previous point
> in time, but I am not allowed to select the date I want in the 'point
> in time' dialog - it always reverts back to the date of the transaction
> log backup.
> So far, I have done the following, in the following order:
> 1. Backed up the database.
> 2. Backed up the transaction log.
> 3. Restored the database from a previous backup.
> 4. Do a 'point in time restore' from the transaction log backup
> completed in step 2.
> At step 4, if I follow through with the restore, choosing the date and
> time of the log backup as my 'point in time', I sucessfully get the
> data I started with.
> Can anyone tell me why I cannot specify a 'point in time' of, say, five
> days ago - and, more importantly, *how* to specify another point in
> time? If the transaction log has the data to restore my database to the
> point at which the log backup was taken, surely it has the data to
> restore to a point a few days earlier.
> Thanks,
> Joe
>|||Hi Uri,
Thanks for the reply. Yes, I'm working in EM. I reviewed the Books
Online before doing anything - that's where I got much of my
instruction. According to the "How to restore a point in time" article
in BOL, I should be able to specify a date and time to which I want to
restore. But EM won't let me change the date.
Thanks again,
Joe
Uri Dimant wrote:[vbcol=seagreen]
> joe
> Do you perfom it from EM, right?
> There is very good topic ( with examples) about it in the BOL
> "joe" <joe.hodsdon@.gmail.com> wrote in message
> news:1155098591.530691.44480@.n13g2000cwa.googlegroups.com...|||Joe,
I remember an issue in SQL2000 with point in time restore from EM.
Actually the very first time you try to restore a database from EM
point in time restore doesn't work. You have to do it from QA using the
SQL statements. I thought it has been fixed in one of the service packs
but don't remember which one.
Markus|||Thank you, I'll give that a try.
Joe
MarkusB wrote:
> Joe,
> I remember an issue in SQL2000 with point in time restore from EM.
> Actually the very first time you try to restore a database from EM
> point in time restore doesn't work. You have to do it from QA using the
> SQL statements. I thought it has been fixed in one of the service packs
> but don't remember which one.
> Markus

Point in time restore - cannot change date

I am trying to roll back a SQL Server 2000 database to a previous point
in time, but I am not allowed to select the date I want in the 'point
in time' dialog - it always reverts back to the date of the transaction
log backup.
So far, I have done the following, in the following order:
1. Backed up the database.
2. Backed up the transaction log.
3. Restored the database from a previous backup.
4. Do a 'point in time restore' from the transaction log backup
completed in step 2.
At step 4, if I follow through with the restore, choosing the date and
time of the log backup as my 'point in time', I sucessfully get the
data I started with.
Can anyone tell me why I cannot specify a 'point in time' of, say, five
days ago - and, more importantly, *how* to specify another point in
time? If the transaction log has the data to restore my database to the
point at which the log backup was taken, surely it has the data to
restore to a point a few days earlier.
Thanks,
Joejoe
Do you perfom it from EM, right?
There is very good topic ( with examples) about it in the BOL
"joe" <joe.hodsdon@.gmail.com> wrote in message
news:1155098591.530691.44480@.n13g2000cwa.googlegroups.com...
>I am trying to roll back a SQL Server 2000 database to a previous point
> in time, but I am not allowed to select the date I want in the 'point
> in time' dialog - it always reverts back to the date of the transaction
> log backup.
> So far, I have done the following, in the following order:
> 1. Backed up the database.
> 2. Backed up the transaction log.
> 3. Restored the database from a previous backup.
> 4. Do a 'point in time restore' from the transaction log backup
> completed in step 2.
> At step 4, if I follow through with the restore, choosing the date and
> time of the log backup as my 'point in time', I sucessfully get the
> data I started with.
> Can anyone tell me why I cannot specify a 'point in time' of, say, five
> days ago - and, more importantly, *how* to specify another point in
> time? If the transaction log has the data to restore my database to the
> point at which the log backup was taken, surely it has the data to
> restore to a point a few days earlier.
> Thanks,
> Joe
>|||Hi Uri,
Thanks for the reply. Yes, I'm working in EM. I reviewed the Books
Online before doing anything - that's where I got much of my
instruction. According to the "How to restore a point in time" article
in BOL, I should be able to specify a date and time to which I want to
restore. But EM won't let me change the date.
Thanks again,
Joe
Uri Dimant wrote:
> joe
> Do you perfom it from EM, right?
> There is very good topic ( with examples) about it in the BOL
> "joe" <joe.hodsdon@.gmail.com> wrote in message
> news:1155098591.530691.44480@.n13g2000cwa.googlegroups.com...
> >I am trying to roll back a SQL Server 2000 database to a previous point
> > in time, but I am not allowed to select the date I want in the 'point
> > in time' dialog - it always reverts back to the date of the transaction
> > log backup.
> >
> > So far, I have done the following, in the following order:
> >
> > 1. Backed up the database.
> > 2. Backed up the transaction log.
> > 3. Restored the database from a previous backup.
> > 4. Do a 'point in time restore' from the transaction log backup
> > completed in step 2.
> >
> > At step 4, if I follow through with the restore, choosing the date and
> > time of the log backup as my 'point in time', I sucessfully get the
> > data I started with.
> >
> > Can anyone tell me why I cannot specify a 'point in time' of, say, five
> > days ago - and, more importantly, *how* to specify another point in
> > time? If the transaction log has the data to restore my database to the
> > point at which the log backup was taken, surely it has the data to
> > restore to a point a few days earlier.
> >
> > Thanks,
> > Joe
> >|||Joe,
I remember an issue in SQL2000 with point in time restore from EM.
Actually the very first time you try to restore a database from EM
point in time restore doesn't work. You have to do it from QA using the
SQL statements. I thought it has been fixed in one of the service packs
but don't remember which one.
Markus|||Thank you, I'll give that a try.
Joe
MarkusB wrote:
> Joe,
> I remember an issue in SQL2000 with point in time restore from EM.
> Actually the very first time you try to restore a database from EM
> point in time restore doesn't work. You have to do it from QA using the
> SQL statements. I thought it has been fixed in one of the service packs
> but don't remember which one.
> Markus

Please help! I have lots of questions.

In case some of you have read my previous posts, you may be aware that I'm writing a webboard application as a replacement for the old one.

The old one currently have approximately 50000 topics in the database, each has on average 10 replies (I just check recently. I though it was only 7000 topics).

I need to provide paging and sorting feature to the topic list. But I can't just SELECT all of them and let GridView do the paging/sorting, right?
I have been using stored procedures to store my SQL statement for several projects now. I know how to deal with the paging feature (ROW_NUMBER), but the sorting requires me to change to change the "ORDER BY" clause.

1. Can somebody tell me how to change the ORDER BY clause in the stored procedure(s) at runtime? Or does anyone have other approach?

Currently I'm thinking about moving back from store procedures to hard-code SQL statements, and then modify/generate the SQL statement for each paging / sorting. But I've learn that stored procedures give more performance and security.

2. According to the situation I provided, is it worth moving from stored procedures to hard-code SQL?

I'm also using 3-tier architecture approach + OOP. But I reach a conflict in my thoughts.

You see, according to OOP, I'm supposed to create classes that reflect the actual objects in the real-world, right? In my case the classes are "Board, Topic, Reply, ..." According to this and 3-tier approach, I intend to use ObjectDataSource as a bridge between Presentation Logic and Business Logic. But I wonder what my datasource class should return

3. Should my data source class return data objects like

1st approach

[DataObject(True)]
pubic class TopicDataSource{
public static Topic[] GetTopicList() { }
}

or should it return DataSet / DataTable / DataReader like

2nd approach

[DataObject(True)]
public class TopicDataSource{
public static DataTable GetTopicList() {}
}

Personally I think approach 1 is more OOP and allow for more extendability, but approach 2 might be faster.

4. If I go with approach 1, how should I control which property of my data objects is read-onlyafter it's has been inserted/created?
Can I just set my data object's property to be readonly? Or do I have to set it at page level (i.e. GridView-> Columns -> BoundField -> ReadOnly=True)?
Or do I set it and the page leveland write a code to throw an exception in the rare case the application / user try to change it's value? Or else?

Please help. These questions slow me down for days now.

If there's any concepts that I misunderstood, please tell me. I'm aware that I don't know as much as some of you.

I will be extremely grateful to anyone who answer any of my questions.

Thanks a lot.

PS. For those who think my questions are stupid, I'm very, very sorry that I bother you.

Hello my friend,

First thing - do not call yourself stupid for asking questions. The stupid ones are those that never ask and then end up breaking something or building something crap. My answers are as follows: -

1) Use stored procedures for performance and then change the order by clause by using a DataView, like this: -

// instead of this
//myRepeater.DataSource = myDataSet;
//myRepeater.DataBind();

// do this (use a stored procedure to fill a dataset and then filter the dataset)
DataView dv = new DataView(myDataSet.Tables[0]);
dv.Sort = "MyField DESC";

myRepeater.DataSource = dv;
myRepeater.DataBind();

2) If all you need to change is the order by clause, use stored procedures with the above example. Only go back to hardcoded SQL if you need to specify the fields to select at runtime or if there are very complex and/or clauses at runtime and the data has a lot of records.

3) If you find approach 2 to be faster then use it. Extensibility is no substitute for performance. We do all the hard stuff so the application runs fast, as opposed to building a second class project to suit us.

4) You can have a Locked property, or whatever name you prefer, on your object. Then in the set property definition, have the following: -

Set(ByVal value As String)
If Me.Locked = False Then
MyProperty = value
End If
End Set

I hope this helps you.

Kind regards

Scotty

|||Thank you Scotty (or Scott, perhaps). That help answer question 2, 3, 4 quite clear. Although if anyone think differently, or if anyone think something should be added, don't hesitate. Please answer it here.

But I still worry about problem 1.
Does the dataset get everything after calling SQLDataAdapter.Fill? In case I SELECT everything without paging? I mean, won't it use too much memory?

Also, if I implement paging in stored procedure, the sorting won't be correct.
For example if the data is like this

Table: Test
Test1
Row 1: a
Row 2: b
Row 3: c
Row 4: d
Row 5: e

Situation : startRecord = 0 MaxRecords = 3 sortColumns = "Test1 DESC"

If I use paging, I will get "a, b, c", right?
Then if I use dataview sorting, my data will reorder from "a, b, c" to - "c, b, a" which is the final result.
But that's not what we actually want. The final data should be "e, d, c" not "c, b, a", right?

Can somebody help?|||

I just thought of something.

Even if I plan to have 2nd, 3rd ... versions, performance come first, right? Just to be sure.

As I told you above, my program intend to replace an existing one create by another company. But currently version 1's main objective is to replace the old system only. I intend to have new features added after version 1 is finished, to be version 2.