DXNbExx0

1ALTER DATABASE DatabaseTest
2MODIFY FILE (NAME = [SqlFile1], SIZE = 5000MB, FILEGROWTH = 50%);
3GO
4	
5ALTER DATABASE [$(DatabaseName)]
6    MODIFY FILE
7    (
8        NAME = [SqlFile1],
9        SIZE = 5000MB,
10        MAXSIZE = UNLIMITED,
11        FILEGROWTH = 50%
12    )
13	
14I’m working on a migration project and this issue has arisen.
15
16Here is what I did to make deployments as easy as possible.
17Used the following SQL to extrapolate all pertinent information about the databases I’m migrating:
18
19SELECT CAST(@@SERVERNAME AS VARCHAR(128)) AS Instance,
20       CAST(LTRIM(RTRIM(sdb.name)) AS VARCHAR(128)) AS [Database],
21       CAST(LTRIM(RTRIM(mf.name)) AS VARCHAR(128)) AS [Filename],
22       (CAST(max_size AS INT) / 1024) * 8 AS MaxSizeInMB,
23       size * 8 / 1024 AS [InitialSizeInMB],
24       CAST((growth / 1024.00) * 8 AS INT) AS AutoGrowthInMB
25FROM [sys].[master_files] mf
26     JOIN sys.databases sdb ON mf.database_id = sdb.database_id
27ORDER BY mf.[name] ASC;
28
29I inserted this data into a new table called [DB_Migration_Information] on the new instance:
30
31USE [BlahBlah];
32GO
33
34/****** Object:  Table [dbo].[DB_Migration_Information]    Script Date: 05/12/2017 11:38:52 ******/
35
36SET ANSI_NULLS ON;
37GO
38SET QUOTED_IDENTIFIER ON;
39GO
40IF NOT EXISTS
41(
42    SELECT *
43    FROM sysobjects
44    WHERE xtype = 'U'
45          AND name = 'DB_Migration_Information'
46)
47    CREATE TABLE [dbo].[DB_Migration_Information]
48([ID]              [INT] IDENTITY(1, 1) NOT NULL,
49 [Instance]        [VARCHAR](128) NULL,
50 [Database]        [VARCHAR](128) NULL,
51 [Filename]        [VARCHAR](128) NULL,
52 [MaxSizeInMB]     [INT] NULL,
53 [InitialSizeInMB] [INT] NULL,
54 [AutoGrowthInMB]  [INT] NULL
55)
56    ON [PRIMARY];
57GO
58I then built a post deployment script in VS that builds some dynamic sql based upon the values in [DB_Migration_Information].  The dynamic sql in then passed through a cursor which executes the dynamic sql:
59
60-- =============================================
61-- Author:      <Danny McGreevy>
62-- Create date: <05-12-2017>
63-- Description: To allow a The post deployment script to set the correct filesize for a deployed database. 
64--                  The @Debug Parameters allows the developer to run the package without activating anything but will show all the relevant information regarding the execution. 1 = Debug, 0 = Run Normally.
65--                  The prerequisite of this process are:
66--              [dbo].[DB_Migration_Information] exists on [BlahBlah]
67--              @DATABASENAME value exists in [dbo].[DB_Migration_Information]
68-- =============================================
69
70DECLARE @DATABASENAME VARCHAR(50)= 'Migrated Database Name'; --This is the only input required from the user
71DECLARE @ALTERDATABASESCRIPT VARCHAR(MAX);
72DECLARE @ALTERDATABASECURSOR AS CURSOR;
73DECLARE @ALTERDATABASE TABLE([SQL] VARCHAR(MAX));
74DECLARE @VALIDATIONCHECKS CHAR(1);
75DECLARE @VALIDATIONCOUNT INT;
76DECLARE @DEBUG BIT= 0;
77
78--Start Validation Routine
79BEGIN
80    IF(EXISTS
81(
82    SELECT *
83    FROM [BlahBlah].INFORMATION_SCHEMA.TABLES
84    WHERE TABLE_NAME = 'DB_Migration_Information'
85))
86        SET @VALIDATIONCHECKS = 'Y';
87        ELSE
88    SET @VALIDATIONCHECKS = 'N';
89END;
90IF @VALIDATIONCHECKS = 'Y'
91    BEGIN
92        SELECT @VALIDATIONCOUNT = COUNT(*)
93        FROM [BlahBlah].[dbo].[DB_Migration_Information]
94        WHERE [DATABASE] = @DATABASENAME;
95        IF @VALIDATIONCOUNT > 0
96            SET @VALIDATIONCHECKS = 'Y';
97            ELSE
98        IF @VALIDATIONCOUNT = 0
99            SET @VALIDATIONCHECKS = 'N';
100    END;
101    ELSE
102RETURN;
103--End Validation Routine
104
105--Start Main Script
106--Start builing of dynamic sql and insert into table variable @ALTERDATABASE
107IF @VALIDATIONCHECKS = 'Y'
108    BEGIN
109        INSERT INTO @ALTERDATABASE([SQL])
110               SELECT '
111BEGIN TRY 
112
113    ALTER DATABASE ['+[Database]+']
114    MODIFY FILE
115    (
116        NAME = ['+[FileName]+'],
117        SIZE = '+CAST([InitialSizeInMB] AS VARCHAR(20))+'MB,
118        MAXSIZE ='+CASE
119                       WHEN [MaxSizeInMB] = 0
120                       THEN 'UNLIMITED'
121                       ELSE CAST([MaxSizeInMB] AS VARCHAR(20))
122                   END+'        
123    )
124
125END TRY
126
127BEGIN CATCH
128 IF @@ERROR =  5039
129   PRINT N''File modification did not work. Specified size is less than or equal to current size'';  
130END CATCH
131
132    '
133               FROM [BlahBlah].[dbo].[DB_Migration_Information]
134               WHERE [DATABASE] = @DATABASENAME;
135--End builing of dynamic sql and insert into table variable @ALTERDATABASE
136
137--Start Cursor
138        SET @ALTERDATABASECURSOR = CURSOR
139        FOR SELECT [SQL]
140            FROM @ALTERDATABASE;
141
142        OPEN @ALTERDATABASECURSOR;
143
144        FETCH NEXT FROM @ALTERDATABASECURSOR INTO @ALTERDATABASESCRIPT;
145
146        WHILE @@FETCH_STATUS = 0
147            BEGIN
148                IF @DEBUG = 0
149                    EXEC (@ALTERDATABASESCRIPT);
150                    ELSE
151                IF @DEBUG != 0
152                    PRINT @ALTERDATABASESCRIPT;
153                FETCH NEXT FROM @ALTERDATABASECURSOR INTO @ALTERDATABASESCRIPT;
154            END;
155
156        CLOSE @ALTERDATABASECURSOR;
157
158        DEALLOCATE @ALTERDATABASECURSOR;
159--End Cursor
160    END;
161    ELSE PRINT 'The script failded validation checks.  Please check [BlahBlah].[dbo].[DB_Migration_Information]'
162--End Main Script
163
164
165
166Hope this helps someone.