Backing up a database - In depth guide
Note: the instructions below cover the same process as in the quick guide above but provide more information about the various options being selected.
Launch SQL Server Management Studio from the Start menu Select the server and instance that you are using to hold your database. For example, if you are using a local SQL Server with an instance name of “CCHSQL”, this would be select as follows:
If you have appropriate security rights, you can log in using Windows Authentication, otherwise select SQL Server Authentication from the dropdown and login with the “sa” user account that you created when the server instance was installed.
The contents of the server will be displayed in the panel on the left:
Expand Databases by clicking on the + icon and select the database to be backed up. In the following case, the database called Checklist has been selected for backup.
Right-click on the database, point to Tasks, and then click Back Up. The Back-Up Database dialog box appears.
In the Database list box, verify the database name. You can optionally select a different database from the list.
You can perform a database backup for any recovery model (FULL, BULK_LOGGED, or SIMPLE), however, for simplicity, we recommend that you always carry out a full back up as the data files will not be significantly large and, if required, you can later restore the database from a single backup file.
In the Backup type list box ensure that Full is selected. For Backup component, check that Database is selected. Either accept the default backup set name suggested, recommended, in the Name text box, or enter a different name for the backup set.
Optionally, in the Description text box, enter a description of the backup set. This can be whatever is appropriate to your backup process, e.g. Month-end backup.
Leave the Backup will expire setting After and the number of days as 0 which indicates to the system that the backup will never expire.
Make sure that the type of backup destination is set to Disk.
The system will offer a default name and destination for the backup file; however, we suggest that you remove this and add your own as follows. This will make it much easier for you to manage your backup files and find them should you need to restore the data at a later date.
To remove the offered backup destination, select it and click Remove. Select the destination for the backup and provide a new file name as follows:
1. Click Add and the Select Backup Destination dialog will appear
2. Click on the ellipsis button by the file name to display the Locate Database Files dialog
Select a suitable folder, e.g. C:\Backups\CCH Checklists, and enter a name to be allocated to the backup file, e.g. ChecklistBackup_2013Dec31.bak and click on OK.
Note: 1. You cannot create folders from this dialog and hence the folder must already exist. 2. Ensure that you enter the file extension of .bak as this will not be automatically added and it will make the file easier to find if you need to restore it.
The path and file name will be presented in the Select Backup Destination dialog, check that it is correct and click on OK to return to the Back-Up Database.
Click on Options in the panel on the left.
Select Overwrite all existing backup sets under the Overwrite Media option.
This will ensure that if you have selected an existing file to back up to, its existing content will be replaced.
Under Reliability, select Verify backup when finished. This will verify that the resulting file contains a valid backup following the completion of the process.
Note: it does not verify that the resulting file can actually be restored. You will need to carry out a test restore to ensure this. We recommend that you carry out test restores on a random basis during the year to ensure your backups are restorable.
Click on OK to perform the backup.
The system will back up the data and display a message confirming that the backup has been completed.