February 25, 2014 at 5:27 am
Hi,
quick sanity check here: migrating from SQL 2005 to 2008 R2. I'm looking to take a full bk up on Source 2005 instance and copy/ restore to 2008. I am then propsing to apply the checkdb/reindex/update stats/compatibility/etc scripts on the migrated databases.
During the migration I will set read-only on source, take a differential and apply this to target instance to get latest data. My question (sorry bit longwinded) is can I take a COPY_ONLY backup (on the target instance) after the checkdb/reindex/update stats/compatibility/etc scripts but before I apply the differential but it won't impact the differential?
qh
February 25, 2014 at 5:56 am
quackhandle1975 (2/25/2014)
Hi,quick sanity check here: migrating from SQL 2005 to 2008 R2. I'm looking to take a full bk up on Source 2005 instance and copy/ restore to 2008. I am then propsing to apply the checkdb/reindex/update stats/compatibility/etc scripts on the migrated databases.
During the migration I will set read-only on source, take a differential and apply this to target instance to get latest data. My question (sorry bit longwinded) is can I take a COPY_ONLY backup (on the target instance) after the checkdb/reindex/update stats/compatibility/etc scripts but before I apply the differential but it won't impact the differential?
qh
If I got your question.
You are planning to take a full backup and restore it to target with norecovery and then apply differential with recovery.
If this is the case, you cannot take any backup in leaving in restoring mode.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 25, 2014 at 6:37 am
quackhandle1975 (2/25/2014)
I'm looking to take a full bk up on Source 2005 instance and copy/ restore to 2008. I am then propsing to apply the checkdb/reindex/update stats/compatibility/etc scripts on the migrated databases.
CheckDB/reindex/stats/compat level/etc require that the full backup was restored WITH RECOVERY. Once the database has been recovered, no further backups can be applied. To restore that differential that you're planning to take, you'd have to restore the full backup again, from scratch, then restore the diff, then run all your checkDB, reindex, update stats, set compat level, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 25, 2014 at 6:49 am
Muthukkumaran/Gail,
Much appreciated, glad I asked, it did seem far too simple in my head! :crazy:
Now I'm looking for a version of sql server where a database in recovery can still be used for checkdb/reindex/etc. 😉
Rgds,
qh
February 25, 2014 at 1:00 pm
We usually run the diff restore with recovery. Then kick off your scheduled CheckDB job to verify all restored DB's are clean.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply