CREATE TABLE #results
(
DatabaseName VARCHAR(250) COLLATE database_default
)
INSERT INTO #results (DatabaseName) SELECT name FROM sys.databases where name <> ‘tempdb’

DECLARE DATABASE_CURSOR CURSOR FOR SELECT DatabaseName FROM #results ORDER BY DatabaseName
Declare @databaseName varchar(200)
OPEN DATABASE_CURSOR;
FETCH NEXT FROM DATABASE_CURSOR INTO @databaseName
WHILE @@FETCH_STATUS = 0
BEGIN

–select @databaseName

DECLARE @MyPath varchar(200)
DECLARE @MyFileName varchar(200)
DECLARE @MyDBName varchar(200)
DECLARE @MyDirectory varchar(200)
SELECT @MyDBName = @databaseName
Select @MyDirectory = (Select ‘D:\sql backups\’+@MyDBName)
SELECT @MyPath = (SELECT (@MyDirectory+’\’+@MyDBName+’_Backup_’ + REPLACE (REPLACE (CONVERT(varchar(20), GETDATE(), 120), ‘:’, ”), ‘-‘, ”) + ‘.bak’))
–Select @MyDirectory
EXECUTE master.dbo.xp_create_subdir @MyDirectory
SELECT @MyFileName = (SELECT (@MyDBName+’_Backup_’ + REPLACE (REPLACE (CONVERT(varchar(20), GETDATE(), 120), ‘:’, ”), ‘-‘, ”)))
BACKUP DATABASE @MyDBName TO DISK = @MyPath WITH COPY_ONLY, RETAINDAYS = 14, NOFORMAT, NOINIT, NAME = @MyFileName, SKIP, REWIND, NOUNLOAD, STATS = 10
declare @backupSetId as int
declare @MyRaiseError varchar(200)
SELECT @backupSetId = position from msdb..backupset where database_name=@MyDBName and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=@MyDBName )
SELECT @MyRaiseError = (N’Verify failed. Backup information for database ”’ + @MyDBName + ”’ not found.’)
if @backupSetId is null begin raiserror (@MyRaiseError,16 ,1) end
RESTORE VERIFYONLY FROM DISK = @MyPath WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

FETCH NEXT FROM DATABASE_CURSOR INTO @databaseName
END
CLOSE DATABASE_CURSOR
DEALLOCATE DATABASE_CURSOR
drop table #results