How to change the SQL state from normal to restore state

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?
atorexAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HumpdyCommented:
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
atorexAuthor Commented:
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
HumpdyCommented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

FrancoCarusiCommented:
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
atorexAuthor Commented:
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
HumpdyCommented:
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
FrancoCarusiCommented:
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
HumpdyCommented:
FrancoCarusi,
Just as FYI, the 300 hours you're talking about is dependent on how often the transaction log is flushed out.
0
FrancoCarusiCommented:
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
HumpdyCommented:
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
FrancoCarusiCommented:
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
HumpdyCommented:
:-D

I was doubting myself and had to double-check it !!
0
atorexAuthor Commented:
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
HumpdyCommented:
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
FrancoCarusiCommented:
Yes, it's a standard fail over.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
atorexAuthor Commented:
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
HumpdyCommented:
we are aren't we :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.