Best way to test backup restores

Ok, so I'm using SQL Server 2000, and I'm just wondering what the best way
to test my backups is.
I don't want to restore over the actual database because it's still being
used. I realise I can just take a copy of the database and use that as the
new database - thus allowing me to restore the original, but this is not
actually what I want.
Is there any way to just 'verify' that the backup will be useable? Or to
restore it to a different database?
Thanks in advance
Amanda
Only way to really verify a SQL Server backup is to restore it. You don't need to create the
database beforehand, as the new database will be created with the RESTORE command. Pay attention to
the MOVE option of the RESTORE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Amanda Toogood" <amanda@.autumncare.com.au> wrote in message
news:OkQj1kOXEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Ok, so I'm using SQL Server 2000, and I'm just wondering what the best way
> to test my backups is.
> I don't want to restore over the actual database because it's still being
> used. I realise I can just take a copy of the database and use that as the
> new database - thus allowing me to restore the original, but this is not
> actually what I want.
> Is there any way to just 'verify' that the backup will be useable? Or to
> restore it to a different database?
> Thanks in advance
> Amanda
>
|||Hi,
Use the VERIFYONLY option in RESTORE DATABASE comamnd. This verifies the
backup but does not restore the backup. THis command Checks to see that the
backup set is complete and that all volumes are readable. However, RESTORE
VERIFYONLY does not attempt to verify the structure of the data contained in
the backup volumes.
RESTORE VERIFYONLY from disk='c:\backup\dbname.bak'
This command executes for the backup file provided and If the backup is
valid, Microsoft SQL Server 2000 returns the message:
"The backup set is valid."
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e8r#3yOXEHA.2716@.tk2msftngp13.phx.gbl...
> Only way to really verify a SQL Server backup is to restore it. You don't
need to create the
> database beforehand, as the new database will be created with the RESTORE
command. Pay attention to[vbcol=seagreen]
> the MOVE option of the RESTORE command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Amanda Toogood" <amanda@.autumncare.com.au> wrote in message
> news:OkQj1kOXEHA.1764@.TK2MSFTNGP10.phx.gbl...
way[vbcol=seagreen]
being[vbcol=seagreen]
the
>
|||I do not recommend using VERIFYONLY, as it basically only verify that the backup file is readable.
I.e., it gives us false security, IMO.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OQ2iGFQXEHA.1440@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Use the VERIFYONLY option in RESTORE DATABASE comamnd. This verifies the
> backup but does not restore the backup. THis command Checks to see that the
> backup set is complete and that all volumes are readable. However, RESTORE
> VERIFYONLY does not attempt to verify the structure of the data contained in
> the backup volumes.
> RESTORE VERIFYONLY from disk='c:\backup\dbname.bak'
> This command executes for the backup file provided and If the backup is
> valid, Microsoft SQL Server 2000 returns the message:
> "The backup set is valid."
>
> Thanks
> Hari
> MCDBA
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:e8r#3yOXEHA.2716@.tk2msftngp13.phx.gbl...
> need to create the
> command. Pay attention to
> way
> being
> the
>
|||I have to agree with Tibor on this, the only true way to test is to actually
do the complete restore. I've certainly see restore issues with backups that
passed a verifyonly. Better safe than sorry :-)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eD6n3bQXEHA.3640@.TK2MSFTNGP11.phx.gbl...
> I do not recommend using VERIFYONLY, as it basically only verify that the
backup file is readable.[vbcol=seagreen]
> I.e., it gives us false security, IMO.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OQ2iGFQXEHA.1440@.TK2MSFTNGP12.phx.gbl...
the[vbcol=seagreen]
RESTORE[vbcol=seagreen]
contained in[vbcol=seagreen]
in[vbcol=seagreen]
don't[vbcol=seagreen]
RESTORE[vbcol=seagreen]
best[vbcol=seagreen]
as[vbcol=seagreen]
not[vbcol=seagreen]
Or to
>
|||I have to agree with Tibor on this, the only true way to test is to actually
do the complete restore. I've certainly see restore issues with backups that
passed a verifyonly. Better safe than sorry :-)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eD6n3bQXEHA.3640@.TK2MSFTNGP11.phx.gbl...
> I do not recommend using VERIFYONLY, as it basically only verify that the
backup file is readable.[vbcol=seagreen]
> I.e., it gives us false security, IMO.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OQ2iGFQXEHA.1440@.TK2MSFTNGP12.phx.gbl...
the[vbcol=seagreen]
RESTORE[vbcol=seagreen]
contained in[vbcol=seagreen]
in[vbcol=seagreen]
don't[vbcol=seagreen]
RESTORE[vbcol=seagreen]
best[vbcol=seagreen]
as[vbcol=seagreen]
not[vbcol=seagreen]
Or to
>
|||I will also strongly agree. You might even wish to run a DBCC CHECKDB on it
after the restore. Of coarse if there was corruption in the original db it
will be in the restored one as well.
Andrew J. Kelly SQL MVP
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23K4hwWTXEHA.644@.tk2msftngp13.phx.gbl...
> I have to agree with Tibor on this, the only true way to test is to
actually
> do the complete restore. I've certainly see restore issues with backups
that
> passed a verifyonly. Better safe than sorry :-)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
> message news:eD6n3bQXEHA.3640@.TK2MSFTNGP11.phx.gbl...
the[vbcol=seagreen]
> backup file is readable.
the[vbcol=seagreen]
that[vbcol=seagreen]
> the
> RESTORE
> contained in
is[vbcol=seagreen]
wrote[vbcol=seagreen]
> in
> don't
> RESTORE
> best
still[vbcol=seagreen]
that[vbcol=seagreen]
> as
is
> not
> Or to
>
|||I will also strongly agree. You might even wish to run a DBCC CHECKDB on it
after the restore. Of coarse if there was corruption in the original db it
will be in the restored one as well.
Andrew J. Kelly SQL MVP
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23K4hwWTXEHA.644@.tk2msftngp13.phx.gbl...
> I have to agree with Tibor on this, the only true way to test is to
actually
> do the complete restore. I've certainly see restore issues with backups
that
> passed a verifyonly. Better safe than sorry :-)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
> message news:eD6n3bQXEHA.3640@.TK2MSFTNGP11.phx.gbl...
the[vbcol=seagreen]
> backup file is readable.
the[vbcol=seagreen]
that[vbcol=seagreen]
> the
> RESTORE
> contained in
is[vbcol=seagreen]
wrote[vbcol=seagreen]
> in
> don't
> RESTORE
> best
still[vbcol=seagreen]
that[vbcol=seagreen]
> as
is
> not
> Or to
>
|||Thanks for the advice everyone, I'll probably end up doing a verify only AND
an actual restore. Definitely better to be safe than sorry.
Thanks again
Amanda
"Amanda Toogood" <amanda@.autumncare.com.au> wrote in message
news:OkQj1kOXEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Ok, so I'm using SQL Server 2000, and I'm just wondering what the best way
> to test my backups is.
> I don't want to restore over the actual database because it's still being
> used. I realise I can just take a copy of the database and use that as the
> new database - thus allowing me to restore the original, but this is not
> actually what I want.
> Is there any way to just 'verify' that the backup will be useable? Or to
> restore it to a different database?
> Thanks in advance
> Amanda
>
|||Thanks for the advice everyone, I'll probably end up doing a verify only AND
an actual restore. Definitely better to be safe than sorry.
Thanks again
Amanda
"Amanda Toogood" <amanda@.autumncare.com.au> wrote in message
news:OkQj1kOXEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Ok, so I'm using SQL Server 2000, and I'm just wondering what the best way
> to test my backups is.
> I don't want to restore over the actual database because it's still being
> used. I realise I can just take a copy of the database and use that as the
> new database - thus allowing me to restore the original, but this is not
> actually what I want.
> Is there any way to just 'verify' that the backup will be useable? Or to
> restore it to a different database?
> Thanks in advance
> Amanda
>