February 28, 2011 at 10:29 pm
Comments posted to this topic are about the item Using Disparate File Paths in SQL Server Database Mirroring
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 28, 2011 at 10:30 pm
Excellent tip to "trick" SQL into thinking we did a full backup/restore of the DB to be able to move the new database file.
Cheers,
Steve
March 1, 2011 at 2:13 am
nice article. one question?
in your backup TLog statement did you mean to say....
BACKUP DATABASE AdventureWorks TO DISK = '\\hanode1\backup\AdventureWorks_createnewfile.trn' WITH INIT
?
March 1, 2011 at 2:16 am
dibbydibby (3/1/2011)
nice article. one question?in your backup TLog statement did you mean to say....
BACKUP DATABASE AdventureWorks TO DISK = '\\hanode1\backup\AdventureWorks_createnewfile.trn' WITH INIT?
Hi
well spotted, no the backup and the restore statement should read
BACKUP LOG
RESTORE LOG
Regards
Perry
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 1, 2011 at 2:32 am
good article and good tip for addining files without the need to do a full backup and restore.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
March 1, 2011 at 2:40 am
Hi Perry,
Another good article, thanks for the tip!
Cheers
John
March 1, 2011 at 11:06 am
nice article Perry and very useful info.
I think the lesson to take away though is to set your live and failover servers up with exactly the same drive\directory structures in the first place. 🙂
---------------------------------------------------------------------
March 1, 2011 at 1:17 pm
Thank you very much for the article, it is good to learn about the pitfalls before actually falling into them.
March 1, 2011 at 2:02 pm
Nice article Perry. Thanks
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 1, 2011 at 7:44 pm
Guys
Thank you very much for all your kind replies I'm glad you find it useful
george sibbald (3/1/2011)
I think the lesson to take away though is to set your live and failover servers up with exactly the same drive\directory structures in the first place. 🙂
George I couldn't agree more, although sometimes you may inherit a system and will have to roll with it. This is especially for these types of systems
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 3, 2011 at 12:53 pm
Excellent article.
It gave me new trick.
After testing with new File Group and File at principal, mirroring works.
But, I cannot create database snapshot at mirror.
Before adding File, the following one worked.
CREATE DATABASE Mirror_Test_snapshot ON
( NAME = N'Mirror_Test_Data',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Mirror_Test_Data_snapshot.snap')
AS SNAPSHOT OF Mirror_Test;
After adding File at Node1\Mirror_Test_FG_IDX_1.Mirror_Test_FG_IDX_1.ndf at principal
Restoring at Node2\Mirror_Test_FG_IDX_1.ndf at mirror
, this one doesn't work.
CREATE DATABASE Mirror_Test_snapshot ON
( NAME = N'Mirror_Test_Data',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Mirror_Test_Data_snapshot.snap'),
(NAME = N'Mirror_Test_FG_IDX_1',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Node2\Mirror_Test_FG_IDX_1_snapshot.snap')
AS SNAPSHOT OF Mirror_Test;
March 3, 2011 at 3:03 pm
tuntun.oo (3/3/2011)
Excellent article.It gave me new trick.
After testing with new File Group and File at principal, mirroring works.
But, I cannot create database snapshot at mirror.
Before adding File, the following one worked.
CREATE DATABASE Mirror_Test_snapshot ON
( NAME = N'Mirror_Test_Data',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Mirror_Test_Data_snapshot.snap')
AS SNAPSHOT OF Mirror_Test;
After adding File at Node1\Mirror_Test_FG_IDX_1.Mirror_Test_FG_IDX_1.ndf at principal
Restoring at Node2\Mirror_Test_FG_IDX_1.ndf at mirror
, this one doesn't work.
CREATE DATABASE Mirror_Test_snapshot ON
( NAME = N'Mirror_Test_Data',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Mirror_Test_Data_snapshot.snap'),
(NAME = N'Mirror_Test_FG_IDX_1',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Node2\Mirror_Test_FG_IDX_1_snapshot.snap')
AS SNAPSHOT OF Mirror_Test;
do you get an error message, if so what is it?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 4, 2011 at 5:55 am
Location: recovery.cpp:3080
Expression: m_recType != REPLICA
SPID: 54
Process ID: 1748
Msg 1823, Level 16, State 1, Line 1
A database snapshot cannot be created because it failed to start.
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
Msg 9003, Level 20, State 5, Line 1
The log scan number (45:1518:0) passed to log scan in database 'Mirror_Test' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
I also attached SQLdump file.
--SQL Server Version -- Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) Dec 10 2010 10:56:29 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
April 27, 2011 at 11:08 am
Nice tip. Thanks for the post.
April 27, 2011 at 2:13 pm
Thank you, please don't forget to rate the article
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply