Moving database from one Server to another

I am sorry for such a basic question.

But i have done a search and the answers dont seem to be relevant in my situation

I have two servers with the same setup where the sql is installed in default C/programme and the db is in D/some folder

So the file structure is the same

Back in SQL 2000 days the transfer was so painless, where I made the DB offline and transferred the main db.mdf and log to the new server and i attached it to the new sql server in the new server.

Now this process does not work. I have tried the old method and it says something like you should do it with Move or something. I transfer the back up and try and restore and it says that it is read only.

Surely things should get easier with sql 2005 or sql Express not more complex. I cannot any documentation for this either as obviously it is such a simple job that documentation does not touch it. So i am sure there is a very simple answer to this.

Many thanks for any help on this.

Salar

Answer this question

Moving database from one Server to another

drhoades32

Many thanks Madhu

Sorry as I may have not been clear

Both servers have SQL Express2005

However

What you said here "(c) REstore the Database with Move Option"

I have seen this on other forums

I dont see it in SQL server managemnet tool - so such option is available in the restore view

I wonder how this is done

Regards

Salar
rp666

Both Detach/attach and Backup /restore method works in SQL Server 2000 to SQL Server 2005 migration... So the question is how did u do this... Have u tried to attach the detached sql 2000 database... if Yes then post the error... It supports in 2005... Then the second question is what was the error when u tried to restore the database... Have u used Move Method.... Just post the script and full error

Madhu
Yasir Imran
One more thing that you have to realize is that the users from one machine will have different SIDs from the other (unless you are only using Windows Authentication with machines being a part of a domain). If not, I recommend that you also migrate the logins. Refer to this documentations for more details http://support.microsoft.com/kb/918992/
GoldRunner

RESTORE DATABASE DNN4Packaged FROM DISK = 'E:\Dreamer\DNN4Packaged\DB\DNN4Packaged_Backup'

WITH MOVE 'DNN4Packaged' TO :\Dreamer\DNN4Packaged\DB\newDNN4Packaged.mdf',

MOVE 'DNN4Packaged_log' TO ':\Dreamer\DNN4Packaged\DB\newDNN4Packaged_log.ldf'

Above statement is Described here

Restored Database Name will be --DNN4Packaged (here is the problem- the name should have NewDNN4Packaged)

Restored Database Logical Datafile Name will be -- DNN4Packaged (run SP_helpfile in query analyser )

Restored Database Logical LOgfile name will be --'DNN4Packaged_log' ( As above)

Restored Database Physical Datafile name will be --newDNN4Packaged.mdf ( go to the physical pholder and see)

Restored Database Physical LogFile name will be -- newDNN4Packaged_log.ldf (go to the physical folder)

And read about Restore /Spfile/Backup in BOL

Madhu
Todd Jaspers
Using the statement@

RESTORE DNN4Packaged FROM DISK will install the database as DNN4Pacakaged and if you use name as NewDNN4Packaged in the place of database name then you will be able to see the new names.
Mark Flamer

Since Copy Database Wizard uses Integration Services to transfer the Databases and SQL Server express does not support Integration Services... this option will not work in your case...

Refer these links :

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

http://msdn2.microsoft.com/en-us/library/ms188664.aspx

So next best option is

(a) Take Backup of SQL Server 2000 DB

(b) Copy the Database backup file to SQL Server Express Machine

(c) REstore the Database with Move Option

Madhu
Sobia

Many tanks for the answer

I have been reading more

I think I should make it clear that this issue is on SQL express and the SQL express management studio

As I think the standard SQL 2005 management studio has a method to do this.

1- Right click the db,

2- Tasks

3- Copy Database

However, in SQL express setup if you do above you will not see the Copy Database option.

So what do I do

Salar
Tom bernard
Many thanks Madhu

It shows that I should really buy a Good SQL 2005 administration book- Any advise is appreciated.

I did this

first I did

RESTORE DNN4Packaged FROM DISK = 'E:\Dreamer\DNN4Packaged\DB\DNN4Packaged_Backup'

Then I run

RESTORE DATABASE DNN4Packaged FROM DISK = 'E:\Dreamer\DNN4Packaged\DB\DNN4Packaged_Backup'

WITH MOVE 'DNN4Packaged' TO :\Dreamer\DNN4Packaged\DB\newDNN4Packaged.mdf',

MOVE 'DNN4Packaged_log' TO ':\Dreamer\DNN4Packaged\DB\newDNN4Packaged_log.ldf'

which seems to have done the job

However, I was expecting to see a new db called newDNN4Packaged in my SQL Server Manager Studio But instead I see DNN4Packaged - so the name has remaind the same - which is fine but I am just not absolutly sure what the script did.

I am sure it will sink in my head tomorrow. :)

Many thanks

Salar
monkzen
Run in SQL Server 2000 Machine to take Backup of the database

backup database SomeDatabase to Disk='D:\SomeDB.bak'

Run in SQL Server 2005 Machine to Restore

RESTORE FILELISTONLY FROM DISK = 'D:\SomeDB.bak'

RESTORE DATABASE SomeDatabase FROM DISK = 'D:\SomeDB.bak'

WITH MOVE 'SomeDatabase' TO 'NewPath\SomeDatabase.mdf',

MOVE 'SomeDatabase_log' TO 'NewPath\SomeDatabase_log.ldf'

Change the path ,filename and database names accordingly

Madhu
Moving database from one Server to another

Answer this question