April 2, 2009 at 1:29 pm
what exactly happens if am killing a restore process. REstore usually take 8 hrs but i killed the process after 5min realising that i dont need to do. aFter killing the database is still under restore status. will my database recover to original state ot do i need do a restore again to get back.
April 2, 2009 at 1:47 pm
Check the logs for more information.
April 2, 2009 at 2:00 pm
May be, May be not. But the database gets structurally inconsistent I guess, not so sure though.
but you can run this one though:
RESTORE DATABASE 'databasename' WITH RECOVERY.
April 2, 2009 at 2:05 pm
may be i have restore it again.
Can i use a single bak file to restore on 2 servers simultaneously.
April 2, 2009 at 2:06 pm
Mike Levan (4/2/2009)
may be i have restore it again.Can i use a single bak file to restore on 2 servers simultaneously.
I think you should wait for the database to restore, give it some time and see.
April 2, 2009 at 2:13 pm
In all probability you will end up with a database that is suspect or one that has its integrity compromised.
Even if you are able to restore from the same file on 2 servers, I would not recommend that since the restore will end up taking longer than usual.
April 2, 2009 at 2:19 pm
Hi,
SA is correct. I dont know why did you kill the restoration process. Before killing any process you have to think twice. Its better to drop the database even if it shows restored and try restoring again. And you should not use a single backup file to two servers at a time. Because it takes more time as told by sa. Its better you restore on to the first server and next to the second server.
Thanks
Chandra Mohan N
[font="Verdana"]Thanks
Chandra Mohan[/font]
April 2, 2009 at 2:20 pm
SA (4/2/2009)
Even if you are able to restore from the same file on 2 servers, I would not recommend that since the restore will end up taking longer than usual.
Sorry, I did not get this?
After getting back it online then you can restore it freshly
April 2, 2009 at 2:44 pm
Krishna,
Even after you have a fresh copy of the database, you want to restore one copy at a time since reading from the same file may end up causing contention and slow down the restore.
April 2, 2009 at 2:50 pm
SA (4/2/2009)
Krishna,Even after you have a fresh copy of the database, you want to restore one copy at a time since reading from the same file may end up causing contention and slow down the restore.
That's true, I dint understand the previous one. I meant restore the fresh copy on a single server.
April 2, 2009 at 4:12 pm
once you start the restore process if you then kill it you have to wait for it to roll back, this could take some time, hence why it still shows status restoring
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 2, 2009 at 4:15 pm
Yep, that's true even if it does come back the database will be in inconsistent state. So, probably we don't have any other option rather than restoring a fresh copy of the database.
April 2, 2009 at 4:31 pm
When a restore starts the appropriate flag is set in sys.databases (or sysdatabses on SQL2k). This shows in MS/EM as (Restoring) after the database name.
If necessary, it then resizes the files, and zeroing them out if it's SQL2k or below or if SQL2k5 and above doesn't have the "Perform Volume Maintenance Tasks" right. I'm not certain whether the database can be accessed if it's killed during this process, but I think not.
The database is definitely not useable once the restore starts doing the actually data restoration until the restore is complete. Once the restore starts it overwrites extents. As an experiment several years ago (under SQL2k) I did a backup, started restoring it immediately (there were definitely no intermediate transactions) then killed the restore straight away. Even after turning off the Restoring bit I still couldn't access the database and it got marked Suspect. I expect it works the same way under SQL2k5 and above but admittedly I've never tested it.
I can absolutely guarantee SQL Server will never roll forward/back a partially restored database. It will take manual tinkering in the system tables before SQL Server will even try to access it again, and my expectation is that after that manual tinkering SQL Server will immediately decide the database is Suspect anyway.
April 3, 2009 at 6:04 am
Perry Whittle (4/2/2009)
once you start the restore process if you then kill it you have to wait for it to roll back, this could take some time, hence why it still shows status restoring
Just to protect your db's in the future, no, it won't rollback from the restore process. You have to finish the restore, one way or another, or the database will be inaccessible.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply