Restore backup from BAK file i have new data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Restore backup from BAK file i have new data

Author

Topic

officegrabs
Starting Member

1 Post

Posted - 2011-10-31 : 20:06:40

hi

i did some major changes to data in a DB ,so before i did it i did a full backup via the SQL Server managemtn tool now a month later the ownerr decided to revert all the changes, my problem is if i do a resotre from the the bak file it will overwrite all the new data we had add it in the last month

my question is how do i do a full restore from the bak file but not overwrite the new data

Pleasae help

vikki.seth
Yak Posting Veteran

66 Posts

Posted - 2011-11-01 : 01:38:04

You can create restore the backup with a different database name.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-11-01 : 14:36:18

You can use a tool like SQL Compare from Redgate to synchronize the changes from one database to another. In this case, you would restore your backup as a new database, run the comparison between the two databases and synchronize the objects you want to change back.

With that said, if those changes are schema changes to tables - you won't be able to revert back to the previous definition without losing data. I would be very careful with rolling these kinds of changes back.

Jeff

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-01 : 14:38:44

Can you reverse the changes?
Might be better to talk to the owner and see what the issue is - maybe all the changes don't need to be reversed, they've been there a month after all.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

james_wells
Yak Posting Veteran

55 Posts

Posted - 2011-11-02 : 14:19:17

Example of how to create a new data base from a backup file

XYZ is the new name of the database
CURRENT_DATA is the logical data file name of the backed up database
CURRENT_LOG is the logical log file name of the backed up database

after TO (in move) is the desired location of both the mdf and ldf files

This will bring back the backed up database as a different name , same logical names but new phsical file names

use master
go
restore database XYZ
from Disk = N'D:\BACKUP\XYZ.bak'
WITH
move N'CURRENT_DATA' TO N'D:\DATA\XYZ_DATA.mdf',
move N'CURRENT_LOG' TO N'D:\DATA\XYZ_DATA.ldf'

Of course the above is only an example and you will have to tweak it to match your requirements and maybe the backup is on tape?

jamesanderson
Starting Member

1 Post

Posted - 2011-11-03 : 08:38:10

hi officegrabs,

If you are searching any utility for recovery of backup files of SQL Server, then you can try systools SQL Backup Recovery Tool which can help you to retrieve sql backup file elements.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources