All Use Cases
You can migrate your existing file repository to a server repository using SQL Server Compact Toolbox.
- Server repository is a SQL Server Express, Standard, Business Intelligence or Enterprise.
- File repository is a SQL Server Compact file database.
Step 1: Create new server repository
This is covered in this article:
Use the same Dataedo version as you used to connect to your file previously to avoid version mismatch.
Step 2: Prepare the repository
To avoid issues during the copy you will need to empty the target repository. First, turn off all constraints in the repository database:
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
Then connect to it with SqlServer Management Studio (or equivalent), and run the commands below to clear it. Change your_new_repository to your repository name first:
use [your_new_repository]
delete from [dbo].[documentation_custom_fields]
delete from [dbo].[custom_fields]
delete from [dbo].[unique_constraints_columns]
delete from [dbo].[unique_constraints]
delete from [dbo].[triggers]
delete from [dbo].[erd_nodes_columns]
delete from [dbo].[erd_nodes]
delete from [dbo].[erd_links]
delete from [dbo].[tables_relations_columns]
delete from [dbo].[tables_relations]
delete from [dbo].[tables_modules]
delete from [dbo].[procedures_modules]
delete from [dbo].[parameters]
delete from [dbo].[procedures]
delete from [dbo].[tables]
delete from [dbo].[dependencies_descriptions]
delete from [dbo].[dependencies]
delete from [dbo].[ignored_objects]
delete from [dbo].[modules]
delete from [dbo].[licenses]
delete from [dbo].[version]
delete from [dbo].[databases]
delete from [dbo].[triggers_changes]
delete from [dbo].[tables_changes]
delete from [dbo].[schema_updates]
delete from [dbo].[tables_relations_columns_changes]
delete from [dbo].[glossary_mappings]
delete from [dbo].[glossary_term_relationships]
delete from [dbo].[glossary_terms]
delete from [dbo].[glossary_term_relationship_types]
delete from [dbo].[glossary_term_types]
delete from [dbo].[classificator_rules]
delete from [dbo].[classificator_masks]
delete from [dbo].[classificators]
Step 3: Copy data
To extract the data from .dataedo file, you will need to install 2 additional components. First, download and install SQL CE components from Microsoft Download Center. Then download the exportsqlce40.zip file from ErikEj's SQL CE Toolbox repository and unzip it.
Edit the data source in the script below so that it points to the Dataedo file you want to migrate from, then run the command in the folder where you unpacked the ExportSqlCe40.exe file.
ExportSQLCE40.exe "Data Source=C:\sqlce\Documentation1.dataedo;" DataedoExport.sql dataonly
This will generate an .sql file (DataedoExport.sql) holding all data from your repository.
Run the generated script in your server repository through SSMS to transfer data to your new repository.
Few last commands (those related to setting identity) may display an error, but this shouldn't cause any issues - you can test if the identity values are correct by running:
select IDENT_CURRENT( 'columns' )
This should display the same number as the script was trying to set.
Step 5: Reapply the constraints
Now reapply the constraints turned off in step 2 (skipping this can cause errors in the future):
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
Step 6: Add license for your user
To connect to your repository, every user will need a license specified. To do this, open Dataedo Administration console and log in to your server repository with either the user that created it, or one that has the db_owner role.
Find the username that will use Dataedo, then click Edit user, in the new window check the Is Dataedo user box, then fill in the license key (or leave it as Lite if you're using the Lite version), then click the Save button. See also Detailed guide to user management.
You can now log in and use Dataedo with the server-based repository.