Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1008 > unrolled thread

Moving a Database

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  Moving a Database Gene Wirchenko <genew@ocis.net> - 2012-05-02 09:06 -0700
    Re: Moving a Database Jeroen Mostert <jmostert@xs4all.nl> - 2012-05-02 20:11 +0200
      Re: Moving a Database Jeroen Mostert <jmostert@xs4all.nl> - 2012-05-02 20:14 +0200
      Re: Moving a Database Gene Wirchenko <genew@ocis.net> - 2012-05-02 12:41 -0700
        Re: Moving a Database Erland Sommarskog <esquel@sommarskog.se> - 2012-05-02 23:41 +0200
          Re: Moving a Database Gene Wirchenko <genew@ocis.net> - 2012-05-02 20:10 -0700
            Re: Moving a Database Erland Sommarskog <esquel@sommarskog.se> - 2012-05-03 07:21 +0000
        Re: Moving a Database Mark D Powell <Mark.Powell2@hp.com> - 2012-05-03 07:29 -0700

#1008 — Moving a Database

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

[toc] | [next] | [standalone]

#1009

On 2012-05-02 18:06, Gene Wirchenko wrote:
>       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.

>       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.

> 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.

>       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.

-- 
J.

[toc] | [prev] | [next] | [standalone]

#1010

On 2012-05-02 20:11, Jeroen Mostert wrote:
> 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.

-- 
J.

[toc] | [prev] | [next] | [standalone]

#1011

On Wed, 02 May 2012 20:11:30 +0200, Jeroen Mostert
<jmostert@xs4all.nl> wrote:

>On 2012-05-02 18:06, Gene Wirchenko wrote:
>>       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.

>>       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.

>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.

>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.

>> 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.

>>       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.

>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

[toc] | [prev] | [next] | [standalone]

#1012

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

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.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

[toc] | [prev] | [next] | [standalone]

#1013

On Wed, 02 May 2012 23:41:10 +0200, Erland Sommarskog
<esquel@sommarskog.se> wrote:

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

[toc] | [prev] | [next] | [standalone]

#1014

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


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[toc] | [prev] | [next] | [standalone]

#1015

On Wednesday, May 2, 2012 3:41:13 PM UTC-4, Gene Wirchenko wrote:
> On Wed, 02 May 2012 20:11:30 +0200, Jeroen Mostert
> <jmostert@xs4all.nl> wrote:
> 
> >On 2012-05-02 18:06, Gene Wirchenko wrote:
> >>       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.
> 
> >>       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.
> 
> >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.
> 
> >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.
> 
> >> 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.
> 
> >>       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.
> 
> >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

Gene, most SQL Server DBA's seem to do everyting via SSMS so it seems you opposite number probably failied to override the databvase dataset names when he or she peformed the restore since he or she was able to get it to work using a move.

To move or permanently relocate a database from one physical server to another on the same or a higher release of SQL Server I believe that MS recommends you use detach and attach.  This is generally faster than using backup then restore as you save the time to make the backup and to perform the restore operation since an attach just updates the dictionary for the new database.

I use both methods depending on the size of the source database, if I can afford to make the source unavailable, if this is the first time we have done the operation etc ....

HTH -- Mark D Powell --

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web