|
Home > Archive > MS SQL Server > September 2005 > restore complete backup
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
restore complete backup
|
|
|
| Hello,
Does anyone has an example script how to restore a complete database
with transaction log?
Thnx
| |
| Uri Dimant 2005-09-29, 9:23 am |
| 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
| |
| Jason 2005-09-29, 11:23 am |
| 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.
| |
| Hari Prasad 2005-09-29, 8:23 pm |
| 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?
|
|
|
|
|