How to Replicate Database 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 2000 Forums

SQL Server Administration (2000)

How to Replicate Database Server

Author

Topic

TeamWeb
Starting Member

1 Post

Posted - 2008-08-25 : 17:20:11

Hello. I have a SQL database that needs to be duplicated on a different server (and host). I have created the SQL script selecting ALL options for scripting. When I attempt to the run query analyzer on the destination server I get the following error:

Server: Msg 5105, Level 16, State 2, Line 2
Device activation error. The physical file name 'D:\SQLDataRoot\vaculaga\vaculaga_data.mdf' may be incorrect.
Server: Msg 1802, Level 16, State 1, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check previous errors.
Server: Msg 15010, Level 16, State 1, Procedure sp_dboption, Line 70
The database 'vaculaga' does not exist. Use sp_helpdb to show available databases.

Server: Msg 15010, Level 16, State 1, Procedure sp_dboption, Line 70
The database 'vaculaga' does not exist. Use sp_helpdb to show available databases.

Server: Msg 15010, Level 16, State 1, Procedure sp_dboption, Line 70
The database 'vaculaga' does not exist. Use sp_helpdb to show available databases.

Server: Msg 15010, Level 16, State 1, Procedure sp_dboption, Line 70
The database 'vaculaga' does not exist. Use sp_helpdb to show available databases.

############

And it goes on and on... all the way down complete failure.

I can see a couple of possible problems, but I don't know how to resolve them.

The first error: Device activation error. The physical file name 'D:\SQLDataRoot\vaculaga\vaculaga_data.mdf' may be incorrect.

Where is this .MDF file, or what path should i use? is this the same as my .SQL file? what drive or path should i be using instead?

Second problem is the user name (or would that be DB owner?) is different on the destination server. would it help if i matched up the user names, or can i simply do a find and replace in the script for that name?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-25 : 17:32:41

Why don't you just use BACKUP/RESTORE or detach/attach?

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

Subscribe to my blog

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2008-08-26 : 16:18:16

TeamWeb,

Can you look into that script? If so, then just see where it is trying to create your files.

Then, either create same folder names it is looking for (assuming the drive names match) or change the script to match the drives and folders where you want the data saved.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources