Results 1 to 6 of 6

Thread: copy database

  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: copy database

    I wanted copy a database from one server to another server.So far I'm doing this is by detaching and attaching the DB.But when i tried to restore the DB on the new server its giving some error. Is't a good process to detach and attach.PLease let me know if there is any better way of doing this.

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    BACKUP DATABASE database_name
    TO DISK = '\\server\share\folder\whatever.bak'

    Copy file

    RESTORE DATABASE database_name
    FROM DISK = '\\server\share\folder\whatever.bak'
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Apr 2003
    Posts
    176
    Thanks for ur information.So, is't a bad practice to attach and detach the DB ....

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by bruce_Reid
    Thanks for ur information.So, is't a bad practice to attach and detach the DB ....

    No, it's ok...and fatser....

    But the destination db must already exists, and already be detached....

    The restore will create one where one doesn't exist
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2003
    Posts
    176
    Even I have tried to do the same with copy database wizard but it was generating some problems.So i was doing this with attach and detach.However my aim is to copy this database to another server and to have the database available on both the servers.

    Thanks.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah...I have a scheduled job that does that every night...it executes this sproc

    Code:
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Restore_Production]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[usp_Restore_Production]
    GO
    
    SET QUOTED_IDENTIFIER ON 
    GO
    SET ANSI_NULLS ON 
    GO
    
    
    
    Create Proc usp_Restore_Production as
    
    
    ALTER DATABASE TaxReconDB_Prod SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    
    RESTORE DATABASE TaxReconDB_Prod
       FROM DISK = 'D:\Tax\BackUp\TaxReconDB.dmp'
       WITH   MOVE 'TaxReconDB_Data' TO 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Data\TaxReconDB_Prod_Data.MDF' 
            , MOVE 'TaxReconDB_Log'  TO 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Data\TaxReconDB_Prod_Log.LDF'
    	, REPLACE
    
    
    ALTER DATABASE TaxReconDB_Prod SET READ_WRITE
    
    
    GO
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •