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@gmail.com) writes:
> 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..
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@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
On Nov 6, 12:55 pm, Erland Sommarskog
> (agnes...@gmail.com) writes:
> > 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..
>
> 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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx
Thanks Erland ,
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..
(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 = '
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 = '
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 = '
WITH FILE = n,
MOVE '
MOVE '
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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx