Point In Time Query &>:[
Jul 9, 2007
Hey guys and gals,
I'm having a real problem with this query at the moment...
Basically I have to produce a query which will tell me the total number of people employed by the company at any given date and the total salary for all these people.
We have a people table and a career table.
People(unique_identifier, known_as_and_surname, start_date, termination_date ...)
Career(unique_identifier, parent_identifier, career_date, basic_pay ...)
Relationship people.unique_identifier = career.parent_identifier
Employees can be identified like so
SELECT *
FROM people
WHERE start_date <= DateSelected
AND (termination_date > DateSelected
OR termination_date IS NULL)
Passing the selected date to the query is no trouble at all I am just having problems with the point in time side of this.
All and any help is greatly appreciated :)
~George
P.S. SQL Server 2000 ;)
View 14 Replies
ADVERTISEMENT
Mar 26, 2008
Hello all,
First off, I appreciate the time that those of you reading and responding to this request are offering. My quesiton is a theoretical and hopefully simple one, and yet I have been unable to find an answer to it on other searches or sources.
Here's the situation. I am working with SQL Server 2005 on a Windows Server 2003 machine. I have a series of databases, all of which are in Full recovery mode, using a backup device for the full database backups and a separate device for the log backups. The full backups are run every four days during non-business hours. The log backups are run every half hour.
Last week, one of my coworkers found that some rarely-used data was unavailable, and wanted to restore a database to a point in time where the data was available. He told me that point in time was some time back in November.
To accomplish this, I restored the database (in a separate database, as to not overwrite my production database) using the Point in Time Recovery option. I selected November from the "To a point in time" window (I should note that this window is always grey, never white like most active windows, it seems), and the full database backup and the subsequent logs all became available in the "Select the backup sets to restore" window.
I then tried a bevy of different options from the "Options" screen. However, every restore succeeds (ie: it doesn't error out), but seems to be bringing the database back to a current point in time. It's never actually going back to the point in time I specify.
My questions are as follows:
a) Is it possible to do a point in time recovery to a point in time BEFORE the last full database backup?
b) If so, what options would you recommend I use? (ie: "Overwrite the existing database", restore with recovery, etc etc).
I again appreciate any and all advice I receive, and I look forward to hearing from anyone and everyone on this topic. Thank you.
Ryan
View 4 Replies
View Related
Sep 21, 2015
I need to do a time test for restoring an Azure SQL database from a point in time. Can I automate this through PowerShell.
View 3 Replies
View Related
Jun 25, 2015
I am reading about the RESTORE command to a point in time using logs, I would like to know the minimum point in time recovery for a backup image using T-SQL command before applying a log restore and what are the log ranges needed for the restore during restore.
My Version 2008 R2
View 7 Replies
View Related
Dec 8, 1999
I can't "point in time restore" a test DB if I had only a Full DB Backup (with overwrite option).
Example
12:00 Fullbackup new (overwrite)
12:01 update any rows
12:02 update any rows
12:03 delete any rows
12:05 Transaction Log backup (overwrite)
RESTORE: We can't set "point in Time" to 12:01 od 12:02 ???
why
View 2 Replies
View Related
Mar 13, 2000
Hi
1. could any one explain point in time recovery.
2. Problem we had was the database froze 'inload' while doing transaction restore. Is there any way to recover this
regards
rajeev
View 1 Replies
View Related
Aug 31, 2005
Hi, I am pretty new to Analysis Services and I have come across a small problem to do with point in time balances, I guess that this is similar shiver72's post titled 'Date Range Problem'. I am not really after a solution as such (although that would be great :-), more interested in a pointer to some resource(s) which I can read up on myself.
Here is the scenario:
A student enrols into a course that has a start and finish date. This enrolment has a certain value based upon its length, the course type etc which means that each
enrolment's 'value' can vary at any time between the start and finish dates.
For example a student may enrol in a full year basket weaving course with a value of 0.0500, they then enrol in a part year course titled 'Dimensional Modelling 101' with a value of 1.000
...and their enrolments would look like this...
01-JAN-2005 31-DEC-2005 0.0500
02-JAN-2005 15-JUN-2005 1.0000
The client wishes to know at any point in time the 'value' of their enrolments. These values can vary due to a student starting or finishing their enrolment, withdrawing, having their
enrolment suspended etc, etc
Now, in the Data Warehouse Toolkit it mentions point in time balances in the 'Financial Services' section but it uses SQL to prove the point which is no use to me because I am using MS Analysis Services to dynamically create the result.
As it says in the book its no use creating a row in the fact table to represent the value for each day that the enrolment is in effect because with 400,000 enrolments that works out to over 140 million rows. The alternative is to create one row for each occurence of the variation in the value of the enrolment and then the facts would be completely additive and useful.
I would be looking at using something similar to the following:
fact table
----------
fkdate
value
fksudent
fkcourse
<possibly some degenerate measure like the UID of the course enrolment held in the source system>
dimensions
----------
Student - and of course a whole heap of related dimensions like gender, age, ethnicity etc
Course
For example given the above information the fact table would look like this:
(Student UID is 2005123, course UIDs are 1000 and 1001)
20050101 0.0500 2005123 1000
20050102 1.0000 2005123 1001
20050615 -1.0000 2005123 1001
20051231 -0.0500 2005123 1000
That way I can run a query at, say 15-JUN-2005 and sum the values and it will come out with the correct balance - and yes I know that this idea is straight out of the book :-)
OK, if you've read to this point then I take it that you are an extremely patient person and therefore will forgive my next question.
My question is this, my clients use simple tools like Excel pivot tables to gouge the information they want out of the warehouse, sure, I can write an MDX query to get a point in time balance (just as soon as I learn a bit more about it) but is there _any_ way/means/algorithm/trick/way to hold your head to one side, that I can use so that they can still just 'drag and drop' using the pivot table service as opposed to having to me having to create an MDX-based report for them ?
cheers
View 2 Replies
View Related
Sep 2, 2004
We have been testing point of time recovery using EM and found that this does not work.
We enter date and time and do net get the logs restored. Even if we use the default date it does not work. In Query Analyser we have have managed to recover to a point in time. Anybody got any idea why EM does not work.
We are using 2000 sp3
View 2 Replies
View Related
Nov 26, 2004
My database is in full recovery mode. When I have created some full backups of the database, I would like try to point in time restore. Unfortunately, this option is greyed out on the restore screen. What have I done wrong?
View 1 Replies
View Related
Oct 13, 2006
Dear All,
How do we accomplish point in time recovery with SQL Server database.
For example:
My backup schedule is
Monday - Complete database
Tuesday - transaction log backup
Wednesday-transaction log backup
Thursday - transaction log backup
Friday - Complete database
Saturday - transaction log backup
Sunday - transaction log backup
For complete database backup, I use the below syntax:
BACKUP DATABASE myDB
TO DISK= @File1
WITH DESCRIPTION = @Desc
For transaction log backup, I use the below syntax:
BACKUP LOG myDB
TO DISK= @File1
WITH DESCRIPTION = @Desc
With this scenario, can I accomplish a point in time recovery? For example, if my database crashes on thursday night. How do i do complete recovery till that time?
Pls guide...
Regards,
qA
View 1 Replies
View Related
Jan 31, 2014
I know NOTHING about SQL. I have a SQL 2005 database. I'm trying to restore a point in time. I get the error:
RESTORE FAILED...AMT12-2-13.TRN... CANNOT FIND THE FILE SPECIFIED.
The file exists, and in the proper location. something is wrong with it. Is there any way to rebuild the entire TRN so that a point in time recovery will work?
View 2 Replies
View Related
Nov 7, 2007
I have a SQLServer 2005 database running in Windows 2003 Advanced Server environment. I want to restore from the backup to 2 days back point in time. I am using the Microsoft SQLServer Managment Studio. After I pick the file and specify the time I keep getting the error:
System.Data.SqlClient.SqlError: RESTORE cannot process database 'DBNAME' because it is in use by this session. It is recommended that the master database be used when performing this operation. (Microsoft.SqlServer.Smo)
I did a server reboot to clear off any hanging session.Stil I am getting the message. Please suggest.
Thanks
R
View 7 Replies
View Related
Jul 20, 2005
I have full backup of database at 13:00and another full backup at 17:00.I've made backup of transaction log at 17:05When I try to restore database to state at15:10 (point in time) , the dialogue in Enterprise Managersays that only time after 17:05 is valid.It seems to me that I've done something wrong at 17:05 while takingtrans. log backup.But, again, if I have full backups at 13:00 and 17:00 restoringdatabase to point in time at 15:10 should be possible ?!Any help is appreciated.Pagus
View 5 Replies
View Related
Nov 7, 2007
I have a SQLServer 2005 database. I want to restore from the backup to 2 days back point in time. I am using the Microsoft SQLServer Managment Studio. After I pick the file and specify the time I keep getting the error:
[System.Data.SqlClient.SqlError: RESTORE cannot process database 'DBNAME' because it is in use by this session. It is recommended that the master database be used when performing this operation. (Microsoft.SqlServer.Smo)
I did a server reboot to clear off any hanging session.Stil I am getting the message. Please suggest.
Thanks
View 4 Replies
View Related
Aug 10, 2000
Hi Everybody:
We plan to do point-in-time recovery for certain databases. We plan to do Complete Database Backup every night and transaction log backup every two hours from 8:00 AM to 5:00 PM. I have following questions regarding the log backup.
1. There are two type of backup 'Append to media' or 'Overwrite'. If I choose 'Append' for log backup, is that mean I only need to restore database against last log backup file because all previous log backups have been accumulated there?
2. Can I automatically truncate log after the backup is done? How I can do it?
Thank you very much.
Joan
View 1 Replies
View Related
Nov 11, 2001
Is is true that in SQL Server 7, a point in time recovery can not read the active logs? So that any point in time recovery has to be with in some time frame of the backups of the logs?
Example:
If you have a full backup at 5 am.
Log backups every 2 hours.
You need to recover at 3:55. You would have to go back to your full backup from 2 am and the transaction logs from 6, 8, 10, 12, and 2.
You would lose any work done from 2-3:55pm?
View 3 Replies
View Related
Jan 18, 2008
If I want to return to a point in time for all my databases in SQL 2000 can I just copy all the files in the data directory to a safe place. And then when I want to go back to that point in time, just copy them all back?
View 4 Replies
View Related
Mar 2, 2002
Hello,
I have lost a table's contents and need to restore them urgently. I backed up the database. I selected the backup set in order to restore it, but the "Point in time restore " was disabled and I couldn't select it to set the time to which to restore.Could anyone tell me the reason behing that?
Another interesting thing is that when I backup a database, I only have two options for backing it up,
1. database complete
2. database differential
Thanks a lot,
Joe
View 2 Replies
View Related
Jul 22, 2004
Help.........
I'm fairly new to sql server, and trying to restore to a point in time from my log file in sql server 2000 SP3.
I am getting the error "Invalid Value Specified for Stopat parameter".
I am applying this with Enterprise manager, not via T-SQL.
My login is set to english.
Can anyone help?
Many thanks
Neil
View 1 Replies
View Related
May 27, 2004
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.
View 3 Replies
View Related
Nov 28, 2007
I have full Recovery mode.
I just accidently deleted a handful of records out of a table about 10 minutes ago. My last backup was last night.
Can I go back 10 minutes ago for a specific table? If so how... DO I need to backup now, then use the trans-logs to recover up to a specific point?
Thanks!!
View 15 Replies
View Related
Mar 16, 2008
hi all!
can you help me, in which case will i need to Specify a Point in Time, when restoring a database?
for newbee like me, it looks like it's better to restore without Specifying a Point in Time, because i will restore my data, without headache.
can you give me some sort of example when and why to specifying a point in time?
thank you 1000x in advance!
View 1 Replies
View Related
Jul 20, 2005
In the hereunder written message I talk about point in time restore.It is now based upon the fact that there are no hardware problems or what soever.I just would like to roll back to a situation of some time (minutes, hoursor what ever) ago.Used to the ingres database a point in time restore can take place UP toany, any, any time since the last FULL backup. (any time up to now !!!)I can't understand why a point in time restore can only be done based upontransaction log backups. The current transaction log is also available in myopinion. (Turn off the power, turn on the power and you will notice that theautomatic recovery is based upon this transaction log file; so in that casethis file is used)That's what my question is about. Is it correct that a point in time restorein a SQL server environment can only be done up to the last transaction logbackup.ByeArno de Jong,The Netherlands.
View 1 Replies
View Related
Jul 20, 2005
I will make it simpler to look...I have DB1 - as backup for day 1LOg1 as backup of logsT1 T2 T3 T4 T5 ...some transaction on day 2Now i backup againDB2Log2I want to restore the database till the point of transaction T3 say. Iknow the time or i assume a certain time.Is this possible .....i tried several options but hand in between forsome reason or the other. How can i achieve my solution. Is there someextra parameter i will require or what....i am wondering now that it isnot at all possible. Please help.RVGIf possible guys can you please mail me the sloution onJoin Bytes!*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
Aug 11, 1999
How could i restore a filegroup to a certain point of time (not to apply all
transaction log up to the latest one) ??
View 2 Replies
View Related
Aug 9, 2005
I would appreciate a bit of advice here. There is a largish complaint here regarding a cutomer who has entered data in 1 of our online forms, but we suspect this was then overwritten by a cached form she also had open.
Anyway to cut a long story short i need to roll back the database to a point in time.
Not something i have ever had to do.
The row where the id field is equal to 3352, and this would have been written to the database at 14:58:36 on 08-aug-2005. This was over written by the data in row with id 3380 at about 11am this morning, now is this is a live database on a webserver so i cant compromise its uptime as it get written to about 3 times a minute, so how should i go about this?
Anyway, what do you think?
Cheers
View 2 Replies
View Related
Oct 27, 2005
hi. i am managing a sql server 2000 database. for some reason, a table got dropped from the database--why i don't know; i'll worry about that later. is there a way to "rollback" the database to a point in time...say like 5 minutes ago? I have taken the database offline for the moment so no more changes can take place. i have never done this before and i would really appreciate some advice/online tutorials/help on this matter. i do have a full backup of the database because of nightly backups. any help will be greatly appreciated. thanks!
View 1 Replies
View Related
Jul 23, 2005
I'm writing a classic ASP application that records all logging of userlogins on our support site. The logging is a rolling window of how manypeople have logged in for a given month, i.e. tracked by a 'lastlogin'field so the tracking is done in a date range. So, for the month ofJanuary I would record lastlogin dates between January 1 and January31. My question is this...on the last day of the month (example beingJanauary)at 23:59:59PM I want to write the total number of users thathad a lastlogin date within the month of January and write that totalnumber to another table so I have a point-in-time figure for Januarylogins and how many users logged in in the month of January. How do Ido that? How can I have the sql server dynamically check the last dayof each month at 23:59:59PM and then run that query to obtain the totalnumbers users that logged in and then record that value to anothertable?? Do I use a SQL Job to do that?Any help would be greatly appreciated! Thanks!
View 1 Replies
View Related
Jul 23, 2005
Dear All,Do anyone know where I can find some useful documentation or anydocumentationabout restoring database to the point in time (using logs).SQL Server 2000 on Win 2000Thanks for all,M&M
View 1 Replies
View Related
Sep 21, 2006
Hello,I am using SQL Server 2000 with SP4. I have a database with two fullbackups at 4:00 PM and 5:00 PM and a transactional log backup at 5:30PM. Is there a possible way to do a point in time restore to 4:30 PM,that is between two full backups?When I try to use the transactional log backup that is taken at 5:30, Ican never specify a time before 5:00 PM. Is the transaction logtruncated at each full backup? If so, even if you take transactionallog backup every ten minutes, and full backups every once in a while,there will be some point in time which cannot be recovered to, namelythe time between a transactional log backup and a full backup. Take alog backup at 4:50, and full backup at 5:00 and you can never recoverto 4:55, can you?Any insight on the topic will be appreciated,Regards,M. Baris Caglar
View 3 Replies
View Related
Jul 20, 2005
We're considering purchasing an application,which stores some data in the filesystem, andsome data (meta data and links to files) inMS-SQL.We need to be able to create a backup whereinthe 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 server2. set database to readonly (flush updatesfrom cache to disk)3. backup database to disk files4. back up application files and backup files.(We could use "snapshot" capabilities of our diskhardware, so that the backup could run against thesnapshot; that would minimize downtime.)4. set database to read_write; restart app serverRecovery scenario would be:1. Stop application server.2. restore application files and database backup files3. restore database from disk backup4. restart application serverIs there a simpler way to get a "point in time" backupof application files and the database?
View 1 Replies
View Related
Aug 27, 2007
hey guys
i need help urgently
i just ran an update statement without a where statement by mistake
and i need to rollback this changes
i;m runnning sql express sp2
and the database is set to recovery model simple
i hace the mdf and ldf file
the mdf is 1.5 gb ledf is 65 mb and the last changed date is the same time i ran the update statement
so i think the changes are there in the ldf file but i just need to roll back to 1 minute b4 i run the update
plz helpppppppppppp
thx in advance
View 5 Replies
View Related
Oct 27, 2007
Greetings, All -
Is it possible to restore to a point in time without a preexisting full backup? The situation is this:
I have a table in the DB from which an unknown number of records were accidentally deleted. The table in questio has about 2 million records; the user ran a query to delete all records from the table by accident, and cancelled the query after about 3 seconds.
The DB recovery mode is full, so I should be able to do a point in time restore to go back to just before the deletion, but unfortunately, the DB has never been backed up, so I have no backup to work from.
The DB has not been used since the incident, and is otherwise operational, but I need to recover these records if at all possible. All the instructions I've seen for this involve restoring from a full backup first, then restoring the log backup second. Is there any way for me to accomplish the same task?
Mark Faulcon
View 6 Replies
View Related
