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:
- 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.
- 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.
- 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:
- The column number to export, where the first column is numbered "1".
- What format to export in, although SQLCHAR seems to always work for all
columns.
- Always 0.
- The maximum length of the data in this column.
- 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.
- The column number again.
- The column name.
- 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:
- Create a text file of database delete commands, looking something like this:
delete from mydatabase.dbo.myfirsttable
go
delete from mydatabase.dbo.mysecondtable
go
- 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
| 