Tuesday, September 24, 2019

Backup All Databases, SQL Server

Using the below script we can take all databases backup with one command!!

You can check my other post BACKUP STATUS to know the current DB backup status.

exec sp_msforeachdb '
use [?];
IF ''?'' not in ( ''master'',''tempdb'',''model'',''msdb'',''DBAdmin'')
BEGIN
       use [master]
       Print ''?''
       ALTER DATABASE [?] SET READ_ONLY WITH ROLLBACK IMMEDIATE
       Print ''Marked -- READ_ONLY''
       BACKUP DATABASE [?]
       to disk = ''<Disk Location/Path>\?_Backup_FULL_20190924.bak''
       with COMPRESSION
       Print ''Backup Done''
       ALTER DATABASE [?] SET MULTI_USER WITH NO_WAIT
END
ELSE
BEGIN
       Print DB_NAME()+ '' -- IGNORED''
END'

Thanks.

1 comment: