I want to set a variable (binary) from another variable
(varchar) with the actual string. i.e I have generated the
actual binary value and want to pass it to another
variable...
i have tried to use 'set @.x (binary) = @.y (varchar)' but
an Implicit conversion is not allowed. I cannot do an
explicit convertion by using 'cast(@.y as binary)' as this
converts the string into its own binary value
help
MatConvert the binary value into a hex string & assign it to the variable.
Search SQL Server Books Online for sp_hexadecimal, which is a stored
procedure which can do this conversion. Also search the google archives of
this newsgroup for examples of various approaches to binary datatype
conversions.
--
Anith|||Here's the easiest way I know of (although it's somewhat of a kludge):
declare @.binary varbinary(5)
declare @.string varchar(12)
declare @.sql nvarchar(200)
set @.string='0x1234567890'
set @.sql = 'set @.binary = ' + @.string
exec sp_executesql @.sql, N'@.binary varbinary(5) output', @.binary output
print @.binary
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:458501c42b98$09762ea0$a101280a@.phx.gbl...
> I want to set a variable (binary) from another variable
> (varchar) with the actual string. i.e I have generated the
> actual binary value and want to pass it to another
> variable...
> i have tried to use 'set @.x (binary) = @.y (varchar)' but
> an Implicit conversion is not allowed. I cannot do an
> explicit convertion by using 'cast(@.y as binary)' as this
> converts the string into its own binary value
> help
> Mat|||Jeff,
Problem is, 1101 in base 2 (binary) = 13 in base 10 (decimal); if you print
convert(int, @.bin2), you'll get a result of 77, not 13 (at least, on my
system).
"Jeff Duncan" <anonymous@.discussions.microsoft.com> wrote in message
news:C07F51FE-1BD8-462F-B0D1-24D0A93CD5B2@.microsoft.com...
> Mat
> try playing with this. It seems to work fine for me
>
> declare @.bin varbinary,
> @.var Varchar(30),
> @.bin2 varbinary
> select @.bin = 1101
> print @.bin
> select @.var = convert(varchar(30), @.bin)
> print @.var
> select @.bin2 = convert(varbinary, @.var)
> print @.bin2
> Jeff Duncan
> MCDBA, MCSE+Isql
Showing posts with label actual. Show all posts
Showing posts with label actual. Show all posts
Thursday, March 22, 2012
Binary Data Type
I want to set a variable (binary) from another variable
(varchar) with the actual string. i.e I have generated the
actual binary value and want to pass it to another
variable...
i have tried to use 'set @.x (binary) = @.y (varchar)' but
an Implicit conversion is not allowed. I cannot do an
explicit convertion by using 'cast(@.y as binary)' as this
converts the string into its own binary value
help
Mat
Convert the binary value into a hex string & assign it to the variable.
Search SQL Server Books Online for sp_hexadecimal, which is a stored
procedure which can do this conversion. Also search the google archives of
this newsgroup for examples of various approaches to binary datatype
conversions.
Anith
|||Here's the easiest way I know of (although it's somewhat of a kludge):
declare @.binary varbinary(5)
declare @.string varchar(12)
declare @.sql nvarchar(200)
set @.string='0x1234567890'
set @.sql = 'set @.binary = ' + @.string
exec sp_executesql @.sql, N'@.binary varbinary(5) output', @.binary output
print @.binary
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:458501c42b98$09762ea0$a101280a@.phx.gbl...
> I want to set a variable (binary) from another variable
> (varchar) with the actual string. i.e I have generated the
> actual binary value and want to pass it to another
> variable...
> i have tried to use 'set @.x (binary) = @.y (varchar)' but
> an Implicit conversion is not allowed. I cannot do an
> explicit convertion by using 'cast(@.y as binary)' as this
> converts the string into its own binary value
> help
> Mat
|||Mat
try playing with this. It seems to work fine for me
declare @.bin varbinary,
@.var Varchar(30),
@.bin2 varbinary
select @.bin = 1101
print @.bin
select @.var = convert(varchar(30), @.bin)
print @.var
select @.bin2 = convert(varbinary, @.var)
print @.bin2
Jeff Duncan
MCDBA, MCSE+I
|||Jeff,
Problem is, 1101 in base 2 (binary) = 13 in base 10 (decimal); if you print
convert(int, @.bin2), you'll get a result of 77, not 13 (at least, on my
system).
"Jeff Duncan" <anonymous@.discussions.microsoft.com> wrote in message
news:C07F51FE-1BD8-462F-B0D1-24D0A93CD5B2@.microsoft.com...
> Mat
> try playing with this. It seems to work fine for me
>
> declare @.bin varbinary,
> @.var Varchar(30),
> @.bin2 varbinary
> select @.bin = 1101
> print @.bin
> select @.var = convert(varchar(30), @.bin)
> print @.var
> select @.bin2 = convert(varbinary, @.var)
> print @.bin2
> Jeff Duncan
> MCDBA, MCSE+I
(varchar) with the actual string. i.e I have generated the
actual binary value and want to pass it to another
variable...
i have tried to use 'set @.x (binary) = @.y (varchar)' but
an Implicit conversion is not allowed. I cannot do an
explicit convertion by using 'cast(@.y as binary)' as this
converts the string into its own binary value
help
Mat
Convert the binary value into a hex string & assign it to the variable.
Search SQL Server Books Online for sp_hexadecimal, which is a stored
procedure which can do this conversion. Also search the google archives of
this newsgroup for examples of various approaches to binary datatype
conversions.
Anith
|||Here's the easiest way I know of (although it's somewhat of a kludge):
declare @.binary varbinary(5)
declare @.string varchar(12)
declare @.sql nvarchar(200)
set @.string='0x1234567890'
set @.sql = 'set @.binary = ' + @.string
exec sp_executesql @.sql, N'@.binary varbinary(5) output', @.binary output
print @.binary
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:458501c42b98$09762ea0$a101280a@.phx.gbl...
> I want to set a variable (binary) from another variable
> (varchar) with the actual string. i.e I have generated the
> actual binary value and want to pass it to another
> variable...
> i have tried to use 'set @.x (binary) = @.y (varchar)' but
> an Implicit conversion is not allowed. I cannot do an
> explicit convertion by using 'cast(@.y as binary)' as this
> converts the string into its own binary value
> help
> Mat
|||Mat
try playing with this. It seems to work fine for me
declare @.bin varbinary,
@.var Varchar(30),
@.bin2 varbinary
select @.bin = 1101
print @.bin
select @.var = convert(varchar(30), @.bin)
print @.var
select @.bin2 = convert(varbinary, @.var)
print @.bin2
Jeff Duncan
MCDBA, MCSE+I
|||Jeff,
Problem is, 1101 in base 2 (binary) = 13 in base 10 (decimal); if you print
convert(int, @.bin2), you'll get a result of 77, not 13 (at least, on my
system).
"Jeff Duncan" <anonymous@.discussions.microsoft.com> wrote in message
news:C07F51FE-1BD8-462F-B0D1-24D0A93CD5B2@.microsoft.com...
> Mat
> try playing with this. It seems to work fine for me
>
> declare @.bin varbinary,
> @.var Varchar(30),
> @.bin2 varbinary
> select @.bin = 1101
> print @.bin
> select @.var = convert(varchar(30), @.bin)
> print @.var
> select @.bin2 = convert(varbinary, @.var)
> print @.bin2
> Jeff Duncan
> MCDBA, MCSE+I
Binary Data Type
I want to set a variable (binary) from another variable
(varchar) with the actual string. i.e I have generated the
actual binary value and want to pass it to another
variable...
i have tried to use 'set @.x (binary) = @.y (varchar)' but
an Implicit conversion is not allowed. I cannot do an
explicit convertion by using 'cast(@.y as binary)' as this
converts the string into its own binary value
help
MatConvert the binary value into a hex string & assign it to the variable.
Search SQL Server Books Online for sp_hexadecimal, which is a stored
procedure which can do this conversion. Also search the google archives of
this newsgroup for examples of various approaches to binary datatype
conversions.
Anith|||Here's the easiest way I know of (although it's somewhat of a kludge):
declare @.binary varbinary(5)
declare @.string varchar(12)
declare @.sql nvarchar(200)
set @.string='0x1234567890'
set @.sql = 'set @.binary = ' + @.string
exec sp_executesql @.sql, N'@.binary varbinary(5) output', @.binary output
print @.binary
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:458501c42b98$09762ea0$a101280a@.phx.gbl...
> I want to set a variable (binary) from another variable
> (varchar) with the actual string. i.e I have generated the
> actual binary value and want to pass it to another
> variable...
> i have tried to use 'set @.x (binary) = @.y (varchar)' but
> an Implicit conversion is not allowed. I cannot do an
> explicit convertion by using 'cast(@.y as binary)' as this
> converts the string into its own binary value
> help
> Mat|||Mat
try playing with this. It seems to work fine for me
declare @.bin varbinary,
@.var Varchar(30),
@.bin2 varbinary
select @.bin = 1101
print @.bin
select @.var = convert(varchar(30), @.bin)
print @.var
select @.bin2 = convert(varbinary, @.var)
print @.bin2
Jeff Duncan
MCDBA, MCSE+I|||Jeff,
Problem is, 1101 in base 2 (binary) = 13 in base 10 (decimal); if you print
convert(int, @.bin2), you'll get a result of 77, not 13 (at least, on my
system).
"Jeff Duncan" <anonymous@.discussions.microsoft.com> wrote in message
news:C07F51FE-1BD8-462F-B0D1-24D0A93CD5B2@.microsoft.com...
> Mat
> try playing with this. It seems to work fine for me
>
> declare @.bin varbinary,
> @.var Varchar(30),
> @.bin2 varbinary
> select @.bin = 1101
> print @.bin
> select @.var = convert(varchar(30), @.bin)
> print @.var
> select @.bin2 = convert(varbinary, @.var)
> print @.bin2
> Jeff Duncan
> MCDBA, MCSE+I
(varchar) with the actual string. i.e I have generated the
actual binary value and want to pass it to another
variable...
i have tried to use 'set @.x (binary) = @.y (varchar)' but
an Implicit conversion is not allowed. I cannot do an
explicit convertion by using 'cast(@.y as binary)' as this
converts the string into its own binary value
help
MatConvert the binary value into a hex string & assign it to the variable.
Search SQL Server Books Online for sp_hexadecimal, which is a stored
procedure which can do this conversion. Also search the google archives of
this newsgroup for examples of various approaches to binary datatype
conversions.
Anith|||Here's the easiest way I know of (although it's somewhat of a kludge):
declare @.binary varbinary(5)
declare @.string varchar(12)
declare @.sql nvarchar(200)
set @.string='0x1234567890'
set @.sql = 'set @.binary = ' + @.string
exec sp_executesql @.sql, N'@.binary varbinary(5) output', @.binary output
print @.binary
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:458501c42b98$09762ea0$a101280a@.phx.gbl...
> I want to set a variable (binary) from another variable
> (varchar) with the actual string. i.e I have generated the
> actual binary value and want to pass it to another
> variable...
> i have tried to use 'set @.x (binary) = @.y (varchar)' but
> an Implicit conversion is not allowed. I cannot do an
> explicit convertion by using 'cast(@.y as binary)' as this
> converts the string into its own binary value
> help
> Mat|||Mat
try playing with this. It seems to work fine for me
declare @.bin varbinary,
@.var Varchar(30),
@.bin2 varbinary
select @.bin = 1101
print @.bin
select @.var = convert(varchar(30), @.bin)
print @.var
select @.bin2 = convert(varbinary, @.var)
print @.bin2
Jeff Duncan
MCDBA, MCSE+I|||Jeff,
Problem is, 1101 in base 2 (binary) = 13 in base 10 (decimal); if you print
convert(int, @.bin2), you'll get a result of 77, not 13 (at least, on my
system).
"Jeff Duncan" <anonymous@.discussions.microsoft.com> wrote in message
news:C07F51FE-1BD8-462F-B0D1-24D0A93CD5B2@.microsoft.com...
> Mat
> try playing with this. It seems to work fine for me
>
> declare @.bin varbinary,
> @.var Varchar(30),
> @.bin2 varbinary
> select @.bin = 1101
> print @.bin
> select @.var = convert(varchar(30), @.bin)
> print @.var
> select @.bin2 = convert(varbinary, @.var)
> print @.bin2
> Jeff Duncan
> MCDBA, MCSE+I
Friday, February 24, 2012
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
>
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
>
Subscribe to:
Posts (Atom)