Easiest way to move database from 1 machine to another ?

I am not a DBA, so pardon me if my question is basic.
What is the easiest way to move SQL Server 2005 database from 1 machine to
another ?
If I backup the database, I can not restore it in another database or in
another machine, can I ?
Is exporting the database the easiest way to move it to another machine (in
the new machine before importing the data I will still need to create the
database and run the database script to create all the tables and stored
procedures and views, right ?) ?
Thank you.
fniles,
Yes, restoring a backup to another server is a very easy way to move a
database from 1 machine to another. If you no longer want the database on
machine 1, detach the database, move the files (mdf & ldf( to the new
server, then attach them.
You will need to deal with any issues raised by logins not existing on both
servers, if that turns out to be the case for you.
RLF
"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>
|||Backups are easy, however, if you are using differently physically
configured machines that make the backup incompatible, you may want to
look at Microsoft SQL Server Management Studio and first scripting the
database and table definitions (in 2005, right click the database name
or table name and select Script As->Create To File) to generate SQL
scripts that recreate the database and table structures (less the
data) and then running the SQL Scripts in SQL Server Management Studio
on the target machine.
After doing this, you can move the data over by creating import/export
packages in SQL Server Management Studio by right clicking the
database the table is in and selecting tasks Export Data on the source
machine, and tasks Import Data on the target machine. Select
Microsoft Excel file format as the way to transport the data between
the machines if they cannot be connected to each other via a network
cable.
|||Two easy ways:
1) Backup and restore. Backup database and restore it on another instance.
2) Detach and attach. Detach database, copy all database files to another
instance and attach database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"fniles" wrote:

> I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine (in
> the new machine before importing the data I will still need to create the
> database and run the database script to create all the tables and stored
> procedures and views, right ?) ?
> Thank you.
>
>
|||For me, the easiest way to move databases from one machine to the other is
have a good documentation to back it up. Some pointers
1) Service pack levels and hotfixes should be the same on both source and
target
2) If you want the backup/restore to be easy and straightforward, make sure
that you have the same disk configurations. You can also do a detach/attach
approach
3) No need to move tempdb nor model databases
"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>
|||"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...

>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?

> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
sure you can; make sure you detach the DB first (after backup of course) ...
you might have to change the location of the physical files in the restore
dialogue options
or you can use the "Copy Database" facility in SQL Server Management Studio
(select DB, right click for menu; Tasks->Copy Database
or ... there are other options still ... but those are likely the easiest
ones

> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>
|||Thank you all for the replies.
I still want the original database on machine A, I just want another copy of
it in another machine.
So I backup the original database (say it's called MYDB) on machine A.
I then copied the MYDB.bak file from machine A to machine B.
In machineB, I then created a database called MYDB. Then I right click on
the database and select "Restore". Under "Specify source and location of
backup sets to restore" I selected "From device" and I pointed it to the
MYDB.bak in machine B.
I then got the error "Restore failed for server "serverB"
System.data.SqlClient.sqlerror: The backup set holds a backup of a database
other than the existing "MYDB" database. (microsoft.sqlserver.smo)
What did I do wrong on the above steps ?
Since I still need the original database and still using it, can I detach it
after backing it up ?
When I tried to copy the database from machine A to machine B, it works when
I selected "Use the detach and attach method" but when I selected "Use the
SQL Management Object method", I got an error with a view about an invalid
table name HistTradesOrig, which exists in the database, and I am able to
run the query fine.
Event Name: OnError
Message: ERROR : errorCode=-1073548784 description=Executing the query "
Create view [dbo].[Commission] AS
SELECT HistTradesOrig.email, Sum([Quantity]) AS Quan, HistTradesOrig.Status
FROM HistTradesOrig
GROUP BY HistTradesOrig.email, HistTradesOrig.Status
HAVING (((HistTradesOrig.Status)='F'))
" failed with the following error: "Invalid object name 'HistTradesOrig'.".
Possible failure reasons: Problems with the query, "ResultSet" property not
set correctly, parameters not set correctly, or connection not established
correctly.
helpFile= helpContext=0
idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
StackTrace: at
Microsoft.SqlServer.Management.Dts.DtsTransferProv ider.ExecuteTransfer()
at Microsoft.SqlServer.Management.Smo.Transfer.Transf erData()
at
Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask. TransferObjectsTask.TransferDatabasesUsingSMOTrans fer()
Operator: PFGBEST\sqladm
Thank you.
"Liz" <liz@.tiredofspam.com> wrote in message
news:eaGlygxMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> "fniles" <fniles@.pfmail.com> wrote in message
> news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>
>
> sure you can; make sure you detach the DB first (after backup of course)
> ... you might have to change the location of the physical files in the
> restore dialogue options
> or you can use the "Copy Database" facility in SQL Server Management
> Studio (select DB, right click for menu; Tasks->Copy Database
> or ... there are other options still ... but those are likely the easiest
> ones
>
>
|||Did you solve this issue? If not, try going to tab 2 of the restore db page,
and clicking Overwrite Existing Database. That should work.
John
"fniles" wrote:

> Thank you all for the replies.
> I still want the original database on machine A, I just want another copy of
> it in another machine.
> So I backup the original database (say it's called MYDB) on machine A.
> I then copied the MYDB.bak file from machine A to machine B.
> In machineB, I then created a database called MYDB. Then I right click on
> the database and select "Restore". Under "Specify source and location of
> backup sets to restore" I selected "From device" and I pointed it to the
> MYDB.bak in machine B.
> I then got the error "Restore failed for server "serverB"
> System.data.SqlClient.sqlerror: The backup set holds a backup of a database
> other than the existing "MYDB" database. (microsoft.sqlserver.smo)
> What did I do wrong on the above steps ?
> Since I still need the original database and still using it, can I detach it
> after backing it up ?
> When I tried to copy the database from machine A to machine B, it works when
> I selected "Use the detach and attach method" but when I selected "Use the
> SQL Management Object method", I got an error with a view about an invalid
> table name HistTradesOrig, which exists in the database, and I am able to
> run the query fine.
> Event Name: OnError
> Message: ERROR : errorCode=-1073548784 description=Executing the query "
> Create view [dbo].[Commission] AS
> SELECT HistTradesOrig.email, Sum([Quantity]) AS Quan, HistTradesOrig.Status
> FROM HistTradesOrig
> GROUP BY HistTradesOrig.email, HistTradesOrig.Status
> HAVING (((HistTradesOrig.Status)='F'))
> " failed with the following error: "Invalid object name 'HistTradesOrig'.".
> Possible failure reasons: Problems with the query, "ResultSet" property not
> set correctly, parameters not set correctly, or connection not established
> correctly.
> helpFile= helpContext=0
> idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
> StackTrace: at
> Microsoft.SqlServer.Management.Dts.DtsTransferProv ider.ExecuteTransfer()
> at Microsoft.SqlServer.Management.Smo.Transfer.Transf erData()
> at
> Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask. TransferObjectsTask.TransferDatabasesUsingSMOTrans fer()
> Operator: PFGBEST\sqladm
> Thank you.
> "Liz" <liz@.tiredofspam.com> wrote in message
> news:eaGlygxMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>
>