I use sql server and have a huge database that partitioned by date in multiple file groups.
the database filegroups is PRIMARY, FG2010, FG2011, FG2012, FG2013, and FG2014
, that FG2010, FG2011, FG2012 and FG2013 is readonly.
Now, the backup scenario is:
each Friday get a full backup at 2:00 AM
each day on week except Friday get a differential backup at 2:00 AM
I want to change this scenario to:
get a full backup of database(One Time)
get a full backup of PRIMARY and FG2014 each Friday at 2:00 AM
get a differential backup of PRIMARY and FG2014 each day except Friday at 2:00 AM
Questions 1: Can I have this scenario?
I have a recovery plan too. each day I copy backup file to another server automatically by job and then restore it, in order to have recovery test plan and also use restored database to developer and tester users.
I want to have following scenario for recovery plan:
restore full backup of database.
restore last full backup of PRIMARY and FG2014.
restore last differential backup of PRIMARY and FG2014.
Question 2: can I have this scenario to recovery plan?
Question 3: can I have better scenario to backup and restore?
Please answer my question with TSQL query.
Best Answer
Q1. Yes, you can. You would also need to be taking regular transaction log backups.
Q2. Yes, this would also work for a recovery strategy, again you need to be taking transaction log backups.
Q3. As you are using the backups to create a development database, I would stick with this. There are options such as log shipping but I would not implement them to keep a development database updated from production.
I have written some demo scripts which you can use to run through the scenario you suggested on your local instance.
The scripts will:-
Please have a look and run through the scripts. Let me know if you have any questions.
Before you use the scripts, just make sure that you have the following filepaths on your computer:-
The version of SQL Server I use is 2012 SP2 CU2 Developer Edition.
First create the test database:-
Then create tables in each of the filegroups:-
Insert data (100 rows) into each of the tables:-
Then set certain filegroups to read only:-
Take a full backup:-
Then create a development database from the full backup (this will be used to restore the filegroup backups that will be taken further next):-
Take backups of each of the filegroups:-
Now we will modify data in the Primary & FG2014 filegroups:-
Take differential backups of the filegroups:-
Again, modify the data:-
Backup the transaction log (you will probably have more than one of these in a real life environment, but for demo purposes I'm just taking one):-
OK, now we can restore the development database. First we take a Tail Log backup, putting the database into recovery. Note:- we will not use this backup!
Now we can restore the full backups of the read-write filegroups:-
Then the differential backups:-
Then the transaction log backup:-
Finally, the database can be recovered:-
And as a final test, check the data:-
So, from the data changes made, we would expect to see 300 records in the PRIMARY & FG2014 filegroups, 100 in the rest and the all the values in the name column in the FG2014 set as 'NEW'.