February 14, 2006 at 7:06 am
Hi, sorry for the dumbness, but I am pretty new to SQL SERVER. I made a backup of a data base using the Enterprise Mgr.
But when using the same Mgr I tried to restore the data base I got a msg telling that to restore there must be only 1 user logged. SInce I am the only one using my machine, and I am not in any net, I dont understand the message.
What shyould I do?
Thanks in advance
February 14, 2006 at 7:20 am
Hello Homero,
Can you give more details?
From your message, I think that you have taken a backup of a database "abc" through Enterprise Manager.
When you try to restore the backup, are you giving a new database name or trying to restore the same.
Right click on Databases in your Enterprise Manager and select "Restore database" option. Here you provide the new database name.
Thanks and have a nice day!!!
Lucky
February 14, 2006 at 8:00 am
Another possibility is that you have other software attempting to access that database still running. Ensure there is nothing else running that may be attempting to connect to that database.
If you reboot the machine and just open EM do you still get this message when attempting to restore?
Steve.
February 15, 2006 at 2:18 am
Hi Homero!
To find out who/what is accessing your database, in EM browse to Management, Current Activity and Process Info. If you right click over the process you can kill it. Then browse to your database and right click, go to Properties, select the Options tab and check Restrict Access, Single User. Now you have exclusive access and should be able to restore.
Good luck!
February 15, 2006 at 8:18 am
Many thanks for your help. I browsed to Mgmnt, Curr. Act, Process info, right clicked on every process held by 'sa' and tried to kill it. But nothing happens (I mean, the process does not dissapear)
And when I goto my database the Properties/Options tab does no allow me to declare Restrict access (It is greyed)
I think that this is because there are still many processes going on. There are the 'system' processes that I cannot kill (not allowed) and the 'sa' processes that I cannot kill, I dont know why.
February 15, 2006 at 8:36 am
Hi Homero
What permissions does the login you're using have? There's a SQL Server role 'Process Administrators' which allows you to kill any process running, but if you already have sa permissions you should be able to do anything.
Cath
February 15, 2006 at 10:07 am
Actually I DO NOT login. I just invoke the Enterprise Mgr.
SHould I?
February 15, 2006 at 10:28 am
Hi Homero,
Everyone has given you good advice. Here's what I suggest:
Start up QueryAnalyzer. Make sure the drop down at the top shows the "master" database. Tye in sp_who2, and click on run (ctrl-E). Scroll down and you will see all the processes (spids) using the database you are wanting to restore. Type "Kill x" where x is the spid number. Do this for all the spids using your database. Then try the restore.
If that doesn't work, then do the RESTORE from query analyzer (much better way to do almost anything, really, as you learn how things work) after following the above steps. In Query Analyzer, go to Help - Transact SQL Help to learn about the correct syntax for your RESTORE statement.
Good luck.
Paul
- Paul
http://paulpaivasql.blogspot.com/
February 16, 2006 at 5:05 am
Thanks Homero - it sounds as though you're being authenticated via your Windows account in that case. You really need to find out what permissions your Windows account has within SQL Server as that will determine what you see and what you can do. In EM Browse to Security, Logins and check there.
Cath
February 16, 2006 at 5:19 am
Many thanks everyone for your kind support.
I used Paul suggestion and were able to kill the process that was bothering me.
I hope one day I will be able to return your support.
regards
Homero
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply