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

On 2012-05-02 18:06, Gene Wirchenko wrote:
server, ALTER DATABASE SET OFFLINE is the way to go. Otherwise,
backup/restore is usually the way to go.

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.

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.

"something's just wrong", would you?

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

--
J.

On 2012-05-02 20:11, Jeroen Mostert wrote:
Obviously you shouldn't try fancy mathematics if mere words will do: "later
versions won't restore on earlier versions". Disregard the symbolic fraud.

--
J.

On Wed, 02 May 2012 20:11:30 +0200, Jeroen Mostert
<jmostert (AT) xs4all (DOT) nl> wrote:

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.

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.

Both systems are using SQL Server 2008 Express.

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

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.

Yes, he dropped the ball there.

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

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
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.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Wed, 02 May 2012 23:41:10 +0200, Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

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

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
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.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx