Sql-server – Export sql azure DB as bacpac file without including data for certain tables

azure-sql-databasebackupdata-tier-applicationsql server

When I export a sql azure database as data tier application from sql server management studio I get the option to tick off certain tables but then these tables are not included in the resulting file.

I want to export these tables schemas, but ignore their data (import empty tables essentially). For all the other tables I also want the data.

Is it possible to do that?

Best Answer

You can create a perfect copy of a table and the whole schema by doing:

SELECT TOP 0 * INTO new_table FROM old_table

If you have a linked server or a database on the same server that is called, let's say, database_copy you can actually iterate all tables and you will end up having the same number of tables, all with the same schema.

Otherwise, if you can mask the data or encrypt them. But in this case they will still be on the database

Related Solutions

Sql-server – Out of Memory Exception When Creating BACPAC File

Unfortunately, you can't use .back failes in Azure SQL Database (not Managed Instance). I'd sugesst that you should consider to use BCP which is export to local csv or binary files and can import there to Azure SQL Database.

Azure SQL Database – Grant Select on Certain Tables

You need to ensure the login is added on all the levels

---- ON SERVER LEVEL
CREATE LOGIN TestUser
    WITH PASSWORD = 'ThisIsAStrongPassword!' 
GO

---- ON Master Level
CREATE USER TestUser
    FOR LOGIN TestUser
    WITH DEFAULT_SCHEMA = dbo


---- ON SQL DB LEVEL
CREATE USER TestUser
    FOR LOGIN TestUser
    WITH DEFAULT_SCHEMA = dbo
GO

GRANT SELECT ON OBJECT::dbo.Sales TO TestUser;
GO

You need to add the login on server level under the master context

then add a user to the master db under master context

and then add the user to the sqldb and grant its rights

EDIT You cannot use the use statement, so you will have to manually switch context in SSMS

Best Answer

Related Solutions

Sql-server – Out of Memory Exception When Creating BACPAC File

Azure SQL Database – Grant Select on Certain Tables

Related Question