In December the topic of the month for #Tsql2sday was to talk about our learning goals for 2018. One of the goals I put forth was to get better at SQL development. As part of that goal, I also realized I have to get more familiar with Visual Studios. So to help with that, I do a lot more of my script writing in Visual Studio, plus I check in my code (another goal I had).
I happened to notice that Kenneth Fisher (b/t) has a homework series going that challenges DBAs and Devs to get better at certain tasks. In May, the task was to create a db. I didn’t think much of it at the time, I’ve created thousands of dbs from SSMS, but it occurred to me this morning that I haven’t done as code from Visual Studio. So here’s what I came up with.
/* Project Name: Kenneth Fisher's homework challenge 1 Date: 6/27/18 Author: SQLMac Purpose: Create a database in a different manner than SSMS. In this case, as code that is deployed and ran. */ USE master; GO IF DB_ID(N'Fisher_HW') IS NOT NULL DROP DATABASE Fisher_HW; GO CREATE DATABASE Fisher_HW ON PRIMARY (NAME = F_HW1_dat, FILENAME = 'D:\$SQL_Instance\Data\F_HW1_dat.mdf', SIZE = 100mb, MAXSIZE = 500MB, FILEGROWTH = 50mb), FILEGROUP Non_Prime_Write DEFAULT (NAME = F_HW2_dat, FILENAME = 'D:\$SQL_Instance\Data\F_HW2_dat.mdf', SIZE = 100mb, MAXSIZE = 5GB, FILEGROWTH = 100mb), (NAME = F_HW3_dat, FILENAME = 'D:\$SQL_Instance\Data\F_HW3_dat.mdf', SIZE = 100mb, MAXSIZE = 5GB, FILEGROWTH = 100mb), FILEGROUP Non_Prime_Read (NAME = F_HW4_dat, FILENAME = 'D:\$SQL_Instance\Data\F_HW4_dat.mdf', SIZE = 100mb, MAXSIZE = 100MB, FILEGROWTH = 50mb) LOG ON (NAME = F_HW_log, FILENAME = 'D:\$SQL_Instance\Data\F_HW_log.ldf', SIZE = 100mb, MAXSIZE = 1gb, FILEGROWTH = 10mb) COLLATE SQL_Latin1_General_CP1_CS_AS; GO ALTER DATABASE Fisher_HW SET RECOVERY SIMPLE; go ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2; GO ALTER DATABASE [Fisher_HW] MODIFY FILEGROUP [Non_Prime_Read] READONLY; GO ALTER AUTHORIZATION ON DATABASE::[Fisher_HW] TO [sa]; go