The backup contains two pieces of information:
The actual data in the backup is those 1.8 GB. But the metadata describes the layout of files as on the original production server. While it looks like compression, is not actual compression. Is just metadata vs. content. the problem is that during RESTORE the reporting machine creates a layout as described in the metadata, therefore 114.6GB + 24.8GB. Now for the real question: can you restore it w/o recreating the original DB file layout? No. However, there is another solutions to your problem: bring the original DB back in check. Shrinking the Transaction Log covers the steps you need to do, since the log is your major pain point. Next I would deploy log shipping instead of backup/restore.
Seeing that the recovery model is set to simple and msdn states that the simple recovery model does not support point-in-time recovery - Does this mean that I won't be able to use my transaction log backups to restore the database in a disaster to an hour before it happened?
Even taking a transaction log backup is not supported for databases using the SIMPLE
recovery model. This is a restriction of the database engine based on how this recovery model works, and the recovery features it doesn't support, as you mentioned.
A transaction log backup maintenance plan task automatically skips databases in SIMPLE
recovery to avoid causing errors.
Which backup should be done first, the database backup or the transaction log backups? Articles that I'm busy reading say I should do the database backup first and then the transaction log backup else I will get maintenance plan errors, but I'm currently first backing up my transaction logs and then data databases and I'm not getting any errors.
For the reasons I mentioned above, it won't matter for databases using SIMPLE
recovery, as they will be skipped by the transaction log backup task.
For databases in the other two recovery models, a full backup must exist before you start taking transaction log backups (just the first time), or you will get an error -- this is probably what the articles refer to.
Point-in-time recovery ability is normally driven by business need -- in other words, you determine how critical the data is and how much you can afford to lose, then set the appropriate recovery model to meet those needs, and finally create a backup solution.
Even though SIMPLE
recovery does not support point-in-time recovery, if an hour of data loss is okay, perhaps a differential backup solution could work for you. (There are far too many variables that go into developing this kind of solution to give you a complete picture with what was provided in the question.)
Best Answer
It's "Most Likely" a Permissions Issue
If you're performing a network SQL Server backup to a file share you'll most likely need to do one or more of these three common (A,B,C) tasks below:
A.> SQL Server Service
If you're running your backup from a SSMS query window, you need to make sure that the SQL Server service is using an Active Directory account that has permissions to write to the share location you're writing to.
B.> SQL Server Agent Service
If you're running the backup from the SQL Agent with a schedule job, you need to make sure that the SQL Agent is using an AD account that has permissions to write to the target share.
C.> Trusted Connection with sqlcmd
If you're running sqlcmd you need to use the -E and make sure the account that's running sqlcmd (the account running the cmd window) has permissions to write to the target share.
SQL Server Configuration Manager
If you need to change your SQL Server services to use an AD account, you'll need to apply the AD account using SQL Server Configuration Manager (SSCM). SSCM configures other things on the Windows Server besides just adding the AD account. After you change the associated service account, you'll need to restart that service for the account to take affect.
Workgroups instead of AD
If you're using a Windows Workgroup environment instead of AD, you'll want to make sure that both the Windows SQL Server and the target Windows box, have separate but identically named accounts on them with the same passwords. Run the SQL service you're using for the backup with this account.
Target Server Permissions
If you're unsure that the SQL Service/Agent accounts has the right permissions, just recreate the share on the target Windows machine and give that account at least read/write access to the share location using the folder's properties --> Share tab --> Share ... button.
Note
All that being said, if you're configuring backups for an AlwaysOn configuration there are separate gotchas that apply depending on which SQL Server is primary and which one is secondary and how you have backups configured. But, based on the video you presented, I don't think you're having issues with AlwaysOn backups.