Friday, February 10, 2012

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 wrote for the database backups as how can we perform full database backup completely and split in to multiple files to reduce IO and time. As we have performed full database backups into split multiples 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 below script , it will restore the database from complte 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'


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'


#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 shown as single backup set.


Hope this help you.


Thank you for visiting this post. Please get updated with more posts at http://beyondrelational.com/modules/2/blogs/88/sqlideas.aspx .

3 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

Related Posts Plugin for WordPress, Blogger...