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.
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)
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.
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. If this is not possible, for a live database for example, follow the steps above to create a .bak backup. Restore that on the same server with a new name. Then create the bacpac.
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.