Solved

How to change the SQL state from normal to restore state

Posted on 2010-11-08
17
1,882 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 1

Expert Comment

by:FrancoCarusi
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
FrancoCarusi,
Just as FYI, the 300 hours you're talking about is dependent on how often the transaction log is flushed out.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Expert Comment

by:FrancoCarusi
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
:-D

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

Author Comment

by:atorex
Comment Utility
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
Comment Utility
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
Comment Utility
Yes, it's a standard fail over.
0
 

Author Comment

by:atorex
Comment Utility
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
Comment Utility
we are aren't we :-)
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now