Restore Database with SSMS and T-SQL in SQL Server

In previous article we have seen database backups and types, so in continuation to that we are going to discuss about how to restore database on same instance or different instance. We have already taken full backup and transaction log backup of ‘Inventory’ database. We will restore the database on different instance by SSMS and T-SQL.

We already have ‘Create Database’ permission on new instance. Now connect to SQL Server Management Studio and follow below steps to restore.

In Object Explorer, right click on Databases, and select Restore Database…

Figure 1: Restore Database option

Database Restore window will pop up where you can select backup device location in source andAdd backup media from backup path and click OK. [refer Figure-2]

Figure 2: Backup File Selection to Restore

Once backup file is selected you can refer restore plan details like database type, server name, database name, first LSN, last LSN, size, user name etc.

Figure 3: Restore General Page

Now go to Files in ‘Select a page’ section on left side of the window and you can change the restore path of master data file and log file in ‘Restore As’ navigation.

Figure 4: Restore Database File page

In Options page you have different choice to select as per your requirement and work scenario.

– Overwrite the existing database (WITH REPLACE)

– Preserve the replication settings (WITH KEEP_REPLICATION)

– Restrict access to the restored database (WITH RESTRICTED_USER)

In Options you can also select recovery state as per your recovery strategy. Recovery state has 3 types.

– RESTORE WITH RECOVERY: It is a default option, once restore is completed it makes the database online to work.

– RESTORE WITH NORECOVERY: It keeps the database in recovering state to restore more backup files, you cannot use database until it is fully recovered.

– RESTORE WITH STANDBY: It sets the database in read only mode.

Figure 5: Restore Database Options page

As we have to restore transaction log also so we have kept database state in RESTORE WITH NORECOVERY.

Once you click OK button, restore operation will begin and you will get message Database ‘Inventory’ restored successfully.

Now refresh the database node and you can observe that database is in ‘Inventory (Restoring…)‘ state as shown below.

Figure 6: Database Restoring State

Now we will restore transaction log WITH RECOVERY state so that once restore is completed, database will be online and ready to use.

To restore transaction log, right click on Inventory database, navigate to Tasks –> Restore and click Transaction Log…

Figure 7: Restore Transaction Log

Now choose From file or tape option and add your transaction log file from backup path. By default point in time is selected, then go to Options page and select recovery state as RESTORE WITH RECOVERY and click OK. Once restore is completed you will get successful message as shown below and database is online and ready to use.

Figure 8: Restore Completed

Now we will do the restore process of full backup and transaction log backup of database with T_SQL.

First of all check the logical name of database backup copy to restore with RESTORE FILELISTONLY. [refer Figure-9]

--Check Logical Name of Database
RESTORE FILELISTONLY
FROM DISK = 'C:\Backup\FullBackup_Inventory.bak'

Figure 9: Check Logical Name of Database

Now restore the Inventory database with NORECOVERY as follows. Once restore is successfully completed you will get details in message pane. [refer Figure-10]

--Restore Database Inventory with NORECOVERY
USE [master]
RESTORE DATABASE [Inventory] FROM DISK = N'C:\Backup\FullBackup_Inventory.bak' WITH FILE = 1, 
MOVE N'Inventory' TO N'C:\RestoreDB\Inventory.mdf', 
MOVE N'Inventory_log' TO N'C:\RestoreDB\Inventory_Log.ldf', 
NORECOVERY, NOUNLOAD, STATS = 5
GO

Figure 10: Restore Database WITH NORECOVERY

Then restore transaction log with RESTORE LOG command as given below.

--Restore Transaction Log with RECOVERY
RESTORE LOG [Inventory] FROM DISK = N'C:\Backup\TranLog_Backup_Inventory.trn' 
WITH FILE = 1, NOUNLOAD, STATS = 10
GO

Figure 11: Restore Log WITH RECOVERY

Once all files have restored you make sure that your database is ONLINE and in MULTI_USER state. You can check the same with below query. [refer Figure-12]

--Check Database Status
SELECT name, user_access_desc, state_desc, recovery_model_desc FROM sys.databases 
WHERE name = 'Inventory'

Figure 12: Check Restored Database Status

Our ‘Inventory’ database is now restored completely and ready to use.

CLICK HERE to watch live practical.

Reference: Manzoor Siddiqui [www.SQLServerLog.com]

Different States of SQL Server Database

Introduction

SQL Server Database can reside in one among the seven states. For example these can be ONLINE, OFFLINE, RESTORING, SUSPECT etc. This article explains each of these states and includes a TSQL code which can be used to identify the current state of a SQL Server Database.

How to Identify Current State of SQL Server Database

Execute the below query to identify the current state of all the SQL Server Database in your Instance.

Use master
GO

SELECT
@@SERVERNAME AS [Server Name]
,NAME AS [Database Name]
,DATABASEPROPERTYEX(NAME, 'Recovery') AS [Recovery Model]
,DATABASEPROPERTYEX(NAME, 'Status') AS [Database Status]
FROM dbo.sysdatabases
ORDER BY NAME ASC
GO

TSQL Query to Identify Database which are Offline in SQL Server

Before we discuss different states of SQL Server Database we assume that you are aware of Different Recovery Mode in SQL Server.

Different States of SQL Server Database

A SQL Server Database is can only be in one specific state at a given time. Different States of SQL Server Database are:-

• ONLINE:- When a database is in ONLINE state the database is available for access. The primary filegroup is online eventhough the undo phase of recovery may not have been completed.



• OFFLINE:- When a database is in OFFLINE state then the database is not accessable for user connections. One can set the database to this state if you don’t want users to connect to the database. For example you have migrated the database to a new server and don’t want users to accidently connect to the Old SQL Server Database.



• RESTORING:- When a database is in RESTORING state then it means one or more files of the primary filegroup is been restored or one or more secondary files are being resotored offline.



• RECOVERING:- When a database is in RECOVERING state it means its in the process of recovery and it will become automatically ONLINE for user connectivity. In case of a failure the database will become SUSPECT and become unable for use until a database intervene and fixes the issues.



• RECOVERY PENDING: - When a database is in RECOVERY PENDING state it means SQL Server has encountered a resource related error during recovery. The database might be missing files. DBAs intervention is required in such a case.



• SUSPECT: - When a database is in SUSPECT state it means the database is unavailable for user connection. Database may be damaged or at leasr the primary filegroup is supect. DBAs intervention is required in such a case. Read the following article which explains “How to Repair SUSPECT Database in SQL Server”



• EMERGENCY: - When a database is in EMERGENCY state it means a user has changed the status of the database to EMERGENCY. In EMERGENCY mode database will remain in SINGLE_USER mode and the database can be repaired or restored. Database will remain READ_ONLY. You need to be a member of SYSADMIN role to set database in EMERGENCY mode. You make have to set the state of the database as EMERGENCY when the database is market as SUSPECT. Read the following article which explains “How to set Database in EMERGENCY state”
Reference: Database States on TechNet

Conclusion

SQL Server Security in itself is a vast topic and in this article we have discussed few of the settings which can be easily implemented to improve the overall security of the SQL Servers which you manage day-to-day in your work.

Read more: http://www.mytechmantra.com/LearnSQLServer/Different-States-of-SQL-Server-Database/#ixzz3h4ciXlCj
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook

Recovering Suspect Database

Everyone knows that, how much pain it is when a LIVE database is marked as suspect and especially there is no latest backup of the database.

What Happened when a data base is marked as Suspect:

If your LIVE database is in suspect mode, then no transaction will take place until and unless you repair your database. That causes a show stopper for your up and running application. Here, you will find a way to get out of this.

So, What the Solutions:

In this article, I am going to illustrate some T-SQL statements that can help you in case of suspect database marked.

EXEC sp_resetstatus 'YourDBName'

GO

ALTER DATABASE YourDBName SET EMERGENCY

DBCC checkdb('YourDBName')

ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('YourDBName', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE YourDBName SET MULTI_USER

GO

/*

 Rebuild the index is necessay

*/

ALTER INDEX ALL ON [ TableName] REBUILD

Hope the information of this article is quite informative and thanking you to provide your valuable time on it.

How to Configure Credentials in SQL Server

SQL Server Credentials are used to access external resources of windows by enabling SQL Server login. Credentials creates an internal connectivity with SQL Server login to outside resources so that by using SQL Server login you can fetch or access those resources. These credentials are SQL Server database objects only which is used for accessing outside applications or resources and passwords are encrypted using service master key in this method.

Using a windows identity a user who is connected to SQL Server with SQL Server authentication can access outside resources of server instance. One SQL Server login can be mapped to only one credential but single credential can be mapped to multiple SQL Server logins.

We will see how we can configure credentials in SQL Server. We can create credential by T-SQL or by SSMS with both methods.

Create Credential by T-SQL

USE master
GO
CREATE CREDENTIAL DB_Credential
WITH IDENTITY = 'MANZOOR\ManzoorSiddiqui',
SECRET = 'B@6P@$$w0r6'
GO

As you can see, credentials are stored in master system database and password is called secret. In previous tutorial we have already seen database encryption technique and master key details so when new service master key is generated on regular basis, password for credential are automatically decrypted and again encrypted with new service master key.

To alter the credentials we can use below query, in alter credential, values for identity and secret gets reset and we can change password for credential.

USE master
GO
ALTER CREDENTIAL DB_Credential
WITH IDENTITY = 'MANZOOR\ManzoorSiddiqui',
SECRET = 'N3wP@$$w0r6'
GO

If you want to verify the credential, you can fire the below query.

SELECT *FROM sys.credentials
GO

In sys.credentials you can find credential id, credential name, credential identity and other details.

Now we will map the credential with SQL Server Login ‘Lisa’ so that Lisa can access external resources with windows identity.

ALTER LOGIN [Lisa] ADD CREDENTIAL [DB_Credential]
GO

To drop the credentials, use below query.

DROP CREDENTIAL DB_Credential
GO

Create Credential by SQL Server Management Studio and Map to SQL Login

Go to Object Explorer –> Security –> right click on Credentials and select New Credential… as shown below.

Figure 1: Create New Credential

Once New Credential window will pop up, provide details about credential name, Identity and password. Here I am selecting my local domain user ‘MANZOOR\ManzoorSiddiqui’ as identity. In your case, you can provide proper network domain user and write some strong password.

Figure 2: New Credential

After entering all detail, click on OK button and refresh the credential node and check the newly created credential as shown below.

Figure 3: Credential Created

Now, to map the credential with SQL Server Login, go to Security and select login which you want to map with your credential, here I am mapping SQL Server login ‘Lisa’ with credential‘DB_Credential’ as given below. Select Map to Credential and choose credential from drop down menu and click OK.

Figure 4: Map Credential to SQL Login

CLICK HERE to watch live practical.

Reference: Manzoor Siddiqui [www.SQLServerLog.com]