Detached data and log files disappear when copying?!

[TheBugSlayer]

Hi guys,

I had an assignment to refresh the dev server with data from prod. So I created and tested my scripts accordingly. My idea is to set all non-system databases offline on the Prod server, copy the data and log files to a different location, turn back the prod databases on, then attach the databases from the location to the dev and do some tweaking on dev where necessary. Simple, right? However, in the process of copying the files, some datatabase files completely disappear from the source (Prod data and log folger)!

The questions are: why does that happen? What is the best way to copy such big files.

BTW, I was never able to copy the biggest files, which is 360 GB; I would get an error pertaining to not having enough resources to complete the operation or something like that. The net admin had mention to create an image of the prod data folder with a special program (something like Ghost), but I told him not to worry because I would have to either set the DBs offline or detach them, so he went on his merry way...

Thanks for your help.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[mrdenny]

First, never do it that way. You are taking an outage for no reason.

Backup the databases using the WITH COPY_ONLY flag so that you don't screw up your existing backup process, then copy the database backups to the new server and restore them. Or better yet, use the most recent backup from your existing backup process.

As to why the files would be missing?
1. They were never in the folder that you thought they were to begin with.
2. Your script moved them.
3. Your anti-virus thought they had a virus in them and moved them to the quarantine folder (or just deleted them)
4. Someone else deleted them.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[TheBugSlayer]

Denny,

Thanks for your advice. The files were in the folder; the databases were online. It could be (2) or (3), with (2) being the least likely. My script detached all non-system dbs, then reattached them...in the worse case I could have inadvertently recreated the files in a different folder.

I read a post after the fact where someone had mentioned exactly the same problem. I am going to have to use the most recent backup. I did it on Sunday morning, a few hours after the weekly full backup. Those backup files are still good for dev.

Since I already have the backup files I am thinking it' s easier to restore them on the dev server from their current location (which is on the prod server, don' t ask me why) as I am afraid they might disappear too if I try to copy them to the dev server; also I don' t want to be taking backups during production.

Well, we' ll see. Thanks again.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[TheBugSlayer]

Denny, I believe at this point that the files were never in the folder I thought they were in as while restoring a few of the databases I had to change the location.

Thanks.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)