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

"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

Users do not show up in SharePoint People Search or People Picker

I had this issue with people picker in a classic mode web application in SharePoint 2013 and this site is in 2010 mode - users in certain sub-domains would not show up in People Picker. I was aware of stsadm commands to fix this and we ran the stsadm command to hook up people picker with another domain some time back. The latest issue was that people picker was not returning users from the root domain and few sub domains. After researching on the internet I found (contrary to my thoughts) that we could use PowerShell and not just stsadm to map People Picker to domains. It is a good idea to first check what domains are added/mapped to the web application using the following commands: $wa = Get-SPWebApplication -Identity http://mywebapp.com #List the Domains $wa.PeoplePickerSettings.SearchActiveDirectoryDomains This will list the domains currently People Picker is looking up for that web application. I used the following script to map our AD forest to People Picker:
Post a Comment
Read more

Report Server has encountered a SharePoint error. ( rsSharePointError)

I was receiving this error on the "Report Server Web Service URL" on our SharePoint farm: Report Server has encountered a SharePoint error. ( rsSharePointError) Access to this Web Site has been blocked. Please contact the administrator to resolve this problem. This site URL is configured via Central Administration > Configure Reporting Services Integration > Reporting Services Integration if Reporting Server feature/Add-in is installed on the farm. After doing some research I found out that this page somehow enumerates through entire site collections before it throws this error. As part of troubleshooting step I checked if the domain user configured for reporting service is has sufficient privileges in the farm and also as local admin of the sql box, but this did not solve the issue. We raised this issue with the vendor and they made us update our SQL Server 2005 to latest SP and CU; still we had the error. The reason was nobody was sure if this er
3 comments
Read more