restoring backup - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

restoring backup

Author

Topic

Topaz
Posting Yak Master

199 Posts

Posted - 2009-07-15 : 05:34:23

I need to restore a backup of my master table (wce_contact) to find a few 'missing/deleted' records. The only trouble is I can't restore a backup from a couple of weeks ago to our master wce_contact because we would lose far too much data.

Do you guys think this is a feasible workaround?

Would I be able to create a new database called 'restore' using SQL Management Studio and then restore a backup of (master) wce_contact to the newly created database without disrupting the way the original master wce_contact database works?

JT

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-15 : 05:38:56

use the WITH MOVE option to restore it without overwriting existing. see BOL for full details

Example


Restore database newDbName
With
    move 'wce_dat' to 'd:\sql\newDbName.mdf',
    move 'wce_log' to 'e:\sql\neDbName.ldf',
stats;

Topaz
Posting Yak Master

199 Posts

Posted - 2009-07-15 : 05:43:32

Would I not simply be able to create my new database 'restore' and then go to tasks->restore and use the backup file called 'wce_site_2009_6_3.bak'

Will this just recreate a copy of the database it is trying to restore from the backup? Or will it want to try and restore over the master wce_contact

JT

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-15 : 06:02:02

You don't even need to create the database first if you are using SQL 2005. You can right click on the database level -> restore Database.. and then type the name of the new database (and data and log filenames) and restore from there.

Topaz
Posting Yak Master

199 Posts

Posted - 2009-07-15 : 06:11:34

Im getting this error, I get a similar error when I click 'overwrite' Is this simply down to the fact i'm trying to restor a backup to a database with a different name?

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'restore' database. (Microsoft.SqlServer.Smo)

JT

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-15 : 06:32:25

Go to the options on the restore and use the overwrite existing database tick box.

Make sure the database name is different and the filenames in the restore as are different to the original names.

Topaz
Posting Yak Master

199 Posts

Posted - 2009-07-15 : 06:36:35

The database I want to restore to is different to backaup database name.

How do I change the .mdf and .ldf filenames? At the moment they point to a location on the server

E:\DataBases\wce_site.mdf
D:\DbLogs\wce_site_log.ldf

Should I take a copy of the files and rename them to this? (my new database name is called 'restore')

E:\DataBases\restore.mdf
D:\DbLogs\restore_log.ldf

JT

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-15 : 06:48:13

No, just change the path and filenames to where you want them in the Restore As box on the options tab.

Topaz
Posting Yak Master

199 Posts

Posted - 2009-07-15 : 07:19:20

Thanks for your help!! worked like a charm

JT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources