How to create a simple database backup using SQL Server Management Studio (SSMS)
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.(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.Step 5
Here are the options, file path and name ready to backup.(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.
If we browse to that location we should see the backup.
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