Copy SQL Server Express Database to Another Computer

Question

I am upsizing the back end of a client's Access app tomorrow and need to be ready with a plan for a particular problem with this client. The boss needs to be able to take the data file away from the office where the SQL Server is on a regular basis (usually monthly) and run statistical analyses on the data.

I've looked at Easiest way to copy an entire SQL server Database from a server to local SQL Express, and the solutions there don't work in this scenario because:

1. can't use a one-time solution (Database Publishing Wizard), as this needs to be scriptable.

2. can't use any method that depends on the two computers being networked, as that is not an option -- data has to be transferred via USB thumb drive (so, no replication).

3. can't use any method that depends on running SQL Server management tools from the server console or from the workstation for the backup.

4. can't connect to the SQL Server database directly for the analysis because the data has to be portable to other locations.

What I think I need is some way to call a script that creates a backup file, then copy the result to the USB drive. I then need a second script to copy the backup file from the USB drive and restore it onto the other SQL Server.

The data being transported is read-only (or, any changes made don't need to get back to the main server), and data won't be updated in the second location. It's currently scripted with a plain old batch file to copy the back-end MDB file, and I need something that is as simple for the user.

It can't have any dependencies on, say, Powershell (of SQL Server Management Studio), because I don't want it to have to be installed on the computer the user is running the script from (there are a half dozen workstations the script needs to be runnable from, and I don't want to have to install something on all of those).

I'm going to be setting up the backup agent to create a backup every night, so I could perhaps copy that file without needing to initiate the backup before copying. Thus I might only need to script the restore on the target computer.

Thoughts, suggestions, pointers?

Answer 1

You should definitely be able to create something like that.

One part would be a T-SQL CREATE BACKUP script as a .sql script, and execute that from a standard Windows batch (*.bat) or command (*.cmd) file using the sqlcmd command line tool.

That would be something like this:

backup.sql

BACKUP DATABASE YourDatabase
TO DISK = 'Z:\Backup\YourDatabase.bak'
WITH FORMAT;

The second part would be a .sql file with a T-SQL RESTORE script, basically reading the data from a given location on disk and restoring it to that SQL Server instance there.

restore.sql

RESTORE DATABASE YourDatabase
   FROM AdventureWorks2008R2Backups 
   WITH 
     MOVE 'YourDatabase_Data' TO 'C:\MSSQL\Data\YourDatabase.mdf',
     MOVE 'YourDatabase_Log' TO 'C:\MSSQL\Data\YourDatabase_Log.ldf';
GO

Of course, you need to adapt those names and paths to your own actual requirements - but that should just give you a hint how to get started with this endeavor.

To execute one of those .sql script using sqlcmd, you need something like:

sqlcmd -S (name of server) -U (login) -P (password) -I (name of script file)

e.g.

sqlcmd -S (local) -U someuser -P top$secret -I backup.sql

Resources:

• How to create a full database backup
• T-SQL RESTORE command
• sqlcmd utility

Answer 2

Why not continue copying the entire database file? This reads like a small enough operation to get away with a momentary pause: You could shrink the database, detach, and copy the files directly. On the target system, you could attach the SQLExpress DB by file name in the connection string.

Answer 3

Just a thought but if he currently goes away with a copy of a MDB file with all the data in just for himself then why not carry on like that. You could just do it all in access with a query for each table.

If you setup your “reporting” MDB with the tables linked and called “tblFoo_LINKED” and a local table called “tblFoo” you could then just run a bit of VBA code that would loop through all the tables and execute something like

INSERT INTO tblFoo SELECT * FROM tblFoo_LINKED

The SQL server backup restore would still be the option I would go for but just offering a different spin on things which might fit your needs

Answer 4

I had this same issue transporting db between production (server) and test-development (local in another location) and also transporting finished db to hosted server.

As it turned out I can just transport the .MDF by itself.

1. Make sure target db does not have that db attached, delete it first in SSMS.
2. Move the .MDF by itself (without the log file).
3. At target location default c:\program files...sql..\DATA make sure all prior instance of MDF and LDF are moved or deleted -- if it sees an .ldf there it gets confused.
4. In SSMS, choose to attach. Press Add, select the .mdf.
5. At this point, in the box right below, it will show that an MDF and LDF are attached and that the LDF is missing. Click the LDF and press the REMOVE button.
6. Now the MDF will be attached and a new/empty LDF will be added.

I do this all the time; works perfectly -- saves the trouble of transporting a large .ldf. (I often use dropbox.com instead of a thumb drive and encrypt the file with PKZIP/SecureZip first.)

I'm not sure what happens if you copy the MDF with the server started, though I do not stop it before copying. I'm not sure how to make this scriptable -- the attach can be scriptable but I'm not sure about deleting the .LDF before proceeding.

You could script the server to make a snapshot copy of the database, then transport that MDF knowing that it was not being updated.

Another idea - write a program to insert all the records into a SQL-Server compact edition file and transport that? I've not tried it, but read about it.