check restore verifyonly result

How can I check one of my db backups result is vaild in vb or transact sql?
For example
if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
'restore it
else
' send a message
End if
thanks
The IF/ELSE method might not work since some errors will terminate the
batch. However, you can check @.@.ERROR (or catch in you VB app):
RESTORE VERIFYONLY
FROM DISK='C:\1.bak'
GO
IF @.@.ERROR = 0
BEGIN
RESTORE DATABASE MyDatabase
FROM DISK='C:\1.bak'
END
ELSE
BEGIN
PRINT 'Cannot restore from backup'
END
GO
Note that RESTORE VERIFYONLY does only a cursory check to see if the backup
is valid. The best way to make sure is with an actual restore, perhaps to a
different database name.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tolgay" <tgul@.tgul.com> wrote in message
news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> How can I check one of my db backups result is vaild in vb or transact
> sql?
> For example
> if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
> 'restore it
> else
> ' send a message
> End if
> thanks
>
>
|||You should use SQLDMO
This library has an object RESTORE which has a method SQLVerify that can be
used to check the health of a backup media.
Bien cordialement
Med Bouchenafa
"Tolgay" <tgul@.tgul.com> wrote in message
news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> How can I check one of my db backups result is vaild in vb or transact
> sql?
> For example
> if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
> 'restore it
> else
> ' send a message
> End if
> thanks
>
>
|||thank you Dan,
it works good.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e6y$4bz$FHA.4036@.TK2MSFTNGP10.phx.gbl...
> The IF/ELSE method might not work since some errors will terminate the
> batch. However, you can check @.@.ERROR (or catch in you VB app):
> RESTORE VERIFYONLY
> FROM DISK='C:\1.bak'
> GO
> IF @.@.ERROR = 0
> BEGIN
> RESTORE DATABASE MyDatabase
> FROM DISK='C:\1.bak'
> END
> ELSE
> BEGIN
> PRINT 'Cannot restore from backup'
> END
> GO
> Note that RESTORE VERIFYONLY does only a cursory check to see if the
backup
> is valid. The best way to make sure is with an actual restore, perhaps to
a
> different database name.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tolgay" <tgul@.tgul.com> wrote in message
> news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
>

check restore verifyonly result

How can I check one of my db backups result is vaild in vb or transact sql?
For example
if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
'restore it
else
' send a message
End if
thanksThe IF/ELSE method might not work since some errors will terminate the
batch. However, you can check @.@.ERROR (or catch in you VB app):
RESTORE VERIFYONLY
FROM DISK='C:\1.bak'
GO
IF @.@.ERROR = 0
BEGIN
RESTORE DATABASE MyDatabase
FROM DISK='C:\1.bak'
END
ELSE
BEGIN
PRINT 'Cannot restore from backup'
END
GO
Note that RESTORE VERIFYONLY does only a cursory check to see if the backup
is valid. The best way to make sure is with an actual restore, perhaps to a
different database name.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tolgay" <tgul@.tgul.com> wrote in message
news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> How can I check one of my db backups result is vaild in vb or transact
> sql?
> For example
> if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
> 'restore it
> else
> ' send a message
> End if
> thanks
>
>|||You should use SQLDMO
This library has an object RESTORE which has a method SQLVerify that can be
used to check the health of a backup media.
--
Bien cordialement
Med Bouchenafa
"Tolgay" <tgul@.tgul.com> wrote in message
news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> How can I check one of my db backups result is vaild in vb or transact
> sql?
> For example
> if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
> 'restore it
> else
> ' send a message
> End if
> thanks
>
>|||thank you Dan,
it works good.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e6y$4bz$FHA.4036@.TK2MSFTNGP10.phx.gbl...
> The IF/ELSE method might not work since some errors will terminate the
> batch. However, you can check @.@.ERROR (or catch in you VB app):
> RESTORE VERIFYONLY
> FROM DISK='C:\1.bak'
> GO
> IF @.@.ERROR = 0
> BEGIN
> RESTORE DATABASE MyDatabase
> FROM DISK='C:\1.bak'
> END
> ELSE
> BEGIN
> PRINT 'Cannot restore from backup'
> END
> GO
> Note that RESTORE VERIFYONLY does only a cursory check to see if the
backup
> is valid. The best way to make sure is with an actual restore, perhaps to
a
> different database name.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tolgay" <tgul@.tgul.com> wrote in message
> news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> > How can I check one of my db backups result is vaild in vb or transact
> > sql?
> >
> > For example
> >
> > if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
> > 'restore it
> > else
> > ' send a message
> > End if
> >
> > thanks
> >
> >
> >
>

check restore verifyonly result

How can I check one of my db backups result is vaild in vb or transact sql?
For example
if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
'restore it
else
' send a message
End if
thanksThe IF/ELSE method might not work since some errors will terminate the
batch. However, you can check @.@.ERROR (or catch in you VB app):
RESTORE VERIFYONLY
FROM DISK='C:\1.bak'
GO
IF @.@.ERROR = 0
BEGIN
RESTORE DATABASE MyDatabase
FROM DISK='C:\1.bak'
END
ELSE
BEGIN
PRINT 'Cannot restore from backup'
END
GO
Note that RESTORE VERIFYONLY does only a cursory check to see if the backup
is valid. The best way to make sure is with an actual restore, perhaps to a
different database name.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tolgay" <tgul@.tgul.com> wrote in message
news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> How can I check one of my db backups result is vaild in vb or transact
> sql?
> For example
> if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
> 'restore it
> else
> ' send a message
> End if
> thanks
>
>|||You should use SQLDMO
This library has an object RESTORE which has a method SQLVerify that can be
used to check the health of a backup media.
Bien cordialement
Med Bouchenafa
"Tolgay" <tgul@.tgul.com> wrote in message
news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> How can I check one of my db backups result is vaild in vb or transact
> sql?
> For example
> if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
> 'restore it
> else
> ' send a message
> End if
> thanks
>
>|||thank you Dan,
it works good.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e6y$4bz$FHA.4036@.TK2MSFTNGP10.phx.gbl...
> The IF/ELSE method might not work since some errors will terminate the
> batch. However, you can check @.@.ERROR (or catch in you VB app):
> RESTORE VERIFYONLY
> FROM DISK='C:\1.bak'
> GO
> IF @.@.ERROR = 0
> BEGIN
> RESTORE DATABASE MyDatabase
> FROM DISK='C:\1.bak'
> END
> ELSE
> BEGIN
> PRINT 'Cannot restore from backup'
> END
> GO
> Note that RESTORE VERIFYONLY does only a cursory check to see if the
backup
> is valid. The best way to make sure is with an actual restore, perhaps to
a
> different database name.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tolgay" <tgul@.tgul.com> wrote in message
> news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
>

check restore verifyonly result

How can I check one of my db backups result is vaild in vb or transact sql?
For example
if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
'restore it
else
' send a message
End if
thanksThe IF/ELSE method might not work since some errors will terminate the
batch. However, you can check @.@.ERROR (or catch in you VB app):
RESTORE VERIFYONLY
FROM DISK='C:\1.bak'
GO
IF @.@.ERROR = 0
BEGIN
RESTORE DATABASE MyDatabase
FROM DISK='C:\1.bak'
END
ELSE
BEGIN
PRINT 'Cannot restore from backup'
END
GO
Note that RESTORE VERIFYONLY does only a cursory check to see if the backup
is valid. The best way to make sure is with an actual restore, perhaps to a
different database name.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tolgay" <tgul@.tgul.com> wrote in message
news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> How can I check one of my db backups result is vaild in vb or transact
> sql?
> For example
> if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
> 'restore it
> else
> ' send a message
> End if
> thanks
>
>|||You should use SQLDMO
This library has an object RESTORE which has a method SQLVerify that can be
used to check the health of a backup media.
Bien cordialement
Med Bouchenafa
"Tolgay" <tgul@.tgul.com> wrote in message
news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> How can I check one of my db backups result is vaild in vb or transact
> sql?
> For example
> if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
> 'restore it
> else
> ' send a message
> End if
> thanks
>
>|||thank you Dan,
it works good.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e6y$4bz$FHA.4036@.TK2MSFTNGP10.phx.gbl...
> The IF/ELSE method might not work since some errors will terminate the
> batch. However, you can check @.@.ERROR (or catch in you VB app):
> RESTORE VERIFYONLY
> FROM DISK='C:\1.bak'
> GO
> IF @.@.ERROR = 0
> BEGIN
> RESTORE DATABASE MyDatabase
> FROM DISK='C:\1.bak'
> END
> ELSE
> BEGIN
> PRINT 'Cannot restore from backup'
> END
> GO
> Note that RESTORE VERIFYONLY does only a cursory check to see if the
backup
> is valid. The best way to make sure is with an actual restore, perhaps to
a
> different database name.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tolgay" <tgul@.tgul.com> wrote in message
> news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
>

Check for backups performed

Hi all!
Is there a way to check using transact SQL or any other option that
backup on a database has been performed? Is there any table on the
server that could be queried for successful/unsuccessful backup made?
Or is there a way to get the last date of successful backup made on
the database?
Thanks in advance,
IlyaHi
Use RESTORE VERIFYONLY ... (See BOL for more details)
Nice feature in SQL Server 2005 (You can track backup' percent_complete
,estimated_completion_time )
select top 2 start_time,
percent_complete ,estimated_completion_time
from sys.dm_exec_requests
order by start_time desc
"Ilya" <lentyai@.gmail.com> wrote in message
news:1189513740.265730.67260@.k79g2000hse.googlegroups.com...
> Hi all!
> Is there a way to check using transact SQL or any other option that
> backup on a database has been performed? Is there any table on the
> server that could be queried for successful/unsuccessful backup made?
> Or is there a way to get the last date of successful backup made on
> the database?
> Thanks in advance,
> Ilya
>|||Hi,
The database server mentioned is SQL Server 2000 (SP4).
Thanks,
Ilya|||On Sep 11, 2:31 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> Use RESTORE VERIFYONLY ... (See BOL for more details)
> Nice feature in SQL Server 2005 (You can track backup' percent_complete
> ,estimated_completion_time )
> select top 2 start_time,
> percent_complete ,estimated_completion_time
> from sys.dm_exec_requests
> order by start_time desc
> "Ilya" <lent...@.gmail.com> wrote in message
> news:1189513740.265730.67260@.k79g2000hse.googlegroups.com...
> > Hi all!
> > Is there a way to check using transact SQL or any other option that
> > backup on a database has been performed? Is there any table on the
> > server that could be queried for successful/unsuccessful backup made?
> > Or is there a way to get the last date of successful backup made on
> > the database?
> > Thanks in advance,
> > Ilya
Hi, Uri!
Thanks for your quick reply.
Found smth myself with the BOL and VERIFYONLY you mentioned.
There is a table called backupset in msdb database. There are a plenty
of fields and backup_finish_date & database_name as well. These two
give me what I need (the date of the last backup made on the
database).
Then the following SQL gives me what I need:
SELECT TOP 1 backup_finish_date
FROM msdb..backupset
WHERE database_name = '<The name of the database>'
ORDER BY backup_finish_date DESC
Thanks again,
Ilya

check diskspace UNC via T-SQL

Hello,
I have an EM Job script that backups up my databases using SQLsafe to a
target UNC.
Sometimes, the target server that will store the backups is off line or
it's disk is full.
I am wondering how I can check to see if the machine is up via the job
and secondly, check the disk space.
If either check fails, I would then check another machine machine.
Any ideas appreciated,
Thanks
Rob
SQL 2000 Server and Enterprise, Windows 2003
SQL 2005 Server and Etnerprise, Windows 2003
Target storage is a Windows 2003 and I connect via UNCYou can use a couple of extended procs to get what you want. I have a
stored proc that jumps through some hoops to give me that information.

First I call: EXEC master.dbo.xp_availablemedia

That returns a list of devices on the database server. I loop over the
results from that and do:

EXEC master..xp_cmdshell 'DIR /-C <drive>'

and I look for the line that has "bytes free" and parse that for the
number.

It's not terribly elegant or fancy, but it does the job. The SQL for
the stored proc is below if you're curious. I also reference a table
that I created in msdb to help me track growth over time. You can just
eliminate that part.

Hope it helps,
Teresa Masino

CREATE procedure sp_checkdbspace
AS
SET nocount ON

CREATE TABLE #DriveList (
namevarchar(20)null,
lowfreeintnull,
highfree intnull,
mediatype intnull
)

CREATE TABLE #DirList (
Drive varchar(20) null,
DirResults varchar(255) null
)

INSERT INTO #DriveList EXEC master.dbo.xp_availablemedia

DECLARE @.Drive varchar(20),
@.CMD varchar(255)

DECLARE mycursor CURSOR
FOR
SELECTname
FROM#DriveList
ORDER BY name

OPEN mycursor

FETCH mycursor INTO @.Drive

IF CURSOR_STATUS('variable', '@.mycursor') = 0
BEGIN
PRINT 'No such device'
CLOSE mycursor
DEALLOCATE mycursor
return
END

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.CMD = 'insert into #DirList (DirResults) EXEC
master..xp_cmdshell ''DIR /-C ' + @.Drive + ''''
EXEC (@.CMD)
UPDATE #DirList SET Drive = @.Drive WHERE Drive IS NULL
FETCH mycursor INTO @.Drive
END

CLOSE mycursor
DEALLOCATE mycursor

SELECTDBName, LogicalName, PhysicalName, MinSize = min(SizeMB),
MaxSize = max(SizeMB), MinDate = min(StatusDate), MaxDate =
Max(StatusDate), MaxSizeMB = max(MaxSizeMB)
INTO#SpaceList
FROMmsdb..DBSpaceHistory
GROUP BY DBName, LogicalName, PhysicalName
ORDER BY DBName, LogicalName, PhysicalName

SELECT*, BytesFree = convert(numeric(18,0),
rtrim(ltrim(substring(replace(DirResults, ' bytes free', ''), 26,
50))))
INTO#SpaceOnDisk
FROM#DirList
WHEREDirResults LIKE '%bytes free%'

SELECTDBName = convert(varchar(20), DBName),
PhysicalName = convert(varchar(60), PhysicalName),
MaxSize,
Growth = MaxSize - MinSize,
DiskMBFree = convert(numeric(10,3), BytesFree / 1048576),
GrowthPeriod = datediff(day, MinDate, MaxDate),
DaysLeft = convert(numeric(10,3), (BytesFree / 1048576) / CASE WHEN
(MaxSize - MinSize) <= 0 THEN 1 ELSE ((MaxSize - MinSize) /
datediff(day, MinDate, MaxDate)) END)
FROM#SpaceList, #SpaceOnDisk
WHEREUPPER(substring(PhysicalName, 1, 3)) = Drive

GO