Sync Datafile Size Across Multiple Environments

I wanted to sync datafile sizes of a database for multiple environments.  I used prod as my source database and I wanted to have the other environments have the same size.  This can be useful if someone runs a query and grows the database in one environment and you wanted to not have issues of file growth on the other environments.  Of course, there is always that we just need to RESTORE the database to the other environments to sync the file properties, but I didn't want to do a restore for this scenario.

Here's the script.

SELECT name,
       size*8/1024 as size,
       growth*8/1024 as growth,
       max_size*8/1024 as max_size
FROM sys.master_files
WHERE database_id = DB_ID(DB_NAME())
 
SELECT 'ALTER DATABASE [' + DB_NAME() + '] '
              + 'MODIFY FILE ( '
              + 'NAME = N''' + name + ''', '
              + 'SIZE = ' + CONVERT(VARCHAR,size*8/1024) + 'MB , '
              + 'MAXSIZE = ' + CONVERT(VARCHAR,max_size*8/1024) + 'MB , '
              + 'FILEGROWTH = ' + CONVERT(VARCHAR,growth*8/1024) + 'MB )'
FROM sys.master_files
WHERE database_id = DB_ID(DB_NAME())
 
 
 

NOTE:  I had to convert pages into megabytes, thus *8/1024.

Disable All Users in a Database

Yesterday, I did a point-in-time restore of an Azure SQL Managed Instance database into a new copy of the database.  This copy will be used as backup as we didn't want to have a non-TDE backup laying around.  We don't have a custom managed keys in place yet.

In order to restrict access to the newly created DB, we wanted to disable all users from the database.  Here's the script I used to do that:

SELECT
    'REVOKE CONNECT FROM [' + name + '];',
    name AS username,
    create_date,
    modify_date,
    type_desc AS type,
    authentication_type_desc AS authentication_type
FROM
    sys.database_principals
WHERE
    name NOT IN ('dbo','sys','INFORMATION_SCHEMA','guest')
    AND type_desc NOT IN ('DATABASE_ROLE')
ORDER BY
    username;

 

I hope this helps someone.  Have a great day!