none
Restore same backup with diferent names at same sql server

    Question

  • Can i Restore same backup with diferent names at same sql server ?

    Luciano Pagliarini

    Friday, February 24, 2012 5:17 PM

Answers

  • Definitively yes. Let say your database name is DB01, you want to restore it as DB02 and your BAK file is C:\DB01.BAK

     restore database DB02 from disk = 'C:\DB01.BAK' with stats, replace        
       ,move 'DB01_data' to 'C:\MSSQLSERVER\DB02.mdf'        
       ,move 'DB01_log' to 'C:\MSSQLSERVER\DB02.ldf' 

    To check for the Logical Names and Physical Names you can use VERIFYONLY

    RESTORE VERIFYONLY FROM disk = 'C:\DB01.BAK'


    Javier Villegas | @javier_villhttp://sql-javier-villegas.blogspot.com/

    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Friday, February 24, 2012 6:20 PM

All replies

  • Definitively yes. Let say your database name is DB01, you want to restore it as DB02 and your BAK file is C:\DB01.BAK

     restore database DB02 from disk = 'C:\DB01.BAK' with stats, replace        
       ,move 'DB01_data' to 'C:\MSSQLSERVER\DB02.mdf'        
       ,move 'DB01_log' to 'C:\MSSQLSERVER\DB02.ldf' 

    To check for the Logical Names and Physical Names you can use VERIFYONLY

    RESTORE VERIFYONLY FROM disk = 'C:\DB01.BAK'


    Javier Villegas | @javier_villhttp://sql-javier-villegas.blogspot.com/

    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Friday, February 24, 2012 6:20 PM
  • Check this.

    Assume "MyDb" is your database and your logical file names are as in script.

    This script creates a backup of "MyDb" database and then restores it as a new database named "MyDb_25Feb".

    USE MyDb
    GO
    BACKUP DATABASE [MyDb] 
    TO  DISK = N'C:\Auto Db Extracted\MyDb_25_02_2012_Sch.bak' WITH NOFORMAT, NOINIT,  
    NAME = N'MyDb-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
    GO
    
    USE [master]
    GO
    --MyDb_25Feb
    RESTORE DATABASE [MyDb_25Feb]
    FROM  DISK = N'C:\Auto Db Extracted\MyDb_25_02_2012_Sch.bak' WITH  FILE = 1,  
    MOVE N'MyDB_Data' TO N'D:\sqldata\MyDb\MyDb_25Feb.mdf', 
    MOVE N'MyDB_Log' TO N'E:\sqllog\MyDb\MyDb_25Feb_1.ldf',  
    --STANDBY = N'D:\sqldata\MyDb\ROLLBACK_UNDO_MyDb_25Feb.BAK',  
    NOUNLOAD,  STATS = 10


    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote helpful topics and Mark answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    Saturday, February 25, 2012 11:49 AM
  • Depending upon your method of restore the way you do (or can do) this will vary slightly. SSMS is easier and will automatically rename your data and log files using the new database name that you have specified for the restore. You may go with these names or provide your own ones if you wish -ensure the paths specified are correct.

    Alternatively you could restore through TSQL and in that instance Javier's instruction is sound apart from the verifyonly part. You should actually use RESTORE FILELISTONLY http://msdn.microsoft.com/en-us/library/ms173778.aspx if all you want to do is see the database file locations in the backup.


    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you

    Sunday, February 26, 2012 1:36 PM