Solved

How to change the SQL state from normal to restore state

Posted on 2010-11-08
17
2,003 Views
Last Modified: 2012-05-10
I have 2 SQL 2008 servers setup on a mirror with no witness, due to a fail-over the main server was demoted and the backup server set as the Principal.
The hardware issue was resolved and the main server is now back on and I reactivated the mirror with the backup server as the Principal and the main as the mirror. I need to reverse the Roles as the Main server has extra applications and more robust hardware, my question is how can I quickly change the principal to the restore mode?
I use the following command to take the server from restore state to active state but I dont want to have to do a restore with NORECOVERY I would like to use a script like the below to do this

use master
ALTER DATABASE DBData SET PARTNER OFF

use master
RESTORE DATABASE DBData WITH RECOVERY

is there a reverse of this statements that will achieve what I need?
0
Comment
Question by:atorex
  • 8
  • 5
  • 4
17 Comments
 
LVL 10

Expert Comment

by:Humpdy
ID: 34085387
If the databases are not in restore state now, you have to take a full back up and a transaction log backup of your current principle server, and restore to your mirror.
Then flip over.

use master
ALTER DATABASE DBData SET PARTNER OFF

instead of this, why didn't you run
alter database dbdata set partner failover ?
this would have reversed the roles.
0
 

Author Comment

by:atorex
ID: 34086197
So there is no way to use a sql command as to make the principal the mirror without a restore?
use master
ALTER DATABASE DBData SET PARTNER OFF

as to the use of the above command  instead of alter database dbdata set partner failover is simply know how, I don't have it!!!
I will do so in the future, in that case I would not need to run the ALTER DATABASE DBData SET PARTNER OFF, correct?
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34087362
correct yes....
you can do a set partner off from the principle database, but doing so you are actually removing mirroring altogether.

you should have a look at
alter database dbname set partner off
alter database dbname set partner pause
alter database dbname set partner failover
alter database dbname set partner = 'tcp://etc. etc.etc'
alter database dbname set witness = 'tcp://etc. etc. etc'

to get yourself familiar with everything.

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 1

Expert Comment

by:FrancoCarusi
ID: 34092158
Hi

Sounds like you recoved the database. Reason you can't reverse this is in the event that changes were made to the database. SQL will not allow the mirror to resume. You will have to restore the database to the latest log WITH NORECOVERY and then re-establish the mirror. No way around this.
0
 

Author Comment

by:atorex
ID: 34093461
Ya, I had a failure on the principal then reversed the mirror and made it the principal, after fixing the hardware on the old principal I restored its database and activated it as the mirror. I needed to reverse the set-up to default where the original principal is again set as principal.
so the system is fully up I would like to swap the principal and mirror machines, but I guess I need to follow the normal process to activate the mirror. I was hoping to have a quick process without restore as the 2 databases are synced so as far as data no restore is required.
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34093577
I hear you atorex, bad news though I'm afraid is that you'll have to follow the normal process and do it from scratch :-(
0
 
LVL 1

Expert Comment

by:FrancoCarusi
ID: 34093593
Once a db has been recovered then the option to go back is gone :( even if you have not done any work on the db.
Did the server go down when the hardware issue was detetcted?
If you ran a failover using the gui (or scripts alter database <name> set partner failover") and left the database in a recovery mode you would have been able to fail back (within a specified time frame - ithink it is about 300 hours). With the same command.
I have been there with db's that took days to restore.
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34093612
FrancoCarusi,
Just as FYI, the 300 hours you're talking about is dependent on how often the transaction log is flushed out.
0
 
LVL 1

Expert Comment

by:FrancoCarusi
ID: 34093667
Ye, using the default settings when creating a mirror, the transaction log gets backed up every 15mins (can be changed) but the transaction log backup files are kept in the folders specified during the setup. the logshipping mechanism will automaticaly commit old transaction log backups and catch up (but is limited in time).
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34093699
hmmm..I think you're confusing the log shipping configuration and mirroring configuration.
in mirroring, it's real-time from principle to mirror, or mirror to principle if you're using snychronous.
there is no scheduled backup of the transaction logs in mirroring by default.
0
 
LVL 1

Expert Comment

by:FrancoCarusi
ID: 34093824
sorry your 100% right.
But again in mirroring, if the databases don't sync, the log does not get commited and the transactions remian there and the transaction log file grows. even a backup log will not empty the log file. The only way to reduce the file size is to break the mirror and the re-create it once. there is however a "time to live" setting.
(just had some probled with logshipping and this is what steered my way of thinking)
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34093837
:-D

I was doubting myself and had to double-check it !!
0
 

Author Comment

by:atorex
ID: 34093881
The database crashed with the hardware so it was restored from a backup of the mirror, I have done the normal process and the servers are back as normal, I was just looking for a way out of having to restore the database, lets look at it this way. db1 is running as principal db2 is the mirror with no witness, can I swap db1 and db2 as below,

make db2 principal
make db1 mirror (as I normally do it I would restore the log and leave this db in no-recovery mode then activate the mirror)
this is the step I would like to avoid as per the example these 2 databases are synced so as I see it (just Me) why do I need to restore the same data just to get the database to a no-recovery state could I run an sql command that would enter db1 to the restore state where the mirror can then be activated (don't forget no failure no data loss the mirror was active and synced, I just need to swap the partnership between db1 from principal to mirror and db2 from mirror to principal) or am I just lazy and want to rediscover the wheel???
0
 
LVL 10

Assisted Solution

by:Humpdy
Humpdy earned 400 total points
ID: 34093922
in your scenario,
you have db1 as principle, db2 as mirror.

if you want to make db2 as principle and db1 as mirror,
all you have to do,
is logon to the db1 server, and type in
alter database db_name set partner failover.

and that's it, you don't have to do anything further.
If you refresh your databases you will see that db1 will have (Mirroring...synchronized), and you're db2 will have (Principle....synchronized).

no need for any restores in this approach.
0
 
LVL 1

Accepted Solution

by:
FrancoCarusi earned 100 total points
ID: 34093992
Yes, it's a standard fail over.
0
 

Author Comment

by:atorex
ID: 34094148
that's what I was looking for, you guys are the best I should have started with a full scenario as above would have saved you lot of time however I do appreciate you getting it out of me anyway. The scenario I explained is what I am facing.

Thanks so much
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34094179
we are aren't we :-)
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question