Moving SQL DB to new server with different DB name

Hi,
Sql 2000 sp3
W2k3
I want to move a SQL db to a new server. However I don't wish to keep the
same name of the DB on the new SQL server. I have successfully backed up and
restored the DB from one SQL server to another, as long as the details are
the same, i.e. same db name, log file ect. Is it possible to create a new DB
on a sever with a different name and use the data from the existing DB? IS so
what are the steps I need to follow.
Cheers
Richard
All you have to do is give it a different name while restoring.. Its that
simple.. Whatever your using for same name DB Restore, just specify a
different db name
such as Restore database < New DB Name >... < everything else similar to the
one youve used for same name db >
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:6FFB6F2E-614B-412B-A560-6B9D4BBBEEBF@.microsoft.com...
> Hi,
> Sql 2000 sp3
> W2k3
> I want to move a SQL db to a new server. However I don't wish to keep the
> same name of the DB on the new SQL server. I have successfully backed up
and
> restored the DB from one SQL server to another, as long as the details are
> the same, i.e. same db name, log file ect. Is it possible to create a new
DB
> on a sever with a different name and use the data from the existing DB? IS
so
> what are the steps I need to follow.
> Cheers
> Richard
>
|||alternatively and if the database is now on the machine you could use
sp_renamedb.
regards,
Mark Baekdal
http://www.dbghost.com
+44 (0)208 241 1762
Database change management for SQL Server
"Hassan" wrote:

> All you have to do is give it a different name while restoring.. Its that
> simple.. Whatever your using for same name DB Restore, just specify a
> different db name
> such as Restore database < New DB Name >... < everything else similar to the
> one youve used for same name db >
> "Richard" <Richard@.discussions.microsoft.com> wrote in message
> news:6FFB6F2E-614B-412B-A560-6B9D4BBBEEBF@.microsoft.com...
> and
> DB
> so
>
>
|||When moving a database to a new server in this manner, I have often had to
run sp_SidMap to straighten out the permissions. Otherwise the permissions
appear to be properly assigned, but you will get an access error when trying
to attach to the database
"mark baekdal" wrote:
[vbcol=seagreen]
> alternatively and if the database is now on the machine you could use
> sp_renamedb.
>
> regards,
> Mark Baekdal
> http://www.dbghost.com
> +44 (0)208 241 1762
> Database change management for SQL Server
>
> "Hassan" wrote:
|||You can also detach the db files XXX.mdf and XXX.ldf, copy this two files to the new server and attach the files under a different name.
Dietmar Flick
Message posted via http://www.sqlmonster.com
|||Cheers for the help.
Can you please outline the steps or point in the right direction to an
article as I receive error messages every time I try the process. These are
the steps that i have tried.
1) Create backup of DB on existing server (T621-ITFOL)
2) Copy backup of db from old server to new server
3) Create new DB on new server with new names for mdf file and log file
(test2)
4) Right click on db (test2) - all tasks - restore db
5) select from device, add device and point to backup of DB (T621-ITFOL)
6) Change both the logical file name and physical file name to correspond to
new db
7) Select force restore over existing DB
This is the error message I receive
(ODBC SQLSTATE:42000"
Logical file 'test2_data' is not part of database 'test2'. Use RESTORE
FILELISTONLY to list logical file names. RESTORE DATABASE is terminating
abnormally.
I have not had to do this before and don't have much SQL experience so
please provide as much details as possible.
Thanks in advance for your help.
Richard
"Hassan" wrote:

> All you have to do is give it a different name while restoring.. Its that
> simple.. Whatever your using for same name DB Restore, just specify a
> different db name
> such as Restore database < New DB Name >... < everything else similar to the
> one youve used for same name db >
> "Richard" <Richard@.discussions.microsoft.com> wrote in message
> news:6FFB6F2E-614B-412B-A560-6B9D4BBBEEBF@.microsoft.com...
> and
> DB
> so
>
>
|||The steps you describe will work if you make 1 modification and take one ome
step.
The modification is in (6) don't change the logical name.
The extra step is after the database is restored change the logical name
with the following syntax:
USE master
go
ALTER DATABASE XXX MODIFY FILE
( NAME='XX_old Name',NEWNAME='XX_New Name' )
go
HTH,
Joe
"Richard" wrote:
[vbcol=seagreen]
> Cheers for the help.
> Can you please outline the steps or point in the right direction to an
> article as I receive error messages every time I try the process. These are
> the steps that i have tried.
> 1) Create backup of DB on existing server (T621-ITFOL)
> 2) Copy backup of db from old server to new server
> 3) Create new DB on new server with new names for mdf file and log file
> (test2)
> 4) Right click on db (test2) - all tasks - restore db
> 5) select from device, add device and point to backup of DB (T621-ITFOL)
> 6) Change both the logical file name and physical file name to correspond to
> new db
> 7) Select force restore over existing DB
> This is the error message I receive
> (ODBC SQLSTATE:42000"
> Logical file 'test2_data' is not part of database 'test2'. Use RESTORE
> FILELISTONLY to list logical file names. RESTORE DATABASE is terminating
> abnormally.
> I have not had to do this before and don't have much SQL experience so
> please provide as much details as possible.
> Thanks in advance for your help.
> Richard
> "Hassan" wrote: