Backup a database which is in restoring mode

Question

Backup a database which is in restoring mode

gary1

SSC-Insane

Points: 21152
More actions
November 2, 2009 at 11:13 pm

#213369

Hi,
I have a Log shipping configured database and on secondary the database is in restoring mode & log backups are applying every 15 mins. Is there any way to BACKUP this database(restoring state) on secondary?
thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

maechismo_8514 SSCoach Points: 17510 More actions · Answer 1

gmamata7 (11/2/2009)
Hi,
I have a Log shipping configured database and on secondary the database is in restoring mode & log backups are applying every 15 mins. Is there any way to BACKUP this database(restoring state) on secondary?
thanks

Which database? The one in Restoring mode? You have not made the question clear.

If I am thinking right, you have a backup of your Primary databases already in place when you configured Log shipping!!???. You can restore the same file backup.

Thanks.

gary1 SSC-Insane Points: 21152 More actions · Answer 2

thanks Krishna,

My question is: Is there any way to take the backup of a database, which is in restoring mode?

maechismo_8514 SSCoach Points: 17510 More actions · Answer 3

gmamata7 (11/3/2009)
thanks Krishna,
My question is: Is there any way to take the backup of a database, which is in restoring mode?

When the database is in restoring state, it means the databases is not online/unavailable- Hence not accessible. so, it would not be possible to take a backup of that database unless it is brought back online.

Edited to add:

http://msdn.microsoft.com/en-us/library/ms190442.aspx

gary1 SSC-Insane Points: 21152 More actions · Answer 4

When the database is in restoring state, it means the databases is not online/unavailable- Hence not accessible. so, it would not be possible to take a backup of that database unless it is brought back online.

When a database is in restoring mode, if I stop the SQL Service and copy the .mdf & .ldf file and saved them in a folder. Later, I brought the database online.

Now, If I stop the SQL Service and replace the .mdf & .ldf files with previously copied mdf & ldf files and start the service, then the database will start and it should be in restoring mode right??

thanks

ramuvanparti SSCrazy Points: 2368 More actions · Answer 5

no database will not come online even if the service are restarted...

maechismo_8514 SSCoach Points: 17510 More actions · Answer 6

gmamata7 (11/3/2009)
When the database is in restoring state, it means the databases is not online/unavailable- Hence not accessible. so, it would not be possible to take a backup of that database unless it is brought back online.
When a database is in restoring mode, if I stop the SQL Service and copy the .mdf & .ldf file and saved them in a folder. Later, I brought the database online.

Did you mean, you created a new database? and brought that back online?

Now, If I stop the SQL Service and replace the .mdf & .ldf files with previously copied mdf & ldf files and start the service, then the database will start and it should be in restoring mode right??

Yes, that's right. The database will be on restoring state too. The only way to bring it online is using the backup command and RECOVERY mode.

What you can do is, You can create a new database with the files that you copied.

ramuvanparti SSCrazy Points: 2368 More actions · Answer 7

hey,

try this command on the server where you are shipping the log that is target server

use master

restore database databasename with recovery

it might bring your restoring state db in online.

i have tried it on mirroring it worked..please check and let me know

Thanks & regards

Ramu

maechismo_8514 SSCoach Points: 17510 More actions · Answer 8

ramuvanparti (11/3/2009)
hey,
try this command on the server where you are shipping the log that is target server
use master
restore database databasename with recovery
it might bring your restoring state db in online.
i have tried it on mirroring it worked..please check and let me know
Thanks & regards
Ramu

That breaks the log-shipping configuration which you do not want to do it I suppose.

Mamta,

Is your original question just for reference and to get to know things or do you want to implement it? Please state your purpose which would be more useful.