XPost: microsoft.public.sqlserver.programming   
   From: jmostert@xs4all.nl   
      
   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.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)