Mirror Database - Cannot create snapshot

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Mirror Database - Cannot create snapshot

Mirror Database - Cannot create snapshot

Rate Topic

Display Mode

Topic Options

Author

Message

Matthew Orton

Posted Thursday, August 12, 2010 2:30 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 08, 2012 2:58 AM
Points: 10, Visits: 108

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

Post #967990

GilaMonster

Posted Thursday, August 12, 2010 2:49 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 37,669, Visits: 29,922

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 2008, MVP
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

Post #967999

Matthew Orton

Posted Thursday, August 12, 2010 3:00 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 08, 2012 2:58 AM
Points: 10, Visits: 108

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.

Post #968003

rjspradeep

Posted Wednesday, July 27, 2011 2:17 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 13, 2013 1:19 AM
Points: 1, Visits: 77

Thanks for the help! i had the same issue. got it resolved. Smile

Regards
Pradeep

Post #1149642

xanthos

Posted Thursday, February 14, 2013 9:35 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 03, 2013 9:09 AM
Points: 267, Visits: 622

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

Post #1420152

« Prev Topic | Next Topic »

Permissions