Bulk Backup All SQL databases

This is a simple SQL-T script to backup every database you have on an SQL-Server Engine.

USE master;
GO
DECLARE @dbName NVARCHAR(MAX);
DECLARE curDBs CURSOR FAST_FORWARD READ_ONLY FOR SELECT [s].[name] FROM [sys].[sysdatabases] AS s WHERE [s].[dbid] > 4;
OPEN curDBs
FETCH NEXT FROM curDBs INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
	DECLARE @cmd NVARCHAR(MAX);
	SET @cmd = N'BACKUP DATABASE ['+ @dbName +'] TO DISK = N''D:\xData\_backups\'+ @dbName +'.bak'' WITH NOFORMAT, NOINIT, NAME = ''' + @dbName + N'-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10';
	PRINT @cmd;
	EXEC [sys].[sp_executesql] @cmd;

	FETCH NEXT FROM curDBs INTO @dbName
END
CLOSE curDBs
DEALLOCATE curDBs

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>