Great article Chris! (shows your attachment to the command line from an earlier life as an Oracle DBA, Mate!)
John Kanagaraj
(your friend from way back to '98 in Oracle-l)
Good article but I have to agree with most of the folks out there
backup and restore is the way to go.
By the way how do you attach a database with more than one log file?
Not a bad article for what it covers, however, I've had experience with this sort of thing in the past and whilst I agree it's fine for small databases it is not the way to go with anything medium to large.
Also, if you have full-text indexing working against the database you should be aware that detaching it breaks the cataloging process, this applies even if you leave everything in situ and re-attach. Again, whilst this isn't a concern for some smaller databases you will need to factor in the time required to do a full population after attaching it again.
OK, I"m no SQL expert, but I've been tapped by my company to move our CRM SQL database from a primary to a secondary partition on the same server. I've read about detach and attach, which looks pretty simple, and I've successfully done it with a test database. Now I'm reading the posts about backup and restore to a new location, which I've also tried, however when I try to do the restore I get an error message about the database being in use and I have to take it offline in order to complete the restore. So it appears to me that the backup and restore method DOES require down-time. Can anyone clarify and honestly tell me what the best method is?
-Al
This statement is in correct:
"You should note that you cannot re-attach more than 16 files for a single database."
I routinely re-attach databases with 25 files.
I tried this but it does not work... any ideas?
exec sp_attach_single_file_db N'MyxxDb' , N'E:\SQLServerData\MSSQL\Data\xx_Data.MDF'
I got the error and the log file is not created...
I tried also:
EXEC sp_attach_single_file_db @dbname = N'Altiris', @physname = N'E:\AeXNS_Data.mdf'
EXEC sp_attach_db @dbname = N'AeXNS_Data', @filename1 = N'E:\AeXNS_Data.mdf', @Filename2 = N'F:\AeXNS_Log.LDF'
nothing is working...
