I understand we can't do a diff/log backup of a new Database before a Full Backup. But how about for a restored Database, can we do a diff/log backup of it?
Our situation is we have a full backup. We want to restore it and use it right away. But initially we don't have enough time to do a full backup (takes 4 hr). So can we take a diff/log backup of it, and if a disaster occurs, apply that to the original full back up?
No, sadly you can't.
If you think about it, a diff backup backs up what's different from the last full backup. Thus, no backup, no "diff".
You could try contacting your network/SAN team and experimenting with VSS which might work and be a lot quicker.
Can you explain the scenario you have in more detail - there may be other approaches to resolve your issue or concern.
I cannot see any reason to perform a restore from a backup and then need to perform a restore from that same backup again and apply a differential or log backup.
Maybe you can take a database snapshot after applying your changes. You could then roll back (restore) from the database snapshot.
Thanks everyone so far. And apologies if I didn't explain it well. I think I'll end up just trying it myself and see what happens.
Let me try re-stating it...
Everything is in Full Recovery Mode. Say I have Database DB1 with a full backup of it DB1.bak. Now from DB1.bak I restore it as Database DB2. Can I take a log backup of Database DB2 right away? The reason being is we need to use DB2 as soon as it's restored. And this Database is very large and takes 4 hrs to complete a full backup. i.e. we can't wait the 4hrs for the Full Backup to finish before we start using it. So let's say there is an accident on DB2 2 hours after the restore, we haven't had enough time for the full backup since it's only half-way done. But if we were able to take log backups as soon as DB2 was restored, could we apply the log backups to a restore of DB1.bak?
I poorly titled this thread - I corrected it.
hi
here is the official microsoft docs link of backup and restore .. ...
please see in this if there is anything ... related to what you want
its kind of confusing .. please explain clearly what you are trying to do !!
sorry to ask
So db2 is pretty much identical, its a clone of db2? And is it restored as db2 on the same server? If so what is the purposebof you all trying to do this? What is the business decision of this? What is the end game of this excercise?
Essentially Database DB1 is Production and Database DB2 is Preview (i.e. a test Database). The customer wants to review the new features in the DB2/Preview. So we refresh DB2 from DB1 i.e refresh Preview from Production. But when we do this refresh, we don't have time to wait around for a full backup of DB2/Preview.
What is it that your are doing on a preview database that it requires you to back it up
so if just preview why do you need to back up. if you are trying to maintain code change in the db2 sql server, might be better to save all your changes in sql script files saved in a a source control like gitlab. devs should be discouraged making any direct changes without a sql script that has been vetted. then next time you restore from prod you just reapply those changes from source control of all those changes that are stored in .sql files.
OK everyone. I gave it a try!
Pletcher, you stand corrected. I restored a DB and I did a diff backup without doing a full on it first. SQL Server let me. Then I deleted the new database, restored the original backup in nonrecovery and applied the diff. It worked great.
I tried making a brand new DB and verified that I would encounter an error when doing a diff without a full, just to make sure I wasn't hallucinating.
Hope everyone can try it too. Just in case it is version dependant.