Hi,

I have a sql database server (let call it server A) which has SQL program files installed on C:, and SQL data structures MSSQL (include subdirectories BAckup, DATA, FTDAT, JOBS, LOG, REPLDATA) on F:\. A new server (call it server B) was cloned of server A but only at the C: partition. I still need to manually copy the SQL data structure on F:\ drive from A to B. The purpose of setting up server B is to test the restore of backup of server A and eventually want it to serve as the standby server should server A crashes. My questions are

1. Can MSSQL directory be copied from A to B without stopping the SQL server service on A first?

2. I will use robocopy for copying data over, what switches should I use to retain all database file security, permission, etc..

3. Currently I can not start the SQL service on server B because part of MSSQL directory is missing from the F:\ drive. so theorically, after I manually copy that folder over, I should be able to start the sql service. Am I right? Is there any steps that I might have overlook here. Any errors I might encounter doing it this way

Once I can get the sql started, I can take care of the restore part.

__________________
A.Rajesh Khanna

Hi arjkhanna,

1. You need to stop sql services inorder to copy the data and log files to another server.............

2. After copying the data and log files you need to update the startup parameters with the path of the data and log files of master and error logs residing path ! and then start the sql server but you need to have same editions of sql server i believe to perform the operations for system databases.

I would advise you to go for Log shipping or database mirroring which are high availability solutions for your dbs.

Hi satheesh,

I was able to copy the data structures. Now I run into a problem where I am not able to add, modify, or delete any jobs that was copied from the server A.

error message:14274 can not add, modify, delete a jobs (or its steps or schedule) that originates from MSX server. the job was not saved.

How can I get around? and I looked up on the registry on server B (which was copied from server A) and there are alot of registry that refer to server A. Should do I go about updating those registry.

__________________
A.Rajesh Khanna

Hi arjkhanna,


Pls refer the below links,

PRB: Error 14274 Occurs When You Update a SQL Agent Job After Renaming Windows Server execute the below query,

Code Blockupdate msdb..sysjobs
set originating_server = CONVERT(NVARCHAR(30), SERVERPROPERTY('ServerName'))
go


You need to update the originating server column in the sysjobs table !

Hi,


we have Another problem, while setting up the log shipping so the backup will be restored on the standby server, I discovered that the sysservers table in master db on standby database has the srvrname and database lshowed as the name of production server. I guess that was the result of copying the mssql directory. I am thinking of updating this table to reflect the standby servername and wanted to check if that is what I should do

Also are there any other tables that I need to update to reflect the standby server name instead of the production server?

for log shipping, I already set up the store procedure to restore db from *.bak file on the standby server. just wondering the EXEC of this store procedure should run on the production server where linked server is set up to connect to standby server. is it correct?

__________________
A.Rajesh Khanna

Hi arjkhanna,

You need to create the sp to restore the dbs in standby server and call these sp's in prod server using lionked server as below,


Code BlockEXEC standby_server_name.master.dbo.restore_log_backups

I am not sure about the step 1 i.e sysservers should be the same ? I dont think so ! try setting up log shipping and check if you are interested to configure log shipping in sql server 2005 using GUI refer,

10 Steps to configure Log Shipping