restore complete backup

Hello,
Does anyone has an example script how to restore a complete database
with transaction log?
ThnxJason
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?