Manual Backups

Owned by Mike Sheen
Last updated: Jul 24, 2024 by Julie Hanlon
6 min read

Introduction

Database backups should be taken as frequently as possible and stored offsite. Please establish your backup policy. A good starting point would be nightly backups, with each backup kept for 7 days.  In addition, keep a yearly backup before you rollover each year.  Keep at least the last 5 years (one for each year).

Microsoft SQL Server Express Edition only supports manual backups (as described below), whereas other editions support the automating of backups.

Manually Creating a Backup (.bak)

Follow the steps below to create a backup of your database.


1. Start "Microsoft SQL Server Management Studio"



2. Connect to the SQL Server upon which the database you wish to backup resides.

The following information should be entered in the "Connect to Server" dialog:

  • Server type: Database Engine

  • Server name: <The name of your SQL Server>

  • Authentication: SQL Server Authentication

  • Login: sa

  • Password: <The password for the "sa" user>


3. On the top left hand side of the screen, expand the "Databases" folder.



4. Right click on the node of the database you wish to back up and choose "Tasks->Backup…" from the context menu.



5. The "Back Up Database" screen will appear. On the "General" page, choose "Disk" from the "Back up to:" dropdown. If there are already entries in the backup destination box, select each one and click the "Remove" button.



6. Click on the "Add" button to add a new destination for the backup. In the "Select Backup Destination" dialog, enter the full path and filename for your backup file. We recommend using the filename format of DatabaseNameYYYYMMDD.bak where YYYY is the current year, MM is the current month, and DD is the current day.

Click the OK button to close the dialog.


7. Back on the "Backup Database" screen, go to the "Media Options" page.

Ensure that the "Verify backup when finished" option is ticked. This checks the integrity of the disk file after the backup is complete.


8. Click the "OK" button to perform the backup. You will be notified upon successful completion.



9. SQL backups are compressed by default, except when using SQL Server Express Edition. If your SQL Server is the Express Edition, then you should manually compress the file to reduce transfer times when transporting the file to offline storage.

a. Open windows explorer and locate the .bak file you created using the above steps (the default backup folder for Microsoft SQL Server is “C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Backup” for 32-bit installations and is “C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Backup” for 64-bit installations.

b. Right click on the .bak file and select “Send To->Compressed (zipped) folder” from the context menu.

c. Insert a removable USB stick into the USB drive.

d. Copy the compressed zipped backup folder to the USB drive and take the USB stick offsite. Alternate this USB stick with another so you always have an off-site backup.

Manually Creating a BACPAC Backup (.bacpac)

 

Before you start

NOTE:  A bacpac file is generated table by table so you can have no other users or services accessing the database at the time of the export, otherwise the integrity of the final file will be compromised.

We highly recommend creating a COPY of the database first, then you can be sure there are no services nor users interacting with the database

Copy the database

  1. Copy the target database, which will return straight away, but the database will take some time to copy. This operation will create a full transactional copy:

CREATE DATABASE <name> AS COPY OF <original_name> --No need to use <> around the names

  1. Find the status of your copy operation:

SELECT * FROM sys.dm_database_copies --Run this against the master database

 

Create a BACPAC with the Jiwa Database wizard

Launch the Jiwa app and at the log on screen, click the Databases... button.

 

Click Next at the Welcome prompt

Select Export a Jiwa database, then click Next

 

Enter the SQL Server details.  If your SQL credentials are correct you will be able to choose the database you are backing up from the drop-down list.  You can't type in a database name.

Use the Browse button to edit the backup filename and location for your backup.

Review your selections by expanding the + Database Wizard Summary

Click Finish to start your backup.

Monitor the progress of your backup in the Progress screen.

Your backup is complete when you see the Succeeded status

Click Close to close the wizard

Use File Explorer to navigate to your backup.

We recommend zipping the bacpac file and uploading it to a secure cloud storage, or copying to a USB and store offsite.

When you’re done

Delete the COPY database

Create a BACPAC with Microsoft SQL Server Management Studio

A BACPAC file is a ZIP file with an extension of BACPAC containing the metadata and data from a SQL Server database. A BACPAC file is exported from a SQL Database and can be later imported back into a SQL Database.  Backpac's are essential for backing up cloud / Azure hosted databases.  They also have the advantage of being smaller in size than a .bak and so are easier to upload or transport.

Before you start

NOTE:  A bacpac file is generated table by table so you can have no other users or services accessing the database at the time of the export, otherwise the integrity of the final file will be compromised.

We highly recommend creating a COPY of the database first, then you can be sure there are no services nor users interacting with the database

 

Follow the steps below to create a bacpac of your database.

1. Start "Microsoft SQL Server Management Studio", as in step 1 above

2. Connect to the SQL Server on which your database resides, as in step 2 above

3. On the top left hand side of the screen, expand the "Databases" folder, as in step 3 above

4. Right click on the node of the database you wish to back up and choose "Tasks→Export Data-Tier Application…" from the context menu.

 

5. A wizard will open to assist you.  Click Next on the Introduction page

6. Click Browse to enter the name of your bacpac file and the location you are going to save it to

7. Click Next to view the Summary page of the Wizard

8. Click Finish to start the export.

9. Wait.

10. You can follow the progress of the export on the Progress page.

11. When the export is finished the checkbox will show 'Operation Complete'.  Scroll down to check every table exported with Success.

12. Click Close and you are done.  Use File Explorer to navigate to your bacpac file.

 

Create a BACPAC from Azure SQL Database

See Microsoft’s guidance on exporting to BACPAC from Azure SQL Database in Azure.

https://learn.microsoft.com/en-us/azure/azure-sql/database/database-export?view=azuresql