Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nivaskhan
Starting Member
17 Posts |
 Posted - 2003-02-25 : 01:36:59
|
| I was just wondering how this works.Here is what I have done.I have taken a copy of DB files (.mdf & .ldf) from one SQL server which is down for sometime and then tried to attach those 2 files to another SQL server.And to my surprise ,it worked.But according to BOL it 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.Could someone throw some light on this...I haven't detached the db and i have just copied the files and tried to attach it.is this not contradictory to what BOL says..Regards,Nivas |
|
|
harshal_in
Aged Yak Warrior
633 Posts |
Posted - 2003-02-25 : 02:38:07
|
quote:
I was just wondering how this works.Here is what I have done.I have taken a copy of DB files (.mdf & .ldf) from one SQL server which is down for sometime and then tried to attach those 2 files to another SQL server.And to my surprise ,it worked.But according to BOL it 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.Could someone throw some light on this...I haven't detached the db and i have just copied the files and tried to attach it.is this not contradictory to what BOL says..Regards,Nivas
FROM BOL:Detaching a database removes the database from SQL Server but leaves the database intact within the data and transaction log files that compose the database. These data and transaction log files can then be used to attach the database to any instance of SQL Server, including the server from which the database was detached. This makes the database available in exactly the same state it was in when it was detached..-----------------If the database is online then you can not copy the datafiles it will give u the file inuse error ,unless the server is down .so I think in BOL they have assumed that u'r database server is online and still u want to move the datafiles so u have to detach the database first.moreever dettaching the datbase updates the statistics of the database .Regards,Harshal.Expect the UnExpected |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-02-25 : 13:27:03
|
| BOL says that sp_attach_db SHOULD ONLY BE executed on databases that were previously detached from...That doesn't mean that you can't do it. I do it all of the time. BOL just is giving you a recommendation. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-02-25 : 14:07:35
|
| So then what's the difference between attaching and restoring? Isn't there a negative impact in attaching to something that's in existance already....does it replace it...I've got to give it a try.Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-02-25 : 14:29:10
|
| I don't attach a database file to SQL Server unless the database doesn't exist on that server.The difference in attaching and restoring is that restoring is so slow. The reason why you do restores is because you have a database backup file. You created the backup file because you couldn't do a detach because you couldn't have the database come offline. |
|
|
|
|
|
|
Attach DB - clarify