Moving a Database

[Gene Wirchenko]

Dear SQLers:

What is the proper way for moving a database from location to
location?

I put together a simple database, backed it up, and sent it to
the other location. It will not restore. (No, I was not told the
error message.) My opposite number says that the database is supposed
to be unattached and attached, but he does not know all the ins and
outs either.

How can I simply create a database and send it elsewhere, or
receive one from elsewhere and then have it get installed on the
destination?

Scripts are not acceptable in this case as the databases will
eventually be operational databases, and we need to cover this. For
debugging purposes, I may need to receive and install a full database.

Sincerely,

Gene Wirchenko

[Jeroen Mostert]

Post by Gene Wirchenko
What is the proper way for moving a database from location to
location?

Depends on what you mean by "location". If the database stays on the same
server, ALTER DATABASE SET OFFLINE is the way to go. Otherwise,
backup/restore is usually the way to go.

Post by Gene Wirchenko
I put together a simple database, backed it up, and sent it to
the other location. It will not restore. (No, I was not told the
error message.)

Are you by any chance sending a backup from SQL Server version Y to SQL
Server version X, with X > Y? This will not work and is the most common
cause of restore failures. You can't restore a 2008 R2 backup on 2008, a
2008 backup on 2005 or a 2005 backup on 2000.

Second guess, the restore could just be done incorrectly. When performed
from Management Studio, it will move the files to the default data location
for SQL Server, which is usually but not always appropriate, depending on
your installation. When done with an SQL statement, obviously it has to be
correct.

There's also the possibility of the database using Enterprise-edition
features whereas you're restoring it on a non-Enterprise edition, or a
database that exceeds the size limit of SQL Server Express (which varies by
edition). This is probably the second most common cause. The error message
would really help here.

Most disappointingly, the database might be corrupt. A successful backup
does not guarantee a successful restore. Nor, for that matter, do either a
successful backup or a successful restore guarantee no corruption. DBCC
CHECKDB will help here. If you just "put it together", this is not a likely
scenario, but it's always a possibility.

Post by Gene Wirchenko
My opposite number says that the database is supposed to be unattached
and attached, but he does not know all the ins and outs either.

Backup/restore is the easiest and most reliable method for transferring
databases between servers. Detach/attach makes the database unavailable at
the source, deletes metadata in the master database where some nominally
per-database settings are kept and runs the risk of losing the database if
you're moving the files rather than copying them. It should be considered an
advanced scenario. I certainly wouldn't trust someone who can't tell you
what goes wrong with a restore to handle detach/attach correctly.

Post by Gene Wirchenko
How can I simply create a database and send it elsewhere, or
receive one from elsewhere and then have it get installed on the
destination?
Scripts are not acceptable in this case as the databases will
eventually be operational databases, and we need to cover this. For
debugging purposes, I may need to receive and install a full database.

Get them to tell you the error. You wouldn't visit the doctor and tell him
"something's just wrong", would you?

For debugging purposes, you would always want backup/restore and not
detach/attach anyway, because detaching incurs downtime.

[Jeroen Mostert]

Post by Jeroen Mostert
Are you by any chance sending a backup from SQL Server version Y to SQL
Server version X, with X > Y?

Obviously you shouldn't try fancy mathematics if mere words will do: "later
versions won't restore on earlier versions". Disregard the symbolic fraud.

[Gene Wirchenko]

On Wed, 02 May 2012 20:11:30 +0200, Jeroen Mostert

Post by Jeroen Mostert

Post by Gene Wirchenko
What is the proper way for moving a database from location to
location?

Depends on what you mean by "location". If the database stays on the same
server, ALTER DATABASE SET OFFLINE is the way to go. Otherwise,
backup/restore is usually the way to go.

No, it does not. I am the developer. My system has the
development database. The other system is elsewhere and will
eventually be the production system.

Post by Jeroen Mostert

Post by Gene Wirchenko
I put together a simple database, backed it up, and sent it to
the other location. It will not restore. (No, I was not told the
error message.)

Are you by any chance sending a backup from SQL Server version Y to SQL
Server version X, with X > Y? This will not work and is the most common
cause of restore failures. You can't restore a 2008 R2 backup on 2008, a
2008 backup on 2005 or a 2005 backup on 2000.

We ran into that before. No, it is not the case this time,
because my opposite number did get it to restore after adding move
clauses to the restore command. He says that he also used
detach/attach, but he did not write that command in his return E-mail.

Post by Jeroen Mostert
Second guess, the restore could just be done incorrectly. When performed
from Management Studio, it will move the files to the default data location
for SQL Server, which is usually but not always appropriate, depending on
your installation. When done with an SQL statement, obviously it has to be
correct.
There's also the possibility of the database using Enterprise-edition
features whereas you're restoring it on a non-Enterprise edition, or a
database that exceeds the size limit of SQL Server Express (which varies by
edition). This is probably the second most common cause. The error message
would really help here.

Both systems are using SQL Server 2008 Express.

Post by Jeroen Mostert
Most disappointingly, the database might be corrupt. A successful backup
does not guarantee a successful restore. Nor, for that matter, do either a
successful backup or a successful restore guarantee no corruption. DBCC
CHECKDB will help here. If you just "put it together", this is not a likely
scenario, but it's always a possibility.

It is not the case as I was able to restore the database locally.

Post by Jeroen Mostert

Post by Gene Wirchenko
My opposite number says that the database is supposed to be unattached
and attached, but he does not know all the ins and outs either.

Backup/restore is the easiest and most reliable method for transferring
databases between servers. Detach/attach makes the database unavailable at
the source, deletes metadata in the master database where some nominally
per-database settings are kept and runs the risk of losing the database if
you're moving the files rather than copying them. It should be considered an
advanced scenario. I certainly wouldn't trust someone who can't tell you
what goes wrong with a restore to handle detach/attach correctly.

Neither I nor my opposite number are terribly knowledgeable on
the details of SQL Server, yet. I am not going to rag on him. We are
looking for the answers.

Post by Jeroen Mostert

Post by Gene Wirchenko
How can I simply create a database and send it elsewhere, or
receive one from elsewhere and then have it get installed on the
destination?
Scripts are not acceptable in this case as the databases will
eventually be operational databases, and we need to cover this. For
debugging purposes, I may need to receive and install a full database.

Get them to tell you the error. You wouldn't visit the doctor and tell him
"something's just wrong", would you?

Yes, he dropped the ball there.

Post by Jeroen Mostert
For debugging purposes, you would always want backup/restore and not
detach/attach anyway, because detaching incurs downtime.

Well, he got detach/attach from some research (presumably the
Web), but yes, I know how the Web is not as accurate as it might be
nor are reasons why always given.

I will be taking another look at things and try to figure it out
better.

Thank you for your time.

Sincerely,

Gene Wirchenko

[Erland Sommarskog]

Post by Gene Wirchenko
No, it does not. I am the developer. My system has the
development database. The other system is elsewhere and will
eventually be the production system.

The the proper way is to keep code under version control and install
from scripts. Copying a full database is only going to work for V1.0
anyway.

[Gene Wirchenko]

On Wed, 02 May 2012 23:41:10 +0200, Erland Sommarskog

Post by Erland Sommarskog

Post by Gene Wirchenko
No, it does not. I am the developer. My system has the
development database. The other system is elsewhere and will
eventually be the production system.

The the proper way is to keep code under version control and install
from scripts. Copying a full database is only going to work for V1.0
anyway.

When I change table structures, I use scripting, and I am fine
with that then.

I want to be able to slurp the whole database so that I will be
able to get a copy of the production data for debugging use. On the
current system, when there are problems, my boss may send a copy of
the database with instructions to look at certain rows. This makes it
much easier to replicate trouble, and is not very difficult for him to
do.

So what is the best way of doing this? I am getting conflicting
data.

Sincerely,

Gene Wirchenko

[Erland Sommarskog]

Post by Gene Wirchenko
I want to be able to slurp the whole database so that I will be
able to get a copy of the production data for debugging use. On the
current system, when there are problems, my boss may send a copy of
the database with instructions to look at certain rows. This makes it
much easier to replicate trouble, and is not very difficult for him to
do.
So what is the best way of doing this? I am getting conflicting
data.

BACKUP DATABASE db TO DISK = '<somepath>' WITH INIT

RESTORE DATABASE db FROM DISK '<somepath>' WITH
MOVE '<name1>' TO '<path.mdf>',
MOVE '<name2>' TO '<path.ldf>',
REPLACE

You find name1 and name2 by running sp_helpdb on the source database. You
can also use one of RESTORE HEADERONLY or RESTORE FILELISTONLY. (I never
remember which is which.) These are logical names for the database files.