I am trying to sum some data in a table, and I can't figure it out. Below
is some sample data
Date,Customer,Order_Amt,Paid_Amt
2004-10-02 00:00:00,101,1418.82,-1400.00
2004-10-02 00:00:00,101,265.21,-200.00,
2004-10-02 00:00:00,101,648.74,-648.74
2004-10-02 00:00:00,102,95.95,-95.95
2004-10-02 00:00:00,102,457.42,-450.00
I trying to sum all of the orders for each customer from yesterday, without
showing each order, just the date, customer number, total orders, total
paid. I also want to add a 5th column stating order_amt-Paid_amt. Although
pathetic, this is the furthest I got :
select date, cust, order_amt, paid_amt from day_sales
where date > getdate()-2
group by date, cust, order_amt, paid_amt
I would these results :
date,cust,order_total,paid_total,amt_owe
d
2004-10-02 00:00:00,101,2332.77,-2248.74,84.03
Any help would be greatly appreciated.
ThanksTry this:
select date, cust,
Count(*) OrderCount,
Sum(order_amt) TotalAmt,
Sum(paid_amt) TotalPaid,
Sum(order_amt-Paid_Amt) Balance
from day_sales
where date > DateAdd(day, -2, getdate())
group by date, cust
"J Abrams" wrote:
> I am trying to sum some data in a table, and I can't figure it out. Below
> is some sample data
> Date,Customer,Order_Amt,Paid_Amt
> 2004-10-02 00:00:00,101,1418.82,-1400.00
> 2004-10-02 00:00:00,101,265.21,-200.00,
> 2004-10-02 00:00:00,101,648.74,-648.74
> 2004-10-02 00:00:00,102,95.95,-95.95
> 2004-10-02 00:00:00,102,457.42,-450.00
> I trying to sum all of the orders for each customer from yesterday, withou
t
> showing each order, just the date, customer number, total orders, total
> paid. I also want to add a 5th column stating order_amt-Paid_amt. Althou
gh
> pathetic, this is the furthest I got :
> select date, cust, order_amt, paid_amt from day_sales
> where date > getdate()-2
> group by date, cust, order_amt, paid_amt
> I would these results :
> date,cust,order_total,paid_total,amt_owe
d
> 2004-10-02 00:00:00,101,2332.77,-2248.74,84.03
> Any help would be greatly appreciated.
> Thanks
>
>|||Hi
Check out GROUP BY and SUM in books online.
Try (untested);
Select date, cust, SUM(order_amt), SUM(paid_amt), SUM(order_amt)
-SUM(paid_amt) AS Outstanding, COUNT(*) AS No_Orders from day_sales
where date > getdate()-2
group by date, cust
Your getdate()-2 may not give you the exact information required if you
have times with the orders to round to day one way is to use convert e.g.
Select CONVERT(CHAR(8),date,112) AS Date, cust, SUM(order_amt),
SUM(paid_amt), SUM(order_amt) -SUM(paid_amt) AS Outstanding, COUNT(*) AS
No_Orders from day_sales
where CONVERT(CHAR(8),date,112) >= CONVERT(CHAR(8),getdate()-2,112)
group by CONVERT(CHAR(8),date,112), cust
John
"J Abrams" wrote:
> I am trying to sum some data in a table, and I can't figure it out. Below
> is some sample data
> Date,Customer,Order_Amt,Paid_Amt
> 2004-10-02 00:00:00,101,1418.82,-1400.00
> 2004-10-02 00:00:00,101,265.21,-200.00,
> 2004-10-02 00:00:00,101,648.74,-648.74
> 2004-10-02 00:00:00,102,95.95,-95.95
> 2004-10-02 00:00:00,102,457.42,-450.00
> I trying to sum all of the orders for each customer from yesterday, withou
t
> showing each order, just the date, customer number, total orders, total
> paid. I also want to add a 5th column stating order_amt-Paid_amt. Althou
gh
> pathetic, this is the furthest I got :
> select date, cust, order_amt, paid_amt from day_sales
> where date > getdate()-2
> group by date, cust, order_amt, paid_amt
> I would these results :
> date,cust,order_total,paid_total,amt_owe
d
> 2004-10-02 00:00:00,101,2332.77,-2248.74,84.03
> Any help would be greatly appreciated.
> Thanks
>
>|||thanks for your help, that did the trick. I have one quick question. How
does sql know how to count the total orders per customer ? What if I wanted
a count of all of the orders that equal 1418.82 ? I don't see anything in
the script below that points the count command to the customer number.
Thanks again for your help, I really appreciate it !!
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:18FE707F-8A17-48C5-BEFE-DAE19FADF953@.microsoft.com...
> Try this:
> select date, cust,
> Count(*) OrderCount,
> Sum(order_amt) TotalAmt,
> Sum(paid_amt) TotalPaid,
> Sum(order_amt-Paid_Amt) Balance
> from day_sales
> where date > DateAdd(day, -2, getdate())
> group by date, cust
> "J Abrams" wrote:
>|||The group By statement says to SQL:
1) Collect all the records which match the criteria, and group them into one
groups, based on the values of the columns Cust, and Date, Then
2) Output ONE ROW for each of those GROUPS...
3) Any expression in the Select Clause, which has an aggregate function
(Sum, Count, Min, Max, etc.) IS then evaluated for ALL The records in each o
f
those constructed groups...
"J Abrams" wrote:
> thanks for your help, that did the trick. I have one quick question. How
> does sql know how to count the total orders per customer ? What if I want
ed
> a count of all of the orders that equal 1418.82 ? I don't see anything in
> the script below that points the count command to the customer number.
> Thanks again for your help, I really appreciate it !!
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:18FE707F-8A17-48C5-BEFE-DAE19FADF953@.microsoft.com...
>
>|||Hi
If you wanted to restrict the whole query to add up certain rows i.e an
order_amt value of 1418.82 then you would need to add this to the where
clause.
i.e.
where date > DateAdd(day, -2, getdate())
and order_amt = 1418.82
You should notice that the OrderCount column is less than without the
additional clause (unless the customer only ever orders the one
amount!).
If you wanted to have an additional count that summed everything but
counted the number of times they ordered for an amout of 1418.82
select date, cust,
Count(*) AS OrderCount,
SUM(CASE WHEN order_amt = 1418.82 THEN 1 ELSE 0 END) AS
OrderedSpecificAmtCount,
Sum(order_amt) AS TotalAmt,
Sum(paid_amt) AS TotalPaid,
Sum(order_amt-Paid_Amt) AS Balance
from day_sales
where date > DateAdd(day, -2, getdate())
group by date, cust
The rest is in books online. Please spend some time reading it as is a
very rich source of information.
John
J Abrams wrote:
> thanks for your help, that did the trick. I have one quick question.
How
> does sql know how to count the total orders per customer ? What if I
wanted
> a count of all of the orders that equal 1418.82 ? I don't see
anything in
> the script below that points the count command to the customer
number.
> Thanks again for your help, I really appreciate it !!
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:18FE707F-8A17-48C5-BEFE-DAE19FADF953@.microsoft.com...
out.
yesterday,
total
Showing posts with label customer. Show all posts
Showing posts with label customer. Show all posts
Monday, March 26, 2012
Monday, March 12, 2012
Newbie Locking question
How many lock requests is too many? To explain, we have a customer who is
reporting some slow performance problems and he says that performance monito
r
is recording an average of 30 million lock requests and he thinks this is
excessive. So I guess my real question is, is he right, is this excessive?
I don't know much about SQL Locking so your patience is much appreciated.
Thank you for time,Drew
http://www.sql-server-performance.c...ucing_locks.asp
http://www.sql-server-performance.c...locks_hints.asp
"DrewV" <DrewV@.discussions.microsoft.com> wrote in message
news:95D799B0-A124-4566-A79E-FF5A4E637187@.microsoft.com...
> How many lock requests is too many? To explain, we have a customer who is
> reporting some slow performance problems and he says that performance
> monitor
> is recording an average of 30 million lock requests and he thinks this is
> excessive. So I guess my real question is, is he right, is this
> excessive?
> I don't know much about SQL Locking so your patience is much appreciated.
> Thank you for time,
reporting some slow performance problems and he says that performance monito
r
is recording an average of 30 million lock requests and he thinks this is
excessive. So I guess my real question is, is he right, is this excessive?
I don't know much about SQL Locking so your patience is much appreciated.
Thank you for time,Drew
http://www.sql-server-performance.c...ucing_locks.asp
http://www.sql-server-performance.c...locks_hints.asp
"DrewV" <DrewV@.discussions.microsoft.com> wrote in message
news:95D799B0-A124-4566-A79E-FF5A4E637187@.microsoft.com...
> How many lock requests is too many? To explain, we have a customer who is
> reporting some slow performance problems and he says that performance
> monitor
> is recording an average of 30 million lock requests and he thinks this is
> excessive. So I guess my real question is, is he right, is this
> excessive?
> I don't know much about SQL Locking so your patience is much appreciated.
> Thank you for time,
newbie help--restore to point in time
I have a customer who's cart has been deleting orders incorrectly. I
backed up and restored the database from the remote server to my local.
Would like to restore to point in time before the delete operation.
>From various sources this is the script I'm using:
BACKUP LOG plebesummercom
TO disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\
EDTWebsite\butrans\translog.bak'
WITH NORECOVERY, No_truncate
Go
RESTORE DATABASE plebesummercom
FROM disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\
EDTWebsite\dbbu080106\8_1_2006_5_30_
42_PM.bak'
WITH NORECOVERY;
GO
RESTORE LOG [plebesummercom]
FROM DISK = N'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\
EDTWebsite\butrans\translog.bak'
WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
11:13:04'
GO
Ok, no errrors are thrown, but when I select from a table, I'm still
finding orders with dates past July 16. Am I trying to do something not
allowed? Both the database bu and the log bu contain transactions and
data through August 2nd. Can someone point me in the right direction?Why are you using the NOUNLOAD option when recovering from a file?
Also,
What is the "N" right before the date/time in your STOPAT ?
Your first statement here says to backup log? I think you would want:
RESTORE DATABASE
(FROM ...)
WITH NORECOVERY
--this one for every full log you want to roll in:
RESTORE LOG
(FROM ...)
WITH NORECOVERY
RESTORE LOG
(FROM ...)
WITH RECOVERY, STOPAT = 'Date/time'
Also,
check out the RESTORE topic in Books Online.
I hope this is helpful.
medusa wrote:
> I have a customer who's cart has been deleting orders incorrectly. I
> backed up and restored the database from the remote server to my local.
> Would like to restore to point in time before the delete operation.
> BACKUP LOG plebesummercom
> TO disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\
EDTWebsite\butrans\translog.bak'
> WITH NORECOVERY, No_truncate
> Go
> RESTORE DATABASE plebesummercom
> FROM disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\
EDTWebsite\dbbu080106\8_1_2006_5_3
0_42_PM.bak'
> WITH NORECOVERY;
> GO
> RESTORE LOG [plebesummercom]
> FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\
EDTWebsite\butrans\translog.bak'
> WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> 11:13:04'
> GO
> Ok, no errrors are thrown, but when I select from a table, I'm still
> finding orders with dates past July 16. Am I trying to do something not
> allowed? Both the database bu and the log bu contain transactions and
> data through August 2nd. Can someone point me in the right direction?|||Well, that was the latest round of code. The n is put in there if you
tell management studio to script the action. The backup log is in there
because if it's not an error is thrown about not backing up the log
tail. Here, this doesn't work either--contains transactions through Aug
2:
BACKUP LOG plebesummercom
TO disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\
EDTWebsite\butrans\translog.bak'
WITH NORECOVERY, No_truncate
Go
RESTORE DATABASE plebesummercom
FROM disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\
EDTWebsite\dbbu080106\8_1_2006_5_30_
42_PM.bak'
WITH NORECOVERY;
GO
RESTORE LOG [plebesummercom]
FROM DISK ='C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\
EDTWebsite\butrans\translog.bak'
WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
GO
SGCSNA wrote:[vbcol=seagreen]
> Why are you using the NOUNLOAD option when recovering from a file?
> Also,
> What is the "N" right before the date/time in your STOPAT ?
> Your first statement here says to backup log? I think you would want:
> RESTORE DATABASE
> (FROM ...)
> WITH NORECOVERY
> --this one for every full log you want to roll in:
> RESTORE LOG
> (FROM ...)
> WITH NORECOVERY
> RESTORE LOG
> (FROM ...)
> WITH RECOVERY, STOPAT = 'Date/time'
> Also,
> check out the RESTORE topic in Books Online.
> I hope this is helpful.
>
> medusa wrote:|||Are you restoring from a full backup that falls *before* your STOPAT
date and all of the subsequent logs, up to the one that contains the
STOPAT date?
medusa wrote:[vbcol=seagreen]
> Well, that was the latest round of code. The n is put in there if you
> tell management studio to script the action. The backup log is in there
> because if it's not an error is thrown about not backing up the log
> tail. Here, this doesn't work either--contains transactions through Aug
> 2:
> BACKUP LOG plebesummercom
> TO disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\
EDTWebsite\butrans\translog.bak'
> WITH NORECOVERY, No_truncate
> Go
> RESTORE DATABASE plebesummercom
> FROM disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\
EDTWebsite\dbbu080106\8_1_2006_5_3
0_42_PM.bak'
> WITH NORECOVERY;
> GO
> RESTORE LOG [plebesummercom]
> FROM DISK ='C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\
EDTWebsite\butrans\translog.bak'
> WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
> GO
>
> SGCSNA wrote:|||The full backup goes through Aug 2. I want to stop at July 16.
SGCSNA wrote:[vbcol=seagreen]
> Are you restoring from a full backup that falls *before* your STOPAT
> date and all of the subsequent logs, up to the one that contains the
> STOPAT date?
> medusa wrote:|||STOPAT only works with Transaction Log Backups, so you would need a
full backup from sometime before the time you want on July 16th and the
subsequent log backups through the time you want on 7/16.
medusa wrote:[vbcol=seagreen]
> The full backup goes through Aug 2. I want to stop at July 16.
> SGCSNA wrote:|||Thanks a lot for you help.
SGCSNA wrote:[vbcol=seagreen]
> STOPAT only works with Transaction Log Backups, so you would need a
> full backup from sometime before the time you want on July 16th and the
> subsequent log backups through the time you want on 7/16.
> medusa wrote:
backed up and restored the database from the remote server to my local.
Would like to restore to point in time before the delete operation.
>From various sources this is the script I'm using:
BACKUP LOG plebesummercom
TO disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\
EDTWebsite\butrans\translog.bak'
WITH NORECOVERY, No_truncate
Go
RESTORE DATABASE plebesummercom
FROM disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\
EDTWebsite\dbbu080106\8_1_2006_5_30_
42_PM.bak'
WITH NORECOVERY;
GO
RESTORE LOG [plebesummercom]
FROM DISK = N'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\
EDTWebsite\butrans\translog.bak'
WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
11:13:04'
GO
Ok, no errrors are thrown, but when I select from a table, I'm still
finding orders with dates past July 16. Am I trying to do something not
allowed? Both the database bu and the log bu contain transactions and
data through August 2nd. Can someone point me in the right direction?Why are you using the NOUNLOAD option when recovering from a file?
Also,
What is the "N" right before the date/time in your STOPAT ?
Your first statement here says to backup log? I think you would want:
RESTORE DATABASE
(FROM ...)
WITH NORECOVERY
--this one for every full log you want to roll in:
RESTORE LOG
(FROM ...)
WITH NORECOVERY
RESTORE LOG
(FROM ...)
WITH RECOVERY, STOPAT = 'Date/time'
Also,
check out the RESTORE topic in Books Online.
I hope this is helpful.
medusa wrote:
> I have a customer who's cart has been deleting orders incorrectly. I
> backed up and restored the database from the remote server to my local.
> Would like to restore to point in time before the delete operation.
> BACKUP LOG plebesummercom
> TO disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\
EDTWebsite\butrans\translog.bak'
> WITH NORECOVERY, No_truncate
> Go
> RESTORE DATABASE plebesummercom
> FROM disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\
EDTWebsite\dbbu080106\8_1_2006_5_3
0_42_PM.bak'
> WITH NORECOVERY;
> GO
> RESTORE LOG [plebesummercom]
> FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\
EDTWebsite\butrans\translog.bak'
> WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> 11:13:04'
> GO
> Ok, no errrors are thrown, but when I select from a table, I'm still
> finding orders with dates past July 16. Am I trying to do something not
> allowed? Both the database bu and the log bu contain transactions and
> data through August 2nd. Can someone point me in the right direction?|||Well, that was the latest round of code. The n is put in there if you
tell management studio to script the action. The backup log is in there
because if it's not an error is thrown about not backing up the log
tail. Here, this doesn't work either--contains transactions through Aug
2:
BACKUP LOG plebesummercom
TO disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\
EDTWebsite\butrans\translog.bak'
WITH NORECOVERY, No_truncate
Go
RESTORE DATABASE plebesummercom
FROM disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\
EDTWebsite\dbbu080106\8_1_2006_5_30_
42_PM.bak'
WITH NORECOVERY;
GO
RESTORE LOG [plebesummercom]
FROM DISK ='C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\
EDTWebsite\butrans\translog.bak'
WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
GO
SGCSNA wrote:[vbcol=seagreen]
> Why are you using the NOUNLOAD option when recovering from a file?
> Also,
> What is the "N" right before the date/time in your STOPAT ?
> Your first statement here says to backup log? I think you would want:
> RESTORE DATABASE
> (FROM ...)
> WITH NORECOVERY
> --this one for every full log you want to roll in:
> RESTORE LOG
> (FROM ...)
> WITH NORECOVERY
> RESTORE LOG
> (FROM ...)
> WITH RECOVERY, STOPAT = 'Date/time'
> Also,
> check out the RESTORE topic in Books Online.
> I hope this is helpful.
>
> medusa wrote:|||Are you restoring from a full backup that falls *before* your STOPAT
date and all of the subsequent logs, up to the one that contains the
STOPAT date?
medusa wrote:[vbcol=seagreen]
> Well, that was the latest round of code. The n is put in there if you
> tell management studio to script the action. The backup log is in there
> because if it's not an error is thrown about not backing up the log
> tail. Here, this doesn't work either--contains transactions through Aug
> 2:
> BACKUP LOG plebesummercom
> TO disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\
EDTWebsite\butrans\translog.bak'
> WITH NORECOVERY, No_truncate
> Go
> RESTORE DATABASE plebesummercom
> FROM disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\
EDTWebsite\dbbu080106\8_1_2006_5_3
0_42_PM.bak'
> WITH NORECOVERY;
> GO
> RESTORE LOG [plebesummercom]
> FROM DISK ='C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\
EDTWebsite\butrans\translog.bak'
> WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
> GO
>
> SGCSNA wrote:|||The full backup goes through Aug 2. I want to stop at July 16.
SGCSNA wrote:[vbcol=seagreen]
> Are you restoring from a full backup that falls *before* your STOPAT
> date and all of the subsequent logs, up to the one that contains the
> STOPAT date?
> medusa wrote:|||STOPAT only works with Transaction Log Backups, so you would need a
full backup from sometime before the time you want on July 16th and the
subsequent log backups through the time you want on 7/16.
medusa wrote:[vbcol=seagreen]
> The full backup goes through Aug 2. I want to stop at July 16.
> SGCSNA wrote:|||Thanks a lot for you help.
SGCSNA wrote:[vbcol=seagreen]
> STOPAT only works with Transaction Log Backups, so you would need a
> full backup from sometime before the time you want on July 16th and the
> subsequent log backups through the time you want on 7/16.
> medusa wrote:
newbie help--restore to point in time
I have a customer who's cart has been deleting orders incorrectly. I
backed up and restored the database from the remote server to my local.
Would like to restore to point in time before the delete operation.
>From various sources this is the script I'm using:
BACKUP LOG plebesummercom
TO disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
WITH NORECOVERY, No_truncate
Go
RESTORE DATABASE plebesummercom
FROM disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
WITH NORECOVERY;
GO
RESTORE LOG [plebesummercom]
FROM DISK = N'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
11:13:04'
GO
Ok, no errrors are thrown, but when I select from a table, I'm still
finding orders with dates past July 16. Am I trying to do something not
allowed? Both the database bu and the log bu contain transactions and
data through August 2nd. Can someone point me in the right direction?Why are you using the NOUNLOAD option when recovering from a file?
Also,
What is the "N" right before the date/time in your STOPAT ?
Your first statement here says to backup log? I think you would want:
RESTORE DATABASE
(FROM ...)
WITH NORECOVERY
--this one for every full log you want to roll in:
RESTORE LOG
(FROM ...)
WITH NORECOVERY
RESTORE LOG
(FROM ...)
WITH RECOVERY, STOPAT = 'Date/time'
Also,
check out the RESTORE topic in Books Online.
I hope this is helpful.
medusa wrote:
> I have a customer who's cart has been deleting orders incorrectly. I
> backed up and restored the database from the remote server to my local.
> Would like to restore to point in time before the delete operation.
> >From various sources this is the script I'm using:
> BACKUP LOG plebesummercom
> TO disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> WITH NORECOVERY, No_truncate
> Go
> RESTORE DATABASE plebesummercom
> FROM disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> WITH NORECOVERY;
> GO
> RESTORE LOG [plebesummercom]
> FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> 11:13:04'
> GO
> Ok, no errrors are thrown, but when I select from a table, I'm still
> finding orders with dates past July 16. Am I trying to do something not
> allowed? Both the database bu and the log bu contain transactions and
> data through August 2nd. Can someone point me in the right direction?|||Well, that was the latest round of code. The n is put in there if you
tell management studio to script the action. The backup log is in there
because if it's not an error is thrown about not backing up the log
tail. Here, this doesn't work either--contains transactions through Aug
2:
BACKUP LOG plebesummercom
TO disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
WITH NORECOVERY, No_truncate
Go
RESTORE DATABASE plebesummercom
FROM disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
WITH NORECOVERY;
GO
RESTORE LOG [plebesummercom]
FROM DISK ='C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
GO
SGCSNA wrote:
> Why are you using the NOUNLOAD option when recovering from a file?
> Also,
> What is the "N" right before the date/time in your STOPAT ?
> Your first statement here says to backup log? I think you would want:
> RESTORE DATABASE
> (FROM ...)
> WITH NORECOVERY
> --this one for every full log you want to roll in:
> RESTORE LOG
> (FROM ...)
> WITH NORECOVERY
> RESTORE LOG
> (FROM ...)
> WITH RECOVERY, STOPAT = 'Date/time'
> Also,
> check out the RESTORE topic in Books Online.
> I hope this is helpful.
>
> medusa wrote:
> > I have a customer who's cart has been deleting orders incorrectly. I
> > backed up and restored the database from the remote server to my local.
> > Would like to restore to point in time before the delete operation.
> > >From various sources this is the script I'm using:
> >
> > BACKUP LOG plebesummercom
> > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > WITH NORECOVERY, No_truncate
> > Go
> > RESTORE DATABASE plebesummercom
> > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > WITH NORECOVERY;
> > GO
> >
> > RESTORE LOG [plebesummercom]
> > FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> >
> > WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> > 11:13:04'
> > GO
> >
> > Ok, no errrors are thrown, but when I select from a table, I'm still
> > finding orders with dates past July 16. Am I trying to do something not
> > allowed? Both the database bu and the log bu contain transactions and
> > data through August 2nd. Can someone point me in the right direction?|||Are you restoring from a full backup that falls *before* your STOPAT
date and all of the subsequent logs, up to the one that contains the
STOPAT date?
medusa wrote:
> Well, that was the latest round of code. The n is put in there if you
> tell management studio to script the action. The backup log is in there
> because if it's not an error is thrown about not backing up the log
> tail. Here, this doesn't work either--contains transactions through Aug
> 2:
> BACKUP LOG plebesummercom
> TO disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> WITH NORECOVERY, No_truncate
> Go
> RESTORE DATABASE plebesummercom
> FROM disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> WITH NORECOVERY;
> GO
> RESTORE LOG [plebesummercom]
> FROM DISK ='C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
> GO
>
> SGCSNA wrote:
> > Why are you using the NOUNLOAD option when recovering from a file?
> >
> > Also,
> > What is the "N" right before the date/time in your STOPAT ?
> >
> > Your first statement here says to backup log? I think you would want:
> >
> > RESTORE DATABASE
> > (FROM ...)
> > WITH NORECOVERY
> >
> > --this one for every full log you want to roll in:
> > RESTORE LOG
> > (FROM ...)
> > WITH NORECOVERY
> >
> > RESTORE LOG
> > (FROM ...)
> > WITH RECOVERY, STOPAT = 'Date/time'
> >
> > Also,
> >
> > check out the RESTORE topic in Books Online.
> >
> > I hope this is helpful.
> >
> >
> > medusa wrote:
> > > I have a customer who's cart has been deleting orders incorrectly. I
> > > backed up and restored the database from the remote server to my local.
> > > Would like to restore to point in time before the delete operation.
> > > >From various sources this is the script I'm using:
> > >
> > > BACKUP LOG plebesummercom
> > > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > WITH NORECOVERY, No_truncate
> > > Go
> > > RESTORE DATABASE plebesummercom
> > > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > > WITH NORECOVERY;
> > > GO
> > >
> > > RESTORE LOG [plebesummercom]
> > > FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > >
> > > WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> > > 11:13:04'
> > > GO
> > >
> > > Ok, no errrors are thrown, but when I select from a table, I'm still
> > > finding orders with dates past July 16. Am I trying to do something not
> > > allowed? Both the database bu and the log bu contain transactions and
> > > data through August 2nd. Can someone point me in the right direction?|||The full backup goes through Aug 2. I want to stop at July 16.
SGCSNA wrote:
> Are you restoring from a full backup that falls *before* your STOPAT
> date and all of the subsequent logs, up to the one that contains the
> STOPAT date?
> medusa wrote:
> > Well, that was the latest round of code. The n is put in there if you
> > tell management studio to script the action. The backup log is in there
> > because if it's not an error is thrown about not backing up the log
> > tail. Here, this doesn't work either--contains transactions through Aug
> > 2:
> >
> > BACKUP LOG plebesummercom
> > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > WITH NORECOVERY, No_truncate
> > Go
> > RESTORE DATABASE plebesummercom
> > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > WITH NORECOVERY;
> > GO
> >
> > RESTORE LOG [plebesummercom]
> > FROM DISK ='C:\Documents and Settings\HP_Owner\My
> > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> >
> > WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
> > GO
> >
> >
> > SGCSNA wrote:
> > > Why are you using the NOUNLOAD option when recovering from a file?
> > >
> > > Also,
> > > What is the "N" right before the date/time in your STOPAT ?
> > >
> > > Your first statement here says to backup log? I think you would want:
> > >
> > > RESTORE DATABASE
> > > (FROM ...)
> > > WITH NORECOVERY
> > >
> > > --this one for every full log you want to roll in:
> > > RESTORE LOG
> > > (FROM ...)
> > > WITH NORECOVERY
> > >
> > > RESTORE LOG
> > > (FROM ...)
> > > WITH RECOVERY, STOPAT = 'Date/time'
> > >
> > > Also,
> > >
> > > check out the RESTORE topic in Books Online.
> > >
> > > I hope this is helpful.
> > >
> > >
> > > medusa wrote:
> > > > I have a customer who's cart has been deleting orders incorrectly. I
> > > > backed up and restored the database from the remote server to my local.
> > > > Would like to restore to point in time before the delete operation.
> > > > >From various sources this is the script I'm using:
> > > >
> > > > BACKUP LOG plebesummercom
> > > > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > > WITH NORECOVERY, No_truncate
> > > > Go
> > > > RESTORE DATABASE plebesummercom
> > > > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > > > WITH NORECOVERY;
> > > > GO
> > > >
> > > > RESTORE LOG [plebesummercom]
> > > > FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > >
> > > > WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> > > > 11:13:04'
> > > > GO
> > > >
> > > > Ok, no errrors are thrown, but when I select from a table, I'm still
> > > > finding orders with dates past July 16. Am I trying to do something not
> > > > allowed? Both the database bu and the log bu contain transactions and
> > > > data through August 2nd. Can someone point me in the right direction?|||STOPAT only works with Transaction Log Backups, so you would need a
full backup from sometime before the time you want on July 16th and the
subsequent log backups through the time you want on 7/16.
medusa wrote:
> The full backup goes through Aug 2. I want to stop at July 16.
> SGCSNA wrote:
> > Are you restoring from a full backup that falls *before* your STOPAT
> > date and all of the subsequent logs, up to the one that contains the
> > STOPAT date?
> > medusa wrote:
> > > Well, that was the latest round of code. The n is put in there if you
> > > tell management studio to script the action. The backup log is in there
> > > because if it's not an error is thrown about not backing up the log
> > > tail. Here, this doesn't work either--contains transactions through Aug
> > > 2:
> > >
> > > BACKUP LOG plebesummercom
> > > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > WITH NORECOVERY, No_truncate
> > > Go
> > > RESTORE DATABASE plebesummercom
> > > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > > WITH NORECOVERY;
> > > GO
> > >
> > > RESTORE LOG [plebesummercom]
> > > FROM DISK ='C:\Documents and Settings\HP_Owner\My
> > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > >
> > > WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
> > > GO
> > >
> > >
> > > SGCSNA wrote:
> > > > Why are you using the NOUNLOAD option when recovering from a file?
> > > >
> > > > Also,
> > > > What is the "N" right before the date/time in your STOPAT ?
> > > >
> > > > Your first statement here says to backup log? I think you would want:
> > > >
> > > > RESTORE DATABASE
> > > > (FROM ...)
> > > > WITH NORECOVERY
> > > >
> > > > --this one for every full log you want to roll in:
> > > > RESTORE LOG
> > > > (FROM ...)
> > > > WITH NORECOVERY
> > > >
> > > > RESTORE LOG
> > > > (FROM ...)
> > > > WITH RECOVERY, STOPAT = 'Date/time'
> > > >
> > > > Also,
> > > >
> > > > check out the RESTORE topic in Books Online.
> > > >
> > > > I hope this is helpful.
> > > >
> > > >
> > > > medusa wrote:
> > > > > I have a customer who's cart has been deleting orders incorrectly. I
> > > > > backed up and restored the database from the remote server to my local.
> > > > > Would like to restore to point in time before the delete operation.
> > > > > >From various sources this is the script I'm using:
> > > > >
> > > > > BACKUP LOG plebesummercom
> > > > > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > > > WITH NORECOVERY, No_truncate
> > > > > Go
> > > > > RESTORE DATABASE plebesummercom
> > > > > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > > > > WITH NORECOVERY;
> > > > > GO
> > > > >
> > > > > RESTORE LOG [plebesummercom]
> > > > > FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> > > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > > >
> > > > > WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> > > > > 11:13:04'
> > > > > GO
> > > > >
> > > > > Ok, no errrors are thrown, but when I select from a table, I'm still
> > > > > finding orders with dates past July 16. Am I trying to do something not
> > > > > allowed? Both the database bu and the log bu contain transactions and
> > > > > data through August 2nd. Can someone point me in the right direction?|||Thanks a lot for you help.
SGCSNA wrote:
> STOPAT only works with Transaction Log Backups, so you would need a
> full backup from sometime before the time you want on July 16th and the
> subsequent log backups through the time you want on 7/16.
> medusa wrote:
> > The full backup goes through Aug 2. I want to stop at July 16.
> >
> > SGCSNA wrote:
> > > Are you restoring from a full backup that falls *before* your STOPAT
> > > date and all of the subsequent logs, up to the one that contains the
> > > STOPAT date?
> > > medusa wrote:
> > > > Well, that was the latest round of code. The n is put in there if you
> > > > tell management studio to script the action. The backup log is in there
> > > > because if it's not an error is thrown about not backing up the log
> > > > tail. Here, this doesn't work either--contains transactions through Aug
> > > > 2:
> > > >
> > > > BACKUP LOG plebesummercom
> > > > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > > WITH NORECOVERY, No_truncate
> > > > Go
> > > > RESTORE DATABASE plebesummercom
> > > > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > > > WITH NORECOVERY;
> > > > GO
> > > >
> > > > RESTORE LOG [plebesummercom]
> > > > FROM DISK ='C:\Documents and Settings\HP_Owner\My
> > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > >
> > > > WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
> > > > GO
> > > >
> > > >
> > > > SGCSNA wrote:
> > > > > Why are you using the NOUNLOAD option when recovering from a file?
> > > > >
> > > > > Also,
> > > > > What is the "N" right before the date/time in your STOPAT ?
> > > > >
> > > > > Your first statement here says to backup log? I think you would want:
> > > > >
> > > > > RESTORE DATABASE
> > > > > (FROM ...)
> > > > > WITH NORECOVERY
> > > > >
> > > > > --this one for every full log you want to roll in:
> > > > > RESTORE LOG
> > > > > (FROM ...)
> > > > > WITH NORECOVERY
> > > > >
> > > > > RESTORE LOG
> > > > > (FROM ...)
> > > > > WITH RECOVERY, STOPAT = 'Date/time'
> > > > >
> > > > > Also,
> > > > >
> > > > > check out the RESTORE topic in Books Online.
> > > > >
> > > > > I hope this is helpful.
> > > > >
> > > > >
> > > > > medusa wrote:
> > > > > > I have a customer who's cart has been deleting orders incorrectly. I
> > > > > > backed up and restored the database from the remote server to my local.
> > > > > > Would like to restore to point in time before the delete operation.
> > > > > > >From various sources this is the script I'm using:
> > > > > >
> > > > > > BACKUP LOG plebesummercom
> > > > > > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > > > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > > > > WITH NORECOVERY, No_truncate
> > > > > > Go
> > > > > > RESTORE DATABASE plebesummercom
> > > > > > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > > > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > > > > > WITH NORECOVERY;
> > > > > > GO
> > > > > >
> > > > > > RESTORE LOG [plebesummercom]
> > > > > > FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> > > > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > > > >
> > > > > > WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> > > > > > 11:13:04'
> > > > > > GO
> > > > > >
> > > > > > Ok, no errrors are thrown, but when I select from a table, I'm still
> > > > > > finding orders with dates past July 16. Am I trying to do something not
> > > > > > allowed? Both the database bu and the log bu contain transactions and
> > > > > > data through August 2nd. Can someone point me in the right direction?
backed up and restored the database from the remote server to my local.
Would like to restore to point in time before the delete operation.
>From various sources this is the script I'm using:
BACKUP LOG plebesummercom
TO disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
WITH NORECOVERY, No_truncate
Go
RESTORE DATABASE plebesummercom
FROM disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
WITH NORECOVERY;
GO
RESTORE LOG [plebesummercom]
FROM DISK = N'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
11:13:04'
GO
Ok, no errrors are thrown, but when I select from a table, I'm still
finding orders with dates past July 16. Am I trying to do something not
allowed? Both the database bu and the log bu contain transactions and
data through August 2nd. Can someone point me in the right direction?Why are you using the NOUNLOAD option when recovering from a file?
Also,
What is the "N" right before the date/time in your STOPAT ?
Your first statement here says to backup log? I think you would want:
RESTORE DATABASE
(FROM ...)
WITH NORECOVERY
--this one for every full log you want to roll in:
RESTORE LOG
(FROM ...)
WITH NORECOVERY
RESTORE LOG
(FROM ...)
WITH RECOVERY, STOPAT = 'Date/time'
Also,
check out the RESTORE topic in Books Online.
I hope this is helpful.
medusa wrote:
> I have a customer who's cart has been deleting orders incorrectly. I
> backed up and restored the database from the remote server to my local.
> Would like to restore to point in time before the delete operation.
> >From various sources this is the script I'm using:
> BACKUP LOG plebesummercom
> TO disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> WITH NORECOVERY, No_truncate
> Go
> RESTORE DATABASE plebesummercom
> FROM disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> WITH NORECOVERY;
> GO
> RESTORE LOG [plebesummercom]
> FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> 11:13:04'
> GO
> Ok, no errrors are thrown, but when I select from a table, I'm still
> finding orders with dates past July 16. Am I trying to do something not
> allowed? Both the database bu and the log bu contain transactions and
> data through August 2nd. Can someone point me in the right direction?|||Well, that was the latest round of code. The n is put in there if you
tell management studio to script the action. The backup log is in there
because if it's not an error is thrown about not backing up the log
tail. Here, this doesn't work either--contains transactions through Aug
2:
BACKUP LOG plebesummercom
TO disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
WITH NORECOVERY, No_truncate
Go
RESTORE DATABASE plebesummercom
FROM disk= 'C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
WITH NORECOVERY;
GO
RESTORE LOG [plebesummercom]
FROM DISK ='C:\Documents and Settings\HP_Owner\My
Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
GO
SGCSNA wrote:
> Why are you using the NOUNLOAD option when recovering from a file?
> Also,
> What is the "N" right before the date/time in your STOPAT ?
> Your first statement here says to backup log? I think you would want:
> RESTORE DATABASE
> (FROM ...)
> WITH NORECOVERY
> --this one for every full log you want to roll in:
> RESTORE LOG
> (FROM ...)
> WITH NORECOVERY
> RESTORE LOG
> (FROM ...)
> WITH RECOVERY, STOPAT = 'Date/time'
> Also,
> check out the RESTORE topic in Books Online.
> I hope this is helpful.
>
> medusa wrote:
> > I have a customer who's cart has been deleting orders incorrectly. I
> > backed up and restored the database from the remote server to my local.
> > Would like to restore to point in time before the delete operation.
> > >From various sources this is the script I'm using:
> >
> > BACKUP LOG plebesummercom
> > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > WITH NORECOVERY, No_truncate
> > Go
> > RESTORE DATABASE plebesummercom
> > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > WITH NORECOVERY;
> > GO
> >
> > RESTORE LOG [plebesummercom]
> > FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> >
> > WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> > 11:13:04'
> > GO
> >
> > Ok, no errrors are thrown, but when I select from a table, I'm still
> > finding orders with dates past July 16. Am I trying to do something not
> > allowed? Both the database bu and the log bu contain transactions and
> > data through August 2nd. Can someone point me in the right direction?|||Are you restoring from a full backup that falls *before* your STOPAT
date and all of the subsequent logs, up to the one that contains the
STOPAT date?
medusa wrote:
> Well, that was the latest round of code. The n is put in there if you
> tell management studio to script the action. The backup log is in there
> because if it's not an error is thrown about not backing up the log
> tail. Here, this doesn't work either--contains transactions through Aug
> 2:
> BACKUP LOG plebesummercom
> TO disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> WITH NORECOVERY, No_truncate
> Go
> RESTORE DATABASE plebesummercom
> FROM disk= 'C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> WITH NORECOVERY;
> GO
> RESTORE LOG [plebesummercom]
> FROM DISK ='C:\Documents and Settings\HP_Owner\My
> Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
> GO
>
> SGCSNA wrote:
> > Why are you using the NOUNLOAD option when recovering from a file?
> >
> > Also,
> > What is the "N" right before the date/time in your STOPAT ?
> >
> > Your first statement here says to backup log? I think you would want:
> >
> > RESTORE DATABASE
> > (FROM ...)
> > WITH NORECOVERY
> >
> > --this one for every full log you want to roll in:
> > RESTORE LOG
> > (FROM ...)
> > WITH NORECOVERY
> >
> > RESTORE LOG
> > (FROM ...)
> > WITH RECOVERY, STOPAT = 'Date/time'
> >
> > Also,
> >
> > check out the RESTORE topic in Books Online.
> >
> > I hope this is helpful.
> >
> >
> > medusa wrote:
> > > I have a customer who's cart has been deleting orders incorrectly. I
> > > backed up and restored the database from the remote server to my local.
> > > Would like to restore to point in time before the delete operation.
> > > >From various sources this is the script I'm using:
> > >
> > > BACKUP LOG plebesummercom
> > > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > WITH NORECOVERY, No_truncate
> > > Go
> > > RESTORE DATABASE plebesummercom
> > > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > > WITH NORECOVERY;
> > > GO
> > >
> > > RESTORE LOG [plebesummercom]
> > > FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > >
> > > WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> > > 11:13:04'
> > > GO
> > >
> > > Ok, no errrors are thrown, but when I select from a table, I'm still
> > > finding orders with dates past July 16. Am I trying to do something not
> > > allowed? Both the database bu and the log bu contain transactions and
> > > data through August 2nd. Can someone point me in the right direction?|||The full backup goes through Aug 2. I want to stop at July 16.
SGCSNA wrote:
> Are you restoring from a full backup that falls *before* your STOPAT
> date and all of the subsequent logs, up to the one that contains the
> STOPAT date?
> medusa wrote:
> > Well, that was the latest round of code. The n is put in there if you
> > tell management studio to script the action. The backup log is in there
> > because if it's not an error is thrown about not backing up the log
> > tail. Here, this doesn't work either--contains transactions through Aug
> > 2:
> >
> > BACKUP LOG plebesummercom
> > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > WITH NORECOVERY, No_truncate
> > Go
> > RESTORE DATABASE plebesummercom
> > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > WITH NORECOVERY;
> > GO
> >
> > RESTORE LOG [plebesummercom]
> > FROM DISK ='C:\Documents and Settings\HP_Owner\My
> > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> >
> > WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
> > GO
> >
> >
> > SGCSNA wrote:
> > > Why are you using the NOUNLOAD option when recovering from a file?
> > >
> > > Also,
> > > What is the "N" right before the date/time in your STOPAT ?
> > >
> > > Your first statement here says to backup log? I think you would want:
> > >
> > > RESTORE DATABASE
> > > (FROM ...)
> > > WITH NORECOVERY
> > >
> > > --this one for every full log you want to roll in:
> > > RESTORE LOG
> > > (FROM ...)
> > > WITH NORECOVERY
> > >
> > > RESTORE LOG
> > > (FROM ...)
> > > WITH RECOVERY, STOPAT = 'Date/time'
> > >
> > > Also,
> > >
> > > check out the RESTORE topic in Books Online.
> > >
> > > I hope this is helpful.
> > >
> > >
> > > medusa wrote:
> > > > I have a customer who's cart has been deleting orders incorrectly. I
> > > > backed up and restored the database from the remote server to my local.
> > > > Would like to restore to point in time before the delete operation.
> > > > >From various sources this is the script I'm using:
> > > >
> > > > BACKUP LOG plebesummercom
> > > > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > > WITH NORECOVERY, No_truncate
> > > > Go
> > > > RESTORE DATABASE plebesummercom
> > > > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > > > WITH NORECOVERY;
> > > > GO
> > > >
> > > > RESTORE LOG [plebesummercom]
> > > > FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > >
> > > > WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> > > > 11:13:04'
> > > > GO
> > > >
> > > > Ok, no errrors are thrown, but when I select from a table, I'm still
> > > > finding orders with dates past July 16. Am I trying to do something not
> > > > allowed? Both the database bu and the log bu contain transactions and
> > > > data through August 2nd. Can someone point me in the right direction?|||STOPAT only works with Transaction Log Backups, so you would need a
full backup from sometime before the time you want on July 16th and the
subsequent log backups through the time you want on 7/16.
medusa wrote:
> The full backup goes through Aug 2. I want to stop at July 16.
> SGCSNA wrote:
> > Are you restoring from a full backup that falls *before* your STOPAT
> > date and all of the subsequent logs, up to the one that contains the
> > STOPAT date?
> > medusa wrote:
> > > Well, that was the latest round of code. The n is put in there if you
> > > tell management studio to script the action. The backup log is in there
> > > because if it's not an error is thrown about not backing up the log
> > > tail. Here, this doesn't work either--contains transactions through Aug
> > > 2:
> > >
> > > BACKUP LOG plebesummercom
> > > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > WITH NORECOVERY, No_truncate
> > > Go
> > > RESTORE DATABASE plebesummercom
> > > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > > WITH NORECOVERY;
> > > GO
> > >
> > > RESTORE LOG [plebesummercom]
> > > FROM DISK ='C:\Documents and Settings\HP_Owner\My
> > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > >
> > > WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
> > > GO
> > >
> > >
> > > SGCSNA wrote:
> > > > Why are you using the NOUNLOAD option when recovering from a file?
> > > >
> > > > Also,
> > > > What is the "N" right before the date/time in your STOPAT ?
> > > >
> > > > Your first statement here says to backup log? I think you would want:
> > > >
> > > > RESTORE DATABASE
> > > > (FROM ...)
> > > > WITH NORECOVERY
> > > >
> > > > --this one for every full log you want to roll in:
> > > > RESTORE LOG
> > > > (FROM ...)
> > > > WITH NORECOVERY
> > > >
> > > > RESTORE LOG
> > > > (FROM ...)
> > > > WITH RECOVERY, STOPAT = 'Date/time'
> > > >
> > > > Also,
> > > >
> > > > check out the RESTORE topic in Books Online.
> > > >
> > > > I hope this is helpful.
> > > >
> > > >
> > > > medusa wrote:
> > > > > I have a customer who's cart has been deleting orders incorrectly. I
> > > > > backed up and restored the database from the remote server to my local.
> > > > > Would like to restore to point in time before the delete operation.
> > > > > >From various sources this is the script I'm using:
> > > > >
> > > > > BACKUP LOG plebesummercom
> > > > > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > > > WITH NORECOVERY, No_truncate
> > > > > Go
> > > > > RESTORE DATABASE plebesummercom
> > > > > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > > > > WITH NORECOVERY;
> > > > > GO
> > > > >
> > > > > RESTORE LOG [plebesummercom]
> > > > > FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> > > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > > >
> > > > > WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> > > > > 11:13:04'
> > > > > GO
> > > > >
> > > > > Ok, no errrors are thrown, but when I select from a table, I'm still
> > > > > finding orders with dates past July 16. Am I trying to do something not
> > > > > allowed? Both the database bu and the log bu contain transactions and
> > > > > data through August 2nd. Can someone point me in the right direction?|||Thanks a lot for you help.
SGCSNA wrote:
> STOPAT only works with Transaction Log Backups, so you would need a
> full backup from sometime before the time you want on July 16th and the
> subsequent log backups through the time you want on 7/16.
> medusa wrote:
> > The full backup goes through Aug 2. I want to stop at July 16.
> >
> > SGCSNA wrote:
> > > Are you restoring from a full backup that falls *before* your STOPAT
> > > date and all of the subsequent logs, up to the one that contains the
> > > STOPAT date?
> > > medusa wrote:
> > > > Well, that was the latest round of code. The n is put in there if you
> > > > tell management studio to script the action. The backup log is in there
> > > > because if it's not an error is thrown about not backing up the log
> > > > tail. Here, this doesn't work either--contains transactions through Aug
> > > > 2:
> > > >
> > > > BACKUP LOG plebesummercom
> > > > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > > WITH NORECOVERY, No_truncate
> > > > Go
> > > > RESTORE DATABASE plebesummercom
> > > > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > > > WITH NORECOVERY;
> > > > GO
> > > >
> > > > RESTORE LOG [plebesummercom]
> > > > FROM DISK ='C:\Documents and Settings\HP_Owner\My
> > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > >
> > > > WITH FILE = 1, STOPAT = '07/16/2006 11:13:04'
> > > > GO
> > > >
> > > >
> > > > SGCSNA wrote:
> > > > > Why are you using the NOUNLOAD option when recovering from a file?
> > > > >
> > > > > Also,
> > > > > What is the "N" right before the date/time in your STOPAT ?
> > > > >
> > > > > Your first statement here says to backup log? I think you would want:
> > > > >
> > > > > RESTORE DATABASE
> > > > > (FROM ...)
> > > > > WITH NORECOVERY
> > > > >
> > > > > --this one for every full log you want to roll in:
> > > > > RESTORE LOG
> > > > > (FROM ...)
> > > > > WITH NORECOVERY
> > > > >
> > > > > RESTORE LOG
> > > > > (FROM ...)
> > > > > WITH RECOVERY, STOPAT = 'Date/time'
> > > > >
> > > > > Also,
> > > > >
> > > > > check out the RESTORE topic in Books Online.
> > > > >
> > > > > I hope this is helpful.
> > > > >
> > > > >
> > > > > medusa wrote:
> > > > > > I have a customer who's cart has been deleting orders incorrectly. I
> > > > > > backed up and restored the database from the remote server to my local.
> > > > > > Would like to restore to point in time before the delete operation.
> > > > > > >From various sources this is the script I'm using:
> > > > > >
> > > > > > BACKUP LOG plebesummercom
> > > > > > TO disk= 'C:\Documents and Settings\HP_Owner\My
> > > > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > > > > WITH NORECOVERY, No_truncate
> > > > > > Go
> > > > > > RESTORE DATABASE plebesummercom
> > > > > > FROM disk= 'C:\Documents and Settings\HP_Owner\My
> > > > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\dbbu080106\8_1_2006_5_30_42_PM.bak'
> > > > > > WITH NORECOVERY;
> > > > > > GO
> > > > > >
> > > > > > RESTORE LOG [plebesummercom]
> > > > > > FROM DISK = N'C:\Documents and Settings\HP_Owner\My
> > > > > > Documents\StephsProgramming\Plebesummer\EDTWebsite\butrans\translog.bak'
> > > > > >
> > > > > > WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'07/16/2006
> > > > > > 11:13:04'
> > > > > > GO
> > > > > >
> > > > > > Ok, no errrors are thrown, but when I select from a table, I'm still
> > > > > > finding orders with dates past July 16. Am I trying to do something not
> > > > > > allowed? Both the database bu and the log bu contain transactions and
> > > > > > data through August 2nd. Can someone point me in the right direction?
Friday, March 9, 2012
Newbie Design Question
I am a newbie in BI and have the following design question:
I have a scenario where I have a Sales fact table linked to a customer dimension. The customers some customers attributes might change and I would like to keep history of those attributes, such as Marital Status.
One way is to store the Marital Status as attribute in the customer dimension and enter multiple records for each customer whenever the marital status changes, but I should then add date and status fields to the customer dimension.
Another way is to store the Marital Status as a separate dimension and link it directly to the fact table. While maintaining only one record for each unique customer in the customer dimension, and in this case no Date and Status fields are needed on customer.
What is the best way of implementing this scenario? Are there any pros cons for each one, Or Maybe there is a better way of solving it?
And suppose I use the first method where multiple customer records are kept, while processing to get the sales done for people that are married, some processing should be done to only consider each customer once (Only Status is Active). This is through MDX I suppose.
I am sure this issue is typical and basic but just wanted to get some guidelines.
Appreciate your help,
Grace
Hi Grace,
These design issues are often discussed as part of the "Slowly Changing Dimension" techniques in dimensional modelling, for example in the Kimball article below. Typically these technique ensure that only the appropriate row corresponding to a dimension member is considered (one way is by using surrogate keys):
http://www.intelligententerprise.com/db_area/archives/1999/990308/warehouse.jhtml
>>
When A Slowly Changing Dimension Speeds Up
Ralph Kimball
...
>>
|||Thanks Deepak for the guidance.
Subscribe to:
Posts (Atom)