QUERY TO YESTERDAY DATABASE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

High Availability (2008)

QUERY TO YESTERDAY DATABASE

Author

Topic

footmaniac84
Starting Member

12 Posts

Posted - 2013-02-25 : 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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-25 : 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 - 2013-02-25 : 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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-25 : 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 - 2013-02-25 : 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

52326 Posts

Posted - 2013-02-26 : 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 - 2013-02-26 : 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

52326 Posts

Posted - 2013-02-26 : 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 - 2013-02-27 : 06:41:47

OK, thank you very much. Your help has been very useful.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-27 : 23:35:11

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources