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
After collection and putting logical file names in script below, it will restore the database from complete backup.
As above, we can restore database from multiple files as following,
#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.
#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.
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.
ReplyDeleteHello friends,
ReplyDeleteSQL 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...
its very helpful information....nice work by author...
ReplyDeleterefer my site...
http://skyofkp.blogspot.in/
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.
ReplyDeletehttp://www.recoverydeletedfiles.com/sql-database-recovery-software.html
Thanks
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.
ReplyDeleterestoresqlbakfiles.blog.fc2.com/
2 percent processed.
ReplyDelete4 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
Thanks so much
ReplyDeleteNice, I prefer the second method. However, TQSL is also ok.
ReplyDelete