none
How to restore SQL Server 2008 R2 databases on SQL Server 2012

    Question

  • Hi everybody,

    After long installation process I was finally successful in installing SQL Server 2012. So, my first idea was to try installing out databases and testing our application against it. But I'm not sure how exactly should I restore the databases? Can you show me the script? I've tried creating the DB first, then selecting the backup of the database, but at that point SSMS 2012 hang up.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, April 02, 2012 3:39 AM

Answers

  • Thanks. I actually did what Denis Gobo suggested in this thread:

    http://forum.lessthandot.com/viewtopic.php?f=22&t=8401

    So, in SQL 2008 R2 I ran these commands, then my final restore command looked like

    use master 
    Restore database SiriusSQL
    FROM DISK='C:\SQL DB Backups\All DBs\SiriusSQL.bak'
    WITH MOVE N'SiriusSQL_dat' 
    TO N'C:\SQL 2012 Databases\SiriusSQL.mdf',
    MOVE N'SiriusSQL_log' TO N'C:\SQL 2012 Databases\SiriusSQL.ldf',
    MOVE N'SaleTransactions' TO N'C:\SQL 2012 Databases\SiriusSQL_Transact.ndf',
    MOVE N'Logs' TO N'C:\SQL 2012 Databases\SiriusSQL_Logs.ndf'
    Now I need to install SSMS Tools Pack as I am missing FORMAT SQL option.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Naomi N Monday, September 16, 2013 7:12 PM
    Monday, April 02, 2012 6:09 PM
  • from the provided error message in your last posting I assume that the logical file name was effectively named SiriusSQL_Semar_logs etc. and that you're having additional data files.

    therefore you've to list the MOVE statement for each logical file in the backup (SiriusSQL_Seminar_Logs, SiriusSQL_Seminar_Transact etc.)

    to get the list of logical files use the RESTORE HEADERONLY command

    http://msdn.microsoft.com/en-us/library/ms178536.aspx

    RESTORE HEADERONLY FROM DISK='C:\SQL DB Backups\All DBs\SiriusSQL.bak'

    and use the information for listing all the logical files in the real restore command.

    • Marked as answer by Naomi N Monday, April 02, 2012 6:07 PM
    Monday, April 02, 2012 5:28 PM

All replies

  • Backup the database from Source as DBBackup.Bak file.
    On SQL Server 2012

    Restore database Database_Name
    from Disk = 'd:\DBBackup.Bak'

    When you see "hang" did you check if any activity is happening in SQL via sysprocesses or its just the UI?

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    Monday, April 02, 2012 3:46 AM
    Moderator
  • Can you be a bit more specific with the command? Is it exactly what you said here? 

    I haven't checked the processes, but it's the UI which hang. At the end I had to kill SSMS 2012. I was running SSMS 2008 at the same time, could it be a problem?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, April 02, 2012 2:06 PM
  • I dropped the newly created database (because using the above command I was getting an error) and tried the following:

    use master

    Restore database SiriusSQL

    from Disk = 'C:\SQL DB Backups\All DBs\SiriusSQL.bak'

    After which I received these errors:

    Msg 3634, Level 16, State 1, Line 2
    The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Dev\V40\ReservedSeating\Data -  Scripts\SiriusSQL_Seminar.mdf'.
    Msg 3156, Level 16, State 8, Line 2
    File 'SiriusSQL_dat' cannot be restored to 'C:\Dev\V40\ReservedSeating\Data -  Scripts\SiriusSQL_Seminar.mdf'. Use WITH MOVE to identify a valid location for the file.
    Msg 3634, Level 16, State 1, Line 2
    The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Dev\V40\ReservedSeating\Data -  Scripts\SiriusSQL_Seminar_Transact.ndf'.
    Msg 3156, Level 16, State 8, Line 2
    File 'SaleTransactions' cannot be restored to 'C:\Dev\V40\ReservedSeating\Data -  Scripts\SiriusSQL_Seminar_Transact.ndf'. Use WITH MOVE to identify a valid location for the file.
    Msg 3634, Level 16, State 1, Line 2
    The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Dev\V40\ReservedSeating\Data -  Scripts\SiriusSQL_Seminar_Logs.ndf'.

    Msg 3156, Level 16, State 8, Line 2
    File 'Logs' cannot be restored to 'C:\Dev\V40\ReservedSeating\Data -  Scripts\SiriusSQL_Seminar_Logs.ndf'. Use WITH MOVE to identify a valid location for the file.
    Msg 3634, Level 16, State 1, Line 2
    The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Dev\V40\ReservedSeating\Data -  Scripts\SiriusSQL_Seminar.ldf'.
    Msg 3156, Level 16, State 8, Line 2
    File 'SiriusSQL_log' cannot be restored to 'C:\Dev\V40\ReservedSeating\Data -  Scripts\SiriusSQL_Seminar.ldf'. Use WITH MOVE to identify a valid location for the file.
    Msg 3119, Level 16, State 1, Line 2
    Problems were identified while planning for the RESTORE statement. Previous messages provide details.
    Msg 3013, Level 16, State 1, Line 2
    RESTORE DATABASE is terminating abnormally.

    ------------------------------------------

    So, what is the correct way of restoring database I made a compressed backup of in SQL 2008 R2?

    May be I need to try attach/detach way instead?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, April 02, 2012 2:50 PM
  • I've attempted to use WITH MOVE option (as I found a sample in BOL), but it's still failing:

    Msg 3634, Level 16, State 1, Line 2
    The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Dev\V40\ReservedSeating\Data -  Scripts\SiriusSQL_Seminar_Transact.ndf'.
    Msg 3156, Level 16, State 8, Line 2
    File 'SaleTransactions' cannot be restored to 'C:\Dev\V40\ReservedSeating\Data -  Scripts\SiriusSQL_Seminar_Transact.ndf'. Use WITH MOVE to identify a valid location for the file.
    Msg 3634, Level 16, State 1, Line 2
    The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Dev\V40\ReservedSeating\Data -  Scripts\SiriusSQL_Seminar_Logs.ndf'.
    Msg 3156, Level 16, State 8, Line 2
    File 'Logs' cannot be restored to 'C:\Dev\V40\ReservedSeating\Data -  Scripts\SiriusSQL_Seminar_Logs.ndf'. Use WITH MOVE to identify a valid location for the file.
    Msg 3119, Level 16, State 1, Line 2
    Problems were identified while planning for the RESTORE statement. Previous messages provide details.
    Msg 3013, Level 16, State 1, Line 2
    RESTORE DATABASE is terminating abnormally.

    --------------

    The command I tried was:

    use master 
    Restore database SiriusSQL
    from Disk = 'C:\SQL DB Backups\All DBs\SiriusSQL.bak'
    
    WITH MOVE 'SiriusSQL_Dat' TO 'C:\SQL 2012 Databases\SiriusSQL_dat.mdf',
       MOVE 'SiriusSQL_Log' TO 'C:\SQL 2012 Databases\SiriusSQL.ldf';


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, April 02, 2012 3:06 PM
  • from the provided error message in your last posting I assume that the logical file name was effectively named SiriusSQL_Semar_logs etc. and that you're having additional data files.

    therefore you've to list the MOVE statement for each logical file in the backup (SiriusSQL_Seminar_Logs, SiriusSQL_Seminar_Transact etc.)

    to get the list of logical files use the RESTORE HEADERONLY command

    http://msdn.microsoft.com/en-us/library/ms178536.aspx

    RESTORE HEADERONLY FROM DISK='C:\SQL DB Backups\All DBs\SiriusSQL.bak'

    and use the information for listing all the logical files in the real restore command.

    • Marked as answer by Naomi N Monday, April 02, 2012 6:07 PM
    Monday, April 02, 2012 5:28 PM
  • Thanks. I actually did what Denis Gobo suggested in this thread:

    http://forum.lessthandot.com/viewtopic.php?f=22&t=8401

    So, in SQL 2008 R2 I ran these commands, then my final restore command looked like

    use master 
    Restore database SiriusSQL
    FROM DISK='C:\SQL DB Backups\All DBs\SiriusSQL.bak'
    WITH MOVE N'SiriusSQL_dat' 
    TO N'C:\SQL 2012 Databases\SiriusSQL.mdf',
    MOVE N'SiriusSQL_log' TO N'C:\SQL 2012 Databases\SiriusSQL.ldf',
    MOVE N'SaleTransactions' TO N'C:\SQL 2012 Databases\SiriusSQL_Transact.ndf',
    MOVE N'Logs' TO N'C:\SQL 2012 Databases\SiriusSQL_Logs.ndf'
    Now I need to install SSMS Tools Pack as I am missing FORMAT SQL option.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Naomi N Monday, September 16, 2013 7:12 PM
    Monday, April 02, 2012 6:09 PM
  • I had the same problem. The problem occurs if you have specified more than 1 media set/file paths while taking a backup. Follow below mentioned steps to take backup from source database (i.e. SQL Server 2008 R2):

    1) Change the name of the Backup Set while taking the backup.

    2) Remove all of the destinations listed, and add 1 new destination.

    3) Do a full backup.

    Then restore the .bak file on the SQL Server 2012 normally. It should work.

    Good Luck.


    Villpates

    Wednesday, August 01, 2012 9:15 AM
  • I had the same 'Access denied' error issue when I was restoring my .bak files( taken from SQL Server 2008 R2) into SQL Server 2012.

    It got resolved when I changed my SQL Server service account to 'Local System'.

    Thanks,

    Prakash

    Monday, August 27, 2012 7:02 AM