Restore Database in SQL Server - Steps to Restore a Full Database Backup Up

Introduction

In this article we will take a look at the steps you need to follow to restore a SQL Server Database from a Full Database Backup. The steps mentioned in this article are applicable for SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012.

Permissions Required to Restore Database Backup in SQL Server

In order to restore database a user must be a member of DBCREATOR Server Role and DB_OWNER Database Role else you will receive the below mentioned error message while trying to restore a database in SQL Server.

Error Message 

CREATE DATABASE permission denied in database 'master'

RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Error: 262)

Sample T-SQL Script to create login with DBCREATOR Server Role and DB_OWNER Database Role

USE [master]
GO

DROP USER [BackupRestoreAdmin]
GO 

DROP LOGIN [BackupRestoreAdmin]
GO

CREATE LOGIN BackupRestoreAdmin WITH PASSWORD='$tr0ngP@$$w0rd'
GO
CREATE USER BackupRestoreAdmin FOR LOGIN BackupRestoreAdmin
GO
EXEC sp_addsrvrolemember 'BackupRestoreAdmin', 'dbcreator'
GO
EXEC sp_addrolemember 'db_owner','BackupRestoreAdmin'
GO

A DBA or a user who is a member of DBCREATOR Server Role and DB_OWNER Database Role will be able to restore a SQL Server database from databases full backup using either :-

• Restore database using SSMS
• Restore database using TSQL scripts

Restore a full database backup using SSMS

Open SSMS and connect ot the appropriate instance of MSSQL Server Database Engine in Object Explorer.

Right click Databases node and then select Restore Database.... option from the drop down list as shown in the below snippet to open up Restore Database dialog box.

In General Page of Restore Database Dialog box, select or type the name of a new or existing database for your restore operation. In Source for restore specify the source and location of backup sets to restore. Choose From Device radio button and then click the "..." button to specify backup file location.

In Specify Backup dialog box choose File as Backup Media and then click the Add button to choose the location of database backup file from which you want to restore the database as shown in the below snippet. Click OK to return to Restore Database dialog box.

In Restore Database Dailog box select the checkbox under the Restore as shown in the below snippet and then select Option Page from the left pane.

In Options Page of Restore Database dialog box select the checkbox next to Overwrite the existing database (WITH REPLACE) and choose the radio button next to Leave the database ready to use by rolling back uncommited transactions. Additional transactional logs cannot be restored.(RESTORE WITH RECOVERY) as shown in the below snippet. Finally click OK to start restoring the SQL Server Database.

To Generate TSQL Script for the database restore click Scripts and choose Script Action to your choice from the different options which are available as shown in the snippet below

Once the database is successfully restored you will get a popup message similar to the one shown in the below snippet.

How to restore a Full Database Backup using TSQL Scripts

The below script can be used to restore AdventureWorks database.

RESTORE DATABASE [AdventureWorks] 

FROM DISK = N'C:\DBBackups\AdventureWorks.BAK' 

WITH FILE = 1, 

MOVE N'AdventureWorks_Data' TO N'D:\MSSQL\MSSQL10_50.SQL2008R2\MSSQL\DATA\AdventureWorks.mdf', 

MOVE N'AdventureWorks_Log' TO N'D:\MSSQL\MSSQL10_50.SQL2008R2\MSSQL\DATA\AdventureWorks.ldf', NOUNLOAD, REPLACE, STATS = 10 

GO

Conclusion

In this article you have seen the steps to restore a database from its full backups.

Create maintenance backup plan in SQL Server 2008 R2 using the wizard

Launch the SSMS (SQL Server Management Studio)

Once you are logged in got to the 'Maintenance Plans' folder under 'Management'. Right click on 'Maintenance Plans' and choose 'Maintenance Plan Wizard'.



Click Next



Select 'Separate schedules for each task' radio button and click next.



Select backup Database Full, Differential and Transactional Log check boxes and click next.



Click Next

Full Backup Settings

This is where you set-up the backup settings for the full backup task.

• Select the database you want to backed up (All user database).
• Choose when you want the backups to expire. In case I choose (14 days - optional). This will overwrite the oldest backup file for rotation.
• Choose your backup media (disk).
• Choose a location for your backups. (Default or to you liking).
• Verify the integrity of the backup.
• Click the configure button at the bottom right to configure the scheduling options for this task.

• Choose the schedule type (Recurring).
• Choose the frequency of the backup. In this example full backups are running on Monday, Wednesday and Friday (Alter this to fit your backup plan).
• Adjust the Daily frequency (what time does your backup need to run)?
• Adjust the start and end date. In this example I left it at 'No End date'.

Click Ok when you are finished. Go back to the maintenance plan wizard and click next.

Differential Backup Settings

Next we will configure the differential backup task.

• Select the databases you want backed up (All users databases).
• Choose when you want the backups to expire. In this case I choose (14 days). This will overwrite the oldest backup file for rotation.
• Choose your backup media (disk).
• Choose a location for your backups (Default or to your liking).
• Verify the integrity of the backup.
• Click the configure button at the bottom right to configure the scheduling options for this task.

• Choose the schedule type (Recurring).
• Choose the frequency of the backup. In this example differential backups are running on Tuesday, Thursday, Saturday and Sunday (Alter this to fit your backup plan).
• Adjust the daily frequency (What times does your backup need to run)?
• Adjust the start and end date. In this example I left it at "No End Date".

Click OK when you are finished. Go back to the maintenance plan wizard and click next to configure the transaction log backup.

Transaction Log Backup Settings

Next, set-up the transaction log backup.

• Choose your database (All user databases).
• Do not check 'backup set will expire'.(We will setup a cleanup task next).
• Choose your backup media (disk).
• Choose your location for your backups. (Default or to your liking). ****Copy and paste the path to notepad. You will need to reference this in later steps.****
• Verify the integrity of the backup.
• Click the configure button at the bottom right to configure the scheduling options for this task.

The transaction log backup intervals will need to be determined based on transaction logs growth. Therefore it may be necessary to run the transaction log backups in shorter intervals to prevent transactions logs from growing.

• Choose the schedule type (Recurring).
• Choose the frequency of the backup. In this example the transaction log backups are running daily.
• Adjust the daily frequency (I set mine to run every hour).
• Adjust the start and end date. In this example I left it at 'No end date'.

Click Ok when you are finished. Go back to the maintenance plan wizard and click next to finish the wizard.

Now that you are back to the SSMS main screen you need to refresh the maintenance plan. Press F5 to refresh the new settings. You should see the new maintenance plan listed.

Transaction Log Cleanup Task

Next, select the 'Sub-plan_2' in the maintenance plan. Drag over 'Maintenance Cleanup Task' onto the white space and place it under the transaction log backup.

Right click on the 'Maintenance Cleanup Task' and choose edit.

This section allows you to setup the maintenance cleanup task. This task will clean up the transaction logs after three days. This will keep the one hour transaction logs for three days until the maintenance clean up task deletes the old data. The transaction log clean up will need to include a series of 3 days, this will ensure if you need to revert back to the second differential you can apply the transaction logs from that period. The goal is to have enough transaction logs between the full and differential backups.

• Choose 'Backup files'.
• Choose 'Search folder and delete files based on extension'.
• **Paste in the copied path that you copied earlier**. Make sure you include the same path that your transaction logs are backing up to. And enter the File extension type (.trn).Also include the first-level sub-folders. Use trn and not .trn, it will confuse SQL.
• Set the file age to '3 days'.
• Click 'Ok' to return to the SSMS main screen.

Drag the green arrow from the differential backup task to the Maintenance cleanup task. Double click on the connected green line.

Configure the value to 'Completion'.This allows the task to become conditional. "If the differential backup did not run then do not run the transaction cleanup task.If the backup did run proceed with the cleanup task."

Click 'OK' to return to the SSMS main screen. You should now see that the green line has become blue.

You are now finished with you maintenance task setup. You can adjust your backup plan to your needs. Be sure to save your work. ****Save your work by going to File >> Then Save All****

Test your Setup

After you are done setting up your maintenance plan you will need to verify that it works. You can choose to wait a few days to see if the job completes or you can force the job to run.

Browse down to 'Jobs' under 'SQL Server Agent' and right click on the first maintenance plan and choose 'Start job at This Step'.

This will run the first section of the maintenance plan you created.

At this step if the job completes continue on to the next step of the maintenance plan and test run the setup. Do this for all sub plans you created in the maintenance plan. If all your steps run without error you are finished.

Errors

If your job fails when you are performing a test run you will need to view the history to see what failed. Right click on the failed sub plan and select 'View History'

This will bring up new window that shows the history

If your job failed you will see a red X next to the time you ran the job. Drag up the dialog box below to see the details of the error.

Troubleshoot the error and repeat the test-job to see if it cleared up. Once your maintenance plan is solid it is a good practice to check back in a few days to see if it is running the way you want. Check to see if the .bak files are being removed after the expiration. Check to see if the transaction logs are being clean up after 3 days. 

SQL SERVER 2008 & 2008 R2 - Create Script to Copy Database Schema and All The Objects - Data, Schema, Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects

Quite Often we get request regarding how to copy all the objects - including schema and data from any database and re-create it on another instance.

SQL Server 2008 & SQL Server 2008 R2 has script generator wizard which does it for us.









Pay attention to the option Types of data to script - select option "Schema and data"










After the script is generated, the next challenge often users face is how to execute this large script as SSMS does not open the file. One can use SQLCMD for the same as shown in the following image

As the file with data will be very large, use SQLCMD to execute the larger script which will create with schema & data.

Failover and Failback in LogShipping - SQL Server 2008 R2

Database State & Jobs on Primary & Secondary Server

Database state on primary server (bhamare3)

Database state on secondary server (bhamare3\named)

Log Shipping jobs on Primary & Secondary Server

Log Shipping jobs on primary server (bhamare3)

Log Shipping job on secondary server (bhamare3\Named)

Check the log shipping is in sync by executing the following query on secondary server (bhamare\named)

select secondary_database,last_copied_file,last_restored_file from msdb..log_shipping_monitor_secondary

you can generate the log shipping sync report on secondary server (bhamare\Named) by

Right click on Instance Name > Reports > Standard Reports > Transaction Log Shipping Status

Backup Logins

If you simply want to make a backup of your SQL Sever Logins for disaster recovery purposes, just follow these easy steps:

On the primary server (bhamare3) launch SSMS & connect to the instance of SQL Server

USE master

GO

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL

  DROP PROCEDURE sp_hexadecimal

GO

CREATE PROCEDURE sp_hexadecimal

    @binvalue varbinary(256),

    @hexvalue varchar (514) OUTPUT

AS

DECLARE @charvalue varchar (514)

DECLARE @i int

DECLARE @length int

DECLARE @hexstring char(16)

SELECT @charvalue = '0x'

SELECT @i = 1

SELECT @length = DATALENGTH (@binvalue)

SELECT @hexstring = '0123456789ABCDEF'

WHILE (@i <= @length)

BEGIN

  DECLARE @tempint int

  DECLARE @firstint int

  DECLARE @secondint int

  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

  SELECT @firstint = FLOOR(@tempint/16)

  SELECT @secondint = @tempint - (@firstint*16)

  SELECT @charvalue = @charvalue +

    SUBSTRING(@hexstring, @firstint+1, 1) +

    SUBSTRING(@hexstring, @secondint+1, 1)

  SELECT @i = @i + 1

END

SELECT @hexvalue = @charvalue

GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL

  DROP PROCEDURE sp_help_revlogin

GO

CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS

DECLARE @name sysname

DECLARE @type varchar (1)

DECLARE @hasaccess int

DECLARE @denylogin int

DECLARE @is_disabled int

DECLARE @PWD_varbinary  varbinary (256)

DECLARE @PWD_string  varchar (514)

DECLARE @SID_varbinary varbinary (85)

DECLARE @SID_string varchar (514)

DECLARE @tmpstr  varchar (1024)

DECLARE @is_policy_checked varchar (3)

DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)

  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name,l.hasaccess, l.denylogin FROM

sys.server_principals p LEFT JOIN sys.syslogins l

      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'

ELSE

  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name,l.hasaccess, l.denylogin FROM

sys.server_principals p LEFT JOIN sys.syslogins l

      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name =@login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type,@is_disabled, @defaultdb, @hasaccess, @denylogin

IF (@@fetch_status = -1)

BEGIN

  PRINT 'No login(s) found.'

  CLOSE login_curs

  DEALLOCATE login_curs

  RETURN -1

END

SET @tmpstr = '/* sp_help_revlogin script '

PRINT @tmpstr

SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' +@@SERVERNAME + ' */'

PRINT @tmpstr

PRINT ''

WHILE (@@fetch_status <> -1)

BEGIN

  IF (@@fetch_status <> -2)

  BEGIN

    PRINT ''

    SET @tmpstr = '-- Login: ' + @name

    PRINT @tmpstr

    IF (@type IN ( 'G', 'U'))

    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'

    END

    ELSE BEGIN -- SQL Server authentication

        -- obtain password and sid

            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash') AS varbinary (256) )

        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT

        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        -- obtain password policy state

        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON'WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name= @name

            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )

        BEGIN

          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked

        END

        IF ( @is_expiration_checked IS NOT NULL )

        BEGIN

          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' +@is_expiration_checked

        END

    END

    IF (@denylogin = 1)

    BEGIN -- login is denied access

      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME(@name )

    END

    ELSE IF (@hasaccess = 0)

    BEGIN -- login exists but does not have access

      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME(@name )

    END

    IF (@is_disabled = 1)

    BEGIN -- login is disabled

      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'

    END

    PRINT @tmpstr

  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type,@is_disabled, @defaultdb, @hasaccess, @denylogin

   END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

GO

Note This script creates two stored procedures in the master database. The two stored procedures are named the sp_hexadecimal stored procedure and the sp_help_revlogin stored procedure.

Run the following statement
EXEC sp_help_revlogin
The output script that is generated by the sp_help_revlogin stored procedure is the login script. This login script creates the logins that have the original security identifier (SID) and the original password.

Failover

Before disabling log shipping jobs please copy any uncopied backup files from the shared folder on primary server  to the shared destination folder on secondary server by using copy job.

Check all the transaction log has been successfully applied on the secondary server, if not please run the restore job to apply unapplied transaction log backups on secondary server.

Disable Log Shipping Jobs

Disable Log Shipping jobs on primary server (bhamare3)

Disable Log shipping jobs on secondary server (bhamare3\Named)

T-Log Backup 

• Kill the session open for the DB on which you have to take tail log backup.
• Manually perform a tail log backup of the transaction log on the primary database specifying leave database in restoring state. This log backup captures any log records that have not been backed up yet and takes the database in Restoring state. The following example creates a tail log backup of the "AdventureWorks2008" database on the primary server.
• We recommend that you use a distinct file naming convention to differentiate the manually created backup file from the backup files created by the log shipping backup job.

DB on primary server (bhamare3) will go in restoring state.

Restore T-Log Backup

Now restore the tlog backup on secondary server (bhamare3\Named)

The database on the secondary server (bhamare3\Named) will come in online state.

Check all the transactions are happening correctly.

Log Shipping Configuration

Now configure log shipping on secondary server (bhamare3\Named) DB.

Fallback

Run Log Shipping Jobs

Run the log shipping jobs on the primary server (bhamare3\Named)

Run the log shipping jobs on the secondary server (bhamare3)

 

Log Shipping Sync

Check the log shipping is in sync by executing following query on secondary server (bhamare3)

select secondary_database,last_copied_file,last_restored_file from msdb..log_shipping_monitor_secondary

You can generate the log shipping sync report on secondary server (bhamare3) by

Right Clicking on Instance Name > Reports > Standard Reports > Transaction Log Shipping Status