We help IT Professionals succeed at work.

Backup and Restore a Specific Schema in a DB

High Priority
63 Views
Last Modified: 2020-03-26
Hello All,

So we have a database where each client have their own schema.  I need to implement a backup and restore process.  So far I've been able to backup a specific schema but I'm getting errors when I try to restore that backup.

Here are the steps I've done so far:

Step 1 Create schema:
ALTER DATABASE [TestSchemaRestore] ADD FILE ( NAME = N'TestSchemaRestoreHost_Data',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\TestSchemaRestoreHost_Data.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
TO FILEGROUP [HOST_FILEGROUP]
GO
ALTER DATABASE [TestSchemaRestore] ADD FILE ( NAME = N'TestSchemaRestoreTenantOne_Data',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\TestSchemaRestoreTenantOne_Data.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
TO FILEGROUP [TENANTONE_FILEGROUP]
GO

--sp_help EventHistory

USE [TestSchemaRestore]
GO
/****** Object:  Table [host].[EventHistory]    Script Date: 1/13/2020 1:18:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [host].[EventHistory](
      [EventHistoryID] [int] IDENTITY(1,1) NOT NULL,
      [WhenOccurred] [datetime] NULL,
      [Category] [nvarchar](50) NULL,
      [Description] [nvarchar](200) NULL,
      [UserID] [int] NULL,
      [Value1] [int] NULL,
      [Value2] [nvarchar](max) NULL,
      [Value3] [datetime] NULL,
      [Value4] [bit] NULL,
      [Value5] [decimal](18, 2) NULL,
      [SubTenantID] [int] NULL,
      [TenantID] [int] NULL,
      [ExecutionDuration] [time](7) NULL,
      [IpAddress] [varchar](20) NULL,
      [UserAgent] [varchar](max) NULL,
 CONSTRAINT [PK_EventHistory] PRIMARY KEY CLUSTERED
(
      [EventHistoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [HOST_FILEGROUP]
) ON [HOST_FILEGROUP] TEXTIMAGE_ON [HOST_FILEGROUP]
GO




Step 2 Create Backup:
Right click on database, task, backup
Select Files and filegroups and selected the host filegroup
Gave it a name xxx.bak

Step 3 Attempt restore:
Right click on database, task, restore, files and filegroups
Selected From Device and selected the host backup file
Hit ok and get the following message:

File 'TestSchemaRestore' was not backed up in file 1 on device 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Backup\HostSchema.bak'. The file cannot be restored from this backup set.
RESTORE DATABASE is terminating abnormally. (.Net SqlClient Data Provider)

Is this because of the version of sql server I'm useing?  version 17.9.1 \ Sqlexpress
Or am I completely doing this wrong?

Thank you for your time and effort!
Comment
Watch Question

David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
Hi,

that version number looks wrong - it appears to be the SSMS version number.

Your suggestion that SQL Express might be a limiting factor is probably right. I did note the statement that all editions of SQL 2019 support offline file restore. (https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/file-restores-simple-recovery-model?view=sql-server-ver15)

Can I suggest attempting to script out the backup and restore sequences and then we have something a little more solid to go on, rather than using the GUI?

Many thanks
  David
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Can you post images of the backup screens.
If you backup, database, to disk

Not quite sure where you select f.......

Are you trying to restore where the DB already exists?
CERTIFIED EXPERT

Commented:
Backup database ( not file)
Restore a copy of db under different name
Drop objects in the original db
Copy objects from the copy of db
Drop the copy of db
——
I recommend to use separate db for users (not schema)
Backup can backup all existing databases and restore required.
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
A restore where the database already exist, under the options, you gave to allow it to override.
CERTIFIED EXPERT
Commented:
Override restore everything.
If you want to restore just one schema and left other schemas unchanged then:
1. Restore copy_db
2. Drop objects from the schema.
3. Copy the schema from copy_db to db.
4. Drop copy_db.

Problems could rise if there are cross references between schemas. Separate db for a specific user is more flexible.

Author

Commented:
Hello All,
Sorry for the delayed response.

So here goes:
David Todd,
Here is the restore scripted out:
RESTORE DATABASE [TestSchemaRestore] FILE = N'TestSchemaRestore',  FILE = N'TestSchemaRestoreHost_Data',  FILE = N'TestSchemaRestoreTenantOne_Data' FROM  
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Backup\HostSchema.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

Arnold,
Yes the database already exists.  Screen shots attached.

Andrei,
I agree, each client should be their own database.  I have no idea why it was decided to design the db this way(before I joined) and I've been unsuccessful in convincing the decision makers that this complicates things unnecessarily.

Doing the backup of the db and copying over just the needed schema is an option.  I'm just concerned that over time the db will get quite large and this will slow down the restoring process if I have to restore the copy and do all required steps.
Step1.png
Step2.PNG
ErrorMessage.PNG

Author

Commented:
Also, thank you everyone for all your help!