Using Disparate File Paths in SQL Server Database Mirroring

  • Perry Whittle

    SSC Guru

    Points: 233859

    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" 😉

  • S. Kusen

    SSChampion

    Points: 10872

    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

  • schwizla

    SSCrazy

    Points: 2668

    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

    ?

  • Perry Whittle

    SSC Guru

    Points: 233859

    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" 😉

  • dave-dj

    SSChampion

    Points: 11292

    good article and good tip for addining files without the need to do a full backup and restore.

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • john.knight-557608

    SSC Enthusiast

    Points: 132

    Hi Perry,

    Another good article, thanks for the tip!

    Cheers

    John

  • george sibbald

    SSC Guru

    Points: 104200

    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. 🙂

    ---------------------------------------------------------------------

  • UMG Developer

    SSChampion

    Points: 13482

    Thank you very much for the article, it is good to learn about the pitfalls before actually falling into them.

  • SQLRNNR

    SSC Guru

    Points: 281243

    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

  • Perry Whittle

    SSC Guru

    Points: 233859

    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" 😉

  • tuntun

    SSC Journeyman

    Points: 84

    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;

  • Perry Whittle

    SSC Guru

    Points: 233859

    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" 😉

  • tuntun

    SSC Journeyman

    Points: 84

    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)

  • SQLKnight

    Old Hand

    Points: 316

    Nice tip. Thanks for the post.

  • Perry Whittle

    SSC Guru

    Points: 233859

    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 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply