January 2, 2012 at 2:58 am
Hi Folks,
my question might be silly. But, Kindly Clarify me.
SCENARIO:
-----------
Abc---Instance name
XYZ---Database Name
books---Table Name.
For EX:In books Table following are the Records
id Name Sal
-- ------ ---
1 free 23
2 Tree 34
3 fire 54
Yesterday I took Full backup of XYZ Database. and Restored in DEF Instance as XYZ_QA Database.
Now my question is:
Today there some more records inserted into Books Table and Modified Existing Records, and once again i took the full backup of XYZ Database and appended to existing Backup and Restored with Replace option on XYZ_QA Database in DEF Instance.
Then, am not able to see the yesterdays records
id Name Sal
-- ------ ---
1 free 23
2 Tree 34
3 fire 54
My Requirement is i want yesterdays records and todays inserted records and today updated yesterday's records.
How it possible and kindly tell me solution.
January 2, 2012 at 3:22 am
I'm sorry, it's unclear to me.
You took a backup, restored with a different name, took another backup, restored with replace and you don't see the records in the first backup set?
Something else happened, you're not dealing with a backup/restore issue.
-- Gianluca Sartori
January 2, 2012 at 3:30 am
exactly, after restoring second backup in QA Instance am not able to see yesterday's Records am able to see only today inserted and updated records.
January 2, 2012 at 3:49 am
MasterDB (1/2/2012)
exactly, after restoring second backup in QA Instance am not able to see yesterday's Records am able to see only today inserted and updated records.
You took FULL backups, right? It’s possible in FULL backup / restore, if and only if the rows are removed from main database before the backup. Please verify your main database for missing rows.
January 2, 2012 at 3:50 am
As I said before, it's not something on the backup/restore. You must have messed with the backup.
Try taking another backup and restore it with replace: you will see all the data.
-- Gianluca Sartori
January 2, 2012 at 3:56 am
If i take, new full backup and restored with replace option i can lost yesterdays' data in QA Instance..
am i right..?
January 2, 2012 at 3:59 am
MasterDB (1/2/2012)
If i take, new full backup and restored with replace option i can lost yesterdays' data in QA Instance..am i right..?
Full Restore will replace everything on target server with the data on source server (at the time of backup). Thus you will lose QA data, if QA is your target server.
January 2, 2012 at 4:42 am
Is there any way to retain yesterdays data in QA Server,in addition i don't want to loose yesterdays data.
January 2, 2012 at 4:45 am
MasterDB (1/2/2012)
My Requirement is i want yesterdays records and todays inserted records and today updated yesterday's records.
Do you want the data as reflected in the source database? Or do you want some combination of the data from the source and the changes made on the QA server?
A backup, when restored overwrites the database that's on the server and replaces it with an exact copy of the database at the time of backup.
If you're appending backups to the same file (as you said you did), be careful that you restore the correct one. If you just say RESTORE DATABASE... and don't specify otherwise, it will restore the earliest one in the set. If you want to restore another backup (later than the first one), you need to specify FILE = x in the restore, where x is the number of the backup within the set (use RESTORE HEADERONLY to see the backups within the backup file)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 2, 2012 at 4:46 am
MasterDB (1/2/2012)
Is there any way to retain yesterdays data in QA Server,in addition i don't want to loose yesterdays data.
Script out the changes, restore the backup, run the scripts to replace the data. With just backups, the backup will overwrite the destination DB entirely.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 2, 2012 at 4:59 am
Thanks all for your inputs!!!!
Gail,i want to retain the yesterdays data and wants to restore the data what changes happened today.
its just like insert into [QA].[DBNAME].[OBJ.NAME] select * from [OBJ.NAME]
i'll run this query from source server.
This query will works easy for one OBJECT, But the thing is i've lot of Table to do in same manner.
if possible can u provide me step-by-step manner.
Thanks again for your input Gail.
January 2, 2012 at 5:06 am
Actually it’s not that difficult. You just need a loop / cursor (suggesting loops because it’s one time solution probably :unsure:) that will loop through all tables in the database.
For More: http://msdn.microsoft.com/en-us/library/ms187406.aspx
January 2, 2012 at 6:22 am
MasterDB (1/2/2012)
its just like insert into [QA].[DBNAME].[OBJ.NAME] select * from [OBJ.NAME]i'll run this query from source server.
This query will works easy for one OBJECT, But the thing is i've lot of Table to do in same manner.
Are you trying to copy all data from the other instance, or just new data? If just new data it's a lot harder than just insert into ... select from (and btw, when working across instances I recommend you always pull data, don't push it, so run the queries on the server that has the tables you're inserting into. QA in this case)
If you want to copy only today's changes from the other instance, then you need a way to identify what rows are new and what rows have changed (and possibly what rows have been deleted). It's possible to do in T-SQL (merge works very well, otherwise you'd need an insert, update and delete combo), but it is tedious to write and, if you have multi-column primary keys or tables with foreign key constraints it can get very tricky indeed.
If you have the option of 3rd party tools I strongly suggest something like RedGate's SQLDataCompare that will pick up the differences and produce all the T-SQL you need, and can even be run from an automated job if need be.
If that's not an option, I can help you with the merge statements, but it's not as simple as just running a single insert for all the tables in the DB unfortunately.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 3, 2012 at 3:33 am
Thanks for your input gail.
my requirement is exactly what u mentioned. i.e., i want to copy the new data which has changed(insert,update,delete in source Server) to Destination(in my case QA).
i hope i can achieve this with merge statement.
Kindly share the merge statements.
That might helps me alot.
Thanks again Gail.
January 3, 2012 at 3:49 am
MasterDB (1/3/2012)
i hope i can achieve this with merge statement.Kindly share the merge statements.
I can, but I think you missed the portion of my post where I said that was NOT trivial, and tedious to write. It involves either writing a merge statement for each and every table you want to sync, or writing code to dynamically generate merge statements (which is an absolute pain to do especially if you have multi-column primary keys)
So, how many weeks are you able to devote to this project?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply