Scribd Logo
Upload

Welcome to Scribd!

  • 115 views

    BCP Restore Guide

    Uploaded by

    Gopal Arunachalam
    This document provides instructions for using the bcp utility to restore an MS SQL Server database from data files. It outlines reviewing the necessary files, deleting any existing databases, creating logins and users, correcting file references, and executing the files in the proper order to perform the restore. Troubleshooting tips are also included.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    BCP Restore Guide

    Uploaded by

    Gopal Arunachalam
    115 views6 pages
    This document provides instructions for using the bcp utility to restore an MS SQL Server database from data files. It outlines reviewing the necessary files, deleting any existing databases, creating logins and users, correcting file references, and executing the files in the proper order to perform the restore. Troubleshooting tips are also included.

    Original Description:

    roger

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document provides instructions for using the bcp utility to restore an MS SQL Server database from data files. It outlines reviewing the necessary files, deleting any existing databases, creating logins and users, correcting file references, and executing the files in the proper order to perform the restore. Troubleshooting tips are also included.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    115 views6 pages

    BCP Restore Guide

    Uploaded by

    Gopal Arunachalam
    This document provides instructions for using the bcp utility to restore an MS SQL Server database from data files. It outlines reviewing the necessary files, deleting any existing databases, creating logins and users, correcting file references, and executing the files in the proper order to perform the restore. Troubleshooting tips are also included.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 6

    Restore MS SQL Server with BCP utility

    Amendment History

    Author /
    Version Date Change Description
    Changed By
    1.0 27-Apr-2018 Rajesh Initial Draft

    Comments:
    Introduction
    The bcp utility (Bcp.exe) is a command-line tool that uses the Bulk Copy Program (BCP) API. The bcp
    utility performs the following tasks:

     Bulk exports data from a SQL Server table into a data file.

     Bulk exports data from a query.

     Bulk imports data from a data file into a SQL Server table.

     Generates format files.

    The bcp utility is accessed by the bcp command. To use the bcp command to bulk import data, you must
    understand the schema of the table and the data types of its columns, unless you are using a pre-
    existing format file.

    The bcp utility can export data from a SQL Server table to a data file for use in other programs. The
    utility can also import data into a SQL Server table from another program, usually another database
    management system (DBMS). The data is first exported from the source program to a data file and then,
    in a separate operation, copied from the data file into a SQL Server table.

    The bcp command provides switches that you use to specify the data type of the data file and other
    information. If these switches are not specified, the command prompts for formatting information, such
    as the type of data fields in a data file. The command then asks whether you want to create a format file
    that contains your interactive responses. If you want flexibility for future bulk-import or bulk-export
    operations, a format file is often useful. You can specify the format file on later bcp commands for
    equivalent data files. For more information, see Specify Data Formats for Compatibility when Using bcp
    (SQL Server). [1]

    Getting Started
    1. Review the Files.

    Review if you received the right files. To import data with BCP utility usually we need the files
    below:

     Database creation file


     User creation files
     Tables creation files
     Assemblies creation files
     Views creation files
     Roles creation files
     Data records files*
     Restore.bat file (this file contains the BCP utility commands for properly restore the
    database)
    * The data record files can have multiple formats and extensions; it depends on BCP export
    settings. Execute those files at the end of the current process. In this document, we use .dat
    files.

    2. Review that your environment matches with the BCP import files.
    It could happen that the files you received has wrong information about database names and
    paths, so be carefully and identify this situation. This usually happen because in other servers
    environments database’s names and paths differ.
    If you have this issue, use the “Find and replace” utility of your preferred editor and replace the
    fields described above with the right server environment information. E.g. If my database name
    is PRIVSER, the action we take is to change the misleading information:

    3. Delete the current database

    Delete (if exists) and create the target database before use BCP import Utility. In this example,
    we use PRIVDOC as database name with Latin1_General_100_BIN2 collation.
    4. Create the login user.
    Create the PRIVDOC user for the PRIVDOC database.
    5. Review and correct the import BCP files.
    As we said before, replace the information about database names, user credentials, and other
    related misleading information, so they must match the target environment.
    E.g.:

    6. Execute the given files in proper order


    Now, proceed to execute the given files in the order below.
    a. Database and user creation files
    b. Table, views, assemblies and roles creation files
    c. Restore.bat file

    Troubleshooting
    1. If the execution of one of the given files throw an error:
    a. Sometimes some files are too long, so try to execute the same file in chunks.

    References:

    [1] https://docs.microsoft.com/en-us/sql/relational-databases/import-export/import-and-export-
    bulk-data-by-using-the-bcp-utility-sql-server?view=sql-server-2017

    You might also like