Skip to main content

Restore SQL Server database from multiple backup files

To restore a SQL server database from multiple files we can use the RESTORE DATABASE T-SQL command.

Before we do the restore process it is good to know the name of the “.mdf” and “.ldf” files in the backup set. To find this out we can run the RESTORE FILELISTONLY command in the following syntax:

RESTORE FILELISTONLY FROM

DISK = 'E:\Db_Backups\FILE1_FULL.BAK',

DISK = 'E:\Db_Backups\FILE2_FULL.BAK',

DISK = 'E:\Db_Backups\FILE3_FULL.BAK'

In the above example I assume the full backup consists of 3 files, “FILE1_FULL.BAK”, “FILE2_FULL.BAK”, and “FILE3_FULL.BAK”. The files in this case files are locate in E drive “Db_Backups” folder.

The above command will output the name of the “.mdf” and “.ldf” files contained in the backup set. Once we get those we can run the actual restore command in the following way:

RESTORE DATABASE My_DB FROM

DISK = 'E:\Db_Backups\FILE1_FULL.BAK',

DISK = 'E:\Db_Backups\FILE2_FULL.BAK',

DISK = 'E:\Db_Backups\FILE3_FULL.BAK'

WITH

MOVE My_DB ' TO 'E:\DATA\ My_DB.mdf',

MOVE My_DB _log' TO 'E:\LOGS\ My_DB _log.LDF',

STATS =1

Using the Move command we move the restored data and log files to a location of our choice and with STATS set as 1 we get a detailed verbose of the tasks happening similar to this:

1 percent processed.

2 percent processed.

3 percent processed.

…

100 percent processed.

Processed 108352 pages for database My_DB ', file My_DB ' on file 1.

Processed 5 pages for database ' My_DB ', file ' My_DB _log' on file 1.

RESTORE DATABASE successfully processed 108357 pages in 11.006 seconds (80.652 MB/sec).

When the database we try to restore does not exist and when the backup set is not a full backup, we receive the following error:

Msg 3118, Level 16, State 1, Line 1

The database " My_DB " does not exist. RESTORE can only create a database when restoring either a full backup or a file backup of the primary file.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

In this case we have to make sure we are using a Full backup to restore.
Labels:

Comments

Post a Comment

Popular posts from this blog

Difference between the architectures of SSPs and SharePoint Service Application

SharePoint 2010 has some new features and components, which help to overcome difficulties such as redundancy and service-sharing that we faced while using SharePoint 2007.  Services in MOSS 2007 were implemented with the help of Shared Service Providers or SSPs. But we cannot use the services of one SSP for another SSP. So each SSP has to have its own set of services and applications, which increases redundant data in the farm.  For example, different managers in the same organization use a SharePoint site to maintain official data associated with an SSP. This SSP is associated with different services, such as Search service, BDC service, and a user profile service. A project manager in this organization requests an exclusive user profile service to maintain project data security. To provide this, we first need to create a separate SSP for the project manager and then a separate service called user profile service - project name. Next, we need to associate the SSP with ...
5 comments
Read more

SharePoint Designer Workflow Failed to Start - Cancelled by System Account

I was working on a List workflow the other day in SharePoint Designer 2010. This list already had a workflow which was working perfectly fine and I needed another manual workflow in it. No matter what I tried the workflow would fail and would log a message in the history list that Workflow failed to start and it was cancelled by System Account . I observed that the same happened with new workflows in that list, new workflows in other lists and I even tried creating new subsites and workflows were behaving the same way. I googled for a while and there were many suggestions to clear SPD cache which did not help me. I played with SPD 2010 for a while and observed that the workflow xml and xoml files were checked out. I read in some blogs that this could cause this issue as well. I tried to check those files in but then I was greeted with a new error! " Cannot perform this operation. The file is no longer checked out or has been deleted ." I had no clue what was happening...
10 comments
Read more

"Cannot impersonate user for data source" - SSRS reports

We were getting this strange error while viewing our reports in SharePoint that was deployed using BIDS 2005: An error has occurred during report processing. Cannot impersonate user for data source 'datasource' Logon failed I tried many things but nothing worked. I did some research and found many blog posts which suggested me to reapply the password on the datasource file specified in the error message and save it again. That didn't work for me. I saw some check boxes in the data source file that read "Use as Windows Credentials" and "Set Execution Context to this report" and I didn't know what they meant. I unchecked those in my data source and then when the refreshed the page the report started displaying.
2 comments
Read more