Renaming a database

I've set my database to single user mode,and run:
sp_rename 'oldname' 'newname'
which works just fine.
Now I want to rename the oldname_data.mdf to
newname_data.mdf
To do that, I'm trying to detach the database, then go to
Explorer, rename the file and then reattach the database.
sp_detach_db 'newname'
returns an error:
Server: Msg 3702, Level 16, State 1, Line 1
Cannot drop the database 'newname' because it is currently
in use.
EXEC SQL DISCONNECT newname <or> 'newname'
returns an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'newname'.
How can I determine what has the database "in use" and
break that connection? (I'm on a 7.0 box in this case)
TIA MikeFirst open a query analyzer and connect to the SQL Server
in question. Then run this command:
SP_HOW2
search in the result and find any spid that currently
connected to the database and use this command ti kill
those users:
KILL <spid number>
That sould resolve your problem.
This posting is provided "AS IS" with no warranties, and
confers no rights.
http://www.microsoft.com/info/cpyright.htm
>--Original Message--
>I've set my database to single user mode,and run:
>sp_rename 'oldname' 'newname'
>which works just fine.
>Now I want to rename the oldname_data.mdf to
>newname_data.mdf
>To do that, I'm trying to detach the database, then go to
>Explorer, rename the file and then reattach the database.
>sp_detach_db 'newname'
>returns an error:
>Server: Msg 3702, Level 16, State 1, Line 1
>Cannot drop the database 'newname' because it is
currently
>in use.
>EXEC SQL DISCONNECT newname <or> 'newname'
>returns an error:
>Server: Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near 'newname'.
>How can I determine what has the database "in use" and
>break that connection? (I'm on a 7.0 box in this case)
>TIA Mike
>.
>|||I guess I'm not the only one with typo's today:) I think
that should be SP_WHO2
Vern
>--Original Message--
>First open a query analyzer and connect to the SQL Server
>in question. Then run this command:
>SP_HOW2
>search in the result and find any spid that currently
>connected to the database and use this command ti kill
>those users:
>KILL <spid number>
>That sould resolve your problem.
>This posting is provided "AS IS" with no warranties, and
>confers no rights.
>http://www.microsoft.com/info/cpyright.htm
>
>>--Original Message--
>>I've set my database to single user mode,and run:
>>sp_rename 'oldname' 'newname'
>>which works just fine.
>>Now I want to rename the oldname_data.mdf to
>>newname_data.mdf
>>To do that, I'm trying to detach the database, then go
to
>>Explorer, rename the file and then reattach the database.
>>sp_detach_db 'newname'
>>returns an error:
>>Server: Msg 3702, Level 16, State 1, Line 1
>>Cannot drop the database 'newname' because it is
>currently
>>in use.
>>EXEC SQL DISCONNECT newname <or> 'newname'
>>returns an error:
>>Server: Msg 170, Level 15, State 1, Line 1
>>Line 1: Incorrect syntax near 'newname'.
>>How can I determine what has the database "in use" and
>>break that connection? (I'm on a 7.0 box in this case)
>>TIA Mike
>>.
>.
>|||That is correct ;-) it is SP_WHO2.
Thanks for correction.
This posting is provided "AS IS" with no warranties, and
confers no rights.
http://www.microsoft.com/info/cpyright.htm
>--Original Message--
>I guess I'm not the only one with typo's today:) I think
>that should be SP_WHO2
>Vern
>>--Original Message--
>>First open a query analyzer and connect to the SQL
Server
>>in question. Then run this command:
>>SP_HOW2
>>search in the result and find any spid that currently
>>connected to the database and use this command ti kill
>>those users:
>>KILL <spid number>
>>That sould resolve your problem.
>>This posting is provided "AS IS" with no warranties, and
>>confers no rights.
>>http://www.microsoft.com/info/cpyright.htm
>>
>>--Original Message--
>>I've set my database to single user mode,and run:
>>sp_rename 'oldname' 'newname'
>>which works just fine.
>>Now I want to rename the oldname_data.mdf to
>>newname_data.mdf
>>To do that, I'm trying to detach the database, then go
>to
>>Explorer, rename the file and then reattach the
database.
>>sp_detach_db 'newname'
>>returns an error:
>>Server: Msg 3702, Level 16, State 1, Line 1
>>Cannot drop the database 'newname' because it is
>>currently
>>in use.
>>EXEC SQL DISCONNECT newname <or> 'newname'
>>returns an error:
>>Server: Msg 170, Level 15, State 1, Line 1
>>Line 1: Incorrect syntax near 'newname'.
>>How can I determine what has the database "in use" and
>>break that connection? (I'm on a 7.0 box in this case)
>>TIA Mike
>>.
>>.
>.
>

Rename database

I have set the database to single user mode and want to rename the database.
However, it shows the following message:
To change the NAME, the database must be in state in which a checkpoint can
be executed.
I have restart the sql server service but it still shows the above message.
What should I do?
Best Regards,
IvanHi Ivan
I get a different error if I am not connected to the single user database in
SQL 2005, but I can rename a database in single user mode if I am connected
to it or if no-one else is connected to it.
I assume you are using SQL 2000? Is anyone else connected to the database?
John
"Ivan" wrote:

> I have set the database to single user mode and want to rename the databas
e.
> However, it shows the following message:
> To change the NAME, the database must be in state in which a checkpoint ca
n
> be executed.
> I have restart the sql server service but it still shows the above message
.
> What should I do?
> Best Regards,
> Ivan
>
>|||In SQL2k with single user I can rename database easily.
Please check other settings also.
Regards
Amish Shah|||The database is in Single User mode and I have restart the SQL Server 2000
to ensure no one is connect to the database.
However, it still shows that error message.
Ivan
"amish" <shahamishm@.gmail.com>
'?:1147525165.494414.185770@.j33g2000cwa.googlegroups.com...
> In SQL2k with single user I can rename database easily.
> Please check other settings also.
> Regards
> Amish Shah
>|||Is the database READ_ONLY? It must be writable before it can be renamed.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <ivan@.microsoft.com> wrote in message
news:eg3ONfrdGHA.3484@.TK2MSFTNGP04.phx.gbl...
> The database is in Single User mode and I have restart the SQL Server 2000
> to ensure no one is connect to the database.
> However, it still shows that error message.
> Ivan
> "amish" <shahamishm@.gmail.com>
> '?:1147525165.494414.185770@.j33g2000cwa.googlegroups.com...
>|||Hi
If you make the database the current database and then use
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
you should not need to reboot the server.
If you reboot the server and someone connects to the database before you
then you will have a problem.
John
"Ivan" wrote:

> The database is in Single User mode and I have restart the SQL Server 2000
> to ensure no one is connect to the database.
> However, it still shows that error message.
> Ivan
> "amish" <shahamishm@.gmail.com>
> '?:1147525165.494414.185770@.j33g2000cwa.googlegroups.com...
>
>sql

Rename database

I have set the database to single user mode and want to rename the database.
However, it shows the following message:
To change the NAME, the database must be in state in which a checkpoint can
be executed.
I have restart the sql server service but it still shows the above message.
What should I do?
Best Regards,
IvanHi Ivan
I get a different error if I am not connected to the single user database in
SQL 2005, but I can rename a database in single user mode if I am connected
to it or if no-one else is connected to it.
I assume you are using SQL 2000? Is anyone else connected to the database?
John
"Ivan" wrote:
> I have set the database to single user mode and want to rename the database.
> However, it shows the following message:
> To change the NAME, the database must be in state in which a checkpoint can
> be executed.
> I have restart the sql server service but it still shows the above message.
> What should I do?
> Best Regards,
> Ivan
>
>|||In SQL2k with single user I can rename database easily.
Please check other settings also.
Regards
Amish Shah|||The database is in Single User mode and I have restart the SQL Server 2000
to ensure no one is connect to the database.
However, it still shows that error message.
Ivan
"amish" <shahamishm@.gmail.com>
'?:1147525165.494414.185770@.j33g2000cwa.googlegroups.com...
> In SQL2k with single user I can rename database easily.
> Please check other settings also.
> Regards
> Amish Shah
>|||Is the database READ_ONLY? It must be writable before it can be renamed.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <ivan@.microsoft.com> wrote in message
news:eg3ONfrdGHA.3484@.TK2MSFTNGP04.phx.gbl...
> The database is in Single User mode and I have restart the SQL Server 2000
> to ensure no one is connect to the database.
> However, it still shows that error message.
> Ivan
> "amish" <shahamishm@.gmail.com>
> '?:1147525165.494414.185770@.j33g2000cwa.googlegroups.com...
>> In SQL2k with single user I can rename database easily.
>> Please check other settings also.
>> Regards
>> Amish Shah
>|||Hi
If you make the database the current database and then use
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
you should not need to reboot the server.
If you reboot the server and someone connects to the database before you
then you will have a problem.
John
"Ivan" wrote:
> The database is in Single User mode and I have restart the SQL Server 2000
> to ensure no one is connect to the database.
> However, it still shows that error message.
> Ivan
> "amish" <shahamishm@.gmail.com>
> '?:1147525165.494414.185770@.j33g2000cwa.googlegroups.com...
> > In SQL2k with single user I can rename database easily.
> > Please check other settings also.
> >
> > Regards
> > Amish Shah
> >
>
>sql

removing the logs

does anyone know if there is a way to remove transaction
logs applied to database that was restored in a standby
mode ? i want to be able to read data from a database
restored from a full db backup before the tran logs were
applied.
Thanks very much,
NatasaRestores only go forward, not backward. You will have to restore the
databas to an earlier point in time.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"natasa" <anonymous@.discussions.microsoft.com> wrote in message
news:041a01c4dc6f$a4b59260$a601280a@.phx.gbl...
> does anyone know if there is a way to remove transaction
> logs applied to database that was restored in a standby
> mode ? i want to be able to read data from a database
> restored from a full db backup before the tran logs were
> applied.
> Thanks very much,
> Natasa