Post reply

Using Disparate File Paths in SQL Server Database Mirroring

  • February 28, 2011 at 10:29 pm

    #235930

    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

    #1292715

    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

    #1292758

    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

    #1292759

    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

    #1292769

    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

    #1292771

    Hi Perry,

    Another good article, thanks for the tip!

    Cheers

    John

  • March 1, 2011 at 11:06 am

    #1293087

    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

    #1293177

    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

    #1293204

    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

    #1293308

    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

    #1294273

    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

    #1294366

    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

    #1294595

    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

    #1316766

    Nice tip. Thanks for the post.

  • April 27, 2011 at 2:13 pm

    #1316889

    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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy