Backup and Restore Method in SQL Server

Backup and Restore Method in SQL Server Demo:

Before going to see demo kindly read and know the concepts...............

Backup:
We have to backup the source db in the format of .bak file in SQL(contains beginning to now data)

Restore:
Restore the db from backup file with option. 

When you issue a RESTORE DATABASE or RESTORE LOG command the WITH RECOVERY option is used by default.  This option does not need to be specified for this action to take place.

If you restore a "Full" backup the default setting it to RESTORE WITH RECOVERY, so after the database has been restored it can then be used by your end users.

If you are restoring a database using multiple backup files, you would use the WITH NORECOVERY option for each restore except the last.

T-SQL

Restore full backup WITH RECOVERYAs mentioned above this option is the default, but you can specify as follows.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH RECOVERY
GO

Recover a database that is in the "restoring" stateThe following command will take a database that is in the "restoring" state and make it available for end users.

RESTORE DATABASE AdventureWorks WITH RECOVERY
GO

Restore multiple backups using WITH RECOVERY for last backupThe first restore uses the NORECOVERY option so additional restores can be done.  The second command restores the transaction log and then brings the database online for end user use.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
WITH RECOVERY
GO

---

SQL Server Management Studio

When restoring using SSMS the WITH RECOVERY option is used by default, so there is nothing that needs to be set but this can be set or changed on the options page when restoring.

How to restore a SQL Server transaction log backup  (RESTORE LOG)

Overview

The RESTORE LOG command allows you to restore a transaction log backup.  The options include restoring the entire transaction log or to a certain point in time or to a certain transaction mark. 

Explanation

When restoring a transaction log you will need exclusive access to the database, which means no other user connections can be using the database.  If the database is in a restoring state this is not an issue, because no one can be using the database.

The RESTORE LOG option can be done using either T-SQL or using SQL Server Management Studio.

---

T-SQL

Restore a transaction log backupTo restore a transaction log backup the database need to be in a restoring state.  This means that you would have to restore a full backup and possibly a differential backup as well.

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
GO

Restore multiple transaction log files (NORECOVERY)The NORECOVERY option leaves the database in a restoring state after the restore has completed. This allows you to restore additional files to get the database more current. By default this option is turned off. As was mentioned above the database needs to be in a restoring state, so this would have already been done for at least one backup file that was restored.

This shows restoring two transaction log backups, the first using NORECOVERY and the second statement does not which means the database will be accessible after the restore completes.

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks_1.TRN' WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks_2.TRN'
GO

Restore a differential backupTo restore a differential backup, the options are exactly the same. The first thing that has to happen is to do a full restore using the NORECOVERY option. Then the differential can be restored.

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.DIF'
GO

Restore multiple transaction log backups from the same backup fileLet's say we use the same backup file, AdventureWorks.TRN, to write all of our transaction log backups.  This is not a best practice, because if the file is corrupt then this could corrupt all of your backups in this file.  We can use RESTORE HEADERONLY to see the backups and the positions in the backup file. Let's say that the restore headeronly tells us that we have 3 transaction log backups in this file and we want to restore all  three.  The restore commands 

would be.

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' WITH NORECOVERY, FILE = 1

GO RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' WITH NORECOVERY, FILE = 2 GO RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' WITH FILE = 3

Demo:

Source and Destination db see below

Source DB details see below

Backup source db as see below

After backed up choose the restore select on destionation db as see below

After choose .bak file and click option button u can see one conflict Restoring db is RM But in Restore file as shows c:/programfiles............/mg.mdf.................So if u choose ok it will says mdf not found or db is in use error message

So we have to choose the destination db correctly from ... ellips button on Restore file 

as see below

For this purpose of choosing file is irritating so u can use 

sp_helpdb 'dbname'

 it will give path of the db. so u can copy and past from choose easily.

After choose and click overwrite and click ok to restore db as see below

We are choosed RESTORE WITH NORECOVERY That means still db is restoring or we can add more files to restoring. 

We can see db status as see below

RM..(Restoring)...............

If after the backup any transaction was happened in source db that data also we want in the destination db so that purpose we are creating one sample table in source db as see below

So backp log of tranaction log to save the transaction as see below

So restore that Transaction log on destination db as see below

If you applied above without norecovery of transaction log means db will ready use with end user

as see below.

After restore db 

Source and Destination db's are looking same as see below with later transaction of after backup.

Thankx For seeing my demo....................

Now You can fix orphan user as see my blog or kindly use this script as see below

Script 1:

USE MASTER

GO 

SELECT name as SQLServerLogIn,SID as SQLServerSID FROM sys.syslogins

WHERE [name] = 'tester'

GO

USE RM

GO 

SELECT name DataBaseID,SID as DatabaseSID FROM sysusers

WHERE [name] = 'tester'

USE MASTER

GO 

SELECT name as SQLServerLogIn,SID as SQLServerSID FROM sys.syslogins

WHERE [name] = 'reader'

GO

USE RM

GO 

SELECT name DataBaseID,SID as DatabaseSID FROM sysusers

WHERE [name] = 'reader'

Script 2:

USE rm

sp_change_users_login 'report'

sp_change_users_login @Action='update_one', @UserNamePattern='tester', @LoginName='tester'

sp_change_users_login @Action='update_one', @UserNamePattern='reader', @LoginName='reader'

sp_addlogin 'tester'

EXEC sp_change_users_login 'Auto_Fix', 'tester', NULL, '123456'