| Author |
 Topic  |
|
|
mortioli
Average Member
United Kingdom
898 Posts |
 Posted - 11 January 2005 : 16:29:30
|
Is it possible to downgrade from MS SQL, back to Access?
The only option I have with my host is to export the MS SQL database into a backup file (.bak), but can I somehow convert this to Access?
Thanks in advance! |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 12 January 2005 : 05:35:44
|
Possible, yes. To do it, I would advise you to create a blank forum, using an Access DB, adding all the mod Db changes. Then use SQL Server DTS to export the data to the Access DB, making sure you set the transform column for each table so that existing data is deleted and enable identity insert is checked.
This is the only way I know to ensure that you'll get both DB structure and data correctly "transferred" from the SQL Server DB. If you wanted just the data, as a temporary backup, you could simply import the data from Access itself. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
 |
|
|
mortioli
Average Member
United Kingdom
898 Posts |
Posted - 18 January 2005 : 16:42:54
|
| Sorry, how do I get SQL Server DTS ? |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
mortioli
Average Member
United Kingdom
898 Posts |
Posted - 20 January 2005 : 14:51:53
|
| OK, I've installed the SQL Server Trial Version, but I can't for the life of me find out how to restore from a .bak file. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
mortioli
Average Member
United Kingdom
898 Posts |
Posted - 25 January 2005 : 03:29:14
|
Think I have it sorted now!
I just need to create a blank DB with all the mod changes, and work out the enable identity insert etc.
Thanks for your help! |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
mortioli
Average Member
United Kingdom
898 Posts |
Posted - 25 January 2005 : 18:13:30
|
Sorry for being a pain in the neck...
How do I set the transform column for each table, and enable identity insert?
What I've done so far is;
Downloaded the .bak file
Created a new SQL database (on my PC) called MortiOli
I've restored this database from the .bak file, ticking 'Force Restore Over Existing Database' and changing the location of where it will store the .mdf and .ldf files, to where the .bak file is stored
Then I right click on the SQL MortiOli database, and choose 'Export Data' |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 25 January 2005 : 21:38:47
|
After choosing the option to Export Data, it will ask you to specifiy the source and destination for that operation and then it will allow you to define the tables involved in the operation (which should be all). For each table you select, there is a small button in the transform column, which you should click. When you click it, it opens a dialog box with two tabs: column mappings and transformations. On the column mappings tab you should click the option to delete existing rows in the destination table. The option to enable identity insert, when the destination table belongs to an Access DB is not accessible. To make it accessible, click the transformations tab and then click the column mappings tab again, without changing anything in the transformations tab. This should make the enable identity insert check box accessible and you should just select it, by clicking it with the mouse.
You need to do this for each destination table. Remember to setup the forum with the access DB before doing the export, so that all the access tables are created before the export operation is started. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
mortioli
Average Member
United Kingdom
898 Posts |
Posted - 26 January 2005 : 03:44:42
|
Thank you so much!
When I click 'delete rows in destination table', then select the 'transformantions' tab and back again, 'enable identity insert' is enabled, but it defaults back to having 'append rows to destination table' selected. I take it I click 'delete rows in destination table' again, before selecting 'enable identity insert.'
Thanks for all your help, it's greatly appreciated!
EDIT - I selected all the correct items etc, and ran the exporting. However, I received 3 errors;
'Delete from Table FORUM_MEMBERS Step' - The record cannot be deleted or changed because table 'FORUM_MODERATOR' includes related records.
'Copy Data from FORUM_MEMBERS to FORUM_MEMBERS Step' - Not run
'Copy Data from FORUM_REPLY to FORUM_REPLY Step' - Error at Destination for Row number 1. Errors encountered so far in this task:1. You cannot add or changed a record because a related record is required in table 'FORUM_TOPICS'
'Copy Data from FORUM_TOPICS to FORUM_TOPICS Step' - Error at Destination for Row number 872. Errors encountered so far in this task:1. The field 'FORUM_TOPICS.T_POSTS' cannot contain a Null value because the Required property for this field is set to True. Enter a value in this field.
Nothing's ever straight forward   |
Edited by - mortioli on 26 January 2005 03:52:49 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 26 January 2005 : 05:47:12
|
| Probably the simplest way to delete the records is to open the DB in Access and delete them all manually, in the adequate order to avoid those error messages. Then you can just skip the option to delete existing records. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
| |
Topic |
|
Topic Locked
