You probably already have a process in place to back up SQL Server databases on some set schedule, but what do you do when you need to recover a database?
Database Maintenance Plans make it easy to automate the backup process, but there is no equivalent for restore. To restore a database, you can either use Enterprise Manager or T-SQL commands. I will explore the Enterprise Manager command in this tip and T-SQL in a future tip.
Restoring a database on the same server
In addition to being able to back up a database using a maintenance plan, you can back up and restore a database using Enterprise Manager.
In Enterprise Manager, right click on the database and select All Tasks. You will have options to Backup and Restore a database. The backup option was discussed in Selecting a SQL Server backup model
and Maintenance Plan backups were covered in SQL Server backups made easy using Maintenance Plans.
If you select Restore, the following "Restore database" window pops up, which I'll explain below.
Enterprise Manager: Restore database General tab
Restore as database:
This drop-down menu option allows you to select an existing database, or, if you want to restore to a new database, type in a new name. For a new database, the database will be created and then the contents of the backup will be restored. For an existing database, the contents of the backup will be restored over the existing database.
- Database: This option allows you to restore the complete database based on information in the system backup tables.
- Filegroup or files: This will allow you to restore a filegroup or file from the backups. If you only have one filegroup and/or file, this option will work the same exact way as the database option. If you backed up your filegroups using different backups, you will have the ability to restore an individual file or filegroup.
- From device: This is probably what you will use most, especially if you are restoring a database from another server. It allows you to select the physical backup files rather than have SQL Server display a list for you.
- Show backups of database: This option gives you a list of backups that exist on the server for restore. The backup information is pulled from the following system tables in the msdb (not from the files that exist on the file system).
- backupfile - Contains one row for each data or log file backed up.
- Backupmediafamily - Contains one row for each media family.
- Backupmediaset - Contains one row for each backup media set.
- Backupset - Contains a row for each backup set.
- First backup to restore: This will show you a list of all full backups that exist in the backup tables mentioned above to help further define the scope of backup files you want to work with. After both of these options are selected, a list of full, differential and transaction log backups that were run on the server will display.
Note: Since SQL Server tracks which backups have occurred, either of the above options can also show you which backups are available for restore. As long as the backup files have not been moved to another server or folder, these options will work just fine.
- Point in time restore: If you are doing transaction log restores, this will allow you to recover the database to a particular point in time. This option is only available if you are restoring a transaction log file. In order to do transaction log backups, your database must also be in the Full or Bulk_Logged recovery model. This option is helpful if you know when a disruptive action took place on your server and you want to restore to a point in time right before that action occurred.
Note: Even though the backups display in the list, it does not mean the backup files exist on your server. Since the list is pulled from the system tables, it has no idea if the files are still on the file system or not. If the show backups of database and first backup to restore options are used, SQL Server is expecting these files are still in the same place where they were created when the backup occurred. If these files were later moved off the server or deleted, these options will still work but the files won't exist for the restore to happen and you will get an error. In most cases, you will want to restore the most recent backups; so as long as you do not delete or move the backup files, this option will work when restoring on the same server.
Restoring a database on another server
If you are not restoring on the same server, you can select the "From Device" option and find the files you want to restore. The screen will look like this.
Enterprise Manager: Restore database General tab
- Select Devices: This button allows you to select the physical files you want to restore.
Tip: You can select multiple files to restore, but in my testing, I received an error stating that the files were not in the same media set, even though I was using the same exact backup files for both tests. To get around this problem, I had to restore each file individually and use the settings on the "Options" tab.
- Restore backup set: You must select the corresponding option to the type of restore you are doing. So, if you are restoring a transaction log backup, you need to select Transaction Log.
- Read backup set information and add to backup history: This option just reads the header information from the backup files and adds to the system tables in the msdb database.
Additional restore options
For additional restore options, click the Options tab and this window appears:
Enterprise Manager: Restore database Options tab
Eject tape (if any) after restoring each backup: This is used if you are restoring from tape. Selecting this even though you are not using tapes won't cause any problems.
Prompt before restoring each backup: A prompt window will pop up to let you know the restore has finished and asks if you want to restore the next file.
Tip: If you select cancel, it will leave the database in a "Loading" state, which means the restore process was not completed. To fix this, you can just rerun the restore process.
Force restore over existing database: If you are restoring backups and you want to overwrite an existing database with a different name, you must use this option. If you are restoring backups to the same database, this option is not required.
Restore database file as: This displays the name and location of the physical files used when you restore your database. The location and names of the files are stored in the backup file, so if you are restoring to a different server or to a different database name, you must change these options for both the data file and the transaction log file.
Recovery completion state:
- Leave database operational: This is the default option. After you restore your backups, the database is in a useable state and you cannot restore any additional backups.
- Leave database nonoperational, but able to restore additional transaction logs: This option allows you to restore a differential backup or additional transaction log backups. It will leave the database in a "Loading" state until the last restore is issued with the RECOVERY option or the first option above.
- Leave database read-only and able to restore additional transaction logs: This option allows you to use the database in a read-only mode, but also allows you to restore additional transaction logs. This could be used for a reporting environment where you get transaction log backups from your production server and restore them to a different server on a set basis for read-only purposes.
- Undo file: The undo file is used to undo uncommitted transactions when the database is brought fully online. If the undo file does not exist, it will be automatically created.
Restore in review
In the example above where I used physical files, SQL Server kept giving me an error when I tried to restore multiple backup files. To get around this, I selected one file at a time and also selected "Leave database operational but able to restore additional transaction logs" on the Options tab. For the very last transaction log restore, I selected "Leave database operational" instead, which left my database completely restored and useable. This part of Enterprise Manager makes it somewhat easy to restore backups, but as you can see you may run into some problems. In a future tip, we will look at the equivalent T-SQL commands that have all of the same functionality as mentioned above. Using scripts will allow you to create a repeatable process as well as automate the restore process. About the author:Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Robidoux, who also serves as the SearchSQLServer.com Backup and Recovery expert, welcomes your questions.
More information from SearchSQLServer.com