CREATE TABLE #results
\n(
\nDatabaseName VARCHAR(250) COLLATE database_default
\n)
\nINSERT INTO #results (DatabaseName) SELECT name FROM sys.databases where name <> ‘tempdb’<\/p>\n
DECLARE DATABASE_CURSOR CURSOR FOR SELECT DatabaseName FROM #results ORDER BY DatabaseName
\nDeclare @databaseName varchar(200)
\nOPEN DATABASE_CURSOR;
\nFETCH NEXT FROM DATABASE_CURSOR INTO @databaseName
\nWHILE @@FETCH_STATUS = 0
\nBEGIN<\/p>\n
–select @databaseName<\/p>\n
DECLARE @MyPath varchar(200)
\nDECLARE @MyFileName varchar(200)
\nDECLARE @MyDBName varchar(200)
\nDECLARE @MyDirectory varchar(200)
\nSELECT @MyDBName = @databaseName
\nSelect @MyDirectory = (Select ‘D:\\sql backups\\’+@MyDBName)
\nSELECT @MyPath = (SELECT (@MyDirectory+’\\’+@MyDBName+’_Backup_’ + REPLACE (REPLACE (CONVERT(varchar(20), GETDATE(), 120), ‘:’, ”), ‘-‘, ”) + ‘.bak’))
\n–Select @MyDirectory
\nEXECUTE master.dbo.xp_create_subdir @MyDirectory
\nSELECT @MyFileName = (SELECT (@MyDBName+’_Backup_’ + REPLACE (REPLACE (CONVERT(varchar(20), GETDATE(), 120), ‘:’, ”), ‘-‘, ”)))
\nBACKUP DATABASE @MyDBName TO DISK = @MyPath WITH COPY_ONLY, RETAINDAYS = 14, NOFORMAT, NOINIT, NAME = @MyFileName, SKIP, REWIND, NOUNLOAD, STATS = 10
\ndeclare @backupSetId as int
\ndeclare @MyRaiseError varchar(200)
\nSELECT @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 )
\nSELECT @MyRaiseError = (N’Verify failed. Backup information for database ”’ + @MyDBName + ”’ not found.’)
\nif @backupSetId is null begin raiserror (@MyRaiseError,16 ,1) end
\nRESTORE VERIFYONLY FROM DISK = @MyPath WITH FILE = @backupSetId, NOUNLOAD, NOREWIND<\/p>\n
FETCH NEXT FROM DATABASE_CURSOR INTO @databaseName
\nEND
\nCLOSE DATABASE_CURSOR
\nDEALLOCATE DATABASE_CURSOR
\ndrop table #results<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":392,"menu_order":0,"comment_status":"open","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-394","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/www.bartons.org.uk\/index.php?rest_route=\/wp\/v2\/pages\/394","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bartons.org.uk\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.bartons.org.uk\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.bartons.org.uk\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bartons.org.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=394"}],"version-history":[{"count":0,"href":"https:\/\/www.bartons.org.uk\/index.php?rest_route=\/wp\/v2\/pages\/394\/revisions"}],"up":[{"embeddable":true,"href":"https:\/\/www.bartons.org.uk\/index.php?rest_route=\/wp\/v2\/pages\/392"}],"wp:attachment":[{"href":"https:\/\/www.bartons.org.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=394"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}