SQL Server 2008 R2 copy DATA folder

Question

 

I use SQL Server 2008 R2 express. How do I copy the DATA folder C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA to another folder? When the copying gets to master.mdf I get following message:

"Cannot copy master: It is being used by another person or program. Close any be using the file and try again."

I use Explorer to copy. I have just rebooted and no other programs are running apart from my antivirus program (Trend Micro).

---

carl.g.b

Answer 1

Hi Carl,

The master.mdf file is being used by SQL Server and you can't move it as you would move other windows files. Three is a procedure you'll have to follow to move SQL Server user databases and system databases. Please follow the links below.

Moving User Databases - http://www.rcs-solutions.com/blog/2008/10/27/MovingSQLDatabasesToANewDrive.aspx
Moving System Databases - http://msdn.microsoft.com/en-us/library/ms345408.aspx

Hope this helps!

---

Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com

Answer 2

If your SQL Server Express service is marked to autostart, then it is starting.  At least my SQL Server Express instance autostarts when my machine boots up.   Check the Control Panel - Services to see if the SQL Server Express service is running.  If so, then stop it.

Then copy your folder and restart (if that is what you want) SQL Server Express.

Having said that, why are you copying the folder?  What will you use the copy to do?  (Of coursem, I assume that it is not for backup, since the backup tools for SQL Server work without shutting SQL Server down.)

RLF

Answer 3

Thank you for your reply shahfaisal. I just tried the method suggested by Russell Fields below and it worked but it didn't solve my problem as I  explain in my reply to Russell.

---

carl.g.b

Answer 4

Russell, thank you, it worked but it didn't solve my problem. You asked why I wanted to do it. It's a bit of long story. Hope you have the patience to read it as I am looking for a solution.

The problem is that IntelliSense isn't working. But it has worked. Here is the story:

A bit over three months ago my hard disk (I'll call it disk1) was replaced with a bigger hard disk (disk2) and the content on disk1 (files, programs and operating system) was copied to disk2. 

Last week disk2 crashed and disk1 was put back in my computer. I had done backups to an external harddrive both from disk1 and disk2 (Norton Ghost 14.0). As I was now missing three months work that I had done with disk2 I restored the database files to disk1 from the external harddrive. I restored only my database files but not the other files. The files I now restored to disk1 were from the latest backup that I had done from disk2.

When I then started working with disk1 again I had two problems 1) IntelliSense didn't work and 2) I couldn't see some of my databases in Server Management Studio. Problem 2) was not important, but not having IntelliSense is a problem. So, I thought if I restored all the files in C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA from my backup drive instead of just some of them it might make a difference.

Using your suggestion I was able to copy the files from the DATA folder to a temp folder and then delete the files in the DATA folder. I then restored all the files in the DATA folder on the backup drive to the DATA folder on disk1. Now I could see all my databases, but IntelliSense still didn't work. It worked three months ago when I used disk1 and it worked while I used disk2. But it doesn't work now on disk1. The boxes for IntelliSense under Tools, Options are checked and the menu icon shows IntelliSense Enabled. I also did a Shift-Ctrl-R.

Would you by any chance know what I should do to make IntelliSense work

Thanks

Carl

---

carl.g.b

Answer 5

Using your suggestion I was able to copy the files from the DATA folder to a temp folder and then delete the files in the DATA folder. I then restored all the files in the DATA folder on the backup drive to the DATA folder on disk1. Now I could see all my databases, but IntelliSense still didn't work. It worked three months ago when I used disk1 and it worked while I used disk2. But it doesn't work now on disk1. The boxes for IntelliSense under Tools, Options are checked and the menu icon shows IntelliSense Enabled. I also did a Shift-Ctrl-R.

Would you by any chance know what I should do to make IntelliSense work

Hi Carl,

On the Tools menu, click Options. Expand Text Editor, expand Transact-SQL, and then click IntelliSense.

The following conditions might affect the behavior of IntelliSense:

• There is a code error above the cursor.
• The insertion point is inside a code comment.
• The insertion point is inside a string literal.
• The automatic options are turned off.

Troubleshooting Transaction-SQL IntelliSence: http://msdn.microsoft.com/en-us/library/ms173434(v=sql.105).aspx.

Thanks,
Maggie

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 6

Hi Maggie,

How do I check if automatic options are turned off and how do I turned them on?

Thanks

Carl

---

carl.g.b

Answer 7

Hi Carl,

Many IntelliSense features work automatically by default, but you can disable any feature.

Even when automatic statement completion is disabled, you can use an IntelliSense feature. For more information, see How to: Modify IntelliSense Options.

Thanks,
Maggie

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 8

Maggie, I have checked all the IntelliSense parameters and to the best of my knowledge they are all as they should be. But IntelliSense still doesn't work. The only thing I haven't been able to do is what you advised as the last point in your reply of Aug 12 where you asked me to check if the automatic options are turned off. I don't know how to do that. Could you show me, please.

Thanks

Carl

---

carl.g.b