| Author |
Topic |
|
rpc86
Posting Yak Master
200 Posts |
 Posted - 2006-05-22 : 23:16:12
|
| Hi guys,how to rename the database name?say I have MwasLive then temporarily change this to Mwas_TempI use MS SQL Server 7. How can I rename the database using Query Analyser?Thank you. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-05-22 : 23:18:34
|
[code]EXEC sp_renamedb 'MwasLive', 'Mwas_Temp'[/code]
KH |
 |
|
|
rpc86
Posting Yak Master
200 Posts |
Posted - 2006-05-22 : 23:28:41
|
| error:A member of the sysadmin role must set database 'MwasLive' to single user mode with sp_dboption before it can be renamed. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-05-22 : 23:30:16
|
make sure you are in master DB and no other users accessing the MwasLive DB.
KH |
|
|
|
rpc86
Posting Yak Master
200 Posts |
Posted - 2006-05-22 : 23:36:31
|
| No one is using the database now. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-05-22 : 23:37:09
|
did you run the sp_renamedb in master db or in MwasLive db ?
KH |
|
|
|
rpc86
Posting Yak Master
200 Posts |
Posted - 2006-05-22 : 23:40:01
|
| Yes,I followed your advice. I used EXEC sp_renamedb 'MwasLive', 'Mwas_Temp' in Master database. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-05-22 : 23:43:03
|
use sp_who to confirm that no other users is accessing the db
KH |
|
|
|
rpc86
Posting Yak Master
200 Posts |
Posted - 2006-05-22 : 23:43:49
|
| I also use that. sp_whosp_who2but only one computer is connected, the server itself. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-05-22 : 23:46:20
|
but is there any users / connections accessing the MwasLive DB ?And do you have Enterprise Manager running accessing the DB ?
KH |
|
|
|
rpc86
Posting Yak Master
200 Posts |
Posted - 2006-05-22 : 23:48:42
|
None, sir.The query analyser keep on sending us the error message:A member of the sysadmin role must set database 'MwasLive' to single user mode with sp_dboption before it can be renamed |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-05-22 : 23:51:45
|
are you login with user with admin rights ?
KH |
|
|
|
rpc86
Posting Yak Master
200 Posts |
Posted - 2006-05-22 : 23:53:16
|
| No sir, only our DBA is connected with the SQL Server 7. No one is connected now, but only him. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-22 : 23:54:55
|
| do you have sysadmin privileges?under database properties, options, restrict access, single useror run the sp_dboption as advisedHTH--------------------keeping it simple... |
|
|
|
rpc86
Posting Yak Master
200 Posts |
Posted - 2006-05-22 : 23:56:35
|
| I've also triedexec sp_dboption 'Mwaslive','single user'but the same error message returns. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-05-22 : 23:58:20
|
From Books On Linequote:
PermissionsOnly members of the sysadmin and dbcreator fixed server roles can execute sp_renamedb
Ask your DBA to rename the DB.
KH |
|
|
|
rpc86
Posting Yak Master
200 Posts |
Posted - 2006-05-23 : 00:00:56
|
| Khtan,This is what our problem, on how to rename the DB, that is why i'm asking help. The prblem is we cannot rename our db. |
|
|
|
rpc86
Posting Yak Master
200 Posts |
Posted - 2006-05-23 : 00:03:23
|
| Yes,the Enterprise Manager for our DBA is running. |
|
|
|
rpc86
Posting Yak Master
200 Posts |
Posted - 2006-05-23 : 00:08:13
|
After I ran the exec sp_dboption 'Mwaslive','single user' The message is :Optionname Setting----------- -------Single_User Off What does it mean? |
|
|
|
rpc86
Posting Yak Master
200 Posts |
Posted - 2006-05-23 : 00:24:42
|
| Finally, we solved it.using Jen's suggestion to click the single user option.Many many thanks to all of you guys |
|
|
|
DMcCallie
Yak Posting Veteran
62 Posts |
Posted - 2006-05-23 : 10:27:46
|
| Detach the DB and attach it back with a new name... see sp_detach_db and sp_attach_db in Books Online or use EM in SQL/2000... |
|
|
|
Next Page
|
Rename Database