March 11, 2008 at 2:36 am
Hi,
I am new in this forum.Please help me in this problem
I have 2 DataBases Test1 and Test2 .I have Taken the Test1 Full Backup and Restore it on Test2.Then i insert some records on Test1 and then again i take the Differential backup of Test1 and Restore it on Test2.At this stage its ok.But when i again do the Changes on Test1 and take the Differential Backup of Test1 and try to restore it on Test2 then i get the following error.
The Log or Differential Backup Cannot be restored Because no files are Ready to RollForward.
March 11, 2008 at 2:54 am
salmantp2002 (3/11/2008)
Hi,I am new in this forum.Please help me in this problem
I have 2 DataBases Test1 and Test2 .I have Taken the Test1 Full Backup and Restore it on Test2.Then i insert some records on Test1 and then again i take the Differential backup of Test1 and Restore it on Test2.At this stage its ok.But when i again do the Changes on Test1 and take the Differential Backup of Test1 and try to restore it on Test2 then i get the following error.
The Log or Differential Backup Cannot be restored Because no files are Ready to RollForward.
How did you restore the last differential backup (with or without recovery)? Once you restore with recovery, the backup has been restored. If you want to restore a new differential backup, you will need to restore the relevant full backup (with norecovery), and then the differential backup (with recovery if you do not want to apply log backups, and you do not need previous differential backups). More info and examples are on http://technet.microsoft.com/en-us/library/ms186858.aspx
Regards,
Andras
March 11, 2008 at 3:04 am
As already mentioned you need to restore as follows,
1. Restore the Full backup with NORecovery
2. Restore the 1stdifferential backup with Recovery
and check if the changes in Test1 are reflected in Test2.
Or You can perform as shown below,
1. Restore the Full backup with NORecovery
2. Restore the 1stdifferential backup with NoRecovery
3. Restore the 2nddifferential backup with Recovery
Actually it is not required to restore all the differential backups because each one of those contain all the changes made from the last full backup. Hence you can restore the Full backup and the most recent differential backup.
[font="Verdana"]- Deepak[/font]
March 11, 2008 at 5:51 am
Thanks Deepak,
As you said its works fine.But their is some problem in it.when i Restore the DataBase with (No Recovery) then the DataBase is shown Restoring...state.
My requirement is this.I need 2 DataBases.First DataBase contains the Original Data and in Second DataBase I need to copy the Data of First DataBase on every Month end.Then I will do some changes on my Second DataBase like Update Data,Delete Data etc,then after one month I will copy the new Data from my First DataBase with taking differential Backup and Restore It to the Second DataBase.
So for doing this I need to Restore all the Previous Backups start from the Full Backup to the next Latest Differential backup.
e.g
Steps to Restore Backup:
First Month (January)
1)Full backup Restore (with Recovery)
Next Month (February)
1)Full backup Restore (with NoRecovery)(January Full Backup)
2)Differential Backup Restore (with Recovery)
Next Month March
1)Full backup Restore (with NoRecovery)
2)Differential Backup Restore (with NoRecovery)(February Differential Backup)
3)Differential Backup Restore (with Recovery)
So I have to do Like this that Restore first all the Previous Backups and then
at the last the Latest Backup with NoRecovery will be Restore.??????
March 11, 2008 at 7:51 am
May I ask a question, why are you only performing differential backups after completing your first full backup, especially over such a large time period.
As stated previously, you should only need to restore the latest differential on top of the most recent full backup.
Now if you are looking to accomplish end of month comparisons, then, you would have to do the full, and the differential for the end of month you want to compare with recovery, but you would have to repeat that process for every end of month.
In other words, I'm not sure of your goal, and if you can provide further insight a solution may be found.
Thanks
Marvin Dillard
Senior Consultant
Claraview Inc
March 11, 2008 at 11:48 pm
Thanks SSC-Enthusiastic,
I tell you the whole requirement with example .
I have 2 DataBases DataBase1 and DataBase2 and Both have same structure.
DataBase1 have One Table Table1 and DataBase2 also have this table.
Now for example on DataBase1 the Table1 have Records as follow
Table1 Records of DataBase1.
ID ItemName
1 Monitor
2 Mouse
3 Keyboard
In DataBase1 i have these Records on First month January
Now i have coped it on DataBase2
Table1 Records of DataBase2.
ID ItemName
1 Monitor
2 Mouse
3 Keyboard
.......................................................
After this on next month i do some changes on DataBase1 Records.Like
Table1 Records of DataBase1.
ID ItemName
1 Monitor
2 RAM--------(Update Record)
3 Keyboard
4 HardDisk-----(New Record)
Now the Month Feburary is end.
Now On DataBase2 I need only new Recods not the Updated Records.So for this
i need to have copy on Record Number 4 on DataBase2.
Now after coping the last Record the Records on DataBase2 will be
Table1 Records of DataBase2.
ID ItemName
1 Monitor
2 Mouse
3 Keyboard
4 HardDisk
I think u will get my Idea.I need new records from DataBase1 to DataBase2 not the updated records to do that.What i have to do?
I think Backup is not the option for doing this because its records all the transactions which are effected.?????
Please help me out in this problem i will be very thankful of you.....
March 12, 2008 at 12:31 am
Hi
Backups will not help in your case. What abt importing data.
You can use queries where you can take only the required data and import it. You can also use SSIS.
"Keep Trying"
March 12, 2008 at 12:49 am
1.To bring back your database from Norecovery to recovery
RESTORE DATABASE databasename WITH RECOVERY
In above statement change 'databasename' to your orignal database name.
2. Does both of your DB's are on same server Instance.
If yes why dont you write some TSQL scripts to accomplish your task.
March 12, 2008 at 4:40 am
You are correct, database backup is not what you want. However, there are a couple of options, you can write an SSIS package to do this, especially if both servers are not in the same domain. You can write a stored proc to do this that fires off monthly via a job. Or probably, the best thing to do is database replication. You can set the time interval for the replication to occur.
Hope this helps
MD
Marvin Dillard
Senior Consultant
Claraview Inc
March 13, 2008 at 1:08 am
Thanks SSC-Enthusiastic
Now i have the solution to do that by using the Bulk Copy.But now i have another issue that I have table ID Autonumber so its create problem for me.Let me tell you an example.
I have DB1 with Table1 and second DB2 with Table1
Now In DB1 Table1 Records of First month is as follow
Table1 of DB1
ID Item
1 Mouse
2 Keyboard
These are the Records of First Month I have coped it to the DB2 Table1 as follow
Table1 of DB2
ID Item
1 Mouse
2 Keyboard
Now both have the same Records.
Now on next month I enter new Record in Both DataBases as follow
Table1 of DB1 Table1 of DB1
ID Item ID Item
1 Mouse 1 Mouse
2 Keyboard 2 Keyboard
3 Monitor -------New Record on ID 3 3 RAM --------New Record on ID 3
Now when i will copy the DB1 data to DB2 data its create problem for me that
In DB1 ID 3 is link to the item Monitor but in DB2 ID 3 is link with the Item Ram
Now how can i handle this problem and this ID link with many tables.???????
March 13, 2008 at 9:21 am
What you have is identity field that is automatically creating the numbers in your ID column. In order to ensure the proper match, you need to set identity insert on and then turn it off when finished. Search BOL (Books On Line) for how to set Identity inserts on and off.
Hope this helps
MD
Marvin Dillard
Senior Consultant
Claraview Inc
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply