SQL Server 2005 database replication

Using BCP and SSEUTIL

This article discusses how to copy databases from one computer to another. This is a typical problem to face if you have a development version of your system and you want to copy this over to a live version, or simply if you want to make a backup of a live system.

I use ASP.NET 2 and SQL Server 2005 for my database driven websites. As far as I know, there are three possible ways to transfer data from one installation of SQL Server to another:

1. Export all (or some) of the data out of the first computer's database to some text files, copy these text files to the second computer and then import the data into the second database from these text files. There is a tool included with SQL Server called BCP to facilitate this technique, and it works well, except that it is difficult to configure and involves some pretty large text files.
2. Detach the entire database on the first computer, and also on the second computer, then copy the entire database (comprised of at least the .mdb and .ldb files) to the second computer, then re-attach the database in both places. This is a very simple strategy to operate, as long as you have scripts in place to do the detach / attach, and don't mind the fact that you will copy the entire database, not just some tables of it.
3. Use SQL Server's built-in replicate function. The disadvantage of this method is that it is not included in the Express edition of SQL Server, which is the one that I use (because it is free).

This article describes the first two of these methods, explaining them with examples and scripts.

Using BCP to export / import SQL Server data

SQL Server 2005 includes a command line tool called BCP (the Bulk Copy Program). This tool can be used in a batch file (script) to automate the exporting and importing of data from and to SQL Server via text files.

BCP is usually installed in the C:\Program Files\Microsoft SQL Server\90\Tools\Binn folder. A clever thing to do is to add this folder to your DOS PATH variable.

Exporting a table using BCP

To export data using BCP, use this command:

bcp mydatabase.dbo.mytable out c:\path\to\file.txt -S .\SQLEXPRESS -T -U user -f config_file.txt

This tells BCP:

• The name of the table you want to export
• That you want to export ("out") the data
• The text file to export it to
• The SQL Server instance to use
• Whether to use a trusted connection
• The username to login as
• The name of the BCP configuration file for this table

It is this configuration file that will have you tearing your hair out. This file tells BCP exactly what format the data for this table is in, and how to export it. You must create a different configuration file for each table you want to export. Luckily you can use the same configuration file on the second computer when you are running the "import" version of BCP.

Here is an example BCP configuration file:

9.0
6
1 SQLCHAR 0 4  "|"  1 product_id ""
2 SQLCHAR 0 50 "|"  2 wmv_url    ""
3 SQLCHAR 0 50 "|"  3 mov_url    ""
4 SQLCHAR 0 50 "|"  4 thumbnail  ""
5 SQLCHAR 0 50 "|"  5 active     ""
6 SQLCHAR 0 0  "\n" 6 text       ""

The first line tells BCP which version of SQL Server the database was created with (9.0 means SQL Server 2005)

The second line specifies how many columns are to be exported.

Each subsequent line describes one column of the table, and requires the following information, separated by spaces:

1. The column number to export, where the first column is numbered "1".
2. What format to export in, although SQLCHAR seems to always work for all columns.
3. Always 0.
4. The maximum length of the data in this column.
5. The delimiter to use in the output file, to separate pieces of data. I have used the "bar" character in this example, but anything can be used. To ensure that the data is written to the textfile with one record per textfile line, I use \n" as the delimeter at the end of the last column of data, so that BCP inserts a newline there.
6. The column number again.
7. The column name.
8. The sort order to use on this column. A pair of empty quotation marks works fine here.

You must create a configuration file for each database table, then create a batch file to go through each table at a time exporting the data. BCP is very fast, even on large tables, and creates the textfiles in no time.

Importing tables using BCP

Once the textfiles have been copied to the second computer, you must import them again using BCP. This is an example of a BCP import command:

bcp mydatabase.dbo.mytable in C:\path\to\file.txt -E -T -S .\SQLEXPRESS -U user -f config_file.txt -e errors.txt

This tells BCP:

• Which table to import the data into.
• To import the data rather than export it.
• The text file to import it from.
• -E indicates that we should import identity fields from the text file, rather than get SQL Server to generate new identity fields. This is VERY important if you want your ID fields in the target database to be the same as the ones in your source database.
• To use a trusted connection
• Which SQL Server instance to use
• The User to run the command as
• The BCP configuration file to use for this table. Luckily, this is just a copy of the one you used during export.
• An error file to write any errors to.

How to delete the existing data from your second database

If you try to import data into the database on the second computer, and you haven't deleted the existing data from there first, then you will most likely end up with duplicate Primary Key errors. This is because you will probably be trying to add a second copy of the records that already exist in the tables.

To delete the database table contents so that they are empty before doing the import, use this technique:

1. Create a text file of database delete commands, looking something like this:
   

   delete from mydatabase.dbo.myfirsttable
   go
   delete from mydatabase.dbo.mysecondtable
   go

2. Execute this command:
   
   sqlcmd -S .\SQLEXPRESS -i file_of_delete_commands.txt

SQLCMD is just a command line version of SQL Server, allowing you to run sets of database commands in a batch file.

So, we've seen how to copy a database, or just some of the tables in it, from one computer to another using BCP. It's fast, its a bit basic, and it is hard to configure, but once working it works well.

Copying a database's underlying files from one computer to another

If you don't want the complexity of the BCP method, and you want the entire database file to be copied rather than just some tables of it, then this method could be the one to use. It copies the entire database in a low-level way by simply making a copy of the mydatabase.mdf and mydatabase_log.ldf files.

How to copy the files

You can't copy the database files themselves if they are being used by SQL Server -- trying to do so will result in an access denied error. So you will need to detach them from SQL Server so that it closes the files. The strategy is to detach the database on both computers, copy the files from the first computer to the second, then attach them both again. I do the detach and attach using SSEUTIL.

Using SSEUTIL

SSEUTIL in an add-on for SQL Server, called the SQL Server Express Utility, although it also runs with the full version of SQL Server. You use it as follows:

sseutil -s .\SQLEXPRESS -d c:\path\to\my\database.mdf

Where:

• -s specifies the instance of SQL Server to use.
• -d specifies to DETACH the databse
• Then the actual location of the MDF file is specified

To attach a databse again, run the sseutil command with "-a" instead of "-d".

Once you have detached the database on both computers, copy the .MDF and .LDF files across, as well as any full-text index catalogs (see below for more details). Then attach the databases again using SSEUTIL.

The detatch / copy / attach solution is good if you want to copy over an entire database, including stored_procedures, permissions etc. Just watch out though, as .MDF files can be huge.

It's more complicated with full-text indexes though

If your database has a full-text index catalog, then you will also need to copy the full-text index files too. These are located in the FTData folder, and are composed of lots of small files. I advise you to zip up the whole FTData folder and copy the lot over to the second computer.

Copying the username / password file ASPNETDB.MDF

The ASPNETDB.MDF database is a special database for ASP.NET containing all the usernames and passwords for authentication to a website. It is possible to copy one of these login databases from one computer to another in exactly the same way as any other database file. Here is the procedure:

(1) Detach the ASPNETDB.MDF file on the first computer:

   sseutil -s .\SQLEXPRESS -d c:\path\to\App_Data\ASPNETDB.MDF

(2) Copy the file over to the second computer.

(3) Attach it again:

   sseutil -s .\SQLEXPRESS -a c:\path\to\App_Data\ASPNETDB.MDF