Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Copy Database Script

    Anybody got a good copy database script?

    Why don't we have a script library
    George
    Home | Blog

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    How you mean? Just the DDL? Or a backup and restore?

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    backup and restore to a new database.

    I need to take "dblive" and make "dbtemp"
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    BACKUP DATABASE dblive
        TO DISK = 'D:\back\dblive_20080514_100000.bak'
    Code:
    --Database to restore
    RESTORE DATABASE dbtemp--NOTE - we can enter whatever name we like for the restored db name.
    --From file location
        FROM DISK = 'D:\back\dblive_20080514_100000.bak'
    --This is the only file so rollback any incomplete transactions
        WITH RECOVERY, 
    --Move dat file to...
        MOVE 'OLDLogicalDataName_Data' TO 
    'E:\SQLData\dblive_Data.MDF', 
    --move log file to....
        MOVE 'OLDLogicalLogName_Log' 
    TO 'F:\SQLLogs\dblive_Log.LDF',
    --Get a message every 10% of the process completed.
        STATS = 10
    GO
    I'll forgive you for not looking in BoL this one time.



    Actually - I won't: -1 point. I am tough...but I am fair

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Forgot - you can tidy up the logical names too.
    Code:
    ALTER DATABASE dbtemp
    MODIFY FILE
    (NAME = OLDLogicalDataName_Data, NEWNAME='NEWLogicalDataName_Data')
    GO
    
    ALTER DATABASE dbtemp
    MODIFY FILE
    (NAME = OLDLogicalLogName_Log, NEWNAME='NEWLogicalLogName_Log')
    GO
    Last edited by pootle flump; 05-14-08 at 06:34. Reason: Correctification of db names

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Thank-yoo+
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Thank-yoo+
    +.5 points. I'm just such a sucker....

Posting Permissions

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