November 12, 2003 at 8:04 am
I currently have a SQL server database that I want to copy (including data) and use with a completely separate web application under IIS. I duplicated the mdf and ldf files and renamed them. I then pointed the new app to the new database name. Unfortunately, if I update something on the new app, the original database also gets changed. It still seems to update only one set of data. I'm a newbie and obviously missing something basic. Is there a suggested way to do this so the databases are completely separate from each other. I don't have Enterprise Manager.
November 12, 2003 at 8:19 am
You can do this by copying the files like you did and then attaching them back to the server under a different name using the sp_attach_db stored procedure.
It would be a good idea to set the source database offline before you start copying to avoid database consistency issues.
Here is an example taken from Books Online:
EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'
JM
November 12, 2003 at 9:20 am
Did try what you suggested in osql. I received a syntax error on the 2nd line. It seems to have a problem with @filename1.
November 12, 2003 at 9:35 am
Hmmm...I tested it in Query Analyser and it works. Did you type it all on one line in osql?
What version of SQL Server are you using?
Jean
November 12, 2003 at 9:41 am
I was using the editor. I did now type it all on one line and it went by so fast, I couldn't tell if it worked. Is there a log file or way to test it. I'm using MS SQL 7.
Thanks
November 12, 2003 at 10:37 am
Got half of it working! I was able to detach both databases and reattach one of them, however when I go to attach the other database, it says it does not exist. The files, however are there. Any ideas why it does not recognize the 2nd one?
November 12, 2003 at 10:55 am
Can you post the error message you are getting?
JM
November 13, 2003 at 5:42 am
May be missing something.
You are attaching as completely new database which should not exist right?
If so then the database does not exist error
does not seem to make too much sense to me since it is like creating a new database and it should not exist
Mike
November 13, 2003 at 7:52 am
Maybe I'm misunderstanding the function of ATTACH. Does attach have SQL recognize a mdf and ldf file as a database and link the physical files to it. In my case, I copied and pasted from an existing database and then renamed the copied one. I then tried to detach and attach, however once I detach the copied one, it can not re-attach. It shows the log file of the original and claims the file name may not be correct. Am I missing something in my understanding? Thanks for the patience.
November 13, 2003 at 8:01 am
Typically you detach the database physical files (.mdf and .ldf) to move a database elsewhere. You then attach them in SQL Server to create a new database.
In your case, you don't need to detach the files if you already have copies of them. Just take your renamed copy and attach them as a new database.
JM
November 13, 2003 at 8:10 am
When I try to attach to the new files, it gives me an error that the physical log file of the original file may not be correct. It then tells me that the database was not created. It seems to still have information on the physical files of the original database i copied from. Does it matter that all the files are on the same folder in my machine? I'm using IIS on XP.
November 13, 2003 at 8:20 am
Did you detach the original files from SQL Server first? Here is what BOL says:
sp_attach_db should only be executed on databases that were previously detached from the database server using an explicit sp_detach_db operation. If more than 16 files must be specified, use CREATE DATABASE with the FOR ATTACH clause.
In other words, you would need:
1. Detach the original database files (This takes the database offline)
2. Copy and rename the files (.mdf and .ldf)
3. Re-attach the original files to its original database name to put that database back online.
4. Attach your copied files as a new database.
Let me know if this works.
JM
November 13, 2003 at 9:41 am
I unfortunately have gotten my MDF and LDF files out of sync. It gives me an error that the files are out of sync. I tried to delete the LDF file completely, but then it won't let me attach, saying that it cannot activate all the files. My problems seem to be compounding.
November 14, 2003 at 8:19 am
From my experience...
I am not sure you can just copy a database online and be able to attach it.
1)Take the db offline or shut it down or sp_detach
2)Copy your files cp ,copy ,(paste rename) xp_cmdshell etc
3)now reattach with the new db names
This discussion is on the same lines
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply