Well I can tell you for a fact that when such a backup is opened in Notepad++ the syntax highlighting shows an error for every \' and I can also tell you that when such a backup is restored there is missing content and broken links all over the place. So to my mind this indicates quite a problem.
On the other hand when I get a backup with the apostrophes saved in a different format (either from my local host or by exporting a table directly from phpMyAdmin) then the syntax highlighting is correct and restoring does not result in dropped content.
What this means is that even if you think \' is the correct syntax my server happens to disagree with you, and so does the bloke who programmed Notepad++.
ETA: I'll give you two different examples of syntax that does work.
First, this:
-- Dumping data for table `smf_calendar_holidays`
--
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(1, '0004-01-01', 'New Year''s');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(2, '0004-12-25', 'Christmas');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(3, '0004-02-14', 'Valentine''s Day');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(4, '0004-03-17', 'St. Patrick''s Day');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(5, '0004-04-01', 'April Fools');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(6, '0004-04-22', 'Earth Day');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(7, '0004-10-24', 'United Nations Day');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(8, '0004-10-31', 'Halloween');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(9, '2004-05-09', 'Mother''s Day');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(10, '2005-05-08', 'Mother''s Day');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(11, '2006-05-14', 'Mother''s Day');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(12, '2007-05-13', 'Mother''s Day');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(13, '2008-05-11', 'Mother''s Day');
Note that the apostrophes are saved as ''. This is actually two singe apostrophes, not one double. This works for both syntax highlighting in Notepad++ and for restoration of content without any dropped sections, etc.
The other syntax which works is this:
# Dumping data in `smf_calendar_holidays`
#
INSERT INTO `smf_calendar_holidays`
(`ID_HOLIDAY`, `eventDate`, `title`)
VALUES (1, '0004-01-01', 'New Year's'),
(2, '0004-12-25', 'Christmas'),
(3, '0004-02-14', 'Valentine's Day'),
(4, '0004-03-17', 'St. Patrick's Day'),
(5, '0004-04-01', 'April Fools'),
(6, '0004-04-22', 'Earth Day'),
(7, '0004-10-24', 'United Nations Day'),
(8, '0004-10-31', 'Halloween'),
(9, '2004-05-09', 'Mother's Day'),
(10, '2005-05-08', 'Mother's Day'),
(11, '2006-05-14', 'Mother's Day'),
(12, '2007-05-13', 'Mother's Day'),
(13, '2008-05-11', 'Mother's Day'),
(14, '2009-05-10', 'Mother's Day'),
(15, '2010-05-09', 'Mother's Day'),
(16, '2011-05-08', 'Mother's Day'),
(17, '2012-05-13', 'Mother's Day'),
(18, '2013-05-12', 'Mother's Day'),
(19, '2014-05-11', 'Mother's Day'),
(20, '2015-05-10', 'Mother's Day'),
(21, '2016-05-08', 'Mother's Day'),
Again, this is fine for both highlighting in Notepad++ and for error-free restoration of content. Of course I'm currently running ISO not UTF-8.
Anyway the point is that the current way that both SMF and the cpanel SQL manager want to save apostrophes does not work in practice. It's that simple. The only way I can get an uncorrupted table exported from my online server is to export straight from phpMyAdmin, and when I do that apostrophes are not saved as \'
When I export from my local host using SMF admin the backups are fine, and once again the apostrophes are not saved as \'
This has to be a server configuration problem. Something in the way my online server is set up is causing both SMF admin and the cpanel SQL manager to bugger the syntax in backups.
