August 27, 2016 at 3:31 am
erico (8/25/2016)
Detach/Attach can only be done with databases in the same SQL Server compatibility mode - meaning you can only for example Detach/Attach a 2016 database in the SQL server 2016 version; whereas a SQL Server 2008 database backup can be restored onto another version of SQL, say, SQL Server 2016.
That's not true. You can detach/attach across the same versions that you can backup/restore.
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
August 27, 2016 at 3:36 am
DaleB (8/26/2016)
In the context of this thread why is a detach/attach upgrade process more risky than a backup/restore?
Mostly because it doesn't leave you an intact copy of the DB on the old version. SQL database upgrades are a one-way process.
If you were to do a side-by-side install of SQL 2008 & SQL 2016, detach the DB from 2008 and attach it to 2016 and then either something goes wrong with the DB upgrade process or in your post-upgrade checks notice something that mandates a revert back to 2008, you can't detach the DB from 2016 and attach it to 2008, you'll have to go and fetch the pre-upgrade backups (assuming those got made) and restore to 2008.
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
August 27, 2016 at 1:48 pm
The Detach/Attach operation might be doable going across different versions from operation standpoint, but it is very important to have compatibility modes in sync from the perspective of database integrity. Good thing you resolved to changing the compatibility mode, that ensures that erroneous results will not creep in when running queries or doing other such operations due to a faulty compatibility mode setting.
Possible risks associated with using either Detach/attach or Backup/Restore:
Let me say from a general use point, there is no inherent risk involved with either operation as both options can be used for disaster recovery purpose.
Backup/Recovery is the recommended mechanism for the routine database maintenance and optimum availability. A restored database is only as good as the backup. If you have a good backup chances are that, with all other things considered, the restore will be good as well. In other words, there is no need to use Detach/Attach is you have a good known backup.
From a troubleshooting perspective, it could be more efficient and faster for example to "detach" a database with a overblown/corrupt log file and "attach" it back using a SINGLEFiLE option, which restores the data file(s) and recreates the log. This is usually done when Full recovery mode has been accidentally left in place of a Single recovery option. So if maintaining a log file isn't the high priority, Detach/Attach can come handy. Understandably, a SHRINKFILE option can be leveraged when doing a database backup, the result is not always as dramatic (disk space reclaim-wise) as when you do Detach/Attach. And if the you have a corrupt database you might not be able to backup the database in the first place.
August 27, 2016 at 3:40 pm
erico (8/27/2016)
The Detach/Attach operation might be doable going across different versions from operation standpoint, but it is very important to have compatibility modes in sync from the perspective of database integrity. Good thing you resolved to changing the compatibility mode, that ensures that erroneous results will not creep in when running queries or doing other such operations due to a faulty compatibility mode setting.
Huh? Detach/attach and backup/restore both leave the DB in the compat mode that it was when backed up/detached.
From a troubleshooting perspective, it could be more efficient and faster for example to "detach" a database with a overblown/corrupt log file and "attach" it back using a SINGLEFiLE option, which restores the data file(s) and recreates the log.
And sometimes leaves the database unusable and irreparable. Detaching and deleting the log file IS NOT EVER a good thing to do. It's definitely not how you deal with large log files.
And if the you have a corrupt database you might not be able to backup the database in the first place.
And if you detach a corrupt database, the chance of being able to attach it again is rather small.
Not that either has much to do with SQL upgrades.
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
August 29, 2016 at 6:35 pm
Huh? Detach/attach and backup/restore both leave the DB in the compat mode that it was when backed up/detached.
This was part of the answer as a reply to my initial comment: The attach process goes through a database upgrade process and finishes. Once the attach process is complete, you can change the database compatibility mode to 130 (2016).
This indicates that compatibility settings need be adjusted following an upgrade of the database to the newer version.
And sometimes leaves the database unusable and irreparable. Detaching and deleting the log file IS NOT EVER a good thing to do. It's definitely not how you deal with large log files.
Agreed, as a best practice. But this can be a judgement call (case specific) if, as I have mentioned in my answer about having SQL Server regenerates a log file if the database usage is not transaction-based, that's if placing the database in SIMPLE recovery mode is acceptable instead of leaving it in FULL recovery which is the default setting.
And if you detach a corrupt database, the chance of being able to attach it again is rather small.
That's semantically correct. Remember that a database while being functionally unitary, is made of two separate files: the data file(s) which can be attached independently of the log file which is the other file. As a due diligence, you must first resolve what type of corruption the database is under. If for example the database files are without problem and the issue is with the log, and you have not run and/or are not running Data Manipulation Language (DML) type of statements, do not have transactions that have not been committed from which you could incur data loss, SQL Server provides a reliable recovery of the database through Detach/Attach procedure. So, does the chance for recoverability get smaller with a corrupt database with respect to the use of Detach/Attach? Yes. Is recoverability impossible after all? No.
Not that either has much to do with SQL upgrades.
Agreed. Both are more a mean to an end to the extend that either can be leveraged during an upgrade, but hardly an end to itself as there many other components that make up an upgrade process.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply