-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathcrear.sql
148 lines (114 loc) · 2.99 KB
/
crear.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
If Db_Id('BDTravel') is not null
Drop Database BDContoso
go
--Crear BD de forma predeterminada[no recomendado]
Create Database BDContoso
go
--Ver la estructura
sp_helpdb BDContoso
go
if db_id('BDTravel') is not null
drop database BDTravel
go
-----------------------------
create database BDTravel
on
(name = TravelData, filename='C:\Data\TravelData.MDF'
,size=30MB, maxsize=80MB, filegrowth=10MB)
log on
(name=TravelLog, filename='C:\Data\TravelLog.LDF',
size=40MB, maxsize=100MB, filegrowth=10%)
go
-----------------------------------
Create Database BDTravel
On
(name=TravelData, filename='C:\Data\TravelData.MDF',
size=30MB, maxsize=80MB, filegrowth=10MB),
(name=TravelData1, filename='C:\Data\TravelData1.NDF',
size=30MB, maxsize=80MB, filegrowth=10MB),
(name=TravelData2, filename='C:\Data\TravelData2.NDF',
size=30MB, maxsize=80MB, filegrowth=10MB)
Log On
(name=TravelLog, filename='C:\Data\TravelLog.LDF',
size=40MB, maxsize=100MB, filegrowth=10%),
(name=TravelLog1, filename='C:\Data\TravelLog1.LDF',
size=40MB, maxsize=100MB, filegrowth=10%)
go
-----------------------------------------------------
create database DBTravel
on
(name=TravelData1, filename='C:\Data\TravelData1.MDF',
size=20MB, maxsize=50MB, filegrowth=5MB)
log on
(name=TravelLog1, filename='C:\Data\TravelLog1.LDF',
size=50MB, maxsize=200MB, filegrowth=10%)
go
sp_helpdb BDTravel
go
--Modificar Archivos de Data Base--
alter database BDTravel
modify file
(name=TravelData1, maxsize=450MB)
go
alter database BDTravel
modify file
(name=TravelLog1, filegrowth=25%)
go
--Adicionar archivos a la base de datos--
--Adicionar DataFile--
alter database BDTravel
add file
(name=TravelData2, filename='C:\Data\TravelData2.NDF')
go
--Adicionar logFile
alter database BDTravel
add log file
(name=TravelLog2, filename='C:\Data\TravelLog2.LDF')
go
--Mover Archivos de datos a una nueva ubicacion
--Paso 01 : Desacoplar la base de datos
sp_detach_db BDTravel
go
--Paso 02 : Mover los archivos a la nueva ubicacion con el explorador de windows
--Pase 03 : Crear DB , adjunto los nuevos archivos en una nueva ubicacion
create database BDTravel
on
(filename='C:\newFolder\TravelData.MDF'),
(filename='C:\newFolder\TravelData1.NDF'),
(filename='C:\newFolder\TravelData2.NDF'),
(filename='C:\newFolder\TravelLog.LDF'),
(filename='C:\newFolder\TravelLog1.LDF'),
(filename='C:\newFolder\TravelLog2.LDF')
for attach
go
sp_helpdb BDTravel
go
--Crear los grupos para la mejor visualizacion de el jefe
alter database BDTravel
add filegroup FGComercial
go
alter database BDTravel
add filegroup FGAdministra
go
--Adicionar un Datafile a cada filegroup
--Adicionar un Datafile a cada filegroup
Alter DataBase BDTravel
Add File
(name=TravelDataComercial, filename='C:\Data\TravelDataComercial.NDF')
to filegroup FGComercial
go
Alter Database BDTravel
Add File
(name=TravelDataAdministra, filename='C:\Data\TravelDataAdministra.NDF')
to filegroup FGAdministra
go
create table TBPrueba(
id int,
nom varchar(50)
)
go
create table TBDemo(
id int,
nom varchar(50)
) on FGComercial
go