· 6 years ago · Dec 17, 2019, 03:44 AM
1/*
2Deployment script for PAISA_DB
3
4This code was generated by a tool.
5Changes to this file may cause incorrect behavior and will be lost if
6the code is regenerated.
7*/
8
9GO
10SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
11
12SET NUMERIC_ROUNDABORT OFF;
13
14
15GO
16:setvar DatabaseName "PAISA_DB"
17:setvar DefaultFilePrefix "PAISA_DB"
18:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\"
19:setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\"
20
21GO
22:on error exit
23GO
24/*
25Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
26To re-enable the script after enabling SQLCMD mode, execute the following:
27SET NOEXEC OFF;
28*/
29:setvar __IsSqlCmdEnabled "True"
30GO
31IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
32 BEGIN
33 PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
34 SET NOEXEC ON;
35 END
36
37
38GO
39USE [master];
40
41
42GO
43
44IF (DB_ID(N'$(DatabaseName)') IS NOT NULL)
45BEGIN
46 ALTER DATABASE [$(DatabaseName)]
47 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
48 DROP DATABASE [$(DatabaseName)];
49END
50
51GO
52PRINT N'Creating $(DatabaseName)...'
53GO
54CREATE DATABASE [$(DatabaseName)]
55 ON
56 PRIMARY(NAME = [$(DatabaseName)], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_Primary.mdf')
57 LOG ON (NAME = [$(DatabaseName)_log], FILENAME = N'$(DefaultLogPath)$(DefaultFilePrefix)_Primary.ldf') COLLATE SQL_Latin1_General_CP1_CI_AS
58GO
59IF EXISTS (SELECT 1
60 FROM [master].[dbo].[sysdatabases]
61 WHERE [name] = N'$(DatabaseName)')
62 BEGIN
63 ALTER DATABASE [$(DatabaseName)]
64 SET AUTO_CLOSE OFF
65 WITH ROLLBACK IMMEDIATE;
66 END
67
68
69GO
70USE [$(DatabaseName)];
71
72
73GO
74IF EXISTS (SELECT 1
75 FROM [master].[dbo].[sysdatabases]
76 WHERE [name] = N'$(DatabaseName)')
77 BEGIN
78 ALTER DATABASE [$(DatabaseName)]
79 SET ANSI_NULLS ON,
80 ANSI_PADDING ON,
81 ANSI_WARNINGS ON,
82 ARITHABORT ON,
83 CONCAT_NULL_YIELDS_NULL ON,
84 NUMERIC_ROUNDABORT OFF,
85 QUOTED_IDENTIFIER ON,
86 ANSI_NULL_DEFAULT ON,
87 CURSOR_DEFAULT LOCAL,
88 RECOVERY FULL,
89 CURSOR_CLOSE_ON_COMMIT OFF,
90 AUTO_CREATE_STATISTICS ON,
91 AUTO_SHRINK OFF,
92 AUTO_UPDATE_STATISTICS ON,
93 RECURSIVE_TRIGGERS OFF
94 WITH ROLLBACK IMMEDIATE;
95 END
96
97
98GO
99IF EXISTS (SELECT 1
100 FROM [master].[dbo].[sysdatabases]
101 WHERE [name] = N'$(DatabaseName)')
102 BEGIN
103 ALTER DATABASE [$(DatabaseName)]
104 SET ALLOW_SNAPSHOT_ISOLATION OFF;
105 END
106
107
108GO
109IF EXISTS (SELECT 1
110 FROM [master].[dbo].[sysdatabases]
111 WHERE [name] = N'$(DatabaseName)')
112 BEGIN
113 ALTER DATABASE [$(DatabaseName)]
114 SET READ_COMMITTED_SNAPSHOT OFF
115 WITH ROLLBACK IMMEDIATE;
116 END
117
118
119GO
120IF EXISTS (SELECT 1
121 FROM [master].[dbo].[sysdatabases]
122 WHERE [name] = N'$(DatabaseName)')
123 BEGIN
124 ALTER DATABASE [$(DatabaseName)]
125 SET AUTO_UPDATE_STATISTICS_ASYNC OFF,
126 PAGE_VERIFY NONE,
127 DATE_CORRELATION_OPTIMIZATION OFF,
128 DISABLE_BROKER,
129 PARAMETERIZATION SIMPLE,
130 SUPPLEMENTAL_LOGGING OFF
131 WITH ROLLBACK IMMEDIATE;
132 END
133
134
135GO
136IF IS_SRVROLEMEMBER(N'sysadmin') = 1
137 BEGIN
138 IF EXISTS (SELECT 1
139 FROM [master].[dbo].[sysdatabases]
140 WHERE [name] = N'$(DatabaseName)')
141 BEGIN
142 EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
143 SET TRUSTWORTHY OFF,
144 DB_CHAINING OFF
145 WITH ROLLBACK IMMEDIATE';
146 END
147 END
148ELSE
149 BEGIN
150 PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
151 END
152
153
154GO
155IF IS_SRVROLEMEMBER(N'sysadmin') = 1
156 BEGIN
157 IF EXISTS (SELECT 1
158 FROM [master].[dbo].[sysdatabases]
159 WHERE [name] = N'$(DatabaseName)')
160 BEGIN
161 EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
162 SET HONOR_BROKER_PRIORITY OFF
163 WITH ROLLBACK IMMEDIATE';
164 END
165 END
166ELSE
167 BEGIN
168 PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
169 END
170
171
172GO
173ALTER DATABASE [$(DatabaseName)]
174 SET TARGET_RECOVERY_TIME = 0 SECONDS
175 WITH ROLLBACK IMMEDIATE;
176
177
178GO
179IF EXISTS (SELECT 1
180 FROM [master].[dbo].[sysdatabases]
181 WHERE [name] = N'$(DatabaseName)')
182 BEGIN
183 ALTER DATABASE [$(DatabaseName)]
184 SET FILESTREAM(NON_TRANSACTED_ACCESS = OFF),
185 CONTAINMENT = NONE
186 WITH ROLLBACK IMMEDIATE;
187 END
188
189
190GO
191IF EXISTS (SELECT 1
192 FROM [master].[dbo].[sysdatabases]
193 WHERE [name] = N'$(DatabaseName)')
194 BEGIN
195 ALTER DATABASE [$(DatabaseName)]
196 SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF),
197 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = OFF,
198 DELAYED_DURABILITY = DISABLED
199 WITH ROLLBACK IMMEDIATE;
200 END
201
202
203GO
204IF EXISTS (SELECT 1
205 FROM [master].[dbo].[sysdatabases]
206 WHERE [name] = N'$(DatabaseName)')
207 BEGIN
208 ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
209 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
210 ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
211 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
212 ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
213 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
214 ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
215 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
216 END
217
218
219GO
220IF EXISTS (SELECT 1
221 FROM [master].[dbo].[sysdatabases]
222 WHERE [name] = N'$(DatabaseName)')
223 BEGIN
224 ALTER DATABASE [$(DatabaseName)]
225 SET TEMPORAL_HISTORY_RETENTION ON
226 WITH ROLLBACK IMMEDIATE;
227 END
228
229
230GO
231IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
232 EXECUTE sp_fulltext_database 'enable';
233
234
235GO
236PRINT N'Creating [dbo].[User]...';
237
238
239GO
240CREATE TABLE [dbo].[User] (
241 [Id] INT IDENTITY (1, 1) NOT NULL,
242 [EmailAddress] VARCHAR (100) NOT NULL,
243 [Pass] VARCHAR (100) NOT NULL,
244 [FullName] VARCHAR (150) NULL,
245 [RoleId] INT NULL
246);
247
248
249GO
250PRINT N'Creating [dbo].[DF_user_fullName]...';
251
252
253GO
254ALTER TABLE [dbo].[User]
255 ADD CONSTRAINT [DF_user_fullName] DEFAULT ('Usuario') FOR [FullName];
256
257
258GO
259DECLARE @VarDecimalSupported AS BIT;
260
261SELECT @VarDecimalSupported = 0;
262
263IF ((ServerProperty(N'EngineEdition') = 3)
264 AND (((@@microsoftversion / power(2, 24) = 9)
265 AND (@@microsoftversion & 0xffff >= 3024))
266 OR ((@@microsoftversion / power(2, 24) = 10)
267 AND (@@microsoftversion & 0xffff >= 1600))))
268 SELECT @VarDecimalSupported = 1;
269
270IF (@VarDecimalSupported > 0)
271 BEGIN
272 EXECUTE sp_db_vardecimal_storage_format N'$(DatabaseName)', 'ON';
273 END
274
275
276GO
277PRINT N'Update complete.';
278
279
280GO