copy a SQL Express DB and load into SQL Standard

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

SQL Server Administration (2005)

copy a SQL Express DB and load into SQL Standard

Author

Topic

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-05 : 14:53:16

I am working with someone in a different state. He is using SQL Express, I am using SQL standard. He has created a large DB that i need a copy of on my machine.

Is there a way for him to make a copy of his DB so it can be placed on our FTP site for me to download?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-05 : 14:56:21

Yes. Have him backup the database, send the file, then you can perform a restore.

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

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-05 : 15:12:17

I tried that initally but got an error and thopught i war using the wrong approach. What i did is this...

1. He created and zipped a backup of the table he wanted to send.
2. I created a new DB on my machine. Then right clicked on it and selected Task, Restore, Files and File groups...
3. In the destination i entered the DB I just created.
4. Under source to restore i selectedFromdevice and clicked the browse button.
5. In the next dialof i pressed the add button and navigated to the .bak file I just unzipped. The file was add to the window.
In the Select back up sets to restore i selected the one entry.
6. When i prssed OK I get microsoft SQL error 3154 "THe backup holds the backup of a database other than the existing 'PhyPro' database" 'PhyPro' is the name of teh DB I created.

What shouls we be doing differently?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-05 : 15:14:24

The paths are probably different on his machine and yours, so when you do the restore, go to the Options page to change where the MDF and LDF should be located. You can also do this using the WITH MOVE option of the RESTORE DATABASE command.

Also, if you already have a database named PhyPro, make sure to select the option to overwrite on the Options page. This is equivalent to the WITH REPLACE option in the RESTORE DATABASE command.

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

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-05 : 15:27:33

On the options page I selected the two files and navigated to the .mdf and .ldf files on my machine. And i did select overwrite. I am getting ther exact same error. Any other suggestions?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-05 : 15:30:06

Use RESTORE DATABASE command instead of the GUI then. You'll need the WITH MOVE and REPLACE options.

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

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-05 : 15:50:33

I am new to SQL. does this look like the right syntax?

RESTORE DATABASE
{PhyProDB}
From 'C:\FTP HDS Data\PhyProOct2007Backup\PhyProDB.bak'
With Move 'PhyProDB' to 'PhyProDB'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-05 : 15:58:31

No.

You'll first need to get the logical names via RESTORE FILELISTONLY as you'll need those in the WITH MOVE option.

Here's what a sample restore command looks like on one of my systems:

RESTORE DATABASE GT
FROM DISK = 'I:\Backup\GT\GT.BAK'
WITH MOVE 'GT_Data' TO 'I:\Data\GT_Data.MDF', MOVE 'GT_Log' TO 'I:\Log\GT_Log.LDF', REPLACE

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

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-05 : 16:17:28

I thought it would make things easer if I moved the .bak file into the backup folder where the data folder is. I tryed executing:

RESTORE FILELISTONLY
FROM PhyProDB

I get the error:
No entry in sysdevices for backup device 'PhyProDB'. Update sysdevices and rerun statement.

What have i messed up here?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-05 : 16:22:44

Moving the backup into a different directory does nothing to fix your problem.

You'd don't have the syntax right of that command. Look it up in BOL.

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

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-05 : 16:27:57

I was using http://msdn2.microsoft.com/en-us/library/ms190255.aspx as my example. They used

RESTORE FILELISTONLY
FROM MyNwind_1

DId the BOL leave something out that the novice needs to know?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-05 : 16:33:03

You need to use FROM DISK with your backup file to get the information that we need.

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

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-05 : 16:51:07

OK. I got the FILELISTONLY to work:

RESTORE FILELISTONLY FROM disk = 'PhyProDB.bak'

With that information i am back to the RESTORE DATABASE command.

RESTORE DATABASE PhyProDB FROM DISK = 'PhyProDB.bak'
WITH MOVE 'C:\Program Files\Microsoft SQL Server\DataFiles\PhyProDB.mdf' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\PhyProDB.mdf',
MOVE 'C:\Program Files\Microsoft SQL Server\LogFiles\PhyProDB_log.ldf' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\PhyProDB.ldf'

When I execute this i get this error:

RESTORE cannot process database 'PhyProDB' because it is in use by this session. It is recommended that the master database be used when performing this operation.

I don't have any other windows open in management studio. what could it think is using the table?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-05 : 16:55:12

You need to make sure that you aren't using the database even in SSMS. Change the dropdown to master or some other database. If it still errors, then you need to figure out who is connected, then kill those connections.

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

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-05 : 17:26:23

I switched the daatabase to master. That took care of the in use problem. :)

When I ran the command again. I get this error:

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

When I ran the FILELISTONLY two rows were returned containing the .mdf and .ldf files. What else could it be seeing?

Haywood
Posting Yak Master

221 Posts

Posted - 2007-10-05 : 17:28:31

Run RESTORE HEADERONLY and see what backup sets are on the backup file.

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-05 : 17:33:30

Returns one row. These are the first few columns.

PhyProDB-Full Database Backup, NULL, 1, NULL, 0, 1

Is there something i should be looking for?

Haywood
Posting Yak Master

221 Posts

Posted - 2007-10-05 : 17:37:54

Hmm, defenitly odd.

I may see what the problem is though... In the restore script you provided above, you don't give the physical path to the backup file. You have to give it a fully qualified path.

You have:

RESTORE DATABASE PhyProDB FROM DISK = 'PhyProDB.bak'
WITH MOVE 'C:\Program Files\Microsoft SQL Server\DataFiles\PhyProDB.mdf' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\PhyProDB.mdf',
MOVE 'C:\Program Files\Microsoft SQL Server\LogFiles\PhyProDB_log.ldf' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\PhyProDB.ldf'

And it needs to be:

RESTORE DATABASE PhyProDB FROM DISK = 'X:\SOMEPATH\PhyProDB.bak'
WITH MOVE 'C:\Program Files\Microsoft SQL Server\DataFiles\PhyProDB.mdf' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\PhyProDB.mdf',
MOVE 'C:\Program Files\Microsoft SQL Server\LogFiles\PhyProDB_log.ldf' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\PhyProDB.ldf'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-05 : 17:55:28

Also include REPLACE in the WITH MOVE option like in my example, just in case you have an existing container already there.

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

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-05 : 17:56:01

Here is the command i used:

RESTORE DATABASE PhyProDB FROM DISK = 'C:\FTP HDS Data\PhyProOct2007Backup\PhyProDB.bak'
WITH MOVE 'C:\Program Files\Microsoft SQL Server\DataFiles\PhyProDB.mdf' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\PhyProDB.mdf',
MOVE 'C:\Program Files\Microsoft SQL Server\LogFiles\PhyProDB_log.ldf' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\PhyProDB.ldf'

Got the same error message:

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

You have gotten me so close I can almost taste it. Is there something about the way i created my nwe DB? Can I delete the DB I created and have SQL create teh entire DB from the restore command?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-05 : 18:02:23

See my last post!!!

There is no need to create the database first, but since you did, you have to include REPLACE in the WITH MOVE option as seen in my RESTORE DATABASE command example.

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources