Search code examples
sqlsql-serversql-server-2012backupdatabase-restore

Restore a database from multiple .bak files SQL Server 2012

Third party is sending us split backups that we need to restore everyday into single database. Is there a way to automate this which is low maintenance?

Preference is to do this writing either T-SQL or a SQL Server job:

  • AdventureWorks07182018.back0
  • AdventureWorks07182018.back1
  • AdventureWorks07182018.back2
  • AdventureWorks07182018.back3
Solution

  • Too long to comment, but this is usually done in an SQL Agent Job.

    Split backups means 1 of (at least) 4 things which you'll need to get clarification from the vendor, if you don't already know. I excluded partial backups:

    1. The vendor is sending all of the backups listed above in one day, meaning it's a base and 3 differentials or log backups (possible)
    2. The vendor is only sending differential backups since they originally sent you the base (unlikely)
    3. The vendor is backing up filegroups or files on different days (maybe a VLDB) and sending to you as they happen (unlikely scenario)
    4. The vendor is sending you daily full backups, in which the last one you got is the most recent (most likely)

    Excluding file and filegroup restores, you will:

    1. Restore the full backup
    2. Restore the last differential backup, if applicable
    3. Restore any log backups that happened after the last differential backup, or since the last full backup if differentials aren't happening

    You could also choose any differential in step 2, and then all log backups since that differential if it exists. Naturally, if they are only sending you full backups, then you simply need to restore the full backup only.

    Something along these lines... which will vary based on your environment (AlwaysOn, setting to read only, setting to STANDBY, etc...)

    ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE [AdventureWorks] FROM  DISK ='\\UNC\AdventureWorks.back0'
WITH
--what ever options... but likely a file move
MOVE 'data_file_1' TO 'E:\somefolder\data.mdf',
MOVE 'db_log' TO 'E:\somefolder\log.ldf',
REPLACE, --overwrites the database
RECOVERY --sets the DB to READ/WRITE. Use NORECOVERY if you need to restore logs / differentials
GO

--if using logs...
RESTORE LOG AdventureWorks
   FROM '\\UNC\AdventureWorks.back01' --assuming this is a log
   WITH FILE = 1,  --this is the first log
   WITH NORECOVERY;  --keep in norecovery to restore other logs...
GO

    etc...