May 12, 2011 at 12:54 pm
we have litespeed backups for our server for all the databases.
i am looking to do restore verifyonly for those backups to make sure they are readable and complete.
i can run it one by one but the problem is i have about 20 full backups and about 30 trans backups all together .
i just dont have enough time to go one by one.
is there a way i can read the backup files name from the MSDB table and run the command for all the databases.
we have backup of "Disk"
i have found out a stored procedure but SP wont do because i cant make any structural changes on any of the databases.
Thanks in advance.
May 12, 2011 at 1:11 pm
A couple of issues. One I assume you are using CHECKSUM when doing the backups. If not you should consider this.
Also consider test restores periodically (not on top of the production database but onto another server or into a test name on the same server)
Also for instances like this I create a database called DBA on every server. In this database I store any common procs and tables I require. I am not going to alter a user database either so the proc to do the backups, restores, check integrity etc are all in the DBA database. SO if you have a stored proc to list the backups then this is where it would go. btw if you already have this stored proc just check the contents to get the code to display the backup name: eg.
DECLARE @db sysname
set @db = 'DBA' --< databsae name
SELECT c.physical_device_name , a. backup_start_date
FROM msdb.dbo.backupset a join msdb.dbo.backupmediaset b ON a.media_set_id = b.media_set_id
JOIN msdb.dbo.backupmediafamily c ON a.media_set_id = c.media_set_id
WHERE type='D' AND a.database_name= @db AND backup_start_date =
(SELECT TOP 1 backup_start_date FROM msdb.dbo.backupset bs2
WHERE bs2.database_name = a.database_name AND type = 'D'
ORDER BY backup_start_date DESC)
for the full backup name.
Francis
December 8, 2011 at 1:24 am
Hi,
i need the same script for multiple restore verifyonly. Isnt there a script for this?
December 9, 2011 at 8:42 am
Taking SSCommitted's code and wrapping it in a sp_msforeachdb you can run the below:
sp_msforeachdb '
SELECT ''?'',c.physical_device_name , a. backup_start_date
FROM msdb.dbo.backupset a join msdb.dbo.backupmediaset b ON a.media_set_id = b.media_set_id
JOIN msdb.dbo.backupmediafamily c ON a.media_set_id = c.media_set_id
WHERE type=''D'' AND a.database_name= ''?'' AND backup_start_date =
(SELECT TOP 1 backup_start_date FROM msdb.dbo.backupset bs2
WHERE bs2.database_name = a.database_name AND type = ''D'' ORDER BY backup_start_date DESC)'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply