Post reply

Mirror Database - Cannot create snapshot

  • August 12, 2010 at 2:30 am

    #238843

    Hi,

    I have a mirrored database configuration, with two SQL Server 2008 Enterprise boxes, and SQL Server 2008 Express as a witness.

    I have configured the mirroring, and reviewed the mirroring state via a select on sys.database_mirroring. All seems correct, and I now want to take a snapshot of the mirror database (in restoring state) to validate that the data changes made on the principal are in fact being persisted on the mirror.

    I am attempting to create the snapshot using the below script:-

    CREATE DATABASE MyDatabase_DD_MM_YYYY_Snapshot ON

    (NAME = N'MyDatabase', FILENAME = N'C:\TestSnapshots\MyDatabase_DD_MM_YYYY_Snapshot.ss') AS SNAPSHOT OF MyDatabase;

    The problem I am having is that when I attempt to create the snapshot I am receiving the below error:-

    Msg 5014, Level 16, State 3, Line 1

    The file 'MyDatabase' does not exist in database 'MyDatabase'

    I don't understand this, as my data file is named 'MyDatabase'.

    Any advice would be greatly appreciated.

    Thanks

  • August 12, 2010 at 2:49 am

    #1205855

    What does this return? (run in master database)

    select name, type_desc, physical_name from sys.master_files

    where database_id = db_id('MyDatabase')

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 12, 2010 at 3:00 am

    #1205859

    That's great Gail, many thanks! - I was wrong regarding the name of the database file, and your script highlighted it for me. I'm not sure how I got that wrong.

    Using the correct database name I am now able to create a snapshot correctly.

  • July 27, 2011 at 2:17 pm

    #1359659

    Thanks for the help! i had the same issue. got it resolved.:-)

    Regards

    Pradeep

  • February 14, 2013 at 9:35 am

    #1587994

    I'm getting a similar error when creating snapshots:

    Msg 5014, Level 16, State 3, Line 1

    The file 'Admin' does not exist in database 'Admin'.

    When running the following script:

    CREATE DATABASE Admin_SnapShot ON

    (Name = N'Admin', FILENAME = N'E:\SQL_data\Admin_SnapShot.ss')

    AS SNAPSHOT OF Admin

    The logical name seems correct as:

    select name, type_desc, physical_name from sys.master_files

    where database_id = db_id('Admin')

    returns Admin in the name field for the mdf file.

    When the server is rebooted the snapshot gets created using the same script.

    Does any one has any ideas? Are any system resources locked? Are are some statistics not up to date?

    Any ideas how to prevent the error?

    Cheers

  • June 26, 2013 at 8:14 am

    #1627579

    I had exactly the same situation, restarting the MSSQL Service helped

  • November 8, 2016 at 8:31 am

    #1911201

    Thanks, restarting the service helped me a lot!

Viewing 7 posts - 1 through 6 (of 6 total)

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