This blog post is more for my own personal notes so that I don’t bang my head against the wall so much next time, but hopefully it will help someone out one day as well.
I recently set up a new SSISDB Catalog, and needed to back up my Master Key after the catalog was created. This should be a simple and straightforward process, and I haven’t run into any problems doing this in the past. This time, however, I couldn’t get the backup to run.
For starters, here is my simple key backup script:
|
1 2 3 4 5 6 |
--back up master key USE SSISDB BACKUP MASTER KEY TO FILE = 'C:\backup\key' ENCRYPTION BY PASSWORD = 'YourPasswordHere' --password to encrypt the key backup file |
When running that script, I was immediately greeted with the following error:
Cannot write into file ‘C :\backup\key’. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
My first thought was: The directory path must be wrong. So, I verified that the ‘C :\backup\key’ directories did exist, and that they were spelled correctly in my script. I tried again and still got the error.
So, maybe the folder permissions are not correct? I check permissions and everything looked good. Just to see, I created new directories and modified my script and tried it again. Still got the error.
After a good bit of Googling and getting nowhere, I took the time to actually read the error I was getting (which, you would think would be one of the first things I did!) As it turns out, the very first line of the error told me exactly with the problem was.
Cannot write into file ‘C :\backup\key’. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
Do you see the problem now? My backup script was trying to create a FILE named “key”, but I already had a DIRECTORY named “key” in that location. One would think that it could differentiate between the two, and create a file named the same as a directory, but one would be wrong.
The Solution!
In this case, the solution was simple, all I had to do was either delete the “key” directory, or change the name of the file in my backup script (or change the path to put the file in the “key” directory). I chose to delete the directory since it was empty anyway. THAT FIXED IT!
So, what have we learned today?
1) Read the dadgum error message, it may actually tell you what’s wrong!
2) The TO FILE portion of a BACKUP MASTER KEY statement specifies the directory and file name of the file being backed up, instead of just the directory path to place the backup file.