Wiki source code of Database Export

Last modified by Gideon Lombard on 2025/01/22 12:49
Show last authors
1 = SQL db export script =
2
3 {{toc/}}
4
5 == Description ==
6
7 This script exports all the DDL related items of a database and some selected items of the instance into individual files which enables a better way to compare to databases with each other and also to import into a source control repository. Objects file time stamps are aligned with database timestamps with the exception for indexes.
8
9 == Download ==
10
11 git link: [[sql-script-db.ps1>>https://dev.azure.com/ebsphere/ebsphere/_git/incubator?path=/ebsphere.crm/ebs-devops/devops/sql/sql-script-db.ps1&version=GBmaster&_a=contents]]
12
13 == Special Notes ==
14
15 * For Azure you have to use the connection string approach as Azure needs the Initial Catalog set and there is no way currently to do this programmatically. There is a work-around where we mock the connection string but this will be a future fix.
16
17 * When possible, try and run on a remote machine on the same network as the database this will avoid network overhead and speed up the process. After this you can zip and transfer locally as there will be many little files that can take a while to copy when not packed into one archive.
18
19 * Depending on the user's level of access instance level options will only script if the user has the correct server roles, i.e. sysadmin. For the DB level the user has to have DB owner (if not a sysadmin).
20
21 == Prerequisites ==
22
23 * Powershell 5+, works on 7+ (Core) [[~[link~]>>https://docs.microsoft.com/en-us/powershell/scripting/install/installing-powershell-on-windows]]
24 * SqlServer module (automatically installs if missing) [[~[link~]>>https://docs.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module]]
25
26 == Output ==
27
28 The output goes into a folder specified by the outputPath folder, the folder structure is as follows
29
30 * \databases
31 ** \triggers
32 *** [trigger_name].sql
33 ** \[database_name]
34 *** listing.txt
35 *** \triggers
36 **** [trigger_name].sql
37 *** \[schema_name]
38 **** [schema_name].sql
39 **** \stored-procedures
40 ***** [stored_procedure_name].sql
41 **** \tables
42 ***** \[table_name]
43 ****** [table_name].sql
44 ****** \constraints
45 ******* [constaint_name].sql
46 ****** \triggers
47 ******* [trigger_name].sql
48 ****** \indexes
49 ******* [index_name].sql
50 **** \functions
51 ***** [function_name].sql
52 **** \types
53 ***** [type_name].sql
54 **** \synonyms
55 ***** [synonym_name].sql
56 **** \sequences
57 ***** [sequence_name].sql
58 **** \views
59 ***** [view_name].sql
60
61 **Notes**
62
63 * dbo would not have a ddl script as this is the default schema
64 * listing.txt contains a list of the objects to script based on filter criteria
65 * names are escaped where necessary to be filesystem "friendly" see [[~[link~]>>https://docs.microsoft.com/en-us/dotnet/api/system.io.path.getinvalidfilenamechars]]
66
67 == SQL Compatibility ==
68
69 |=sql version|=notes
70 |All|external tables do not script currently
71 |SQL Server 2019|tested
72 |Azure SQL Managed Instance|TODO
73 |Azure SQL Databse|tested, need to use the connectionString approach
74 |SQL Server 2016|TODO
75 |(((
76 SQL Server 2014
77 )))|TODO
78
79 == How to Run ==
80
81 ~1. Create a directory on your C: drive call Dev\Tools\DB Export
82
83 2. Save the file downloaded in the folder
84
85 3. Run the folloing command
86
87 ==== Export all schemas and pass credentials ====
88
89 {{code language="powershell"}}
90 ./sql-script-db.ps1 -server localhost `
91 -instance default `
92 -database "test_db" `
93 -username testdb `
94 -password 'test$db' `
95 -schemas "*" `
96 -outputpath "c:\temp\dbs" `
97 -logfile .\test.txt
98 {{/code}}
99
100 ==== ====
101
102 Notice the single quotes for password parameter this allows passing in reserved characters for the password ie a $
103
104 ==== Export specific schemas ====
105
106 {{code language="powershell"}}
107 ./sql-script-db.ps1 -server localhost `
108 -instance default `
109 -database "test_db" `
110 -username testdb `
111 -password 'test$db' `
112 -schemas "dbo","api","app","web","prd","ussd","aud","scr","fraxion" `
113 -outputpath "c:\temp\dbs" `
114 -logfile .\test.txt
115 {{/code}}
116
117 ==== Use a connection string to Azure SQL ====
118
119 {{code language="powershell"}}
120 .\sql-script-db.ps1 -server 172.10.1.4 `
121 -instance default `
122 -database "Everest_Test2" `
123 -schemas "*" `
124 -outputpath "c:\temp\dbs" `
125 -logfile .\test.txt `
126 -username 'everestsa@eppf.database.windows.net' `
127 -password 'super$secret' `
128 -connectionString 'Data Source=eppf.database.windows.net;Initial Catalog=Everest_Test2;Persist Security Info=True;'
129 {{/code}}
130
131 ==== Export only stored procedures and functions [-objectTypes 'sp','functions'] ====
132
133 {{code language="powershell"}}
134 ./sql-script-db.ps1 -server localhost `
135 -instance default `
136 -database "test_db" `
137 -username testdb `
138 -password 'test$db' `
139 -schemas "*" `
140 -outputpath "c:\temp\dbs" `
141 -objectTypes 'sp','functions' `
142 -logfile .\test.txt
143 {{/code}}
144
145 ==== Export only objects that contain the word test [-objectNameFilter 'test'] ====
146
147 {{code language="powershell"}}
148 ./sql-script-db.ps1 -server localhost `
149 -instance default `
150 -database "test_db" `
151 -username testdb `
152 -password 'test$db' `
153 -schemas "*" `
154 -outputpath "c:\temp\dbs" `
155 -objectNameFilter 'test' `
156 -logfile .\test.txt
157 {{/code}}
158
159 ==== Export only objects that end the word debug [ -objectNameFilter 'debug$' ] ====
160
161 {{code language="powershell"}}
162 ./sql-script-db.ps1 -server localhost `
163 -instance default `
164 -database "test_db" `
165 -username testdb `
166 -password 'test$db' `
167 -schemas "*" `
168 -outputpath "c:\temp\dbs" `
169 -objectNameFilter 'debug$' `
170 -logfile .\test.txt
171 {{/code}}
172
173 ==== Export a delta of all changed/created objects since a specific date time [ -offsetDate '2022-11-01 06:00:00' ] ====
174
175 {{code language="powershell"}}
176 ./sql-script-db.ps1 -server localhost `
177 -instance default `
178 -database "test_db" `
179 -username testdb `
180 -password 'test$db' `
181 -schemas "*" `
182 -outputpath "c:\temp\dbs" `
183 -offsetDate '2022-11-01 06:00:00' `
184 -logfile .\test.txt
185 {{/code}}
186
187 ==== Export a delta of all changed/created objects last 7 days (dynamic date) [ -offsetDate "$((Get-Date).AddDays(-7))" ] ====
188
189 {{code language="powershell"}}
190 ./sql-script-db.ps1 -server localhost `
191 -instance default `
192 -database "test_db" `
193 -username testdb `
194 -password 'test$db' `
195 -schemas "*" `
196 -outputpath "c:\temp\dbs" `
197 -offsetDate "$((Get-Date).AddDays(-7))" `
198 -logfile .\test.txt
199 {{/code}}
200
201 == Future ==
202
203 * add parallel processing to speed up processing time
204 * script database and instance level options
205 * script DML for specified tables i.e. config tables
206 * use an approach to identify index modified dates
207 * adding hashes for cross database compares
208 * standardize formatting based on internal standards

Applications

Navigation

Need help?

If you need help with XWiki you can contact: