Thursday, March 22, 2012

Database backup script

In SQL 7, we backup and restore an OLTP database to
another server to off-load some processing and to have a
static copy of the database at a point in time.
Here's the procedure:
--restore from full backup
RESTORE DATABASE <database name> from
DISK =
N'\\<server>\<share>\<folder>\<subfolder>\file_1.b ak',
DISK = N'<server>\<share>\<folder>\<subfolder>\file_2.bak ',
DISK = N'<server>\<share>\<folder>\<subfolder>\file_3.bak ',
DISK = N'<server>\<share>\<folder>\<subfolder>\file_4.bak '
WITH MOVE '<file name 1>' to 'location\file1.mdf',
MOVE '<file name 2' to 'location\file2.mdf',
MOVE '<log file name 1>' to 'location\LOGfile1.ldf',
MOVE '<log file name 2>' to 'location\LOGfile2.ldf',
NORECOVERY, REPLACE
--restore differential backup
RESTORE DATABASE <database name> FROM DISK
= '<server>\<share>\<folder>\<subfolder>\filename.BA K'
with recovery
Periodically, this script completes, but leaves the
restored database in 'loading' status. I know how to
correct this, by using 'RESTORE DATABASE with RECOVERY',
but I'm wondering why this happens sometimes and not
others. Any suggestions?
Are you sure that your script gets all the way through the differential
restore? If for some reason the differential restore didn't happen or
failed, then you database would be in "loading" status.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"David Thayer" <dthayer@.deltanet.net> wrote in message
news:227bb01c45df3$a0fe1a20$a601280a@.phx.gbl...
> In SQL 7, we backup and restore an OLTP database to
> another server to off-load some processing and to have a
> static copy of the database at a point in time.
> Here's the procedure:
> --restore from full backup
> RESTORE DATABASE <database name> from
> DISK =
> N'\\<server>\<share>\<folder>\<subfolder>\file_1.b ak',
> DISK = N'<server>\<share>\<folder>\<subfolder>\file_2.bak ',
> DISK = N'<server>\<share>\<folder>\<subfolder>\file_3.bak ',
> DISK = N'<server>\<share>\<folder>\<subfolder>\file_4.bak '
> WITH MOVE '<file name 1>' to 'location\file1.mdf',
> MOVE '<file name 2' to 'location\file2.mdf',
> MOVE '<log file name 1>' to 'location\LOGfile1.ldf',
> MOVE '<log file name 2>' to 'location\LOGfile2.ldf',
> NORECOVERY, REPLACE
> --restore differential backup
> RESTORE DATABASE <database name> FROM DISK
> = '<server>\<share>\<folder>\<subfolder>\filename.BA K'
> with recovery
>
> Periodically, this script completes, but leaves the
> restored database in 'loading' status. I know how to
> correct this, by using 'RESTORE DATABASE with RECOVERY',
> but I'm wondering why this happens sometimes and not
> others. Any suggestions?
|||Initially, this was my suspicion, but upon review of the
data, the diff restore did complete, but just left the
database in 'load'.

>--Original Message--
>Are you sure that your script gets all the way through
the differential
>restore? If for some reason the differential restore
didn't happen or
>failed, then you database would be in "loading" status.
>--
>----
--
>----
--[vbcol=seagreen]
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"David Thayer" <dthayer@.deltanet.net> wrote in message
>news:227bb01c45df3$a0fe1a20$a601280a@.phx.gbl...
N'<server>\<share>\<folder>\<subfolder>\file_2.bak ',[vbcol=seagreen]
N'<server>\<share>\<folder>\<subfolder>\file_3.bak ',[vbcol=seagreen]
N'<server>\<share>\<folder>\<subfolder>\file_4.bak '
>
>.
>

No comments:

Post a Comment