Summary: No doubt that the backup and restore the database is the main task of any SQL Server DBA. We will discuss here the various ways to restore the database in SQL Server 2008 R2.
If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database.
(for the FROM DATABASE_SNAPSHOT option, the database always exists).
RESTORE permissions are given to roles in which membership information is always readily available to the server. Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.
We can use the following steps to restore the database:
Here, in the first section (Destination for restore)
Then, in the second section (Source for restore)
you can select one of the following options:
It allows you to write over an existing database when doing a restore without first backing up the tail of the transaction log. The WITH REPLACE basically tells SQL Server to just throw out any active contents in the transaction log and move forward with the restore.
In some cases when you try to do a restore without replace option you may get an error that says
" Msg 3159, Level 16, State 1, Line 1 The tail of the log for the database "AdventureWorks" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. "
It preserves the replication settings when restoring a published database to a server other than the server where the database was created. This option is relevant only if the database was replicated when the backup was created.
This option is available only with the Leave the database ready for use by rolling back the uncommitted transactions option (described later in this table), which is equivalent to restoring a backup with the RECOVERY option.
That specifies that after each backup is restored, the Continue with Restore dialog box will be displayed to inquire whether you want to continue the restore sequence. This dialog box displays the name of the next media set (if known) and the name and description of the next backup set.
This option allows you to pause a restore sequence after restoring any of the backups. This option is particularly useful when you must swap tapes for different media sets; for example, when your server has only one tape device. When you are ready to proceed, click OK.
It makes the restored database available only to the members of db_owner, dbcreator, or sysadmin.
You must select one of the following options to determine the state of the database after the store operation:
It recovers the database after restoring the final backup checked in the Backup sets to restoregrid on the General page.
This leaves the database in the restoring state. This allows you to restore additional backups in the current recovery path. To recover the database, you will have to perform a restore operation by using the RESTORE WITH RECOVERY option in the last backup to be restored.
If you select this option, the Preserve replication settings option is unavailable.
This leaves the database in a standby state, in which the database is available for limited read-only access.
Choosing this option requires that you specify a standby file in the Standby file text box. The standby file allows the recovery effects to be undone.
So for just one restore for the database, we can just select Overwrite the existing database (WITH REPLACE) option and Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY) option.
All of the above steps can be done in the happy scenario, But you may have a corrupted backup file. In that case, you do not have any option to restore this backup then, you can use Stellar Toolkit for MS SQL.
You would like to read on what SQL MVP Damian Widera says about the tool and his critical Review regarding it.
Stellar Toolkit for MS SQL is a combination of three tools developed to repair corrupt SQL server database, extract database from corrupt SQL backup file and to reset SQL Server user and Admin Password.
Conclusion
When we say back & restore the database in SQL Server 2008 R2, which is one of the mandatory tasks for each DBA, we have discussed here some of the options were existed in SQL server 2008 restore. I hope this article has been informative for you.
I am Microsoft® Certified Solutions Expert: Data Management Analytics plus Microsoft® Certified Solutions Expert Data Platform (MCSE). As a DBA, I design, install, maintain and upgrade all databases (production and non-production environments), I have practical knowledge of T-SQL performance, HW performance issues, SQL Server replication, clustering solutions, and database designs for different kinds of systems. I worked on all SQL Server versions (2008, 2008R2, 2012, 2014 and 2016). I am Microsoft® Certified Solutions Expert: Data Management Analytics plus Microsoft® Certified Solutions Expert Data Platform (MCSE).
Few days ago, I took backup of SQL Server Database (.bak file). When I try to access my backup file, it shows backup file corrupt error. I am using SQL Server 2008. How can I get rid of this situation?