Help need for mirroring - 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

SQL Server 2005 Forums

SQL Server Administration (2005)

Help need for mirroring

Author

Topic

cmrhema
Starting Member

20 Posts

Posted - 2008-04-01 : 02:02:08

I hereby let u know the steps that i undertook for performing mirroring

1. Created a database in Principal server named Mirror1 and one table named tblMirror1.( no values inside the table)

2. Repeated the same in Mirror in Witness

3. Selected the Principal server's database -->right click-->tasks-->mirror

4. from the left hand panel selected Options -->recovery model-->full

5. selected Mirroring from left hand panel-->configure secuirty-->entered mirror and witness name--Success

6. When I click on start mirroring I have an error stating

" Alter failed for database mirror1"

"Database mirroring cannot be enabled because Mirror1 database may have bulk logged changes that have not been backed up etc...."

7. followed this one
BACKUP database mirror1 TO DISK='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\mirror1_FULL.BAK'

got the answer as

Processed 160 pages for database 'mirror1', file 'Mirror1' on file 2.
Processed 1 pages for database 'mirror1', file 'Mirror1_log' on file 2.
BACKUP DATABASE successfully processed 161 pages in 0.318 seconds (4.147 MB/sec).

According to sources
Run a restore of this backup on your mirror.
This can be done through the GUI or using a T-SQL command.

The database restore must use the NO RECOVERY option, so the database stays in a loading state.

Also the database name on the mirror must be the exact same name as the principal

RESTORE TestMirror FROM DISK='C:\Backup\ TestMirror_FULL.BAK' WITH NORECOVERY

Hence did the below

RESTORE database mirror1 FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\mirror1_FULL.BAK' WITH NORECOVERY

ERROR

Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\mirror1_FULL.BAK'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

HENCE MODIFIED AS

use master
go
RESTORE database mirror1 FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\mirror1_FULL.BAK' WITH NORECOVERY

Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\mirror1_FULL.BAK'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

========

Kindly let me know please where am i going wrong

Regards
cmrhema

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-01 : 23:29:22

Dup post:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100030

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources