Moving AdventureWorks database to another instance

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Moving AdventureWorks database to another instance

Author

Topic

petey84
Starting Member

14 Posts

Posted - 2010-11-19 : 14:24:40

Hi,

I have a PC with sql server 2005 and managment studio installed on with the AdventureWorks database.

I also have a laptop with sql server 2005 and managment studio installed.

I have done a lot of work on the AdventureWork database which I would like to move to my laptop sql server.

I have tried to detach the database from my desktop and attach it to sql server on my laptop, but I am getting the following error:

Error 5171

Can someone please help. Is there something else I would have to change like header because it is a different instance I am putting the database on?

Please instruct.

Thanks
Pete

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-19 : 14:29:22

Could you post the text of error 5171?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

petey84
Starting Member

14 Posts

Posted - 2010-11-19 : 19:46:01

quote:
Originally posted by tkizer

Could you post the text of error 5171?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Hi,

I get the following error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf is not a primary database file. (Microsoft SQL Server, Error: 5171)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=5171&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-19 : 19:47:18

Did you detach the database or did you just copy the file?

If that doesn't work, then please try backup/restore.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

petey84
Starting Member

14 Posts

Posted - 2010-11-19 : 20:03:59

quote:
Originally posted by tkizer

Did you detach the database or did you just copy the file?

If that doesn't work, then please try backup/restore.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Hi,

Is this not the correct way for moving a database to two different instances.

I've detached the database from the instance on my Desktop PC and copied the files to my laptop, which I then tried to attach to my laptop instance, thats when I get the error.

What does the error mean?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-19 : 23:26:41

You followed the correct procedure. I am not sure why you are getting that error. I'd suggest trying BACKUP/RESTORE.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

petey84
Starting Member

14 Posts

Posted - 2010-11-20 : 00:18:52

quote:
Originally posted by tkizer

You followed the correct procedure. I am not sure why you are getting that error. I'd suggest trying BACKUP/RESTORE.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

The name of he instance on my Desktop PC is different to my Laptop instance name, could this be the reason?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-20 : 16:36:17

No, the instance names don't matter to detach/attach or backup/restore.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

petey84
Starting Member

14 Posts

Posted - 2010-11-25 : 14:08:57

quote:
Originally posted by tkizer

No, the instance names don't matter to detach/attach or backup/restore.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SO should I try to backup the original database and restore it on my laptop instance?

Is that what your suggesting?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-25 : 14:11:01

Yes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources