SQL Server Performance

Problem while creating database snapshot

Discussion in 'SQL Server 2005 General DBA Questions' started by Manoj118, Feb 18, 2008.

  1. Manoj118 New Member

    I am trying to create a database snapshot in SQL 2005 and I am getting the following error:

    Msg 5014, Level 16, State 3, Line 1
    The file 'DATABASENAME_DATA' does not exist in database 'DATABASENAME'.


    This is the TSQL Statement:

    CREATE DATABASE DATABASENAME_SS01 ON
    ( NAME = 'DATABASENAME_DATA', FILENAME = N'D:program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataDATABASENA
    ME_SS01.ss' )AS SNAPSHOT OF DATABASENAME;

    Everything that I am reading online says DATABASENAME_DATA is just the name for the snapshot... So I am confused.
  2. amitadmin New Member

    Hi
    For Example AdventureWorks User Database and you requried the snapshot of this database. I hope below query can resolve your issue.
    CREATE DATABASE AdventureWorks_dbss1800
    ON
    ( NAME = AdventureWorks_Data, FILENAME = 'C:program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_data_1800.ss' )
    AS SNAPSHOT OF AdventureWorks
    GO
  3. dhm1980 New Member

    Hi Amit,
    I am trying following code to create database snapshot. But, I am not able to create. Will you please also explain me first, what exactly I have to take in place of database name_data ? I also went through creating snapshot by T-SQL in msdn. It also says, Logical filename. I am not understanding what is that exactly?
    database Name: Employee
    CREATE DATABASE EMPLOYEESSDB ON(
    NAME = EMPTAB, FILENAME = 'D: DATASQL SERVER TEMP PRACTSNAPSHOTEMPSNAP1.ss' )AS
    SNAPSHOT of EMPLOYEEGO
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    I also used the following script:
    CREATE DATABASE AdventureWorks_dbss1800
    ON
    ( NAME = AdventureWorks_Data, FILENAME = 'C:program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_data_1800.ss' )
    AS SNAPSHOT OF AdventureWorks
    GO
    but still, it gives the same errorMsg 5014, Level 16, State 3, Line 6
    The file 'EMPTAB' does not exist in database 'EMPLOYEE'.
    ---------------------------------------------PLEASE SEND ME THE SOLUTION ON MY EMAIL ID--dhm1980@gmail.com--------------------------------------------------------------
  4. KhalidH New Member

    "EMPTAB" needs to be the logical file name of the AdventureWorks database
    Right click on Database and select properties select files , there u will see the logical filesname u will need the logical filenameof the data file.
  5. satya Moderator

  6. Manoj118 New Member

    Thanks Amit Kumar
    Now i was able to create the snapshot of my database.
  7. harsh kumar New Member

    even i am getting the same problem.... i have used both query provided by you(Manoj) and Amit.
    does it problem with sql server version... i am using developer edition of sql server 2005 with sp2...
    any help here....
  8. satya Moderator

    Developer edition is like to like Enterprise edition and it should work, post the query you use and error too
  9. ashoksuhag4129 New Member

    I have two logical files for the same database namely
    ProjectServer_Reporting_2.mdf
    ProjectServer_Reporting_1.ndf
    both belonging to the same datbase - ProjectServer_Reporting
    While Creating Snapshot of above database, if i choose "ProjectServer_Reporting_1" name- It thows error
    All files must be specified for database snapshot creation. Missing the file "ProjectServer_Reporting_2".
    and vice versa
    ---should i have to pass two filenames in "NAME" part of the query? Below is my querycreate
    DATABASE ProjectServer_Reporting_New_snapON(
    NAME ='ProjectServer_Reporting_1',
    FILENAME = 'E:SQL ServerDATAProjectServer_Reporting_New_snap.ss' )
    AS SNAPSHOT OF ProjectServer_Reporting;
  10. ashoksuhag4129 New Member

    OK! i got it, just another tag needed....

Share This Page