This is one of those, very short, very focused blogs that actually first got me into blogging. It was a very good former colleague of mine over at OnTheFenceDevelopment who first switched me onto blogging and the value of it. Up until that point I just didn’t see the point, his strap-line at the time was ‘Because if I don’t write it down, I’ll forget it’ but it was more than that; For years I’ve been a consumer of the information out there, some good, some not so good and I suddenly realised how selfish I was being. Absorbing all of this information, honing it, correcting where necessary and then using it to make ME look good at my job without giving anything back. Thats why I started blogging.
So this reasoning behind this blog was that I came across a situation whilst migrating an application and database for a client that I had never seen before, after reading up a little I could see that there was some confusion in the wider community. This is my way of giving back. The issue clearly stated was that we were migrating a (full) SQL Server 2008 database to an (express) SQL Server 2016 instance, upon selecting the backup file instead of displaying the 1 backup within this .bak file as it should do absolutely nothing was displayed at all.
Nothing…
Normally I’ve only ever seen this happen when you try to import a newer .bak file into an older system. There were no error messages to tell me why, no hints at all. It just didn’t appear. Digging thorough the forums didn’t help much either, lots of people talking about: –
- File sizes supported by SQL Express (not my issues, the database was quite small)
- Corrupted .bak file which was also not the cost
- “Are you sure the target system is older?…” Yes I was.
- And many other random dead ends.
The actual reason in fact turned out to be decidedly easy, though a little opaque I feel. I decided to go back to basics and try and import the headers for the backup set using T-SQL command rather than the UI. So the following T-SQL was used and promptly failed.
RESTORE HEADERONLY FROM DISK = ‘C:\MyBackUpSet.bak’
Running this bit of SQL which will return the details of the backup set within this file failed because I did not have permissions to create a database, a fact which I then verified by trying to manually create a new database. It would have been lovely if the UI had have told me that instead of hiding this fact away from me and wasting my time chasing down vague errors. Anyway a call to the clients IT department later the user I had been assigned was granted the correct permission and lo and behold…. My back upset was now properly functional. This lack of ‘Create Database Permission’ was indeed the cause of the SQL Server Restore wizard not displaying the backup sets contained within my back up file. I’ve encountered this issue, so you don’t have to. Merry Christmas! Granted, the worst Christmas present ever…. but I’m not made of money y’all.