Backup all SQL instance databases

I had a situation where I needed to backup hundreds of databases to a USB drive. In this circumstance the GUI was out of the question. After some searching online I found this script:

[sourcecode language="sql"]<br />DECLARE @name VARCHAR(50) -- database name<br />DECLARE @path VARCHAR(256) -- path for backup files<br />DECLARE @fileName VARCHAR(256) -- filename for backup<br />DECLARE @fileDate VARCHAR(20) -- used for file name<br /><br />SET @path = 'D:\SQL\Backup\'<br /><br />SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)<br /><br />DECLARE db_cursor CURSOR FOR<br />SELECT name<br />FROM master.dbo.sysdatabases<br />WHERE name NOT IN ('master','model','msdb','tempdb')<br /><br />OPEN db_cursor<br />FETCH NEXT FROM db_cursor INTO @name<br /><br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br />SET @fileName = @path + @name + '_' + @fileDate + '.BAK'<br />BACKUP DATABASE @name TO DISK = @fileName<br /><br />FETCH NEXT FROM db_cursor INTO @name<br />END<br /><br />CLOSE db_cursor<br />DEALLOCATE db_cursor<br /><br />[/sourcecode]

Note:

— This scripts backs up all databases without having to explicitly state them
— Also names them according to database name with the date appended to the end e.g ‘DB_SYSADS_20121210.BAK’
— Change the @path parameter to where you want the backups to be dumped to
— The databases stated after the WHERE command will not be backed up. In the example above the system databases are omitted. You can add other databases within the parentheses if you want them omitted, e.g
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’,’AdventureWorks’,’NorthWind’)

 
comments powered by Disqus