Skip to main content

Restore Full Database from multiple backup files - SQL Server

As I know database restoration is a best practice on periodically to check and verify database backup copies and issues while restoration due to any stuffs. I have written for the database backups as how can we perform a full database backup completely and split into multiple files to reduce IO and time. As we have performed full database backups into split multiple files, Same way we will perform restoration from those complete and split multiple files. First we will see to restore full database complete backup and tried for from multiple files.

#1. Using TSQL : Let us run the below script first and get the logical names for database data and log files
RESTORE FILELISTONLY FROM 
DISK =  'D:\DBBackups\ReportServer\ReportServer.bak'
GO

After collection and putting logical file names in script below, it will restore the database from complete backup.
RESTORE DATABASE ReportServerComplteCopy FROM 
DISK = 'D:\DBBackups\ReportServer\ReportServer.bak'
WITH REPLACE ,
MOVE 'ReportServer' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER11\MSSQL\DATA\ReportServerComplteCopy.mdf',
MOVE 'ReportServer_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER11\MSSQL\DATA\ReportServerComplteCopy_log.ldf'
GO


As above, we can restore database from multiple files as following,
RESTORE DATABASE ReportServerSplitCopy FROM 
 DISK = 'D:\DBBackups\ReportServer\ReportServer_Split1.bak'
,DISK = 'D:\DBBackups\ReportServer\ReportServer_Split2.bak'
,DISK = 'D:\DBBackups\ReportServer\ReportServer_Split3.bak'
WITH REPLACE ,
MOVE 'ReportServer' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER11\MSSQL\DATA\ReportServerSplitCopy.mdf',
MOVE 'ReportServer_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER11\MSSQL\DATA\ReportServerSplitCopy_log.ldf'
GO


#2. From Management Studio : We can also perform restoration from SSMS by adding those multiple backups as per shot.


You can see over here after adding those files it will show as a single backup set.


Hope this help you.

Comments

  1. sql recovery damaged by malware, errors in transferring via Internet, incorrect user actions, hard drive failures. Utility restores system/user tables, stored procedures, functions, etc. data. Software launches under all popular version of Windows OS.

    ReplyDelete
  2. Hello friends,

    SQL server is a relational database management system from Microsoft that's designed for the enterprise environment. It runs on T-SQL, a set of programming extensions from Sybase and Microsoft that add several features to standard SQL. Thanks a lot...

    ReplyDelete
  3. its very helpful information....nice work by author...
    refer my site...
    http://skyofkp.blogspot.in/

    ReplyDelete
  4. Thanks for sharing the recovery guide. I would like also to suggest you a third party utility, with the help of this software we can recover your data in few minutes. Website offering free version also. visit the urls to get more information.

    http://www.recoverydeletedfiles.com/sql-database-recovery-software.html


    Thanks

    ReplyDelete
  5. Above described method is helpful to restore full database from multiple backup files without having any trouble. U can check another method here to restore multiple sql server bak files.
    restoresqlbakfiles.blog.fc2.com/‎

    ReplyDelete
  6. 2 percent processed.
    4 percent processed.
    6 percent processed.
    8 percent processed.
    Msg 3634, Level 16, State 1, Line 1

    The operating system returned the error '32(The process cannot access the file because it is being used by another
    while attempting 'OpenFile' on 'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TEST_ONLINE_2_new.filestreamdata\16af32a0-74f5-4513-a8b2-27301072939d\c2ed4313-2da4-420d-8dcf-a8c5b02da324\00008ecd-0000d988-000a'.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.


    Regards,
    www.dineshvishe.wordpress.com

    ReplyDelete
  7. Nice, I prefer the second method. However, TQSL is also ok.

    ReplyDelete

Post a Comment