locked
move data files RRS feed

  • Question

  • my laptop crashed but i have a full backup of the drive. how can i restore the sql server data into another laptop. i have all the files from the drive backup but they are the entire folder of the database stored ..,
    Monday, August 14, 2017 2:13 AM

All replies

  • Hi The World is mine,

    Since you mentioned it’s a drive-level backup, you properly do not have an up-to-date database backup. In this case, firstly install a new SQL Server instance of the same version and patch level as precious one on the new laptop, also, install SQL to the same directory path as the previous server.

    Secondly, in the new SQL Server instance, perform the following steps:

    1. For system databases:    

    a. Stop the SQL Server services;  

    b. Copy mdf, ldf files of system databases (master, msdb, model)  of old SQL instance to the SQL data directory in the new laptop;    

    c. Restart SQL services. If SQL Server fails to start, please post the error message you’ve seen.

    2. Copy mdf, ldf files of user databases  of old SQL instance to the SQL data directory in the new laptop, then check if user databases appear in the new SQL Server instance, if not, attach user databases following the guide in this article: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-attach-db-transact-sql.

    If you have any other questions, please let me know.

    Regards,

    Hannah


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Hannah Yu Monday, August 14, 2017 10:23 AM
    • Proposed as answer by AV111 Monday, August 14, 2017 1:32 PM
    Monday, August 14, 2017 10:21 AM
  • I did step 1 and I keep finding this error when I start sql server.

    Log Name:      Application
    Source:        MSSQLSERVER
    Date:          2017-08-14 5:31:38 PM
    Event ID:      17204
    Task Category: Server
    Level:         Error
    Keywords:      Classic
    User:          N/A
    Computer:      DESKTOP-TCR4P9S
    Description:
    FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER1\MSSQL\DATA\model.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).
    Event Xml:
    <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
      <System>
        <Provider Name="MSSQLSERVER" />
        <EventID Qualifiers="49152">17204</EventID>
        <Level>2</Level>
        <Task>2</Task>
        <Keywords>0x80000000000000</Keywords>
        <TimeCreated SystemTime="2017-08-14T21:31:38.839513200Z" />
        <EventRecordID>6745</EventRecordID>
        <Channel>Application</Channel>
        <Computer>DESKTOP-TCR4P9S</Computer>
        <Security />
      </System>
      <EventData>
        <Data>FCB::Open failed</Data>
        <Data>C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER1\MSSQL\DATA\model.mdf</Data>
        <Data>1</Data>
        <Data>3(The system cannot find the path specified.)</Data>
        <Binary>3443000010000000100000004400450053004B0054004F0050002D0054004300520034005000390053000000070000006D00610073007400650072000000</Binary>
      </EventData>
    </Event>

    FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER1\MSSQL\DATA\modellog.ldf'. Diagnose and correct the operating system error, and retry the operation.
    Event Xml:
    <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
      <System>
        <Provider Name="MSSQLSERVER" />
        <EventID Qualifiers="49152">17207</EventID>
        <Level>2</Level>
        <Task>2</Task>
        <Keywords>0x80000000000000</Keywords>
        <TimeCreated SystemTime="2017-08-14T21:31:38.841516900Z" />
        <EventRecordID>6746</EventRecordID>
        <Channel>Application</Channel>
        <Computer>DESKTOP-TCR4P9S</Computer>
        <Security />
      </System>
      <EventData>
        <Data>FileMgr::StartLogFiles</Data>
        <Data>2(The system cannot find the file specified.)</Data>
        <Data>C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER1\MSSQL\DATA\modellog.ldf</Data>
        <Binary>3743000010000000100000004400450053004B0054004F0050002D0054004300520034005000390053000000070000006D00610073007400650072000000</Binary>
      </EventData>
    </Event>

    Monday, August 14, 2017 9:36 PM
  • Hi The World is mine,

    According to the error message you provided, SQL Server failed to start. Would you please double check the file path to make sure it’s the same as that on your previous server? Did you install SQL Server 2016 with the same instance name MSSQLSERVER1 on your old laptop?

    Besides, please share SQL Server error log and post it here so that I can better understand your situation. About how to view SQL error log, please refer to this link.

    If you have any other questions, please let me know.

    Regards,

    Hannah


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 15, 2017 2:20 AM
  • Hi The World is mine,

    Any update on your issue? Have you solved your problem yet?

    If you have any other questions, please let me know.

    Regards,

    Hannah


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 22, 2017 6:03 AM