Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
After restore of database after logshipping the file name changes

Odpovědět After restore of database after logshipping the file name changes

  • Monday, November 12, 2012 7:55 PM
     
     
    Sign In to Vote
    0

    If I run the following command use [RE7]go sp_helpfile go in the primary server the names are RE7_Sample and RE7_Sample_log and file names are D:Datare7.mdf and D:Datare7_log.ldf. After the restore of log shipping in the secondary server the file name changes to re7_sample.mdf, RE7_Sample_log.ldf and the names are RE7_sample and RE7_Sample_log.

    How can I restore so that the file name and the names remains the same in the primary and secondary server.
     

All Replies

  • Monday, November 12, 2012 9:23 PM
     
     Answered Has Code
    Sign In to Vote
    0

    Hi,

    It sounds like you restored the full backup from prod to the secondary instance without specifying the logical name and filename during the restore.By default the restore will use the name of the database to populate the logical and physical names.

    Try using a restore via the GUI and change the names under the options section or script the restore using the WITH MOVE option.

    -- This is an example only test in your env before use!
RESTORE DATABASE [RE7]
from DISK = 'C:\SQLBackup\RE7.bak'
WITH NORECOVERY,
    MOVE 'RE7_Sample' to 'C:\SQLData\dataare7.mdf',
MOVE 'RE7_Sample_log' to 'C:\SQLLogs\datare7_log.ldf'
GO

    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

     
  • Monday, November 12, 2012 9:44 PM
     
     
    Sign In to Vote
    0
    Well this is during restore of database when the secondary server database is on standby/Readonly mode and we have to restore from transactional log. If the primary server is down, we can restore from the secondary server by applying the transactional log. In such case when do I use the above script.
     
  • Monday, November 12, 2012 10:06 PM
     
     Answered
    Sign In to Vote
    0

    You can only use that to change the filenames when initialising the log shipped secondary copy of the database. Did you simply use the GUI and select to let SSMS initialize the secondary DB when you setup log shipping?

    The option I gave is only good if you are setting up a new log shipping or re-initialising.

    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!