locked
Unable to move my database to a new environment using database script RRS feed

  • Question

  • I have generated a database script from my dev machine by ; right click on DB >> tasks >> generate scripts. Then on my staging environment I open the script file inside sql server management studio , and I execute the script.

    But the script raised the following errors:-

    Msg 5133, Level 16, State 1, Line 2
     Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TMS.mdf" failed with the operating system error 2 (failed to retrieve text for this error. Reason: 15105). 
    
    Msg 1802, Level 16, State 1, Line 2
     CREATE DATABASE failed. Some file names listed could not be created. Check related errors. 
    
    Msg 5011, Level 14, State 5, Line 1
     User does not have permission to alter database 'TMS', the database does not exist, or the database is not in a state that allows access checks. 
    
    Msg 5069, Level 16, State 1, Line 1
     ALTER DATABASE statement failed. 
    
    Msg 911, Level 16, State 4, Line 3
     Database 'TMS' does not exist. Make sure that the name is entered correctly
    

    can anyone adivce what is the problem ?
    Monday, January 6, 2014 3:42 PM

Answers

  • yes on my dev. environment I have a folder named C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data . while on the staging I do not have such a  folder ? can you advice , although both servers uses sql server 2008 R2 ?
    • Edited by johnjohn11 Monday, January 6, 2014 4:01 PM
    • Marked as answer by johnjohn11 Monday, January 6, 2014 5:16 PM
    Monday, January 6, 2014 3:59 PM
  • Also if you only have a single data file and single log file, simply omit the file and filegroup clauses from CREATE DATABASE it will be created in the default location.

    EG

    CREATE DATABASE MyDatabase

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by johnjohn11 Monday, January 6, 2014 5:16 PM
    Monday, January 6, 2014 4:22 PM
  • you are right,the installtion folders was on different drives D & L .

    Thanks

    • Marked as answer by johnjohn11 Monday, January 6, 2014 5:19 PM
    Monday, January 6, 2014 5:18 PM

All replies

  • If you do "net helpmsg 2" in DOS, you get "the system cannot find the file specified"

    I suspect that the path that your script is trying to create the database in does not exist in your staging environment, or the service account doesn't have permissions on it.


    Thanks, Andrew
    My blog...

    Monday, January 6, 2014 3:48 PM
  • The directory "MSSQL10_50.MSSQLSERVER" is not the installation directory on the new server.  You must change the directory paths to match the installation on the new server.

    Monday, January 6, 2014 3:50 PM
    Answerer
  • yes on my dev. environment I have a folder named C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data . while on the staging I do not have such a  folder ? can you advice , although both servers uses sql server 2008 R2 ?
    • Edited by johnjohn11 Monday, January 6, 2014 4:01 PM
    • Marked as answer by johnjohn11 Monday, January 6, 2014 5:16 PM
    Monday, January 6, 2014 3:59 PM
  • The directory "MSSQL10_50.MSSQLSERVER" is not the installation directory on the new server.  You must change the directory paths to match the installation on the new server.

    but how i can knwo what is the installation directory for my new server ? can you adivce , as this is my first DB task?

    • Edited by johnjohn11 Monday, January 6, 2014 4:05 PM
    Monday, January 6, 2014 4:05 PM
  • refer this article to find default data & log location.

    http://technet.microsoft.com/en-us/library/dd206993.aspx


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Monday, January 6, 2014 4:16 PM
  • Also if you only have a single data file and single log file, simply omit the file and filegroup clauses from CREATE DATABASE it will be created in the default location.

    EG

    CREATE DATABASE MyDatabase

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by johnjohn11 Monday, January 6, 2014 5:16 PM
    Monday, January 6, 2014 4:22 PM
  • you are right,the installtion folders was on different drives D & L .

    Thanks

    • Marked as answer by johnjohn11 Monday, January 6, 2014 5:19 PM
    Monday, January 6, 2014 5:18 PM