restore question

Hi
I am taking a backup of SQLSERVER on remote machine. I need to restore the
database on a daily basis on the remote machine.
I take backup of transaction log using maintenance plan every 30 mins.
It generates 48 transaciton log per day.
How can write a script to restore the database applying those 48 logs.
I can create linked server to production but what table keeps the backup log
information that I can use to build this.
Thanks
MangeshI keep my own table to track which log files I've applied
I store the table in a DB named "Admin"
then I select the logfiles on the file system that have not yet been applied
sorted in datetime order for example
then I apply them one at a time.
hope this makes sense.
I can dig up the scripts if you really need (But it will cost you some
Sushi)
Greg Jackson
PDX, Oregon|||You can get all the DB backup history from the msdb database.
Specifically, you get the media_set_id values for the backups you wish
to restore from dbo.backupset based on DB name, DB vs LOG backups, dates
& times, etc., etc. And then you can get the physical file location for
those backups from dbo.backupmediafamily (using
dbo.backupset.media_set_id, which corresponds to
dbo.backupmediafamily.media_set_id). Something like:
select bs.backup_start_date, bs.type, bmf.physical_device_name
from msdb.dbo.backupset bs
inner join msdb.dbo.backupmediafamily bmf
on bmf.media_set_id = bs.media_set_id
where bs.database_name = N'MyDB'
order by bs.backup_start_date
Based on that kind of query it should be a piece of cake to automate a
restore procedure given some constraints around the restore (like
DBName, start datetime, finish datetime, etc.) also not forgetting to
restore a "D" backup followed by zero or more "L" backups.
HTH,
Mike.
PS. Do I get Sushi now?
pdxJaxon wrote:
> I keep my own table to track which log files I've applied
> I store the table in a DB named "Admin"
> then I select the logfiles on the file system that have not yet been applied
> sorted in datetime order for example
> then I apply them one at a time.
>
> hope this makes sense.
> I can dig up the scripts if you really need (But it will cost you some
> Sushi)
>
> Greg Jackson
> PDX, Oregon
>
--
ÿþC|||Hi Mangesh,
If your objective is to have a standby database, have you thought of SQL
Server log-shipping as an option? Its pretty simple to set-up and get it
running.
--
Thanks
Yogish