Differences between Standby Vs No recovery?
- 2 Answers
- 15275 Views
- Wipro, I also Faced
- E-Mail Answers
Answer Posted / samba shiva reddy . m
When restoring a SQL Server Database, I notice that there are 3 different Recovery States to choose from:
Restore with Recovery
Restore with No Recovery
Restore with Standby
NORECOVERY
Instructs the restore operation to not roll back any uncommitted transactions. Either the NORECOVERY or STANDBY option must be specified if another transaction log has to be applied. If neither NORECOVERY, RECOVERY, or STANDBY is specified, RECOVERY is the default.
SQL Server requires that the WITH NORECOVERY option be used on all but the final RESTORE statement when restoring a database backup and multiple transaction logs, or when multiple RESTORE statements are needed (for example, a full database backup followed by a differential database backup).
Note When specifying the NORECOVERY option, the database is not usable in this intermediate, nonrecovered state.
When used with a file or filegroup restore operation, NORECOVERY forces the database to remain in the restoring state after the restore operation. This is useful in either of these situations:
A restore script is being run and the log is always being applied.
A sequence of file restores is used and the database is not intended to be usable between two of the restore operations.
STANDBY = undofilename
Specifies the undo file name so the recovery effects can be undone. The size required for the undo file depends on the volume of undo actions resulting from uncommitted transactions. If neither NORECOVERY, RECOVERY, or STANDBY is specified, RECOVERY is the default.
STANDBY allows a database to be brought up for read-only access between transaction log restores and can be used with either warm standby server situations or special recovery situations in which it is useful to inspect the database between log restores.
If the specified undo file name does not exist, SQL Server creates it. If the file does exist, SQL Server overwrites it.
The same undo file can be used for consecutive restores of the same database. For more information, see Using Standby Servers.
Important If free disk space is exhausted on the drive containing the specified undo file name, the restore operation stops.
STANDBY is not allowed when a database upgrade is necessary.
| Is This Answer Correct ? | 3 Yes | 0 No |
Post New Answer View All Answers
explain different types of backups avaialabe in sql server? Given a particular scenario, how would you go about choosing a backup plan? : Sql server database administration
What is system stored procedures?
While using a cursor, how can you differentiate between a deleted row and a row that has been inserted with null data values?
How to drop an existing user defined function in ms sql server?
Write SQL queries on Self Join and Inner Join.
What happens if null values are involved in string operations?
This question asked during interview, 2) At the end of each month, a new table is created for each bank that contains monthly metrics consolidated at the account level. The table naming convention is bankX_YYYYMM where X represents the numeric designation of the bank and YYYYMM indicates the 4 digit year and 2 digit month. The tables contain the following fields: name data type description account text account number registered boolean indicates whether the account is registered num_trans integer number of transactions made during the time period spend numeric(9,2) total spend during the time period a) Write a SQL query that will display the total number of transactions and total spend for "Bank1" during the 4th quarter of 2009. b) Write a SQL query that will display the total number of transactions and total spend at "Bank1" and "Bank2", broken out by registered vs. non-registered accounts, during January 2010 not sure what is correct answer and how to solve?
When would you use sql joins?
Find nth lowest salary or get nth lowest salary?
What is a join in sql? What are the types of joins?
What do you understand by mirroring?
What is difference between order by and group by?
How to provide values to user defined function parameters?
Where sql server user names and passwords are stored in sql server? : sql server database administration
What is executereader?
