March 25, 2009 at 2:48 pm
Hello,
I have a database on the server MyDatabase.mdf . When I try to copy the database file it gives me an error cannot copy used by another person or program. But I am sure that No one is using that database as it is just in the testing phase.
Also I want to copy this file (.mdf) to a CD and then I want to load the database from the CD to my computer at home.
Kindly suggest.
Thanks.
March 25, 2009 at 2:59 pm
You can't copy it unless your server is shut down, if it is a production system, the easiest method is to copy the backup file and restore it on your computer
March 25, 2009 at 3:00 pm
The file is constantly open by the sql server process, even if no-one is actually using it at that moment (Unless you have enabled auto-close which I would NOT normally do). So you could stop sql server and then copy the file, but if you do I would take a copy of the log at the same time.
Then copy the log and data files to the new machine, use sp_attachdb on the destination machine.
By far the safer option is to take a backup of the database, then restore the backup to the other machine.
I would also check the legislation in your country with respect to who owns the data and privacy laws, you may not be legally able to copy this db to your home PC, depending on what it contains and who owns it.
Mike John
March 25, 2009 at 3:16 pm
Mike John (3/25/2009)
I would also check the legislation in your country with respect to who owns the data and privacy laws, you may not be legally able to copy this db to your home PC, depending on what it contains and who owns it.Mike John
That's a good point. I think I need to practice more of this kind
March 25, 2009 at 4:24 pm
The Problem is when I try to restore it from the backup file (.bak)
From the Screen:
Specify the source and location of backup sets to restore:
I click on the radio button which says "From Device"
Then I select the bak file from my local hard drive.
Then from the next option:
Select the backup sets to restore:
It gives me the option of the Database on my production server and not my local server.
How can I change that.
So basically this is what i want:
I want to have an identical copy of the database and all the stored procedures from the
production server to the local server on my machine.
I took a backup of the production server (.bak) file and copied it to my local machine Hard drive.
I created a new database on my local machine and now I want to restore the data from this back up file and having all the issues I explained on the top.
Thanks a lot.
March 25, 2009 at 5:37 pm
I Hope you are trying to restore on your local server. You dont have to create database on your local server just specify it when you are restoring it.
Try using TSQL which is the easiest way:
Use master
GO
RESTORE DATABASE 'your database name'
FROM DISK='path of your .bak file'
GO
You should run this on your computer.
March 27, 2009 at 11:11 am
When I run the Command I get the following Error:
Use master
GO
RESTORE DATABASE MSR
FROM DISK= 'C:\MSR.bak'
GO
The backup set holds a backup of a database other than the existing 'MSR' database.
March 27, 2009 at 11:27 am
RESTORE HEADERONLY FROM DISK='backup file path'
and use the correct logical file name of the backup.
March 27, 2009 at 11:31 am
Are you trying to restore a copy of the MSR database OVER TOP of a copy on your local server?
The general process:
- Connect to production with SSMS
- run backup from SSMS
- copy backup file from production file system to your local file system
- connect to local instance with SSMS
- click restore, from device, pick the .bak file you copied.
- If this replaces an existing copy of the database, you need to check the box that says overwrite the existing database.
March 27, 2009 at 4:00 pm
Krishna (3/25/2009)
You can't copy it unless your server is shut down, if it is a production system, the easiest method is to copy the backup file and restore it on your computer
?????
If your server's shut down how can you copy ?
Don't stop your server, or SQL. To copy, you detach the database, copy, then attach at your destination. You also need to re-attach the original.
But the backup & restore as mentioned is a better idea usually, especially if the original needs to remain active.
March 28, 2009 at 1:45 pm
homebrew01 (3/27/2009)
Krishna (3/25/2009)
You can't copy it unless your server is shut down, if it is a production system, the easiest method is to copy the backup file and restore it on your computer?????
If your server's shut down how can you copy ?
Why not? How can you not copy?
You can't copy the .mdf file if the server is online. If you want to copy it over you should make sure that your server is shut down and then copy it over.
March 28, 2009 at 2:23 pm
Don't shut down the Windows host, shut down the SQL Server server instance. Use SSMS, right click, shut down SQL Server.
Or Control Panel/Services, turn the service off.
April 8, 2009 at 6:06 am
Hi
Points to get mdf file.
1. select the database.
2. Right click it and select Task
3 select Detach
Pls note that where the mdf file of the database is located.
go to that location copy it and save it to the location you want.
Note:
Select the database right click it choose properties
choose file and note the path where the .mdf and .ldf is location
regards
kkrchandru:-P
April 8, 2009 at 6:08 am
Hi
Points to get mdf file.
1. select the database.
2. Right click it and select Task
3 select Detach
Pls note that where the mdf file of the database is located.
go to that location copy it and save it to the location you want.
Note:
Select the database right click it choose properties
choose file and note the path where the .mdf and .ldf is location
regards
kkrchandru:-P
April 8, 2009 at 6:12 am
Hi
Points to get mdf file.
1. select the database.
2. Right click it and select Task
3 select Detach
Pls note that where the mdf file of the database is located.
go to that location copy it and save it to the location you want.
Note:
Select the database right click it choose properties
choose file and note the path where the .mdf and .ldf is location
regards
kkrchandru:-P
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy