0

When I run the following code on SQL Server (docker) it makes the db as contained first time around, but in a SQL managed instance it creates it as a "none" contained db, the subsequent ALTER is required to fix that.

if db_id('test11') is not null 
begin
    print 'dropping old db';
    exec('drop database test11;');
end;
GO

print 'making db';
CREATE DATABASE test11 CONTAINMENT = PARTIAL;
GO

IF not EXISTS(SELECT * FROM sys.databases WHERE [name] = 'test11' and containment_desc = 'PARTIAL')
begin
    print 'fixing containment as it didn''t work forst time';
    ALTER DATABASE test11 SET CONTAINMENT = PARTIAL;
end;
GO

SQL server (docker) output

dropping old db
making db

SQL Managed Instance output

dropping old db
making db
fixing containment as it didn't work forst time
Nisar Yaqoob
  • 49
  • 4
  • What is your question here exactly..? – Thom A Apr 05 '23 at 08:23
  • What am I doing wrong on a Managed instance that it doesn't create the db as a contained db when I am telling it to, am I missing a setting somewhere or is this a known issue? – Nisar Yaqoob Apr 05 '23 at 08:40
  • If [the docs](https://learn.microsoft.com/sql/t-sql/statements/create-database-transact-sql?view=azuresqldb-mi-current&preserve-view=true&tabs=sqlpool) are to be believed, this is working as designed and `ALTER` is necessary. The fact that the `CONTAINMENT` clause is ignored on `CREATE DATABASE` rather than resulting in an explicit error may be a misguided attempt to increase compatibility with existing scripts (I say misguided because this is unlikely to be desired behavior when `CONTAINMENT` isn't `NONE`). – Jeroen Mostert Apr 05 '23 at 09:05

0 Answers0