Creating a snapshot on a database error message 5127

Question

Creating a snapshot on a database error message 5127

chad.henningfeld

Old Hand

Points: 362
More actions
December 1, 2008 at 8:51 am

#125247

I'm practicing creating snapshots in SQL, when i perform this syntax i receive the following error
USE Master
GO
CREATE DATABASE AdventureWorks_snap042007 ON
( NAME = AdventureWorks_Data,
FILENAME = 'C:\AdventureWorks_data_042007.ss' )
AS SNAPSHOT OF AdventureWorks
GO
Msg 5127, Level 16, State 1, Line 1
All files must be specified for database snapshot creation. Missing the file "fg0103SALES".
The file group that is missing does exist and shows within the database properties any suggestions. Thanks a lot.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

When you create a snapshot, you must specify all of the files. The error indicated that you haven't specified all of the files that the DB has (excluding the log) within the create snapshot statement.

Did you get that error with the create snapshot of adventureworks?

What does this return in the DB you're trying to snapshot?

select file_id, name, type_desc from sys.database_files

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

chad.henningfeld Old Hand Points: 362 More actions · Answer 2

When i run the command the select file_id, name, type_desc from sys.database_files the output is this:

1AdventureWorks_DataROWS

2AdventureWorks_LogLOG

3fg0103SALESROWS

4fg0406SALESROWS

5fg0708SALESROWS

65537sysft_Cat1FULLTEXT

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

As part of the create snapshot, you need to specify all data files, not just one of them

So, it'll be something like this

CREATE DATABASE AdventureWorks_snap042007 ON

(NAME = AdventureWorks_Data, FILENAME = 'C:\AdventureWorks_data_042007.ss'),

(NAME = fg0103SALES , FILENAME = < Specify filename > )

(NAME = fg0406SALES , FILENAME = < Specify filename > ),

(NAME = fg0708SALES , FILENAME = < Specify filename > ),

AS SNAPSHOT OF AdventureWorks

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

chad.henningfeld Old Hand Points: 362 More actions · Answer 4

Thank you very much I was able to get the query to perform.

rojas9198 Old Hand Points: 395 More actions · Answer 5

Hi, I am running into the same situation as the other person. On my case, I get an incorrect sysntax error on the AS keyword. My code is as follows:

USE Master

GO

CREATE DATABASE AdventureWorks_snap021607 ON

(NAME = AdventureWorks_Data, FILENAME = 'C:\AdventureWorks_data_021607.ss' ),

(NAME = fg0103SALES , FILENAME = 'J:\AdventureWorks_FileGroups\FG0103SALES\0103SALES.ndf'),

(NAME = fg0406SALES , FILENAME = 'J:\AdventureWorks_FileGroups\FG0406SALES\0406SALES.ndf'),

(NAME = fg0708SALES , FILENAME = 'J:\AdventureWorks_FileGroups\FG0708SALES\0708SALES.ndf'),

AS SNAPSHOT OF AdventureWorks

GO

Your help will be greatly appreciated.

Thanks,

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Please post new questions in a new thread. That's not the same error the OP had.

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