How to create a simple database backup using SSMS

By:   |   Comments (7)   |   Related: > Backup


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.

click on the database

Step 2

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

Choose Tasks then the Back Up option

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.

Next click on "Add..." and browse out to a path you know has room for your 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.

Enter the file name.

Once ready click OK.

Step 5

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

the options and 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 on ok to do the backup

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

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

If we browse to that location we should see the backup

Well done on a successful backup using the SSMS GUI.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Graham Okely Graham Okely is a SQL Server DBA and has been working with database systems since 1984 and has been specializing in SQL Server since 2007.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, August 12, 2021 - 12:46:16 AM - Thomas Back To Top (89116)
Thanks for the simple quick guide to follow, I had to urgently perform a backup for one of the databases on our SQL server though I've never done this before.

Cheers

Monday, December 18, 2017 - 9:10:30 PM - Graham Okely Back To Top (74228)

William

You will need to build your own backup and restore testing strategy. Take time to think through what backup options are available to you. Read https://ola.hallengren.com/ and see how that solution might help. What ever you put in place make sure you do regular restores to test your backups.

I can't comment on how you manage media and erasing backups.

Regards

Graham Okely

Monday, December 18, 2017 - 10:00:04 AM - WILLIAM BLAIR Back To Top (74214)

Thanks.  I was becoming confused because I was testing backups and they were different sizes. 
I realized it (I think I realized) had something to do with media sets etc.. and was worried that my backups would not be "complete".

If not doing "Copy-only" option, do I also need to specify a new media set and erase all existing backup sets?

 

Friday, December 8, 2017 - 7:12:13 PM - Graham Okely Back To Top (73794)

 

Hi William

 

Your expression "FULL SIMPLE" is not a usual expression. 

To clarify:

A database has a recovery model of FULL, SIMPLE or Bulk-Logged

A backup is either a full backup, a transaction log backup or a differential backup.

When DBAs use the word "FULL" and mean the database is in FULL recovery model.

When they say a "full backup" they mean the whole database was backed up.

So I think your question "What do I need to do to create a FULL SIMPLE backup...."

Is actually "What do I need to do to create a full backup of a database in SIMPLE recovery model."

Greg is correct in his reply. See my step 5. It has the COPY ONLY check box ticked. That makes it independant of backup chains. etc.

 

On that same screen shot the backup type of "Full" means the whole database.

All the best

Graham

Friday, December 8, 2017 - 4:16:17 PM - Greg Robidoux Back To Top (73791)

Hi William, here is what you can do:

If you do not use Differential Backups then you can just create a Full Backup.  You could also do this using COPY ONLY option, but it won't matter.  Just follow what Graham wrote above.  If you are doing Differential Backups, then you would want to use the COPY ONLY option when creating a backup. Again, you can follow what Graham did above.  If you provide a brand new file name for the backup file, this will just contain the contents for this database backup.

You can also create a full backup of the database using this backup command. This will write to a new file and include all of the database contents.

BACKUP DATABASE [enterDBname] TO DISK = 'enterPath'

Here is an example for database called Test that will write to a file named Test.bak.

BACKUP DATABASE Test TO DISK = 'C:\Test.bak'

-Greg

 

Friday, December 8, 2017 - 3:25:16 PM - WILLIAM BLAIR Back To Top (73789)

Graham:

I'm slightly confused about something.  What do I need to do in order to create a FULL SIMPLE backup that is independent of chains, media sets etc.. basically a a backup where I know it's getting everything, just once.  I think I know, but I'm just not that confident with SQL yet.

Any help is appreciated. 

 

Friday, August 9, 2013 - 1:02:36 AM - Brijesh Pal Back To Top (26170)

Nice tutorial with Screen Shots which helps more to undrestand.

 

 

 














get free sql tips
agree to terms