June 16, 2011 at 9:26 am
Hello sir
can u please tell me any ways by which we can restore multiple .bak files into single database in sql server 2005.
I do have basic knowledge in databases but not like an expert. I have given a task to merge multiple .bak files,which is located in my local memory and its around 400 and above. Restoring each .bak files looks soo teditious. Please help me to find a solution.
Thanks in advance
Sathish.K:-)
June 16, 2011 at 9:42 am
Are these a full backup and multiple diff backups, or multiple log backups, or something else?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 16, 2011 at 12:10 pm
i used this query:
RESTORE HEADERONLY
FROM DISK = N'H:\sample.bak'
WITH NOUNLOAD;
GO
i found '1' in 'backup type' attribute
Its Full backup type.
June 16, 2011 at 12:17 pm
You can only restore one Full backup for a database at a time. It will overwrite the database, if you tell it to, or will fail.
You can restore multiple Full backups if you cause each restore to create a new database with a different name. This will result in multiple databases.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 16, 2011 at 12:25 pm
i got that..
Could u please help me with any script to move the datas in a database to another database.
this could help me a lot
Thanks in advance:-)
June 16, 2011 at 12:55 pm
There's no simple script for that. Try something like RedGate's SQLDataCompare.
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
June 16, 2011 at 12:58 pm
It could MAYBE be somewhat easy.
Read all the backups filelist into temp table
Restore with filelist
restore
Someone might knock this out in a couple hours at most.
June 16, 2011 at 1:36 pm
Ninja's_RGR'us (6/16/2011)
It could MAYBE be somewhat easy.Read all the backups filelist into temp table
Restore with filelist
restore
Someone might knock this out in a couple hours at most.
Not to merge data from multiple full backups. Won't work for that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 16, 2011 at 1:40 pm
Ah! missed 1 little word in the post ;-).
June 16, 2011 at 1:42 pm
You could use SSIS to move that data from one database to another, and you can make the process repeatable if need be. If you want to quickly just pull data from one table into another look into the SELECT INTO command.
EDIT: Just re-read trying to pull data from multiple backup files into database...yeah other than restoring those databases individually, listen to Gail. You'll need a third party tool to compare the data and move what you need.
=============================================================
/* Backups are worthless, Restores are priceless */
Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
June 16, 2011 at 1:57 pm
Jorge and Gail have given you some good hints. There's not good script that we can give you because the movement will depend on the table structures and what's in there, not to mention you'll have to reconcile how the data applies.
If row x in in Backup 1, changed in backup 2, and deleted in backup 3, what do you do?
This is problematic, even with a tool like Data Compare (http://www.red-gate.com/products/sql-development/sql-data-compare/), but without it, it would be a nightmare. You ought to ask some more questions about what someone expects with multiple .BAKs.
Note that with Data Compare, you can do the comparison between the backup file and the database, no need to restore.
(disclosure, I work for Red Gate)
June 16, 2011 at 2:44 pm
:-)thanks a lot guys:-)
Really helpful
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply