| Author |
Topic |
|
MSulewski
Starting Member
3 Posts |
 Posted - 2010-05-27 : 11:12:40
|
| This may be a pretty basic question but:Is there any problem / drawback of using a copy of a Database file on a different SQL server?For instance I make all my updates on Server 'A', then I copy the MDF and log to Server 'B' attach the MDF and log, and let all lookups be done there. Both servers are running the Same OS and and SQL version. The only difference is the log location.I have had some people tell me the only correct way to move a DB to another server is to do a Backup and Restore.Thanks-Mike- |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
MSulewski
Starting Member
3 Posts |
Posted - 2010-05-27 : 11:40:25
|
| The DB in question is very large, and updates can be as large as 25 million records that may be updated or inserted at a time. The indexes on the DB are very large, so we tend to drop all the indexes during updates to the DB, and then rebuild them before putting the DB into production. Application performance and reliabilty are key factors, so making updates to a live DB is not a good option for us.An MDF copy takes only 4 hours to move from one server to the other, where a backup and restore take more than a day to complete.Thanks-Mike- |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-27 : 11:46:33
|
Up to you I think ...We NEVER detach a database unless we have taken a FULL backup - just in case it will not re-attach. If you have made that backup you might consider that it is better to Restore that, rather than Detach / AttachIf the database is in use (24/7 say) then DETACH cannot be done, as it would take the database offline, hence Backup/Restore is often used.Using RESTORE proves that the BACKUP is viable - which can be a nice-to-have side effect  If the database is big then the Backup file is likely to be significantly smaller than MDF file (for a database that is 1GB or 2GB it is unlikely to make much difference, time-wise, but possibly something to consider) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-27 : 12:07:36
|
"Why don't you instead use transactional replication?"I think its (often) a lot of bandwidth if all they need at the far end is a "snapshot as of last night". Just IMHO of course  " An MDF copy takes only 4 hours to move from one server to the other, where a backup and restore take more than a day to complete"Presumably you have the BACKUP anyway? If your database is huge I presume the Backup file is smaller than the MDF? (it should be! possibly significantly smaller depending on how you manage the growth of the MDF)So the physcial server-to-server copy time should be shorter (possibly not by much)That leaves us with the time to restore. If the database already exists (i.e. restore OVER an existing database, of approximately the same size), I've very surprised that it is taking many-many-hours to complete.If main database is running in FULL Recover Model then taking a TLog backup immediately before the FULL backup may reduce the size of additional transaction data that the subsequent Full Backup has to contain |
|
|
|
MSulewski
Starting Member
3 Posts |
Posted - 2010-05-27 : 12:09:18
|
| The DB in question has over 120 million records, eeach record can have 300 fields of data. We have 30 indexes to support various data lookups through our web-based count application. (Yes, "OMG!" *is* the proper reaction!) :)The MDF is 175 Gb without indexes, and 1 TB with the indexes. So due to the size, we have to get creative with migration and backup strategies. We can not just move the smaller DB and apply indexes because it takes up to 5 days to apply all the indexes.Thanks-Mike- |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-05-27 : 22:58:32
|
| You should consider using a third-party backup utility like Litespeed (Quest) or SQL Backup (Redgate). These utilities will compress the backup file up to 70-80%, reducing the amount of time to copy the backup file. The backup itself can be made faster as well as the restore.You should also validate whether or not you have instant initialization enabled (2005 or greater). This will eliminate the need to zero-fill the data file and shorten the restore time also.I currently backup an 800GB+ database nightly using Litespeed in less than 1.5 hours - and restore that database in less than 2 hours (it would be even less, but I also apply a full days worth of transaction logs). We use a DMX SAN, and have setup a BCV volume for our backups. Every night, we split the BCV volumes - mount them on the other system, perform the restores and re-establish on production. The whole process takes less than 2 hours to restore multiple databases including the 800GB database.All this is to say that you can do it - you just have to put together the right infrastructure to support what you want to do. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Using a Copy of MDF File