Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Migration and Copy_Only backup... Expand / Collapse
Author
Message
Posted Tuesday, February 25, 2014 5:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 2,394, Visits: 957
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


SQL 2K acts like a spoilt child - you need to coax it round with lollipops.
Post #1544853
Posted Tuesday, February 25, 2014 5:56 AM This worked for the OP Answer marked as solution


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:38 PM
Points: 1,155, Visits: 4,640
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

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #1544859
Posted Tuesday, February 25, 2014 6:37 AM This worked for the OP Answer marked as solution


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 42,812, Visits: 35,931
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1544877
Posted Tuesday, February 25, 2014 6:49 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 2,394, Visits: 957
Muthukkumaran/Gail,

Much appreciated, glad I asked, it did seem far too simple in my head!

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


SQL 2K acts like a spoilt child - you need to coax it round with lollipops.
Post #1544881
Posted Tuesday, February 25, 2014 1:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 1:07 PM
Points: 39, Visits: 577
We usually run the diff restore with recovery. Then kick off your scheduled CheckDB job to verify all restored DB's are clean.
Post #1545122
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse