[UCCE] Restore HDS1 database to HDS2 + change database location

Nguyen Quang Son · ‎03-31-2011

Hi guys,

Current situation:

- HDS2 database is not synchronized with HDS1's since it was installed about 3 months later

- HDS2 database is stored in C:\ which is not optimized since the D:\ has much more space

So, I'm trying to do the following:

1- Backup the HDS1 database and restore it to the HDS2.

Action plan provided from a friend of mine:

Step 1 - Using SQL Enterprise Manager tool, Backup the database on HDS2.
Step 2 - Stop the Distributor services on HDS2.
Step 3 - Drop (Delete) the HDS database on HDS2
Step 4 - Using SQL Manager Enterprise tool, backup HDS1 database.
Step 5 - Using ICMDBA tool recreate the HDS2 database making sure the database size (data segment and log segments) are exactly the same as on HDS1. (Note: ICMDBA tool has the limitation of 32GB size during creation of database. Once you create the database using ICMDBA tool, then expand the database to the required size equivalent to HDS2 using SQL Enterprise Manager tool.)

Step 6 - Using SQL Enterprise Manager tool, Restore the backup of HDS1 database on HDS2.
Step 7 - Once the Restore has completed successfully, then prior to starting the distributor services on HDS2you will need to truncate the Recovery table on HDS2 database.
Step 8 - Open SQL Query Analyzer and choose the HDS2 database in the drop down. Run the following query "truncate table Recovery" and hit
the green arrow (execute button) . You will get a command ran successfully message.

Step 9 - Start the Distributor services .

From my point of view, this should be OK but do not hesitate if you think we miss something

2- Move the HDS databased (HDS, AW, WVDB) to the D:\

Being a beginer in SQL Server, I'm not quite sure how to do it properly, moreover I suppose some modifications need to be done in ICM setup and ICMDBA

I'm thinking of doing these two things in a sole operation. So, someone please help me with these questions:

Step 3 - Drop (Delete) the HDS database on HDS2 --> From both ICMDBA and SQL Server Management or just ICMDBA ?

After dropping the HDS2 database, can I recreate it directly in D:\ ?

Do we have to run ICM Setup again to recognize the new database location ?

All ideas welcome,

Thank you very much,

geoff · ‎03-31-2011

- HDS2 database is not synchronized with HDS1's since it was installed about 3 months later

Just as a point of clarification ... it is wrong to say that HDS1 and HDS2 are synchronized, or not synchronized. In your case they don't have the same historical information because one was added three months after the other. Fine.

Action plan provided from a friend of mine:

Why don't you look in the Documents area for an action plan?

https://supportforums.cisco.com/docs/DOC-15290

2- Move the HDS databased (HDS, AW, WVDB) to the D:\

Being a beginer in SQL Server, I'm not quite sure how to do it properly, moreover I suppose some modifications need to be done in ICM setup and ICMDBA

I'm thinking of doing these two things in a sole operation. So, someone please help me with these questions:

Step 3 - Drop (Delete) the HDS database on HDS2 --> From both ICMDBA and SQL Server Management or just ICMDBA ?

After dropping the HDS2 database, can I recreate it directly in D:\ ?

Do we have to run ICM Setup again to recognize the new database location ?

All ideas welcome,

Thank you very much,

It is reasonably well documented how to do this in SQL Server, but an easier plan is to simply get some bigger drives and allow the RAID controller to help you expand C:. Then you can add more data segments to SQL and HDS will now grow.

You may even be able to simply add more segments now, making sure they are on D:. Sometimes SQL doesn't like to do this across drives. It's definitely worth trying.

Regards,

Geoff