XPost: microsoft.public.sqlserver.programming   
   From: genew@ocis.net   
      
   On Wed, 02 May 2012 20:11:30 +0200, Jeroen Mostert   
    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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)