|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How do I restore a database that is in use?
I have a massive transaction currently rolling back (I killed the spid.) The
estimation is that it will take this transaction many hours to roll back. I've decided it will be faster to just restore a previous backup of the database. How do I initiate the restore? I can't restore it now, because it says the database is in use - the rolling back transaction is the only "user" accessing the database. Thanks in advance!! I guess one option is to stop SQL Server, delete the database files, start SQL Server, DROP the
database and then restore. -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "apf" <a**@discussions.microsoft.com> wrote in message news:16678A2E-5C94-4178-9D78-340CF4BD99D5@microsoft.com... >I have a massive transaction currently rolling back (I killed the spid.) The > estimation is that it will take this transaction many hours to roll back. > I've decided it will be faster to just restore a previous backup of the > database. > > How do I initiate the restore? I can't restore it now, because it says the > database is in use - the rolling back transaction is the only "user" > accessing the database. > > Thanks in advance!! If you are 100% certain this is what you want to do, you can stop SQL
Server, move the mdf/ldf files off and re-start. The db will come up in suspect mode. Drop it and restore the backup. SQL 2000. I assume similar or identical process for SQL 2005 -- Show quoteHide quoteKevin Hill 3NF Consulting http://www.3nf-inc.com/NewsGroups.htm Real-world stuff I run across with SQL Server: http://kevin3nf.blogspot.com "apf" <a**@discussions.microsoft.com> wrote in message news:16678A2E-5C94-4178-9D78-340CF4BD99D5@microsoft.com... >I have a massive transaction currently rolling back (I killed the spid.) >The > estimation is that it will take this transaction many hours to roll back. > I've decided it will be faster to just restore a previous backup of the > database. > > How do I initiate the restore? I can't restore it now, because it says > the > database is in use - the rolling back transaction is the only "user" > accessing the database. > > Thanks in advance!! Same applies for 2005, but the database isn't suspect. The GUI tools will not let you "expand" the
database. This is, sort of, a soft suspect, so it can go back to normal if you stop SQL Server, slide the files back in and start SQL Server again. -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Kevin3NF" <kevin@SPAMTRAP.3nf-inc.com> wrote in message news:uQD%23B3nCHHA.992@TK2MSFTNGP03.phx.gbl... > If you are 100% certain this is what you want to do, you can stop SQL Server, move the mdf/ldf > files off and re-start. The db will come up in suspect mode. Drop it and restore the backup. > SQL 2000. I assume similar or identical process for SQL 2005 > > -- > Kevin Hill > 3NF Consulting > http://www.3nf-inc.com/NewsGroups.htm > > Real-world stuff I run across with SQL Server: > http://kevin3nf.blogspot.com > > > "apf" <a**@discussions.microsoft.com> wrote in message > news:16678A2E-5C94-4178-9D78-340CF4BD99D5@microsoft.com... >>I have a massive transaction currently rolling back (I killed the spid.) The >> estimation is that it will take this transaction many hours to roll back. >> I've decided it will be faster to just restore a previous backup of the >> database. >> >> How do I initiate the restore? I can't restore it now, because it says the >> database is in use - the rolling back transaction is the only "user" >> accessing the database. >> >> Thanks in advance!! > > Thanks for the help!
I was hoping there was a way to do it without stopping SQL Server, but I suspected that I would have to. I'll take this as verification that I'm going to have to stop SQL Server. > I'll take this as verification that I'm going to have to stop SQL Server. I'd wait out the rollback process if I were you...-- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "apf" <a**@discussions.microsoft.com> wrote in message news:BA964863-8113-41B1-BBA2-8FA6E5183205@microsoft.com... > Thanks for the help! > > I was hoping there was a way to do it without stopping SQL Server, but I > suspected that I would have to. > > I'll take this as verification that I'm going to have to stop SQL Server. >  
Other interesting topics
Indexed view appears slower
Advice about database deployment Cannot change tran log to "unrestricted" growth Revert to checkpoint change precision in a table column Getting a syntax error involving Microsoft.SqlServer.SmoEnum Unknown Error with MDE file Should I be concerned about this? Problem with top 1. urgent help needed About database maintenance |
|||||||||||||||||||||||
