Tag Archives: data security

Data Security, Toolbox

Let’s talk about data security: Restoring a TMS database

1 Comment

So, yesterday we took a backup of our TMS database. Today, we learn how to restore it. This is also a check you should be doing after having taken your first backup and also regularly after you have taken backups because like I have mentioned here otherwise you can’t be sure you have backed up anything. Or, like a friend of mine who deals with a lot of IT messes put it:

“I bought a book!”
“Are there words inside it?”
“Huh? Of course. I didn’t check, but there are always words inside a book, right?”

Before you haven’t checked, you just assume, you aren’t sure. And if there is one rule every collections professional knows by heart it is this: Never assume, always make sure!

Step 1: Again, log into the server you want the database to restore to and open Microsoft SQL Server Management Studio

You already know how to do that by now. If not, re-read step 1 in this article.

Step 2: Navigate to the Restore menu

Go to the folder “Databases”, right-click on it and select “Restore Database…”

Screenshot showing the tree structure from SQL Server Management Studio with the right-click menu enhanced on "Databases" and the menu item "Restore Database..." selected.

Step 3: Chose your backup file

Up comes a rather bleak screen:

Restore Database menu from SQL Server Management Studio with no file selected.

Go to “Device” and click on the three dots …

Same menu as in previous screenshots but with the radio button "Device" selected and an arrow pointing to the three dots menu.

You get another rather bleak screen from which you choose “Add…”

hot with a menu showing Backup type "File" and the option "Add..." to which an arrow points.

You are getting a look at your file system from which you select the backup file you want to restore from. You might remember I cautioned you to store it in a place you can easily find it in Step 6 in in the previous article, right?

Screenshot showing the menu with the file system. The folder "M:\Backups" is open and within it the file "AfterCI2025.bak" is selected

When you have found the right file, you click “OK”.

Same screen like the one before the last screenshot but now you see the file "M:\Backup\AfterCI2025.bak" in the window.

On the next screen you also click “OK”.

Step 4: Restore your database

Now you are on this screen again, but now it is populated with your chosen backup, including the date and time it would restore to:

Same screen as the first screen in step 3 but now the backup file shows up.

You can see how my database “Leer” (yours might be called “TMS” or something else) is showing up both as the source and as the destination. If you really want to overwrite your current database this is fine, for example because something went horribly wrong with your current database and you want to restore it to an older version.

But if you just want to test if our backup file is okay, you don’t want to do that! Imagine something went wrong with the backup. We would be overwriting our totally fine current database with a corrupted backup! Big mistake!

So, for testing, instead of the “Leer” as destination I simply typed another name. I chose “TMSTEST”:

Same screen as previously but this time there is "TMSTEST" instead of "LEER" in the Destination Database: line.

Then I clicked “OK”.

Same screen as before but now there is a line saying "Restoring M:\Backup\AfterCI2025.bak" at the top and a progress bar.

The database will now be restored to a new destination. If the backup file is okay and you have enough storage space you will get this screen after a while:

Same screen as before with a pop-up window "Database "TMSTEST" restored successfully".

You can now click “OK”.

Step 5: Test your database

You should now see an additional database in your databases folder (mine shows up, of course, as “TMSTEST” because I called it that way):

Screenshot showing the tree structure from SQL Server Management Studio showing an additional Database "TMSTEST" now.

If that worked fine, your backup file is okay. But just because I am a bit anal about my data, just to check, just to make sure, I run the mother of all TMS queries: “Select * From Objects”.

SQL query "Select * from Objects" run on TMSTEST giving back 67 rows with objects.

Only when it runs smoothly and the number of objects I get back matches my expectations, I am satisfied.

Housekeeping

Backup files are rather large. Which is logical, because they contain all your valuable data, right? So, after testing to make sure my backup is okay, I deleted that new database again by right-clicking on TMSTEST and choosing “Delete” so it doesn’t clog my server:

Screenshot showing the tree structure from SQL Server Management Studio with TMSTEST selected and the right-click menu enhanced with the item "Delete" highlighted.
Screen showing the "Delete object" menu, the checkbox "Delete backup and restore history information for databases" checked.

Also, because the backup files are so large, I tend to compress them before I move them to a different server. I use the software 7-Zip for it since it proved to be rather reliable (https://www.7-zip.org/). If you don’t have it on your server, you need to install it, first.

Go to your Windows Explorer and find your backup file. Right-click on it, choose “7-Zip” and select the option “Add to [whatever your backup is called].7z”.

Screenshot from the Windows explorer with the right-click menu open showing highlighted "7-Zip" and, following that "Add to "AfterCI2025.7z".

You can now see how the backup is compressed. Wait until it is done (fetch a coffee or a tea, this might take a while, depending on the size of your database).

Screenshot showing the progress bar with some data like the size, speed, and compression rate while the file is being compressed.

After it is finished, you will see a second file on your file system:

AfterCI2025.7z with 770.696 KB and AfterCI2025.bak with 8.195.192 KB

You will notice how much smaller the compressed file is. This is much easier to move to another server, unpack, and restore there, right?

Because I am paranoid I will move the .7z file to a cloud storage that I trust now and try if I can unpack it there without issues. If that is the case I can go back and delete the .bak file and just retain the smaller .7z file.

Take your backups, take them to a safe location, and take good care!

Angela

Data Security, Toolbox

Let’s talk about data security: How to back-up your TMS database

1 Comment

This is a step-by-step guide on how to backup your database if you are using a product of The Museum System (TMS) by Gallery Systems. If you use a different system it will work differently. Ask your vendor about it.

Step 1: Log into your database server and open Microsoft SQL Server Management Studio

You usually find it fastest if you start typing “SQL Server Management…” into the Windows search box.

Screenshot of the search box next to the windows icon, you can see somebody started typing "SQL..." and the option "SQL Server Management Studio 20" pops up as first choice.

Step 2: Enter your credentials

You will be prompted to enter your login credentials. If you are on NT Authentication usually all you have to do is click on “Connect”. If you have another form of authentication you will have to enter those login details. Your IT will tell you what to enter in that case.

Login screen of SQL Server Management Studio.

Step 3: Find your database

In the tree hierarchy, open the folder “Databases” and find your database. It is usually called something like “TMS”. In my case it is called “Leer”.

Screenshot from SQL Server Management studio navigation tree. The databases file folder is opened so you can see all the databases, the database "Leer" is enhanced so you can see the menues underneath it.

Step 4: Navigate to the backup menu

Right click on your database, choose “Tasks” and then “Back Up…”

Screenshot from SQL Server Management studio navigation tree. It shows the enhanced menu you get when you right-clicked on your database, highlighting Tasks and Back Up...

If that option is greyed out, you might not have the rights to do this. in which case you should talk to your IT so you get those rights.

Step 5: Chose your backup method

You will get to this screen:

Screenshot showing the options of the backup dialogue. You can see the Database chosen is "Leer", full is chosen from the dropdown, the checkbox "Copy-only backup" is unchecked, the Back-up component is "Database", Back-up to has "Disk" chosen and the box below that is empty.

Here you can choose if you want to do a full or differential backup (we talked about that here). You select that in the drop-down “Backup type”. We chose “Full” for this backup.

As a destination, usually “Disk” is fine, since you probably want to have the backup on your computer first and then transfer it to a cloud later.

Sometimes you will see a backup file already in the screen below that. If that’s the case, remove it, first.

Then click on “Add…”

Step 6: Add the file you want to back up to

Screenshot showing the window that lets you choose your destination for your backup. Selected path is "M\Backups", Files of type dropdown has chosen "Backup Files (*.bak, *.trn) and the File name entered is "AfterCI2025.bak"

By default, Microsoft suggest a rather cryptic sub-folder for your backups. I’d recommend adding a folder in a more prominent place that you can easily find and back up to there. You can see mine being “M:\Backups”.

Enter a file name for your backup. This can be the date you took it (Best Practice is to note the date in a year-month-day format so you can easily sort by date if you have multiple backup files) or a significant pointer to when you took it, for example “BeforeUpgradeTo995” if this is your backup before upgrading to a new version. Don’t forget to add “.bak” as a file ending, otherwise you might run into difficulties to restore it, later.

You can see that I called mine “AfterCI2025.bak” because it is the backup I took after adding a significant amount of information from our user conference.

Once you entered the name, hit “OK”.

Step 7: Take your backup

Screen from Step 5 again, but this time the box shows the backup file name we chose in step 6.

After that you just need to click on “OK” and your backup will be taken. If you have enough disc space in your chosen location, all is fine, otherwise it will throw an error message.

Same screenshot as before but now there is a message "The backup of database "Leer" completed successfully.

That was it. Wasn’t too hard, was it? And now you are good to go and bring your database to a safe location. I will do another post on how to compress the backup and restore it on another server.

Take your backup and take care!

Angela

Articles, Data Security, Toolbox

Let’s Talk About Data Security – Backups

Leave a comment

As collections professionals we are trained to think about security. We constantly make sure that nothing gets damaged and lost, may it be in our own storage or while on loan, perhaps traveling from continent to continent for a new exhibition. But when it comes to data security we often rely on our IT departments and database managers. In a changing world we need to add data security to our registrar’s toolkit because if we don’t care about it, perhaps no one will be left to care about it. So, I am planning on writing a series of short articles on that topic.

Now, I am not an IT expert by any means. I am basically pulling together what I have learned over the years, drawing from resources I have at hand, ready to stand corrected and update you if something I wrote could be done better, easier, and/or more secure. I am thinking in this day and age, any guidance and ideas on how to safeguard our intellectual heritage is better than doing nothing at all. Feel free to contribute with your own sources and ideas.

I am starting with what I feel most comfortable writing about: Backups.

How often should I back up my database?

This is a risk analysis: How serious will losing all your data since you backed up the last time be? In some cases, once a week can be sufficient if you are the only person who works with it, you have all your changes tracked in another medium (for example written notes on paper), and you don’t enter more than just a few records a day. But if multiple people enter and change data during the day? Well, once a day seems highly recommendable, then.

What is the difference between full backup and differential backup?

A full backup stores ALL data of your database. A differential backup only records the changes to the last time you did a full backup. Which one to use when is about analyzing the risks associated with it. A database can get compromised without you noticing right away. In this case it is good if you can revert back to a full backup of an earlier stage, before it became corrupted and then try to extract the data that was added at a later stage from the other backups.

What backup method should I choose and how many backups shall I retain?

There are no hard rules and usually it is best to talk to experienced users of the same collections management system and to the vendor about what makes sense in your use case.

My rule of thumb: If I know I am entering more than ten records each day and do a lot of updating of other records, I will go with a differential backup every day and a full backup once a week. I will keep the backup of the last five days and a full backup from each of the previous four weeks.

But this is tailored for the case where only I enter data and nobody else. If you have a lot of people entering data there are more options of something going wrong, therefore you will want to do backups more often. This is of course also a question of how much storage space you can afford, but then again, you have to factor in the costs of losing data and the hours it takes to re-enter it. Do a proper risk analysis for your institution, then set up a fitting backup routine.

Where shall I store my backup?

Storing your database backup on the same computer you took it is as good as having not stored it at all! When your computer is destroyed either physically pr by a virus, you will have lost both your original database AND your backup.

Best practice is to have three instances of your data:

  • the original
  • a backup on site
  • a backup offsite

A cloud storage might be a good idea for the latter. In this day and age, maybe even a cloud storage outside of your own country. That way, if you are forced to delete data from your database (if this sounds like a far-fetched idea, let me remind you of this https://www.theguardian.com/us-news/2025/mar/07/military-images-trump-dei) your data will still be somewhere safe and unchanged.

You can also use an external hard drive that you store somewhere safe, preferably outside of the town or city your original database is situated because if there is a catastrophe in this area, your data might still be safe somewhere else. It has the advantage that you pretty much can control where your data goes and that it can’t be hacked, but the disadvantage is that if something happens to that hard drive, the data is lost.

In comparison, a cloud usually has its own backup routines that make sure that your data is safe. Ask the provider about it. Also ask them about their security measures and what data they share with third parties. Only you should have access to your data, nobody else.

Heads up: Make sure your data is actually backed up!

Just because you have taken a backup doesn’t necessarily mean you have a working backup. Once you have created a backup file, try if you can restore it. Caution: Restore it to a separate space, don’t use it to restore your actual database because you risk damaging a working database with a corrupted backup. Check this regularly and don’t assume that just because you can see a backup file on your drive your data is actually fine.

Final thoughts on when to delete backups

As said before, it is good to retain some backups because not all problems are discovered right away and it might take weeks to discover them. This is about keeping your current data safe and retrievable.

But you also might want to preserve the state of your current research. In the future, you might want to come back and compare how facts were recorded in 2024 and how that changed going forward. Your past records may become sources for future scientists and historians. So, it might be a good idea to take a backup NOW and keep that backup in a safe space for the future.

Next up I will be showing you how to take a backup if you are using TMS or TMS Collections. The way it is done in your software might be different, but perhaps it is a bit similar.

Take a backup now and take care!

Angela