Copying a User Database from a non clustered to clustered SQL 2000 
Author Message
 Copying a User Database from a non clustered to clustered SQL 2000

I have a production SQL 2000 user database running on a
Windows 2000 with SP/2 and SQL 2000 Enterprise Edition
with SP/1.  The database is located at (RAID 5) d:\Program
Files\Microsoft SQL Server\MSSQL\Data\xxx.mdf, xxx2.ndf,
and xxx_log.ldf.

I have a two-node clustered Windows 2000 with SP/2 and SQL
2000 Enterprise Edition with SP/2.  The OS system files on
are (RAID 1) C:\ drive, the quorom drive (RAID 1/shared)
is Q:\ and the database drive (RAID 5E/shared) is W:\.  
The path to where the system databases are located are
W:\Program Files\Microsoft SQL Server\MSSQL\data\.  The
cluster appears to function correctly. I can fail over and
fail back, etc.

I have attempted to restore from a .bak copy of the
production database.  This was meant with failure and the
warning:  Only formatted files on which the cluster
resource of the server has a dependency can be used.  

I have attempted to DTC a copy of the database with only
partial success (the tables copied over, but little else).

I have considered doing a detach of the production user
database and then, an attach to the cluster server, but
there really is no opportunity to do this as this database
is tied to several 24X7 web sites.  

What is the best way to perform this operation?  Thank you.



Tue, 20 Jul 2004 22:37:10 GMT
 Copying a User Database from a non clustered to clustered SQL 2000

All drives that are used by the database when restored
must have a dependency on the SQL Server cluster
resource.  Check the dependencies you will find that not
all of your drives are listed.

Kevin J McCarthy
Hawkscope Ltd

Quote:
>-----Original Message-----
>I have a production SQL 2000 user database running on a
>Windows 2000 with SP/2 and SQL 2000 Enterprise Edition
>with SP/1.  The database is located at (RAID 5)
d:\Program
>Files\Microsoft SQL Server\MSSQL\Data\xxx.mdf, xxx2.ndf,
>and xxx_log.ldf.

>I have a two-node clustered Windows 2000 with SP/2 and
SQL
>2000 Enterprise Edition with SP/2.  The OS system files
on
>are (RAID 1) C:\ drive, the quorom drive (RAID 1/shared)
>is Q:\ and the database drive (RAID 5E/shared) is W:\.  
>The path to where the system databases are located are
>W:\Program Files\Microsoft SQL Server\MSSQL\data\.  The
>cluster appears to function correctly. I can fail over
and
>fail back, etc.

>I have attempted to restore from a .bak copy of the
>production database.  This was meant with failure and the
>warning:  Only formatted files on which the cluster
>resource of the server has a dependency can be used.  

>I have attempted to DTC a copy of the database with only
>partial success (the tables copied over, but little else).

>I have considered doing a detach of the production user
>database and then, an attach to the cluster server, but
>there really is no opportunity to do this as this
database
>is tied to several 24X7 web sites.  

>What is the best way to perform this operation?  Thank
you.
>.



Wed, 21 Jul 2004 05:47:20 GMT
 Copying a User Database from a non clustered to clustered SQL 2000
Jae,

Sounds like you have run into

Q295732 INF: Creating Databases or Changing Disk File Locations
http://support.microsoft.com/support/kb/articles/q295/7/32.asp

John Gose
MCDBA
Microsoft SQL Server Support

This posting is provided "AS IS", with no warranties, and confers no rights

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

--------------------
| Content-Class: urn:content-classes:message


| Subject: Copying a User Database from a non clustered to clustered SQL
2000
| Date: Fri, 1 Feb 2002 06:37:10 -0800
| Lines: 29
|
<Snip of Header Stuff>
|
| I have a production SQL 2000 user database running on a
| Windows 2000 with SP/2 and SQL 2000 Enterprise Edition
| with SP/1.  The database is located at (RAID 5) d:\Program
| Files\Microsoft SQL Server\MSSQL\Data\xxx.mdf, xxx2.ndf,
| and xxx_log.ldf.
|
| I have a two-node clustered Windows 2000 with SP/2 and SQL
| 2000 Enterprise Edition with SP/2.  The OS system files on
| are (RAID 1) C:\ drive, the quorom drive (RAID 1/shared)
| is Q:\ and the database drive (RAID 5E/shared) is W:\.  
| The path to where the system databases are located are
| W:\Program Files\Microsoft SQL Server\MSSQL\data\.  The
| cluster appears to function correctly. I can fail over and
| fail back, etc.
|
| I have attempted to restore from a .bak copy of the
| production database.  This was meant with failure and the
| warning:  Only formatted files on which the cluster
| resource of the server has a dependency can be used.  
|
| I have attempted to DTC a copy of the database with only
| partial success (the tables copied over, but little else).
|
| I have considered doing a detach of the production user
| database and then, an attach to the cluster server, but
| there really is no opportunity to do this as this database
| is tied to several 24X7 web sites.  
|
| What is the best way to perform this operation?  Thank you.
|



Sat, 24 Jul 2004 06:05:20 GMT
 Copying a User Database from a non clustered to clustered SQL 2000
Jae,

If I understand correctly, your question is, 'Now that I've created this
cool cluster, how do I get my databases onto it?'

You may be able to restore from a .bak if you diddle the disk dependancies
in EM or via the stored procedure, but the quickest way I've found to take
an entire database from one server to another (especially if they are
seperated by firewalls, distance, time zones, etc.) is good old-fashioned
DTS. I'd say make a few test-runs and make sure it works predictably, then
bite the bullet and schedule a downtime window based on the test transfer
time (which will give the other IT guys times to repoint that they're gonna
need anyhow) and go for it....

Once again, to cover the backside, this info supplied totally as "AS IS"
water-cooler conversation, with neither warranty nor recourse....

Good Luck!

BC


Quote:
> I have a production SQL 2000 user database running on a
> Windows 2000 with SP/2 and SQL 2000 Enterprise Edition
> with SP/1.  The database is located at (RAID 5) d:\Program
> Files\Microsoft SQL Server\MSSQL\Data\xxx.mdf, xxx2.ndf,
> and xxx_log.ldf.

> I have a two-node clustered Windows 2000 with SP/2 and SQL
> 2000 Enterprise Edition with SP/2.  The OS system files on
> are (RAID 1) C:\ drive, the quorom drive (RAID 1/shared)
> is Q:\ and the database drive (RAID 5E/shared) is W:\.
> The path to where the system databases are located are
> W:\Program Files\Microsoft SQL Server\MSSQL\data\.  The
> cluster appears to function correctly. I can fail over and
> fail back, etc.

> I have attempted to restore from a .bak copy of the
> production database.  This was meant with failure and the
> warning:  Only formatted files on which the cluster
> resource of the server has a dependency can be used.

> I have attempted to DTC a copy of the database with only
> partial success (the tables copied over, but little else).

> I have considered doing a detach of the production user
> database and then, an attach to the cluster server, but
> there really is no opportunity to do this as this database
> is tied to several 24X7 web sites.

> What is the best way to perform this operation?  Thank you.



Sat, 24 Jul 2004 09:01:21 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Upgrade of SQL 7.0 Cluster to SQl 2000 Cluster

2. T-SQL to change from clustered to non-clustered index

3. Migration path from SQL 7 non-clustered to SQL2000 clustered

4. SQL 2000 - Configuring MSDTC across internet for Non CLustered SQL Server

5. SQL clustering and Exchange 2000 clustering

6. create clustered / non-clustered index

7. Clustered Primary Key vs Non clustered PK

8. Indexes clustered vs non-clustered

9. Number of levels in a clustered and non-clustered index

10. Changing an Index from Clustered to Non-clustered

11. Clustered Index Vs Non Clustered

12. Effect of changing clustered index to non-clustered?


 
Powered by phpBB® Forum Software