SQLBackup Restore Issue - Error 3156

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:

Answers

  • Eddie DEddie D Posts: 1,497 Rose Gold 3
    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
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
  • 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.


  • 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.
Sign In or Register to comment.