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.
Microsoft SQL Server Express Edition only supports manual backups (as described below), whereas other editions support the automating of backups.
Manually Creating a Backup
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.