October 30, 2008 at 9:33 am
I am trying to create a snapshot of the adventureWorks database on my home pc and keep getting the following error:
Msg 5127, Level 16, State 1, Line 1
All files must be specified for database snapshot creation. Missing the file "CH3f1".
What does this mean and how can resolve it. I do not remember creating a file or filegroup for the database.
Any help would be much appreciated.
May 10, 2009 at 10:35 pm
I was experiencing the same issue while studying Chapter 10 of Gibson's, "MCITP SQL Server 2005 Database Administration" book.
Instead of running the query in the text, after a bit of searching I've come up with this solution:
CREATE DATABASE AdventureWorks_Snapshot_0900 ON
(NAME = AdventureWorks_Data,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Snapshot_0900.ss'),
(NAME = Ch3F1,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Snapshot_0900-Ch3F1.ss'),
(NAME = Ch3F2,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Snapshot_0900-Ch3F2.ss'),
(NAME = Ch3F3,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Snapshot_0900-Ch3F3.ss')
AS SNAPSHOT OF AdventureWorks;
The problems lies in the fact that an exercise in Chapter 3 of the book has you create filegroups, and associate a file with each filegroup. The query from chapter 10 does not include the proper syntax to account for these extra files created earlier, which are now a part of the AdventureWorks database. I found this MSDN article http://msdn.microsoft.com/en-us/library/ms176061.aspx, which includes a sample T-SQL script which creates a database snapshot of a database with multiple files.
The above script worked for me....enjoy.
Jason Hindson
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply