|
|
Posted by Erland Sommarskog on 11/06/07 22:25
(agnesroz@gmail.com) writes:
> Well i tried backing up the database using[rightclick database-
> Tasks->backup] i get a dbname.bak file, then i restored
> the same .bak file using tools->restore->database but i am not able to
> restore this way, i am getting the error "the backup set holds a
> backup of a database other than the existing 'dbname' database.please
> help me how to resolve this error... Or can i have a replica of a
> database within same server... if yes, please tell me how can i do
> this..
For a replica within the same server, the answer is the same:
BACKUP/RESTORE.
First use RESTORE HEADERONLY to see what backups you have in dbname.bak:
RESTORE HEADERONLY FROM DISK = '<path>/dbname.bak'
Judging from the error message it is possible that you have more than
one backup in dbname.bak. BACKUP will append any new backup, but by
default RESTORE tries to restore the first one. Make note of the value
in the Position column for your database.
Next do:
RESTORE FILELISTONLY FROM DISK = '<path>/dbname.bak' WITH FILE = n
Where n is the number you got from header only. Make note of the names
in the LogicalName column, the first.
Now you can restore:
RESTORE DATABASE mydbcopy FROM DISK = '<path>\dbname.bak'
WITH FILE = n,
MOVE '<logical_name_data>' TO '<dbpath>\mydbcopy.mdf',
MOVE '<logical_name_log>' TO '<dbpath>\mydbcopy_log.ldf',
REPLACE
What is a good value for dbpath you can find out by doing sp_helpdb
on some other database. Or use the PhysicalName from RESTORE FILELISTONLY
if you restore on the same instance.
If you had preferred directions for the GUI, I am sorry. I don't use
the GUI, so I don't know how it works. (Well, I know how it works:
it emits RESTORE commands similar to those above.)
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|