restore failure using mgmt studio
-
Why is it that after I set a database to single user mode, management studio reports the database in use when I right click the "databases" node in object explorer and attempt to use the GUI to restore over an existing database. The one tsql window used to set single user is in the master database.
The overwrite check box was checked. Even in sql 2K I found this to be the case and had to use a script to accomplish restores.
-
First you kill all the users from that db before you start your restore...
Overwirte option will not kill the users from the db...
Run the sp_who and check if any users in your restoring db... kill them using KILL command then start your restore.
MohammedU
Microsoft SQL Server MVP -
I had already set the database to single user mode. alter database xxx set single_user so there shouldn't be anyone connected.
-
Some other connection might has got the connection to that database...
Try in Query window (Query Analyzer).. before doing put the db in multi user mode...
Ex:
1. ALTER DATABASE Northwind SET SINGLE_USER WITH ROLLBACK IMMEDIATE
2. RESTORE DATABASE Northwind
FROM DISK = 'c:\Northwind.BAK'
WITH MOVE 'Northwind_Data' TO 'c:\data\Northwind.mdf',
MOVE 'Northwind _Log' TO 'c:\data\Northwind _log.ldf'
3. ALTER DATABASE Northwind SET MULTI_USER
MohammedU
Microsoft SQL Server MVP -
Yes that should work. But sometimes I want to use the GUI to right click and restore, but this never seems to work even after putting the database in single user mode. It seems that the right click / restore functionality is either not possible ( always reports database in use ), or setting to single user mode doesn't really work as advertised ( doesn't completely remove all connections except mine )
-
I don't agree with you...
Single user mode always works as long as you follow the directions...
I have done backup/restore many times using GUI and never had any problems...
MohammedU
Microsoft SQL Server MVP -
After you've close/killed all of the connections to the database, don't right-click on the database you want to restore. Right-click on "Databases" instead, then choose Restore Database.
Don't have any open Query windows using that database either.
-
We are unable to use the GUI to restore the master database, however restoring through SQLQuery ("query analyzer") does work. The trick is to make sure the GUI is not open when configuring to single user mode. Open the GUI, but "cancel" the connection to your server. You can then open SQLQuery and perform the restore. We have been working with Microsoft on this issue because their documentation does not appear to be completely accurate. They confirmed today the GUI has issues and they are working internally to resolve these problems. For now Microsoft recommends using SQLQuery/query analyzer for restoring databases when the server is in single user mode. Another item of note we were only able to get this to work when running sqlservr -m and our version of SQL Server is 2005 Enterprise Edition.
I'm very disappointed with 2005. Several options I found handy in 2000 are no longer available in 2005. I find this frustrating, however even more frustrating is the fact I have opened 5 or 6 tickets with <st1
ersonName w:st="on">Microsoft</st1ersonName> since November and nearly all of the issues I reported turned out to be either undocumented or incorrect documentation on their part. One or two of the documentation issues appear to have been beta-version documentation that was never updated and at least one of the issues <st1ersonName w:st="on">Microsoft</st1ersonName> said would be sent to their development team to be documented as an official bug. In my opinion this product was released to soon to the public.Dave
-
I always use QA to restore MASTER db...
MohammedU
Microsoft SQL Server MVP -
I never had to in 2000 and the documentation for 2005 indicates the GUI should work for master, but unfortunately the GUI appears to not work.
As an FYI, hope you never have to uninstall SQL 2005. That also has issues. It does not do a good job cleaning up files and it may depend upon the options selected during the initial installation. This was our latest headache with 2005.
Dave
-
I don't mean to be picky but we are mixing sql 2000 and sql 2005 posts here in the 2005 forum. I don't believe you can use the sql 2000 query analyzer tool on sql 2005. Anyway, I'm concluding that trying to restore using the "right click" approach in the GUI is unreliable so best to just stick to scripts.
Where this really stinks is if you are restoring a full backup and 100 subsequent transaction log backups. You will have to use one of the stored procedures out there that will create all of the restore statements unless you want to type all day. If the GUI worked, it should show the full and all of the t log backups with a green check mark on all of them, ready to go.
Again, if alter database xx set single_user REALLY worked, yours would be the only connection to the database so no conflicts. Also, if you are trying the GUI you have to be connected to the sql server in object explorer ( management studio ) in order to have something to right click on -- you right click on the "databases" heading, not the database you are trying to restore over.
Randy
-
I believe it was Old Hand who actually answered this for me in another topic. See below - I had a misunderstanding about the command putting a DB in single user mode. The suggestion was to include a use database, set single user and possibly your restore or other command all in one transaction. Next I want to check out this "dedicated admin connection" I read about for sql 2005. I'm probably calling it by the wrong name but it would really be useful to have a command that actually did 'RESERVE THIS DATABASE FOR ME'
You do not have any guarantee that in between the execution of the Alter database statement (putting it in single use) and the next statement, that another person or process can grab the only process. Set Single User does not say "reserve the database for me". It only says "only one connection open at a time." But that one connection can be anyone. -
My comments were in regards to restoring the master database on 2005, but I assume the same GUI issues would occur for user databases. I only referred to SQLQuery as "Query Analyzer" because the name SQLQuery may be unfamiliar to many people new to SQL 2005. It's essentially the same tool as Query Analyzer. The process for restoring multiple transaction logs outside of the GUI can be fairly simple through the use of a script. It takes a few minutes to create the script, but once created I've found it to be just as fast, if not faster, then the GUI.
STEP 1 = RESTORE FILELISTONLY to see the logical & physical file names
STEP 2 = RESTORE HEADERONLY to see the file numbers for the transaction log backups you wish to restore
STEP 3 = Restore the Database
STEP 4 = Loop through the T-log restores. If using STOPAT make sure the loop stops prior to the last T-log file and then execute the RESTORE LOG using the STOPAT.
If you enable logging for successful login attempts you will notice SQL 2005 creates more SQL Server connections then 2000, which is why "single user" mode can be a bit difficult at times.
Good luck,
Dave
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply