Options

SQLBackup Restore Issue - Error 3156

TomRothTomRoth Posts: 7 Bronze 2
in SQL Backup

Using SQLBackup Pro - 8.4.1.527

Trying to restore a database with one logical name (BPT_CU_Data) on E:\SQLData\BTU_CU.mdb to a new empty restore database named RothRestore that consists of three logical names:
-  RothRestore_Data1  on E:\SQLData\RothRestore.mdf
-  RothRestore_Data2  on F:\SQLData\RothRestore2.ndf
-  RothRestore_Data3  on P:\SQLData\RothRestore3.ndf

Due to the lack on available space on the E drive, I need to spread the data files for the restore database over three drives.

Below are images showing the Database Properties (Files) of the two databases as well as the SQLBackup code that I'm trying to use as well as the results.

Tagged:
· Share on Twitter

Answers

  • Options
    Eddie DEddie D Posts: 1,780 Rose Gold 5
    Hi TomRoth,
    Thank you for your forum post.

    Your syntax for the restore task is incorrect, hence the SQL errors 3156 and 5133.

    The restore process will not allow the user to restore a single mdf and restore it across multiple data files, mdf and ndf files.  You will need to restore to a location with sufficient free disk space and move the database objects and data to the new ndf files.
    See this SQL Sever Central forum post from several years ago:
    https://www.sqlservercentral.com/Forums/Topic581996-357-1.aspx

    Many Thanks
    Eddie 
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
  • Options
    rebecarebeca Posts: 2 New member
    Hello, you can use the Restore command using the MOVE option

    USE [master]

    RESTORE DATABASE [AdventureWorks2008R2]

    FROM DISK = N'C:\SQL Backups\AdventureWorks2008R2.bak'

    WITH FILE = 1,

    MOVE N'AdventureWorks2008R2_Data' TO N'C:\MyNewDataLocation\AdventureWorks2008R2_Data.mdf',

    MOVE N'AdventureWorks2008R2_Log' TO N'C:\MyNewLogLocation\AdventureWorks2008R2_Log.ldf',

    MOVE N'FileStreamDocuments2008R2' TO N'C:\MyNewFileStreamLocation\Documents2008R2',

    NOUNLOAD, REPLACE, STATS = 1

    GO


    I hope this will help you.


    · Share on Twitter
  • Options
    rebecarebeca Posts: 2 New member
    If you still have the same problem, your backup may be corrupted. You can take the help of a third-party tool like SysTools SQL Backup Recovery Software. This application will allow you to repair corrupted SQL backup file and directly export all data to the live SQL Server database.
    · Share on Twitter
  • Options
    adamsmith2309adamsmith2309 Posts: 4 New member
    Hi tom,
    You will have to both repair and backup your My SQL One of the best tools for this is the SQL backup repair tool. It has every feature that you need be it error recovery or data recovery.
    Some benefits of this tool are-
    1. Two Scanning modes are available: Standard & Advanced for the different magnitude of corruption and error.
    2.     Smoothly restore MDF, LDF & NDF Files from SQL Server Backup
    3. This software     enables you to fix issues when your SQL Server backup file is compressed.
    4.     SQL Backup Repair software repairs database objects such as triggers, procedures, views, tables, functions, etc. 
    5.    Compatible with all Windows editions
    6. Compatible with all SQL versions.
    · Share on Twitter
  • Options
    adamsmith2309adamsmith2309 Posts: 4 New member
    Hi tom,
    You will have to both repair and backup your My SQL One of the best tools for this is the SQL backup repair tool. It has every feature that you need be it error recovery or data recovery.
    Some benefits of this tool are-
    1. Two Scanning modes are available: Standard & Advanced for the different magnitude of corruption and error.
    2.     Smoothly restore MDF, LDF & NDF Files from SQL Server Backup
    3. This software     enables you to fix issues when your SQL Server backup file is compressed.
    4.     SQL Backup Repair software repairs database objects such as triggers, procedures, views, tables, functions, etc. 
    5.    Compatible with all Windows editions
    6. Compatible with all SQL versions.
    · Share on Twitter
  • Options
    SmithSeanDBASmithSeanDBA Posts: 1 New member
    I follow Grant Fritchey blog and recently, he reviewed a software : Stellar Repair for MS SQL Server which is able to repair SQL database file. I used it and repaired my file. here is the review: https://www.scarydba.com/2020/09/23/review-stellar-repair-for-sql-server/



    · Share on Twitter
Sign In or Register to comment.