Restore Database to different server

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

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

Restore Database to different server

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2006-08-02 : 13:27:15

A client sent me a full database backup (SQL Server 2005 Express). How can I restore that database to my SQL Server 2005 Express?

I've tried this...

RESTORE DATABASE JensenMorganLinen
   FROM DISK = 'C:\JensenMorganSQL.bak'
	WITH NORECOVERY,
      MOVE 'Jensenmorganlinen_Data' TO 
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\JensenMorganLinen.mdf', 
      MOVE 'Jensenmorganlinen_Log' 
TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\JensenMorganLinen.ldf'

But the results is this...

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'JensenMorganLinen' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-02 : 15:36:41

Are you restoring over an existing database?

Run RESTORE HEADERONLY and RESTORE FILELISTONLY against the backup file and post the results here.

Tara Kizer

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2006-08-02 : 15:47:23

HeaderOnly:

Full backup of JENSENMorganLinen NULL 1 NULL 0 1 2 MORGANST41\Jenview JENSRV JENSENMorganLinen 611 2006-07-06 10:17:05.000 1996830208 1073149000000040800050 1073150000000122600001 1073149000000040800050 1069742000000040500049 2006-08-01 03:00:02.000 2006-08-01 03:03:29.000 52 0 1033 196609 90 4608 9 0 2047 JENSRV 512 6B2D3BD2-8859-4E26-AF28-21EE4D0B0A04 E24D97FA-9C1D-4284-A87F-D10C9612A1C5 SQL_Latin1_General_CP1_CI_AS 6B2D3BD2-8859-4E26-AF28-21EE4D0B0A04 0 0 0 0 0 0 0 0 0 0 E24D97FA-9C1D-4284-A87F-D10C9612A1C5 NULL SIMPLE NULL NULL Database A827CD0E-82DA-4DD4-8536-AECDCEE7418C

FILELISTONLY

JENSEN_dat D:\SQL DB\JENSEN.mdf D PRIMARY 2087059456 35184372080640 1 0 0 00000000-0000-0000-0000-000000000000 0 0 1995374592 512 1 NULL 1069742000000040500049 348E347A-ECC8-4129-B7F0-1E31F906E800 0 1
JENSEN_log D:\SQL DB\JENSEN.ldf L NULL 3145728 35184372080640 2 0 0 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 1

The database I am trying to restore to is empty, I just created it. So now I just created a new "Jensen" database and tried this...

RESTORE DATABASE Jensen
FROM DISK = 'C:\JensenMorganSQL.bak'
WITH
MOVE 'Jensen_Dat' TO
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Jensen_Dat.mdf',
MOVE 'Jensen_Log'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Jensen_Log.ldf'

I just deleted the existing Jensen database and I think it is working. Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-02 : 15:52:35

You need to specify WITH REPLACE if you already created the database.

Tara Kizer

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2006-08-02 : 16:09:06

Ah, Thanks Tara. It's restored. Now all I have to do is figure out the schema!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources