Hi all,

I have sql server 2005 express edition installed, i have to
2 registered instances sqlexpress1, sqlexpress2 within this server, i
wanted the exact copy of databases in instance sqlexpress1 to instance
sqlexpress2, Is it possible to have something like this.. if yes how
can i do this... guide me with proper links, documents... please help
me..
Thanks in Advance

(agnesroz (AT) gmail (DOT) com) writes:
For a one-off, or should they constantly be equal?

For the first case, use BACKUP-RESTORE, for the second case, use
replication.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Nov 6, 12:55 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Thanks Erland ,
Well i tried backing up the database using[rightclick database-
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..

(agnesroz (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx