With recovery should take longer than NORECOVERY because it has rollback uncommitted transactions and rollforward committed transactions....

From Book Online...

Comparison of RECOVERY and NORECOVERY
Roll back is controlled by the RESTORE statement through the [ RECOVERY | NORECOVERY ] options:

NORECOVERY specifies that roll back not occur. This allows roll forward to continue with the next statement in the sequence.

In this case, the restore sequence can restore other backups and roll them forward.

RECOVERY (the default) indicates that roll back should be performed after roll forward is completed for the current backup.

Recovering the database requires that the entire set of data being restored (the roll forward set) is consistent with the database. If the roll forward set has not been rolled forward far enough to be consistent with the database and RECOVERY is specified, the Database Engine issues an error.

i didn't do anything ..I created all the databases in the server1(which is going to be the principal server) and i restored one databse in the server 2(which is going to be the mirror) with norecovery state.

then i stared to mirror using management studio.., then i got the error i posted...

please guide me how to setup the mirroring for the first time without witness server

ok..i;ll check with the n/w team.<br />while i try to backup the certificate to c: , i am getting an error.<br />what type of permission i need to backup the certificate.<br /><br />"Msg 15240, Level 16, State 1, Line 1<br />Cannot write into file 'CrincipalServerCert.cer'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist."<br /> <br /><br />no file is already existing on the c<img src='/community/emoticons/emotion-2.gif' alt=':d' />rive

i setup the services to start under windows domain account .e.g domainnameadmin
but when ever i selected to connect the management studio under windows authentication,
it's connecting using my id domainnamepriyaram

alter databse &lt;dbname&gt;<br />set partnet = 'TCP://&lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />rincipalservername&gt;:4040'<br />this ran successfully on the mirror server.<br /><br />but i ran<br />alter databse &lt;dbname&gt;<br />set partnet = 'TCP://&lt;mirrorservername&gt;:4040'<br /><br />it's failing for me with the error<br />The server network address "TCP://&lt;mirrorsservername&gt;:4040" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.<br />

One more thing i want to clarify in this..
In our server we are having 10 user databases , right now i have done this for only one user database, suppose if i want to do it for all the remaining 9 user databases means, i have to repeat all this steps for server and mirror again??

will the front end and all other applicatons which ever is referring to the principal database in the principal server, will chage automatically their setting to the mirror database in the mirror server when the failover occurs.??

As per your note, i have to create user and alter database st. for another database to mirror.
ok, once i create the user , i have to authenticate the certificate to that user, then the alter st. also i have to the same port number right??

quote:
The next step is to establish database mirroring endpoints. Establishing endpoints requires that you have system administrator rights to the SQL Server instance. You must set up endpoints on each server that are specifically created as database mirroring endpoints. The easiest way to set up endpoints is to use the Configure Database Mirroring Security Wizard, which you can invoke by clicking the Configure Security button on the Mirroring page of the Database Properties dialog. The Configure Security dialogs will prompt you for computer names and port numbers, and optionally logins, before constructing and executing the CREATE ENDPOINT commands. You can also execute the CREATE ENDPOINT command using Transact-SQL. (See "How to: Create a Mirroring Endpoint (Transact-SQL)" in SQL Server Books Online.)

Each database endpoint must specify a unique port on the server. When working with SQL Server instances on separate machines, these port numbers can all be the same and the Configure Database Mirroring Security Wizard will automatically suggest port 5022 as the port. If any of the SQL Server instances are on the same machine, each instance must have a distinct port and the port numbers must be unique.

Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderatorhttp://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

suppose in the application it's querying across 2 databases in the principal server, but the only 1 database failed in the principal.. how to handle this situation...

they told to define one connection per database...
so as per their statement if the one database fails and transferred to mirror, then how that query is going to work (since it;s querying one databse in mirror (which is failed) and other database in principal which doesnot fail...

The database mirroring is associated per database and if your application needs both the databases then make sure to involve both of them, in order to preference you have to control the failover and until unless they are failed over you will not be able to connect the application to the databases.,

Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderatorhttp://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

quote:When safety is set to OFF, the communication between the principal and the mirror is asynchronous. The principal server will not wait for an acknowledgment from the mirror that the mirror has hardened a block of transaction records. The mirror will attempt to keep up with the principal, by recording transactions as quickly as possible, but some transactions may be lost if the principal suddenly fails and you force the mirror into service. (See the topic 'Forced Service' in SQL Server Books Online.)

Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderatorhttp://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Why not just stop the mirrroring and take complete backup from principal to mirror, then start the process to ensure it will not mismatch on the point.

If you are using mirroring then do not worry about tlogs and also it depends upon the mode of mirroring used.

Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderatorhttp://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Your point is also a good one, I tried the same, but in situations where you have a large d/b and the full backup taking a long time (and of course it takes almost equal amount time during restore), I had new transactions happening during that long period of full backup/restore that caused mismatch. In such situations T-log backup relatively takes less amount of time helping the d/b's to get in synch. Am I right?

I don't think so if you have such option then anyhow the DB mirroring is taking care of such options, they will not be any issues as I don't have any my end managaing half TB sized database with similar setup.

Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderatorhttp://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.