November 16, 2008 at 8:49 pm
Lets say i have a database called "test". Now i only want to change the recovery state of the database to RESTORE WITH STANDBY.
Is it possible to get the database to this state without actual restore? if yes how and please guide me with possible solution.
I can't restore this database from backup file as it is too huge and will take couple of hours to restore where as changing the recovery state will solve my purpose.
November 16, 2008 at 8:53 pm
Why do you want it in a standby state?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 16, 2008 at 8:58 pm
reason is i am trying to use log shipping process which is custom designed. not using SQL server log shipping. there were couple of business decisions which forced to custom design log shipping.
the database needs to be used (read only) along side regular updates with transaction logs.
RESTORE WITH STANDBY option solves the purpose.
now the problem is, because of time and disk contraints, i thought it would be easy for me to detach and attach the database and then start log shipping. But at that moment i forgot that database has to be RESTORE WITH STANDBY option. I cant afford to restore the database now as it is too big and will take atleast 12-15 hours. any solution to my prob is appreciated.
November 16, 2008 at 10:19 pm
You can switch a database into norecovery using the backup log statement. It's not possible to switch it into standby the same way.
backup log SomeDB to disk = 'd:\someDB.trn' with norecovery
However I don't think that will allow further logs to be restored as the LSNs probably won't match.
12-15 hours to restore a database? How big is is?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 16, 2008 at 11:23 pm
Hi Gail
I tried with query you gave me but it didn't work. the error message i get is
Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
It seems but obvious who would like to switch the database from with recovery state to with norecovery state. But an idiot like me wants it desperately. 🙂
The database size is 120 GB. The problem is that restore can only be done from USB drive because of not enough free disk space to copy the backup file and then restore on the same server. also because of the complexity in the environment, i can't run it over the network as this restore process will kill the network and will effect all other processes. Moreover, please don't laugh at this, the USB port on destination server is not usb 2.0. sometimes you have to come across all these scenarios when clients are not willing to upgarde their hardware.
so now you could understand my pain. 🙁
Other options i tries are but got the same error:
1. take tail log backup and then restore it with standby or norecovery state option.
2. took differential backup and then tried to restore, didnt work.
the query i used for above options is
RESTORE LOG [Test]
FROM DISK = N'E:\TranLogs\200811171517.trn'
WITH FILE = 1, STANDBY = N'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ROLLBACK_UNDO_Test.BAK'
, NOUNLOAD, STATS = 10
GO
Used same query but with norecovery option rather than standby and all in vain.
November 16, 2008 at 11:56 pm
anam (11/16/2008)
Hi GailI tried with query you gave me but it didn't work. the error message i get is
The message is pretty self explanatory. SQL can't take a log backup because no database backup has been taken. Take a full backup and then try
That said, I don't think this route is going to work.
See if you can get more drive space added to that server. A 120GB database is small and should not even take an hour to restore. I've restored 1TB databases in 4 hours and that was with a badly-configured and slow IO system
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2008 at 3:00 am
See if you can get more drive space added to that server.
getting more drive space is not possible i have already raised this issue several times and they are not ready to listen. Anyhow, any delay because of disk space will cost them.
A I've restored 1TB databases in 4 hours and that was with a badly-configured and slow IO system
which server is that which is too quick to restore this big database. it would be good to know. btw what kind of database is it, i mean what industry are you working in?
November 17, 2008 at 6:44 am
To move a database from one server to another, and setup for mirroring, I have done this successfully:
On Server A (was main, will be the mirror):
Run full backup
Run tail of log backup
Restore said log backup with norecovery
On Server B (new main server):
Restore full backup norecovery
Restore log backup normal
Started mirroring from B to A. All appears to work fine. Have not done failover test yet.
If I read your post correctly, you have can do the first set of steps to get where you want to be. But you have to do a full backup before you can do a log backup. I'm still lost as to how you have anything to restore to that database if you've never backed it up.
Student of SQL and Golf, Master of Neither
November 17, 2008 at 5:13 pm
thanks Bob for mirroring advice.
Let me explain you what i am doing here. For logshipping process, there is a requirement that both machines should have same administrator passwords in order to communicate with each other. i think this the main first requirement.
The problem is, the destination machine is not going to be maintained by me. In this scenario, i will not like to give any third party administrator password of my machine.
in order to get around this task, what i did is the process will take transaction log backups on source machine, which will we transfered on network to destination machine and then restored there. all these are happening through scripts. I have taken care of LSNs and considered all other factors which are required in log shipping process in my scripting process.
Now comes the problem. In order to get the log shipping done, i have to move acroos the copy of database from source machine to destination for initial setup. First and foremost option is to take the backup and restore which is the idle condition. Second option which will not work for log shipping process and which i followed in order to get around disk space is detach the db on source machine, copy files across and attach it. But by following this way, the recovery state of the db is "WITH RECOVERY" after attaching the database but i need "RESTORE WITH STANDBY" recovery state in order to restore transaction logs.
Hope all this make sense to you and there is no confusion.
Now i am looking after any technique which will help me in changing the recovery state of the database till i get extra disk on my server for backup and restore.
November 18, 2008 at 7:57 am
anam (11/17/2008)
Now i am looking after any technique which will help me in changing the recovery state of the database till i get extra disk on my server for backup and restore.
This is the problem I also saw when I set up mirroring. I didn't NEED to restore the transaction log backup on the first server, but that was the only way I could see to get it into NoRecovery mode. So I made a 'tail of log' backup, then restored it right back with the NoRecovery option.
Student of SQL and Golf, Master of Neither
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply