Dynamically Script Database Backups

,

The below script, once given a path to store the backup files, will dynamically script and execute all non-system databases and append the date and time.

To include system database remove 'where sid <> 0x01' from the query

Create this proc on the Master DB. Don't forget to prefix with USP.

Hope this helps πŸ™‚

create Proc USP_BackupDatabasesOnPr01 -- exec USP_BackupDatabasesOnPr01 'drive:\FolderPath'
@path nvarchar(4000)

as
/* 
Author:β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆShanu Hoosen
Date:β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆCreated:31/01/2011
Execute as:β€ˆβ€ˆβ€ˆβ€ˆExec USP_BackupDatabasesOnPr01 'drive:\FolderPath'

*/

declare @sql nvarchar(4000)
declare @i nvarchar(3)
declare @mx int
declare @DBName nvarchar(500)


select ROW_NUMBER() OVER(ORDER BY name)id,name 
into #d
from sysdatabases
where sid <> 0x01
set @i = (select min(id) from #d)
set @mx = (select max(id)+1 from #d)

while @i <> @mx
begin 
set @DBName=(select '['+name+']' from #d where id = @i)

set @sql = 'BEGIN TRY
β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆBACKUP DATABASE '+ @DBName +' 
β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆTO DISK = N'''+@path+ replace(replace (@DBName, '[',''),']','') +'-'+ replace(cast(getdate() as nvarchar(500)),':','')+'.bak'''+
β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆ' WITH NOFORMAT, NOINIT, β€ˆβ€ˆβ€ˆβ€ˆ
β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆ NAME = N'''+@DBName +'-Full Database Backup'''+','
β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆ+'SKIP, NOREWIND, NOUNLOAD, STATS = 10
β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆEND TRY
β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆBEGIN CATCH
β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆSELECT
β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆERROR_NUMBER() AS ErrorNumber,
β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆERROR_SEVERITY() AS ErrorSeverity,
β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆERROR_STATE() AS ErrorState,
β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆERROR_PROCEDURE() AS ErrorProcedure,
β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆERROR_LINE() AS ErrorLine,
β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆERROR_MESSAGE() AS ErrorMessage
β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆEND CATCH'β€ˆβ€ˆβ€ˆβ€ˆ


β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆ--print(@sql)
β€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆβ€ˆexec(@sql)



set @i =@i+1
end
drop table #d

Rate

β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜…

3.5 (2)

Share

Share

Rate

β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜…

3.5 (2)