restore to a different database

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 Administration

restore to a different database

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-11-29 : 07:37:03

i have a database I backup and the logs

I just tried to restore it to a new database and I get an error
the backup set holds a backup of a database other thenn the existing database

what's the trick to be able to restore it to a new database.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-29 : 07:43:57

Pick the destination database, and when picking the source files to restore, point to the desired source. Most of this should be accessible in the restore wizard.

in T-SQL it would be something like
RESTORE DATABASE NEWDB
FROM DISK = 'D:OriginalDB.bak'
WITH MOVE 'OriginalMDF' TO 'D:NEWDB.mdf',
MOVE 'OriginalLDF' TO 'D:NEWDB.ldf'

Poor planning on your part does not constitute an emergency on my part.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-11-29 : 07:48:03

that's what I did and got that error

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-29 : 07:56:36

Suspect you haven't set the dataase name and it's still specifying an existing database.

==========================================
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.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-11-29 : 07:57:28

no i chose it in the wizard - restoring from my backup and transaction logs

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-29 : 08:02:49

quote:
Originally posted by esthera

no i chose it in the wizard - restoring from my backup and transaction logs

Do you mean you chose it or types it in?
If it's a new database you would need to type it in. If you chose it from the list then you would have an existing database and get that error if it didn't have the same name as in the backup.

==========================================
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.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-11-29 : 08:11:49

i created a new database

I right clicked and chose to restore a backup
chose new database in To Database
chose point in time
chose from database (as old existing db)

I get the error
the backup set holds a backup of a database other thenn the existing database
what am i doing wrong?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-29 : 08:26:14

>> i created a new database

This bit.
You don't create the new database, the restore does it for you.

==========================================
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.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-11-29 : 08:45:00

thanks seems to be running - taking a long time but I guess it's copying a complete database

thanks - i just wanted to make sure i could easily get a restore.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-11-30 : 02:15:43

thanks for all your help

i just want to be prepared in advance if we would have trouble

if i take copy of the .bak and the trn files -- would this be enough to restore to a new server in case of hardware failure?

what do I need to take a backup and restore to a new server?

this site is backed up nightly and transaction logs every 15 minutes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources