Copying database file to another drive fails

I have an active/passive cluster involving 2 machines. I have 3 instances
running on the production server. One of the drives allocated to instance 2
was running out of space so I decided to move one of the larger databases to
another drive that can handle its size. The drive that I was going to move
the data file to is part of the same instance group in the cluster. After
detaching the database I attempted to move the data file to this drive but at
the end of the copy I receive a message that there was an error copying file
because of insufficient resources exist to complete the requested server. I
double checked the drive and there is plenty of space for this file and like
I said the drive is already part of the cluster group.
Anyone have any other suggestions on what the problem might be? Thanks
You could be out of physical memory or file handles. I have seen some
monitoring programs that "leaked" handles badly. Sometimes stopping and
restarting the SNMP service fixed it, but sometimes I had to wait and reboot
the host node during a scheduled maintenance window.
You could also have a problem due to Anti-virus programs. AV and clusters
have never gotten along well.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ronc" <ronc@.discussions.microsoft.com> wrote in message
news:E699545D-9DD4-47EB-A6F1-F7A29EFD7C39@.microsoft.com...
>I have an active/passive cluster involving 2 machines. I have 3 instances
> running on the production server. One of the drives allocated to instance
> 2
> was running out of space so I decided to move one of the larger databases
> to
> another drive that can handle its size. The drive that I was going to
> move
> the data file to is part of the same instance group in the cluster. After
> detaching the database I attempted to move the data file to this drive but
> at
> the end of the copy I receive a message that there was an error copying
> file
> because of insufficient resources exist to complete the requested server.
> I
> double checked the drive and there is plenty of space for this file and
> like
> I said the drive is already part of the cluster group.
> Anyone have any other suggestions on what the problem might be? Thanks
|||One other thing that I forgot to mention was that I was able to successfully
move a 20 GB backup file to the drive I was trying to copy to and it copied
successfully. Memory does not seem to be the problem. I still think it
has something to do specifically with the .dat file being moved but what that
is I'm not sure. I know that I can always do a backup and restore and
speicify the drive but I know that I should be able to do it this way by
coping and then reattching. Thanks again.
"Geoff N. Hiten" wrote:

> You could be out of physical memory or file handles. I have seen some
> monitoring programs that "leaked" handles badly. Sometimes stopping and
> restarting the SNMP service fixed it, but sometimes I had to wait and reboot
> the host node during a scheduled maintenance window.
> You could also have a problem due to Anti-virus programs. AV and clusters
> have never gotten along well.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "ronc" <ronc@.discussions.microsoft.com> wrote in message
> news:E699545D-9DD4-47EB-A6F1-F7A29EFD7C39@.microsoft.com...
>
>
|||Do you use any anti virus software or things like that ?
"ronc" wrote:
[vbcol=seagreen]
> One other thing that I forgot to mention was that I was able to successfully
> move a 20 GB backup file to the drive I was trying to copy to and it copied
> successfully. Memory does not seem to be the problem. I still think it
> has something to do specifically with the .dat file being moved but what that
> is I'm not sure. I know that I can always do a backup and restore and
> speicify the drive but I know that I should be able to do it this way by
> coping and then reattching. Thanks again.
>
> "Geoff N. Hiten" wrote:

Copying .mdf and .ldf files bewteen servers

We have a user at work at enjoys stopping SQL Server 7 and 2000 machines and
copying the .mdf and .ldf files from one server to another. He then simply
restarts the production server and re-attaches the database to the new
server.
I have asked this employee to either backp the DB to disk and copy this to
another server or use the sp_detach command and copy file then re-attach.
What are the ramifications of doing a simple .mdf and .ldf copy?
We have a number of MSDE databases as well. Is this acceptable for these?
Perhaps the reason I am getting the SQL-DMO the name 'dbo' was not found in
the users collection is because of these types of copies.
Thanks
--
...david
http://www.micro-mess.com
http://www.va-mustang.com
If you wish to reply to me personally, please remove
the "underline" from scandal_123@.cox.net. The is done to avoid SPAM!I agree with you that backup/restore is probably a better approach. This
eliminates the need to stop the SQL Server service on the source server.
Although sp_attach_db often works without sp_detach_db, the documentation
clearly states that it should only be used with database files detached with
sp_detach_db.
After attaching or restoring databases from another server, you can run
sp_changedbowner to correct the login mapping for the 'dbo' user. This will
correct the DMO 'dbo' user problem. You may also need to run
sp_change_users_login to correct the login/user mapping for other users.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DavidM" <scandal_123@.cox.net> wrote in message
news:%23I8gmHdxDHA.1364@.tk2msftngp13.phx.gbl...
> We have a user at work at enjoys stopping SQL Server 7 and 2000 machines
and
> copying the .mdf and .ldf files from one server to another. He then simply
> restarts the production server and re-attaches the database to the new
> server.
> I have asked this employee to either backp the DB to disk and copy this to
> another server or use the sp_detach command and copy file then re-attach.
> What are the ramifications of doing a simple .mdf and .ldf copy?
> We have a number of MSDE databases as well. Is this acceptable for these?
> Perhaps the reason I am getting the SQL-DMO the name 'dbo' was not found
in
> the users collection is because of these types of copies.
> Thanks
>
> --
> ...david
> http://www.micro-mess.com
> http://www.va-mustang.com
> If you wish to reply to me personally, please remove
> the "underline" from scandal_123@.cox.net. The is done to avoid SPAM!
>

Copying .mdf and .ldf files bewteen servers

We have a user at work at enjoys stopping SQL Server 7 and 2000 machines and
copying the .mdf and .ldf files from one server to another. He then simply
restarts the production server and re-attaches the database to the new
server.
I have asked this employee to either backp the DB to disk and copy this to
another server or use the sp_detach command and copy file then re-attach.
What are the ramifications of doing a simple .mdf and .ldf copy?
We have a number of MSDE databases as well. Is this acceptable for these?
Perhaps the reason I am getting the SQL-DMO the name 'dbo' was not found in
the users collection is because of these types of copies.
Thanks
...david
http://www.micro-mess.com
http://www.va-mustang.com
If you wish to reply to me personally, please remove
the "underline" from scandal_123@.cox.net. The is done to avoid SPAM!I agree with you that backup/restore is probably a better approach. This
eliminates the need to stop the SQL Server service on the source server.
Although sp_attach_db often works without sp_detach_db, the documentation
clearly states that it should only be used with database files detached with
sp_detach_db.
After attaching or restoring databases from another server, you can run
sp_changedbowner to correct the login mapping for the 'dbo' user. This will
correct the DMO 'dbo' user problem. You may also need to run
sp_change_users_login to correct the login/user mapping for other users.
Hope this helps.
Dan Guzman
SQL Server MVP
"DavidM" <scandal_123@.cox.net> wrote in message
news:%23I8gmHdxDHA.1364@.tk2msftngp13.phx.gbl...

quote:

---

> We have a user at work at enjoys stopping SQL Server 7 and 2000 machines

---

and

quote:

---

> copying the .mdf and .ldf files from one server to another. He then simply
> restarts the production server and re-attaches the database to the new
> server.
> I have asked this employee to either backp the DB to disk and copy this to
> another server or use the sp_detach command and copy file then re-attach.
> What are the ramifications of doing a simple .mdf and .ldf copy?
> We have a number of MSDE databases as well. Is this acceptable for these?
> Perhaps the reason I am getting the SQL-DMO the name 'dbo' was not found

---

in

quote:

---

> the users collection is because of these types of copies.
> Thanks
>
> --
> ...david
> http://www.micro-mess.com
> http://www.va-mustang.com
> If you wish to reply to me personally, please remove
> the "underline" from scandal_123@.cox.net. The is done to avoid SPAM!
>

---

Copyind data from one database table to other

hi all
i have two databases on two different machines.
both databses r having same names.
i want to copy data from the table in other database to table in databse on my machine .
how can i do this.
i will be very thankful to receive help.Take a look at DTS in sql book online. That's the easiest to start with.