How to clean up the Repository

  • Article

Have you ever wondered, after a session of experimenting and playing with the M tools and the Repository, if there was a simple and reliable way to get your Repository back to its original state? A clean slate, of sorts, without the need to reinstall everything? Well, here’s one way – and while it might not be absolutely perfect, it’s the best way we know, and recommend (just ask your db admin friend): detaching the database and creating a copy.

 

Here’s some simple SQL and the steps you need to perform right after a new Repository installation. You can use SQL Server Management Studio to do this.

 

1. Find the name of the database file. You will need to know where the file is to attach the database after you detach it.

select physical_name from sys.database_files where type_desc = N'ROWS';

2. Find the name of the log file, for the same reason as #1.

select physical_name from sys.database_files where type_desc = N'LOG';

3. Detach the database. In order to be able to safely copy the files, you have to perform this operation.

alter database [<db_name>] set single_user with rollback immediate;

execute sp_detach_db @dbname=N'<db_name>', @keepfulltextindexfile=N'true';

4. Copy the files to a safe location. You can compress them, if you want to later move them between many locations, or use them many times and keep your space.

5. Attach the database. Here you use the file names you found in points #1 and #2.

create database [<db_name>] on

  ( filename = N'<data file path from point #1>' ),

  ( filename = N'<log file path from point #2>' )

  for attach;

 

6. Run the server instance repair code.

 

This is a very important step: it will fix Repository-related items on the server – such as triggers – so they work properly after detach/attach. It will also fix the database owner to be ‘sa’, because the attach/detach operation changes the owner to whoever performed it. These operations are what your db admin friend would normally do after detaching and attaching a database.

 

execute [Repository].[InitializeServer]

7. Work, play, experiment with Repository!

8. Detach the database (#3).

9. Copy files back from safe location (uncompress them, if they were compressed).

10. Attach the database (#5).

11. Run the server instance repair code (#6), and voilà, your Repository is back in its clean state.

12. Rinse and repeat, from steps #7 here on out.

 

There are alternatives, when it comes to cleaning up Repository, and you may have used some of them to date: backup/restore is one of them. We do not recommend this for restoring clean database state – it is intended for disaster recovery, and is much less reliable than attach/detach shown here.