SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 High Availability (2008)
 QUERY TO YESTERDAY DATABASE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

footmaniac84
Starting Member

12 Posts

Posted - 02/25/2013 :  11:24:38  Show Profile  Reply with Quote
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

3568 Posts

Posted - 02/25/2013 :  11:38:24  Show Profile  Reply with Quote
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)
Go to Top of Page

footmaniac84
Starting Member

12 Posts

Posted - 02/25/2013 :  11:48:27  Show Profile  Reply with Quote
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?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 02/25/2013 :  11:56:27  Show Profile  Reply with Quote
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.
Go to Top of Page

footmaniac84
Starting Member

12 Posts

Posted - 02/25/2013 :  17:48:31  Show Profile  Reply with Quote
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.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/26/2013 :  01:11:18  Show Profile  Reply with Quote
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/

Go to Top of Page

footmaniac84
Starting Member

12 Posts

Posted - 02/26/2013 :  06:29:04  Show Profile  Reply with Quote
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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/26/2013 :  23:29:27  Show Profile  Reply with Quote
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/

Go to Top of Page

footmaniac84
Starting Member

12 Posts

Posted - 02/27/2013 :  06:41:47  Show Profile  Reply with Quote
OK, thank you very much. Your help has been very useful.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/27/2013 :  23:35:11  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000