August 2, 2005 at 8:20 am
I previously posted this in the discussion on Chris Kempster's article, but it seems no-one is reading that thread. Does anyone have an answer for me? Or at least give me the pros and cons?
Thanks.
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?
August 2, 2005 at 8:33 am
If you are simply wanting to move the files to a new "drive" but have it on the same instance of SQL Server it will certainly require some downtime regardless of the method used. Detach and Attach will be much faster and simpler than backup and restore. But do make sure that you have a good backup of the database, just in case...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
August 2, 2005 at 9:13 pm
The nice thing about backup and restore is that you can backup the database while the primary database is active, Copy the backup file while the primary is active, and restore the secondary (to a temporary database name) while the primary is active. You can then drop the primary database and rename the secondary to match the primary's name:
ALTER DATABASE MODIFY NAME
In this scenario the only down time is the time in between dropping the original primary and renaming the secondary, which should be minimal.
Hope this helps.
August 3, 2005 at 6:58 am
See if you can use my SP spF_build_import on http://www.sqlservercentral.com/scripts/contributions/1537.asp which will "Import database from source server using full and transaction log backups as recorded in msdb. Use it to import one or more databases to your development environment, to test your backups regularly or to move databases to a new server." We use it regularly at our site to move databases in production or to a development server. When we move a production database we put it in read only, do a transaction log backup, run the SP on the new server and then we change the table were we store info on where a particular database is located so that the application will use the proper database.
Lennart Gerdvall
payex.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply