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.
Next Steps
- Where is my backup located? Is a record kept of your backup inside the SQL Server databases? See my previous backup tip 2960.
- See this MSSQLTips backup tutorial
- What size is my database? sp_helpdb
- See this MSSQLTips tutorial on SQL Server Recovery Models
