Simple script to backup all SQL Server databases
\nBy: Greg Robidoux | Read Comments (168) | Related Tips: 1 | 2 | 3 | 4 | More > Backup
\nProblem
\nSometimes things that seem complicated are much easier then you think and this is the power of using T-SQL to take care of repetitive tasks. One of these tasks may be the need to backup all databases on your server. This is not a big deal if you have a handful of databases, but I have seen several servers where there are 100+ databases on the same instance of SQL Server. You could use SQL Server Management Studio to backup the databases or even use Maintenance Plans, but using T-SQL is a much simpler and faster approach.<\/p>\n
Solution
\nWith the use of T-SQL you can generate your backup commands and with the use of cursors you can cursor through all of your databases to back them up one by one. This is a very straight forward process and you only need a handful of commands to do this.<\/p>\n
Here is the script that will allow you to backup each database within your instance of SQL Server. You will need to change the @path to the appropriate backup directory.<\/p>\n
File Naming Format DBname_YYYYDDMM.BAK
\nDECLARE @name VARCHAR(50) — database name
\nDECLARE @path VARCHAR(256) — path for backup files
\nDECLARE @fileName VARCHAR(256) — filename for backup
\nDECLARE @fileDate VARCHAR(20) — used for file name
\n— specify database backup directory
\nSET @path = ‘C:\\Backup\\’
\n— specify filename format
\nSELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
\nDECLARE db_cursor CURSOR FOR
\nSELECT name
\nFROM master.dbo.sysdatabases
\nWHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’) — exclude these databases
\nOPEN db_cursor
\nFETCH NEXT FROM db_cursor INTO @name
\nWHILE @@FETCH_STATUS = 0
\nBEGIN
\nSET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
\nBACKUP DATABASE @name TO DISK = @fileName
\nFETCH NEXT FROM db_cursor INTO @name
\nEND
\nCLOSE db_cursor
\nDEALLOCATE db_cursor
\nFile Naming Format DBname_YYYYDDMM_HHMMSS.BAK
\nIf you want to also include the time in the filename you can replace this line in the above script:<\/p>\n
— specify filename format
\nSELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
\nwith this line:<\/p>\n
— specify filename format
\nSELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),’:’,”)
\nNotes
\nIn this script we are bypassing the system databases, but these could easily be included as well. You could also change this into a stored procedure and pass in a database name or if left NULL it backups all databases. Any way you choose to use it, this script gives you the starting point to simply backup all of your databases.<\/p>\n
Also, if you wanted to bypass some of your user databases you can include them in the NOT IN section as well.<\/p>\n
Next Steps
\nAdd this script to your toolbox
\nModify this script and make it a stored procedure to include one or many parameters
\nEnhance the script to use additional BACKUP options
\nLast Update: 10\/15\/2012<\/p>\n","protected":false},"excerpt":{"rendered":"
Simple script to backup all SQL Server databases By: Greg Robidoux | Read Comments (168) | Related Tips: 1 | 2 | 3 | 4 | More > Backup Problem Sometimes things that seem complicated are much easier then you … Read More »<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1738","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1738","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1738"}],"version-history":[{"count":1,"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1738\/revisions"}],"predecessor-version":[{"id":1742,"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1738\/revisions\/1742"}],"wp:attachment":[{"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1738"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1738"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1738"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}