database 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

General SQL Server Forums

New to SQL Server Administration

database mirroring

Author

Topic

gorashy
Starting Member

15 Posts

Posted - 2015-01-11 : 18:01:07

hi ,all
hi,tara
when I tried to restore the database on mirror server I got the follwing error
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'mirror' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

the code I wrote as follows
RESTORE DATABASE mirror
FROM DISK = 'C:\backup\mymirror.bak'
WITH NORECOVERY
please help

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-12 : 09:10:34

Please post the results of RESTORE FILELISTONLY from your backup file.

gorashy
Starting Member

15 Posts

Posted - 2015-01-12 : 10:10:28

sorry gbritton, I am new to sql server, what is FILELISTONLY?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-12 : 11:05:51

Did you look it up?

e.g.


RESTORE FILELISTONLY FROM StorageMor_Backup  WITH FILE = 1
GO

returns


LogicalName     PhysicalName                                                                                Type    FileGroupName   
StorageMor      C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014EXPRESS\MSSQL\DATA\StorageMor.mdf      D       PRIMARY         
StorageMor_log  C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014EXPRESS\MSSQL\DATA\StorageMor_log.ldf  L       NULL

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-12 : 12:25:19

WITH NORECOVERY, REPLACE

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gorashy
Starting Member

15 Posts

Posted - 2015-01-13 : 12:12:25

it returned the following
LogicalName PhysicalName Type FileGroupName Size MaxSize FileId CreateLSN DropLSN UniqueId ReadOnlyLSN ReadWriteLSN BackupSizeInBytes SourceBlockSize FileGroupId LogGroupGUID DifferentialBaseLSN DifferentialBaseGUID IsReadOnly IsPresent TDEThumbprint
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- -------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- --------------------------------------- --------------------------------------- ------------------------------------ --------------------------------------- --------------------------------------- -------------------- --------------- ----------- ------------------------------------ --------------------------------------- ------------------------------------ ---------- --------- ------------------------------------------
mirror C:\Program Files\Microsoft SQL Server\MSSQL12.MYPUBLISHER\MSSQL\DATA\mirror.mdf D PRIMARY 5242880 35184372080640 1 0 0 344EB430-44D3-4780-8CEC-2D2596E2FB5C 0 0 2818048 512 1 NULL 41000000018100037 956B5417-8A0E-4F1A-8D48-B69FB749E4CF 0 1 NULL
mirror_log C:\Program Files\Microsoft SQL Server\MSSQL12.MYPUBLISHER\MSSQL\DATA\mirror_log.ldf L NULL 2359296 2199023255552 2 0 0 E2E2D7ED-F586-4EEF-9510-1CFBE5438D11 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 1 NULL

(2 row(s) affected)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-13 : 12:48:46

Add REPLACE to your command.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gorashy
Starting Member

15 Posts

Posted - 2015-01-13 : 14:02:36

USE [master]
BACKUP LOG [mirror] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MYPUBLISHER\MSSQL\Backup\mirror_LogBackup_2015-01-13_10-28-10.bak' WITH NOFORMAT, NOINIT, NAME = N'mirror_LogBackup_2015-01-13_10-28-10', NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5
RESTORE DATABASE [mirror] FROM DISK = N'C:\mirrorbackup\mymirror.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5

GO

but failed
I got the following error

Msg 3104, Level 16, State 1, Line 2
RESTORE cannot operate on database 'mirror' because it is configured for database mirroring or has joined an availability group. If you intend to restore the database, use ALTER DATABASE to remove mirroring or to remove the database from its availability group.
Msg 3013, Level 16, State 1, Line 2
BACKUP LOG is terminating abnormally.
Msg 3104, Level 16, State 1, Line 3
RESTORE cannot operate on database 'mirror' because it is configured for database mirroring or has joined an availability group. If you intend to restore the database, use ALTER DATABASE to remove mirroring or to remove the database from its availability group.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-13 : 14:10:30

The error indicates mirroring is already in place. If you are starting over, then you need to first remove mirroring.

This is the command: ALTER DATABASE dbname SET PARTNER OFF

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gorashy
Starting Member

15 Posts

Posted - 2015-01-15 : 14:11:51

It worked fine
returned
100 percent processed.
Processed 5 pages for database 'mirror', file 'mirror_log' on file 1.
BACKUP LOG successfully processed 5 pages in 0.218 seconds (0.147 MB/sec).
7 percent processed.
12 percent processed.
17 percent processed.
21 percent processed.
26 percent processed.
31 percent processed.
36 percent processed.
41 percent processed.
46 percent processed.
51 percent processed.
55 percent processed.
60 percent processed.
65 percent processed.
70 percent processed.
75 percent processed.
80 percent processed.
85 percent processed.
92 percent processed.
97 percent processed.
100 percent processed.
Processed 328 pages for database 'mirror', file 'mirror' on file 1.
Processed 2 pages for database 'mirror', file 'mirror_log' on file 1.
RESTORE DATABASE successfully processed 330 pages in 0.685 seconds (3.752 MB/sec).
35 percent processed.
70 percent processed.
100 percent processed.
Processed 0 pages for database 'mirror', file 'mirror' on file 2.
Processed 23 pages for database 'mirror', file 'mirror_log' on file 2.
RESTORE LOG successfully processed 23 pages in 0.083 seconds (2.129 MB/sec).
what is the next step?
do I issue the following command?
alter database mirror set partner on
please be patient

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-15 : 14:14:50

I have a blog on it: http://weblogs.sqlteam.com/tarad/archive/2007/02/13/60091.aspx

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gorashy
Starting Member

15 Posts

Posted - 2015-01-16 : 16:55:55

very good blog, Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-19 : 12:27:19