Backup SQL Schema Only

how to take sql database backup without data

Use "tasks" -> "Generate scripts" and choose what you want to script. Run, save to a file, open the file against the new database and run the script after changing the database name to match (if it changed)

Backup SQL Schema Only?

Use a 3 step process:

1. Generate a script from the working database
2. Create a new database from that script
3. Create a backup of the new database

Best way to backup specific schema?

A couple of ideas.

Using File Groups

Put the tables each tenant has into their own file group. SQL Server has the ability to backup and restore individual file groups. You can also perform some other operations such as taking indivudual tenants offline if required. For example:

CREATE TABLE tenant1.Table1 
(Column1 INT, Column2, INT)
ON Tenant1FileGroup

Views & Separate Databases

Probably not the right way to go, but it will work. Have the tables for each tenant in their own database and reference them from the 'master' database with a view in the tenant schema. For example:

Tenant1DB
    dbo.Table1
    dbo.Table2

Tenant2DB
    dbo.Table1
    dbo.Table2

MasterDB
    tenant1.Table1 
    tenant1.Table2 
    tenant2.Table1 
    tenant2.Table2 

Where the objects mentioned above in the MasterDB database are views such as:

CREATE VIEW tenant1.Table1
AS
SELECT * FROM Tenant1DB.dbo.Table1

This way you can easily backup/restore individual tenant databases. Some other benefits of this strategy:

• Individual tenants can be restored without bringing the main database into single user mode.
• The system will scale out well as the tenant database can be moved to other servers.

Recreate Database Schema-Only

One way is to use DBCC CLONEDATABASE(structures + statistics):

1. DBCC CLONEDATABASE('original_db_name', 'cloned_db')
2. ALTER DATABASE [cloned_db] SET READ_WRITE WITH NO_WAIT
3. Backup
4. Restore on second server
5. ALTER DATABASE [cloned_db] MODIFY NAME = original_db_name;

How do I do a schema only backup and restore in PostgreSQL?

pg_dump --schema=masters oldDB > masters1.sql
cat masters1.sql | psql newDB

or

in single command you can do by this

pg_dump oldDB --schema masters  | psql -h localhost newDB;

Database: backup few tables with data+schema and other tables with only schema

backup few tables with data+schema and other tables with only schema

For such a scenario, a regular SQL Server Backup functionality will not work at all, because there is no way to split data and structure and no way to avoid backup of some tables.

Even if you will perform a filegroup backup, it does not mean that you can restore only that filegroup and leave other tables as is. The filegroup backups just do not work this way.

Therefore, scripting can be one of the solutions:

• 1. Create a script of data and structure for smaller tables
• 
  
  2. Create a script of the structure only of transactional tables

---

Another approach is a dump necessary data and structure into a separate database with a further backup:

Something like:

SELECT * INTO ExportDB.dbo.Table1
SELECT * INTO ExportDB.dbo.Table2
SELECT * INTO ExportDB.dbo.Table3
--- two tables below will  have no data, only a structure
SELECT * INTO ExportDB.dbo.Table4 WHERE 1=0 -- A large transactional table 1
SELECT * INTO ExportDB.dbo.Table5 WHERE 1=0 -- A large Transactional table 1

BACKUP DATABASE ExportDB TO DISK='..'
DROP DATABASE ExportDB

However, native backups (that are not an option for your scenario) can ensure data consistency, enforced by PK and FK, while custom options I mentioned after, cannot really guarantee it

References:
How can I take backup of particular tables in SQL Server 2008 using T-SQL Script

---

Related Topics