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

Groups > comp.databases.ms-sqlserver > #1015

Re: Moving a Database

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

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

csiph-web