Renaming Database or files

Hi all, I have a dev database on a dev machine that is the same name as a database on a production server.

I would like to attach the dev database to the production server but I can't because there would be two database with the same name. I tried just renaming my dev .mdf to another name but that doesn't work., I think I need to rename the database files but I don't know how.

Can anyone tell me the solution to this problem?

Thanks

This will rename pubs to pubs2

EXEC sp_renamedb 'pubs', 'pubs2'

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Thanks SQL_Menace, I'll give that a try

Renaming Database Files

How can I go about safely renaming the data and log files for a
database?
Thanks.Hello ExcelMan,
I would go this way:
- Detach the database
- Rename the files
- Attach the database
Ekrem Önsoy
"ExcelMan" <sfarkas@.sjfcg.com> wrote in message
news:1188583811.122857.12230@.g4g2000hsf.googlegroups.com...
> How can I go about safely renaming the data and log files for a
> database?
> Thanks.
>|||On Aug 31, 11:31 am, Ekrem =D6nsoy <ek...@.btegitim.com> wrote:
> Hello ExcelMan,
> I would go this way:
> - Detach the database
> - Rename the files
> - Attach the database
> --
> Ekrem =D6nsoy
> "ExcelMan" <sfar...@.sjfcg.com> wrote in message
> news:1188583811.122857.12230@.g4g2000hsf.googlegroups.com...
>
> > How can I go about safely renaming the data and log files for a
> > database?
> > Thanks.- Hide quoted text -
> - Show quoted text -
That's what I tried and it does not work. The database will not
reattach if you change the names. When I changed them back to what I
started with it did reattach.
Any other ideas?|||Did you specify the new names when you executed sp_attach_db?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"ExcelMan" <sfarkas@.sjfcg.com> wrote in message
news:1188588512.215040.28540@.22g2000hsm.googlegroups.com...
On Aug 31, 11:31 am, Ekrem Önsoy <ek...@.btegitim.com> wrote:
> Hello ExcelMan,
> I would go this way:
> - Detach the database
> - Rename the files
> - Attach the database
> --
> Ekrem Önsoy
> "ExcelMan" <sfar...@.sjfcg.com> wrote in message
> news:1188583811.122857.12230@.g4g2000hsf.googlegroups.com...
>
> > How can I go about safely renaming the data and log files for a
> > database?
> > Thanks.- Hide quoted text -
> - Show quoted text -
That's what I tried and it does not work. The database will not
reattach if you change the names. When I changed them back to what I
started with it did reattach.
Any other ideas?|||There must be something you miss.
Tom could be right. How about trying this process via SSMS?
--
Ekrem Önsoy
"ExcelMan" <sfarkas@.sjfcg.com> wrote in message
news:1188588512.215040.28540@.22g2000hsm.googlegroups.com...
On Aug 31, 11:31 am, Ekrem Önsoy <ek...@.btegitim.com> wrote:
> Hello ExcelMan,
> I would go this way:
> - Detach the database
> - Rename the files
> - Attach the database
> --
> Ekrem Önsoy
> "ExcelMan" <sfar...@.sjfcg.com> wrote in message
> news:1188583811.122857.12230@.g4g2000hsf.googlegroups.com...
>
> > How can I go about safely renaming the data and log files for a
> > database?
> > Thanks.- Hide quoted text -
> - Show quoted text -
That's what I tried and it does not work. The database will not
reattach if you change the names. When I changed them back to what I
started with it did reattach.
Any other ideas?|||Logical or physical ones?
Whatever you try, first make a full backup.
See "alter database" statement in BOL. One way could be doing a backup and
restore it using options "replace" and "move".
create database test
go
select [name], physical_name
from sys.master_files
where database_id = db_id('test')
go
backup database test
to disk = 'c:\temp\test.bak'
go
alter database test
set single_user with rollback immediate
go
restore database test
from disk = 'c:\temp\test.bak'
with replace,
move 'test' to 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\test1.mdf',
move 'test_log' to 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\test1_log.ldf'
go
alter database test
modify file (name = 'test', newname = 'test1')
go
alter database test
modify file (name = 'test_log', newname = 'test1_log')
go
select [name], physical_name
from sys.master_files
where database_id = db_id('test')
go
drop database test
go
I played a little bit trying to change the physical file names usainf "alter
database" but there is a msg saying:
"The new path will be used the next time the database is started."
but I do not know how to start the db programatically. I tried setting it to
offline and then online but it fails. Here is the script.
use master
go
create database test
go
select [name], physical_name
from sys.master_files
where database_id = db_id('test')
go
alter database test
set single_user with rollback immediate
go
alter database test
modify file (name = 'test', newname = 'test1')
go
alter database test
modify file (name = 'test_log', newname = 'test1_log')
go
alter database test
modify file (name = 'test1', filename = 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\test1.mdf')
go
alter database test
modify file (name = 'test1_log', filename = 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\test1_log.mdf')
go
alter database test
set online
go
select [name], physical_name
from sys.master_files
where database_id = db_id('test')
go
alter database test
set multi_user
go
drop database test
go
AMB
"ExcelMan" wrote:
> How can I go about safely renaming the data and log files for a
> database?
> Thanks.
>

Renaming database after log shipping.

In order to move from one data center to another, I am planning to use log shipping.
Lets say,

Current Data center - DC1
Current Server- S1
Current Database- DB1

New Data Center -DC2
New Server -S2

Now, on the New Server S2, I already restored DB1 as DB1 for QA testing. QA has to do some testing before we move from DC1 to DC2. In the mean time, I need to start log shipping from S1 to S2, so that we can fail-over to S2. Now since DB1 database is already present on S2, I can not use the same name. I will have to use some other name (say DB1_New).
I can not wait for QA to finish their testing. I have to start log shipping before that. When QA is done, I will delete the DB1.

So for this log shipping, -

Primary - S1- DB1
Secondary - S2 DB1_New

So when we fail-over, we will have the database name as DB1_New. But all the apps use the name DB1. So I will have to rename the database as DB1.

So want to know, will this renaming the database (DB1_New to DB1) cause any issues?

Anyone had similar experience?

Thanks

Generally, you would be okay - some of that depends on how you have things configured, how long you are going to ship the logs before failing over to the other site, if logins/users have changed. You would want to consider what jobs you have running on the server, how this impacts your fail over plans and backups, anything pointing to the DB_new database name would need to be evaluated, etc. After the fail over and rename, you'd probably want to do a full backup of your databases, make sure the users are all intact, etc.

-Sue

|||

hi,

even if u have the same dbs in both source and detination prior to configuring log shipping, u need not create a new db in secondary instead u can drop the db in secondary......and while configuring log shipping u have the option to create the db @.destination if the db doesnt exist there already........if the failover is done properly all users from primary will be able to connect.....ensure that wherever u specified the db1 name in application change it to db1_new in your scenario........

Renaming Database

I got an MSSQL database which I'm trying to export to MySQL using DTS, the name of the database is MyDatabase.com and when I try to export the tables I get the following error:

Error Source: Microsoft OLE DB Provider for SQL Server

Error Description: Unspecified error
Could not find server 'MyDatabase.com' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

Context: Error calling GetRowset to get DBSCHEMA_TABLES schema info. Your provider does not support all the schema required by DTS.

My guess is that since the database name ends with .com DTS is thinking that it's a server and not a db..... I got 2 databases that end with .com and a few others that don't, if I try to export any of the ones that end with .com I get the same error, but if I try to export another one it works just fine... My question is, is there any way I can rename the database to something else or is there any other way around this problem..?

Thanks in advance,
Fernansp_renamedb will rename a database.

Have you tried [MyDatabase.com]sql

renaming database

I tried to renaming database using query analyser of sql
server 2000 but I got this error msg.
Server: Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform
the operation.
My sysntax was
sp_renamedb 'old_dbname', 'new_dbname'
How to handle this? what could be the solution?
Thanx
Dave
hi dave,
make sure no-one else is connected to the database, including you.
you can try:
use master
go
exec sp_renamedb ..........
--or (if sql 2000) you can try
alter database old_Database_name modify name = new_database_name
--if, still you get errors you can run following command,to disconnect other
users:
--note:it will rollback existing transaction
alter database <db_name> set single_user with rollback IMMEDIATE
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||still same proble, bro...
dave
>--Original Message--
>hi dave,
>make sure no-one else is connected to the database,
including you.
>you can try:
>use master
>go
>exec sp_renamedb ..........
>--or (if sql 2000) you can try
>alter database old_Database_name modify name =
new_database_name
>--if, still you get errors you can run following
command,to disconnect other
>users:
>--note:it will rollback existing transaction
>alter database <db_name> set single_user with rollback
IMMEDIATE
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>.
>
|||Try this. Stop the SQL Server from SQL Server Service Manager and Start
again, and then try to rename it.
Rohtash Kapoor
http://www.sqlmantra.com
"dave" <anonymous@.discussions.microsoft.com> wrote in message
news:28f5f01c4656d$493c0f50$a601280a@.phx.gbl...[vbcol=seagreen]
> still same proble, bro...
> dave
> including you.
> new_database_name
> command,to disconnect other
> IMMEDIATE
|||Hi,
Follow the below steps to rename a database in SQL server 2000.
1. Remove all the users connected to the database
alter database <dbname> set single_user with rollback immediate
2. Run the below command to rename the database
alter database <current_db_name> modify name=<new_db_name>
3. Set to multi user mode
alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"dave" <anonymous@.discussions.microsoft.com> wrote in message
news:28e1e01c4654c$a8962ab0$a601280a@.phx.gbl...
> I tried to renaming database using query analyser of sql
> server 2000 but I got this error msg.
> Server: Msg 5030, Level 16, State 2, Line 1
> The database could not be exclusively locked to perform
> the operation.
> My sysntax was
> sp_renamedb 'old_dbname', 'new_dbname'
> How to handle this? what could be the solution?
> Thanx
> Dave
|||all you can do is restart the sql server service, which will clear out all
the connection to the db, and run
use master
go
exec sp_renamedb --
--or (if sql 2000) you can try
alter database old_Database_name modify name = new_database_name
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||You could detach the database and then re-attach it with a different
name.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Renaming Data Source view giving error

Hi,

After I renamed my data source view in BIDS (SSAS ServicePack1), I now get an error when I try to build my SSAS project:

Error 1 Dimension 'Date' : The 'Source' property references unknown 'Data Source View' with ID='XXXX'. 0 0

I tried to change this directly in the XMLA for Date.dim file, but it still doesn't work.

Any pointer\workarounds anyone? I would hate to have to recreate by dims again, just for this.

Thanks,

JGP

Anyone from MSFT can comment on this?

Thanks,

JGP

Renaming Data Source view giving error

Hi,

After I renamed my data source view in BIDS (SSAS ServicePack1), I now get an error when I try to build my SSAS project:

Error 1 Dimension 'Date' : The 'Source' property references unknown 'Data Source View' with ID='XXXX'. 0 0

I tried to change this directly in the XMLA for Date.dim file, but it still doesn't work.

Any pointer\workarounds anyone? I would hate to have to recreate by dims again, just for this.

Thanks,

JGP

Anyone from MSFT can comment on this?

Thanks,

JGP