Restore

Good morning everyone,
I need to restore a database on a nightly basis from a nightly backup. I
was going to use the following script but then realized that my .bak
file name is dynamic. (i.e My backup runs nightly, so the file name gets
stamped with a date, so the it is not always the same)
USE master
RESTORE DATABASE SomeDatabase
FROM DISK = 'E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\SomeDatabase.bak'
WITH RECOVERY,
MOVE 'SomeDatabase_Data' TO 'E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\SomeDatabase.mdf',
MOVE 'SomeDatabase_Log' TO 'E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Log\SomeDatabase.ldf'
GO
So, how do I then script this job? Please let me know. Thanks!
*** Sent via Developersdex http://www.developersdex.com ***See backupset in Books OnLine. There is a name column in there you can use
to dynamically create the correct RESTORE statement.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Audrey Ng" <odd26uk@.yahoo.co.uk> wrote in message
news:ecG%23rlxcIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Good morning everyone,
> I need to restore a database on a nightly basis from a nightly backup. I
> was going to use the following script but then realized that my .bak
> file name is dynamic. (i.e My backup runs nightly, so the file name gets
> stamped with a date, so the it is not always the same)
> USE master
> RESTORE DATABASE SomeDatabase
> FROM DISK = 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Backup\SomeDatabase.bak'
> WITH RECOVERY,
> MOVE 'SomeDatabase_Data' TO 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\SomeDatabase.mdf',
> MOVE 'SomeDatabase_Log' TO 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Log\SomeDatabase.ldf'
> GO
> So, how do I then script this job? Please let me know. Thanks!
> *** Sent via Developersdex http://www.developersdex.com ***|||The physical name of the backup file is stored in the backupmediafamily table
in the MSDB database. You can put the physical files name in a variable and
then dynamically build the command you want. This command will get you the
name of the last full backup for database 'SomeDatabase' If you have
transaction logs as well this must be modified.
declare @.physical_device_name nvarchar(128)
select @.physical_device_name = c.physical_device_name
from msdb..backupset a join msdb..backupmediaset b on a.media_set_id =b.media_set_id
join msdb..backupmediafamily c on a.media_set_id = c.media_set_id
where type='D' and backup_start_date = (select top 1 backup_start_date from msdb..backupset
where database_name = 'SomeDatabase' and type = 'D'
order by backup_start_date desc)
select @.physical_device_name
"Audrey Ng" wrote:
> Good morning everyone,
> I need to restore a database on a nightly basis from a nightly backup. I
> was going to use the following script but then realized that my .bak
> file name is dynamic. (i.e My backup runs nightly, so the file name gets
> stamped with a date, so the it is not always the same)
> USE master
> RESTORE DATABASE SomeDatabase
> FROM DISK = 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Backup\SomeDatabase.bak'
> WITH RECOVERY,
> MOVE 'SomeDatabase_Data' TO 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\SomeDatabase.mdf',
> MOVE 'SomeDatabase_Log' TO 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Log\SomeDatabase.ldf'
> GO
> So, how do I then script this job? Please let me know. Thanks!
> *** Sent via Developersdex http://www.developersdex.com ***
>