I always seem to find myself in these situations. The type of situations where you wished you had not put something off and just done it at the time. The type of situation where you ask yourself why do I make life so difficult for myself.
My colleague had to work from home in the afternoon and since I was not interacting with anyone else at work I decided to finish up the last two hours of work for the day at home. I, however, needed an up to date copy of the database to carry on at home.
I copied a database backup to a flash drive and ran out the door to avoid traffic. The backup file was a cool 3.5 GB and the restored copy on my work machine was about 10 GB. With 20GB free on my harddrive at home I was rather certain I would have enough space. Oh boy was I wrong.
When I tried to restore it I was met with this:
It then dawned on me, this is a copy of a production database that had been anonymized and it had been reduced by 30GB during the process of anonymization. It also dawned on me that the process did not include shrinking the files and forcing them to release all the free space before doing the backup.
So even though the restored database was only 10GB, the database files are actually still 60GB even though 50GB are filled with nothing.This means during the restore it needs to allocated 60GB of the drive space to the database files .
I have a 128 GB SSD, which after all the software I have installed, leaves me with about 40 GB for data. It was now that I had the feeling that I should probably just have stayed at work. But I hadn't, so what now? Drive back to work ?
A quick search on the Google machine painted a gloomy picture of having to either write code that I can't test or driving back to work. I then stumbled across this stack overflow post and decided, like any desperate person, to go down the list and try each productone by one.
Luckily I didn't have to go past the first one: Apex SQL Restore.
I signed up for the free 14 day trial and after installing it, I was presented with this screen:
I was perplexed by this virtual database concept and proceeded to blindly click "Add virtual database" and proceeded to see what happens next. After connecting to my SQL Server instance I was presented with this screen:
I selected the file and after a good 1 minute of waiting, a glimmer of hope appeared:
I very quickly clicked "Next" before the world could throw another curve ball. I waited a good 5-10 minute for praying for it to work correctly. It completed and it seemed too easy. I opened up SQL server Management Studio and saw the database was now attached:
My day had been saved and from here it was simple:
- I shrunk the database
- I shrunk the database files
- I backed up the Database again
The database restored happily and I was able to carry on working and get those 2 hours of work done. Apex seemed to mount the backup file and then attach it to the server and then proceed to act as the mediator. It is simply brilliant and I will be using it again.
In reality it had turned out to be a good 2 hour process to enable 2 hours of works. Yes I should have probably just stayed at work but as my fiance often says Dumb Luck strikes again.