Help with SQL backup verification, unable to verify date appended

Hi,
I need help with my SQL script for appending the current date to my backup
and then being ale to verify the daily changing DB backup name. Here is my
script, i realize that the convert part is probably part of teh problem but
are there any commands to take it place in order for the verify to happen.
please help it is sort of urgent. Thanks - Nik
USE [master]
/* points to database that will be used */
GO
DBCC SHRINKDATABASE(N'master' )
/* This line shrinks database and log file*/
GO
USE [master]
/* points to database that will be used */
GO
DBCC CHECKDB(N'master', NOINDEX)
/* This line does integrity check of database*/
GO
declare @.f sysname
set @.f=N'C:\SQL
Backups\master_fullbackup_'+convert(nvarchar,getda te(),112)+N'.bak'
BACKUP DATABASE [master] TO DISK = @.f WITH NOFORMAT, NOINIT, NAME =
N'master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
/* This line backups named database to location specified*/
GO
declare @.backupSetId as int
select @.backupSetId = position from msdb..backupset where
database_name=N'master' and backup_set_id=(select max(backup_set_id) from
msdb..backupset where database_name=N'master' )
if @.backupSetId is null begin raiserror(N'Verify failed. Backup information
for database ''master'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'C:\SQL
Backups\master_fullbackup_'+convert(nvarchar,getda te(),112)+N'.bak'
WITH FILE = @.backupSetId, NOUNLOAD, NOREWIND
GO
Hi,
I did not test your script but I can give you some comments here:
1) The part to append the current date works fine
2) Store the filename in a variable instead of runing getdate() twice as the
date could be different the first and the second time (like just after
midnight)
3) Why do you need to verify data from msdb?
4) If you are using SQL Server 2005 also consider BACKUP WITH CHECKSUM.
Hope this helps,
Ben Nevarez
"lca1630" wrote:

> Hi,
> I need help with my SQL script for appending the current date to my backup
> and then being ale to verify the daily changing DB backup name. Here is my
> script, i realize that the convert part is probably part of teh problem but
> are there any commands to take it place in order for the verify to happen.
> please help it is sort of urgent. Thanks - Nik
> USE [master]
> /* points to database that will be used */
> GO
> DBCC SHRINKDATABASE(N'master' )
> /* This line shrinks database and log file*/
> GO
> USE [master]
> /* points to database that will be used */
> GO
> DBCC CHECKDB(N'master', NOINDEX)
> /* This line does integrity check of database*/
> GO
> declare @.f sysname
> set @.f=N'C:\SQL
> Backups\master_fullbackup_'+convert(nvarchar,getda te(),112)+N'.bak'
> BACKUP DATABASE [master] TO DISK = @.f WITH NOFORMAT, NOINIT, NAME =
> N'master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
> /* This line backups named database to location specified*/
> GO
> declare @.backupSetId as int
> select @.backupSetId = position from msdb..backupset where
> database_name=N'master' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset where database_name=N'master' )
> if @.backupSetId is null begin raiserror(N'Verify failed. Backup information
> for database ''master'' not found.', 16, 1) end
> RESTORE VERIFYONLY FROM DISK = N'C:\SQL
> Backups\master_fullbackup_'+convert(nvarchar,getda te(),112)+N'.bak'
> WITH FILE = @.backupSetId, NOUNLOAD, NOREWIND
>
> GO
|||Thanks for looking at this Ben. I am using SQL Express therefore the need to
come up with a workaround maintanence plan etc. The verify portion is
basically me manually setting up the backup and then having the SQL
management studio express script it for me. This is what it comes up with.
The variable part I am not sure of what to set that too, if you have any
suggestions please let me know. Thanks - Nik
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Hi,
> I did not test your script but I can give you some comments here:
> 1) The part to append the current date works fine
> 2) Store the filename in a variable instead of runing getdate() twice as the
> date could be different the first and the second time (like just after
> midnight)
> 3) Why do you need to verify data from msdb?
> 4) If you are using SQL Server 2005 also consider BACKUP WITH CHECKSUM.
> Hope this helps,
> Ben Nevarez
>
> "lca1630" wrote:
|||What I mean is to use a variable for the date or the filename to make sure
that both BACKUP and RESTORE VERIFYONLY use the same file. For example when
you run the BACKUP getdate() may return 20080109 but by the time the job is
about to run RESTORE VERIFYONLY getdate may return 20080110 and will fail
because there is no such file. Something like
set @.filename = ... getdate(), 112 ...
...
backup database ... to disk = @.filename
...
restore verifyonly from disk = @.filename
Hope this helps,
Ben Nevarez
"lca1630" wrote:
[vbcol=seagreen]
> Thanks for looking at this Ben. I am using SQL Express therefore the need to
> come up with a workaround maintanence plan etc. The verify portion is
> basically me manually setting up the backup and then having the SQL
> management studio express script it for me. This is what it comes up with.
> The variable part I am not sure of what to set that too, if you have any
> suggestions please let me know. Thanks - Nik
> "Ben Nevarez" wrote:
|||"lca1630" <lca1630@.discussions.microsoft.com> wrote in message
news:C6063C2C-D9F0-4082-BBDA-DE010216638E@.microsoft.com...
> Hi,
> GO
> DBCC SHRINKDATABASE(N'master' )
> /* This line shrinks database and log file*/
Another cmment: Don't do the above step.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html