restore complete backup

Hello,

Does anyone has an example script how to restore a complete database
with transaction log?

Thnx

Jason
create database test
GO
create table test..test(id int identity)
insert test..test default values
backup database test to disk = 'd:\db.bak' WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak'WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak' WITH NOINIT
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, recovery

"Jason" <jasonlewis@hotrmail.com> wrote in message
news:exy2NPPxFHA.2720@TK2MSFTNGP10.phx.gbl...
> Hello,
>
> Does anyone has an example script how to restore a complete database with
> transaction log?
>
> Thnx

Uri Dimant  wrote:
> Jason
> create database test
> GO
> create table test..test(id int identity)
> insert test..test default values
> backup database test to disk = 'd:\db.bak' WITH INIT
> insert test..test default values
> backup log test to disk = 'd:\log.bak'WITH INIT
> insert test..test default values
> backup log test to disk = 'd:\log.bak' WITH NOINIT
> GO
> RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
> RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 1, norecovery
> RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, recovery
>
> "Jason" <jasonlewis@hotrmail.com> wrote in message
> news:exy2NPPxFHA.2720@TK2MSFTNGP10.phx.gbl...
> 
>
>
>
Hi Uri,

And how about the filenumber? I have a backupdevice where the backups
adds on a daily basis. How can i always use the last backup added.

Hi,

You can query the max position for the backup file in  MSDB..BACKUPSET
table. Use the below query as sample.

SELECT MAX(POSITION) FROM MSDB..BACKUPSET A
inner join MSDB..BACKUPMEDIAFAMILY b
ON A.MEDIA_SET_ID = B.MEDIA_SET_ID
AND   PHYSICAL_DEVICE_NAME
='C:\MAST.BAK'

You can give the output in the Restore statement.

Thanks
Hari
SQL Server MVP


"Jason" <jasonlewis@hotrmail.com> wrote in message
news:uONG9GQxFHA.900@TK2MSFTNGP11.phx.gbl...
> Uri Dimant wrote: 
> Hi Uri,
>
> And how about the filenumber? I have a backupdevice where the backups adds
> on a daily basis. How can i always use the last backup added.

Hari Prasad  wrote:
> Hi,
>
> You can query the max position for the backup file in  MSDB..BACKUPSET
> table. Use the below query as sample.
>
> SELECT MAX(POSITION) FROM MSDB..BACKUPSET A
>             inner join MSDB..BACKUPMEDIAFAMILY b
>        ON A.MEDIA_SET_ID = B.MEDIA_SET_ID
>        AND   PHYSICAL_DEVICE_NAME
='C:\MAST.BAK'
>
> You can give the output in the Restore statement.
>
> Thanks
> Hari
> SQL Server MVP
>
>
> "Jason" <jasonlewis@hotrmail.com> wrote in message
> news:uONG9GQxFHA.900@TK2MSFTNGP11.phx.gbl...
> 
>
>
>
Hi,

I'm getting the wrong number returned from your example. If i check the
backup, there are 3 files in it. When i execute your example it says 6.

Is there something missing?