| Author |
 Topic  |
|
|
footmaniac84
Starting Member
12 Posts |
 Posted - 02/25/2013 : 11:24:38
|
Hello.
I have a problem. Each night I need to execute a query about actual data and about yesterday data.
I can do it manually restoring my backup and making a manual query but..
is possible to make a query using yesterday database and do it automatically?
Thank you. |
|
|
James K
Flowing Fount of Yak Knowledge
1730 Posts |
Posted - 02/25/2013 : 11:38:24
|
Assuming you have a datetime column in your table(s) that can be used to decided what rows are to be included for "yesterday's" calculation, you can add a where clause in your query like shown below.WHERE
YourDateTimeColumn >= select DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-1)
AND YourDateTimeColumn < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) |
 |
|
|
footmaniac84
Starting Member
12 Posts |
Posted - 02/25/2013 : 11:48:27
|
I think, i explained so bad. my english is not good.
I'd like to restore my backup and to make a query to this database but do it automatically. Could i restore a database automatically and after this make a query in a Job to this restored database? |
|
|
|
James K
Flowing Fount of Yak Knowledge
1730 Posts |
Posted - 02/25/2013 : 11:56:27
|
You can do all of that using a T-SQL script. Probably the easiest way to generate the script is as follows: Bring up the "Restore Database" window as you normally would if you were to do it manually, make all the changes you normally do, but instead of clicking OK to proceed, click the script button near the top left of the right panel. That will generate a script to do the restore.
Once you have that script, you can run that script each day, or you can schedule a SQL Server Agent job t run that script.
But, I did not follow the reason you need to restore the prior day's database to do this query if you still have the database on the server. The query I posted earlier was showing you how to get the data for a prior day from the current database without having to restore from yesterday's backup. |
|
|
|
footmaniac84
Starting Member
12 Posts |
Posted - 02/25/2013 : 17:48:31
|
That's exactly what i need, this script. Thank you!
I have to do it because data changes between yesterday and today and in today database i haven't yesterday data.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48041 Posts |
Posted - 02/26/2013 : 01:11:18
|
for that why you need to restore db? sounds like what you need is a audit table to track data changes that happened each day for use in the next day. your current data will reside in main table and previous data in your audit table.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
footmaniac84
Starting Member
12 Posts |
Posted - 02/26/2013 : 06:29:04
|
I have to access to a many tables using a stored procedure.
How could i access to audit tables? Could i access to old data in a specific moment? For example, yesterday 4:00 a.m.? I have to change all references to tables or could i only change the reference to an audit DATABASE in that specific moment?
Thanks
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48041 Posts |
Posted - 02/26/2013 : 23:29:27
|
quote:
Originally posted by footmaniac84
I have to access to a many tables using a stored procedure.
How could i access to audit tables? Could i access to old data in a specific moment? For example, yesterday 4:00 a.m.? I have to change all references to tables or could i only change the reference to an audit DATABASE in that specific moment?
Thanks
you dont need to change database reference
Assuming all your audit data for a table is captured to a corresponding table in audit db with date field indicating date
you can simply use query like
SELECT *
FROM AUDITDB.dbo.AuditTable
WHERE Datefield>=@Start
AND Datefield<= @End
here @Start and @End represent range for which you want to capture audit
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
footmaniac84
Starting Member
12 Posts |
Posted - 02/27/2013 : 06:41:47
|
| OK, thank you very much. Your help has been very useful. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48041 Posts |
Posted - 02/27/2013 : 23:35:11
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
QUERY TO YESTERDAY DATABASE
