I have a script to restore a database from a backup. Prior to the restore command, it has:
set single_user with rollback immediate
The script seemed to be hanging here. I used sp_who2
to find out a SPID number 70 was in ROLLBACK state. I then used kill 70 with statusonly
to obtain estimated time remaining – and it translates to more than 20,000 hours.
How can I arrest this rollback, even if it means the DB ends up in a transactionally inconsistent state? (I don't really care, as I just want to restore it from a backup.) Is my only option to shut down the server (and impact other DBs on that server)?
Best Answer
Let that thing rollback. There's nothing you can do to stop it or speed it up. If you shut down the server or "trip" on the power cord, the rollback will just start back up again when you turn the server back on.
In any case, if you can restore as a different name, you don't need to rename the data file. First, see what files you have:
It will indicate two or more files. You need to map those into the below. To find out what the current data path is, in case the backup is from a different instance, you can grab it from:
Then the restore command is like this: