problem copying db from one sql server to another

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 2000 Forums

SQL Server Administration (2000)

problem copying db from one sql server to another

Author

Topic

johntchow
Starting Member

5 Posts

Posted - 2004-07-28 : 13:11:15

to make a long story short, i am trying to create a dev environment from our production db. when i try to you use the copy db wizard in sql server enterprise manager and i choose the destination db server (dev environment) i get an error: Your SQL Server Service is running under the local system account. You need to change your SQL Server Service account to have the rights to copy files over the network.

Does anyone know what i need to do? I went to dev server and changed the SQL service agent in the services panel to run under the administrator account but it still didn't work.

Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-28 : 13:39:29

Do not use the copy database wizard. Instead, backup the production database. Copy that file to the development server. Then restore that file to the development server using RESTORE DATABASE command. If the path information to the MDF and LDF files is changing, you will need to use WITH MOVE option in the RESTORE command. Please check SQL Server Books Online for syntax of RESTORE DATABASE command.

Tara

johntchow
Starting Member

5 Posts

Posted - 2004-07-28 : 16:27:51

hi Tara, thanks for the reply. I am pretty new at this, does this look right to you?

RESTORE DATABASE imediaconnection FROM DISK = 'd:\fileshare\public\imediaconnection'
WITH NORECOVERY ,
MOVE = 'imediaconnection' TO 'D:\MSSQL\Data\imediaconnection.mdf'

because this is the error msg i get:

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'TO'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-28 : 16:59:51

Your backup file probably has BAK as the extension. You'll want to use RECOVERY option since you won't be adding transaction logs to the restore process. You probably need to move the LDF file as well. To find out if you have the logical name (imediaconnection, it might be imediaconnection_data) correct, use RESTORE FILELISTONLY.

RESTORE DATABASE imediaconnection
FROM DISK = 'd:\fileshare\public\imediaconnection.BAK'
WITH RECOVERY ,
MOVE 'imediaconnection' TO 'D:\MSSQL\Data\imediaconnection.mdf',
REPLACE

Tara

johntchow
Starting Member

5 Posts

Posted - 2004-07-28 : 17:18:13

ok, i tried this:

RESTORE DATABASE imediaconnection
FROM DISK = 'd:\fileshare\public\imediaconnection'
WITH RECOVERY ,
MOVE 'imediaconnection_data' TO 'D:\MSSQL\Data\imediaconnection.mdf' ,
REPLACE

but got this error msg:

Server: Msg 3241, Level 16, State 37, Line 1
The media family on device 'd:\fileshare\public\imediaconnection' is incorrectly formed. SQL Server cannot process this media family.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

how do i find out the media family of the source database?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-28 : 17:54:24

Show us the command you used to backup the database.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-28 : 17:56:26

It doesn't sound like you have a valid backup.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-28 : 17:57:35

It's brillllliant. :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

johntchow
Starting Member

5 Posts

Posted - 2004-07-28 : 18:07:41

i just went into enterprise manager on our production server and right clicked the database name, "imediaconnection" chose All Tasks and then Backup Database. I chose a complete backup and to append to media. in the options tab the media set name is empty...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-28 : 19:03:19

You want to overwrite it or you'll need to figure out where your backup is inside that file. When you append, you've got multiples in there and would need to tell the restore command which one it is. Overwriting is so much easier. Try that out and let us know.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-28 : 19:55:46

Better yet, just use this:

BACKUP DATABASE imediaconnection
TO DISK = 'd:\fileshare\public\imediaconnection_20040728.bak'

then, run your restore script we gave you. You might as well learn how to do it with scripts. :)

RESTORE DATABASE imediaconnection
FROM DISK = 'd:\fileshare\public\imediaconnection.bak'
WITH RECOVERY ,
MOVE 'imediaconnection_data' TO 'D:\MSSQL\Data\imediaconnection.mdf' , --You need to replace this with the correct file.
MOVE 'imediaconnection_log' TO 'D:\MSSQL\Log\imediaconnection.ldf',
REPLACE

You can find the logical file names ('imediaconnection_data','imediaconnection_log') by running

RESTORE FILELISTONLY
FROM DISK = 'd:\fileshare\public\imediaconnection.bak'

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

johntchow
Starting Member

5 Posts

Posted - 2004-07-28 : 20:04:44

thanks for all your help guys. Tara, it worked! derek ill try your way for fun tomorrow. thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources