Simple script to backup all SQL Server databases

Problem

Sometimes 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.

Solution

With 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. 

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.

File Naming Format DBname_YYYYDDMM.BAK

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name

 
-- specify database backup directory
SET @path = 'C:\Backup\'  

 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

 
DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases

 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

 
WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

 
       FETCH NEXT FROM db_cursor INTO @name   
END   

 
CLOSE db_cursor   
DEALLOCATE db_cursor

File Naming Format DBname_YYYYDDMM_HHMMSS.BAK

If you want to also include the time in the filename you can replace this line in the above script:

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

with this line:

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

Notes

In 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.

Also, if you wanted to bypass some of your user databases you can include them in the NOT IN section as well.

Next Steps

• Add this script to your toolbox
• Modify this script and make it a stored procedure to include one or many parameters
• Enhance the script to use additional BACKUP options

ref: http://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20140117

By: Greg Robidoux  

How to create a simple database backup using SQL Server Management Studio (SSMS)

By: Graham Okely

Problem

You are brand new to SQL Server and you need to create a SQL Server database backup, but you have no idea what to click on.  In this tip we walk through the steps to create a simple backup using SQL Server Management Studio.

Solution

This tip guides you through the steps to make a simple one-off backup.

Step 1

Open SSMS and expand the Database tree as shown below and right mouse click on the database you wish to backup. Then move your mouse carefully over Tasks and then click on Back Up... as shown below.

Step 2

At this point pause and look at the options before you click.

When we see so many options, and there are more under the options tab, it is hard to know what to do so let's look at only the essential things. I have circled the essentials in red.

(If you wish to know more about the options page look at this page at Back Up Database (Options Page) at Microsoft. You may wish to visit this MSSQLTips backup tutorial as well.)

• First the source database is listed, confirm it is the database you wish to backup.
• Next the backup type is Full. This option will give us a full backup. There are other types, but let's leave it as Full.
• Notice the "Copy-only Backup" check box. Check it if you wish to use this option. You may be thinking, a backup is a copy of the database. Yes that is true however the copy it is referring to relates to a "chain" of backups. This is important if you do not wish to break the backup chain. My tip is to check that check-box. See tip 1772 by Atif Shehzad or Copy-Only Backups at Microsoft for more information.
• Next leave the "Database" as the component we wish to backup.  This will backup the entire database.
• Next unless you know the path and name displayed are what you want, click "Remove" to remove the default backup path and name. Note this does not remove that file if it exists it simply removes that path and file name from this dialog box.

Step 3

Next click on "Add..." and browse to a path you know has room for your backup. It is possible to enter a URL at this point. (\\Server\Drive\Path\File_Name)

Note that if at all possible you should place your backup into the usual location for backups. This means it is easy to find all your backups should you need to restore it and any automated clean up process will clean out your one-off backup.

Step 4

Enter the file name. I suggest the format "Database_Name_backup_YYYY_MM_DD.bak" as it is simple and follows the pattern of an automated SQL Server backup.

Once ready click OK.

Step 5

Here are the options, file path and name ready to backup.

It is ready to backup now. However at this point we could choose to click OK and back it up or click on the "Script" menu item. Clicking on Script will not run the backup, but produce a script for you.

(Note that tip 1070 by Greg Robidoux explains one TSQL method of performing backups.)

Let’s click on "OK" to do the backup. Notice the green circle icon indicates the backup percentage. Then it pops up a message once completed.

Click "OK" and the screen goes back to normal.

If we browse to that location we should see the backup.

Well done on a successful backup using the SSMS GUI.