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
|
drop Procedure RestaurationAutomatique
go
Create Procedure RestaurationAutomatique
@evchBase varchar(100),
@evchFichierBackup varchar(1000),
@eintDebug integer = 0
As
set nocount on
Declare @vchData as varchar(100)
Declare @vchLog as varchar(100)
Declare @vchFichierData as varchar(1000)
Declare @vchFichierLog as varchar(1000)
if @evchBase is null and @evchFichierBackup is null
Return
-- kill de toutes les connexions sur la base cible
create table #Connections
(
SPID int
)
Insert into #Connections
Select p.spid
from master.dbo.sysprocesses p (nolock)
join master..sysdatabases d (nolock) on p.dbid = d.dbid
Where d.[name] = @evchBase
Declare @vchSQLText varchar(200), @intSPID int
While 1 = 1
Begin
Select top 1 @intSPID = SPID
From #Connections
Where SPID > IsNull(@intSPID, 0)
order by SPID asc
If @@RowCount = 0
Break
Set @vchSQLText = 'Kill ' + Convert(varchar(10), @intSPID)
Exec( @vchSQLText )
End
drop table #Connections
-- recuperation nom des fichiers logiques
create table #tmpFicLogiques
(
LogicalName varchar(100),
PhysicalName varchar(1000),
Type varchar(5),
FileGroupName varchar(30),
[Size] decimal,
[MaxSize] decimal
)
insert into #tmpFicLogiques (LogicalName,PhysicalName,Type,FileGroupName,[Size],[MaxSize])
exec('RESTORE FILELISTONLY FROM DISK = N''' + @evchFichierBackup + ''' WITH FILE = 1')
select @vchData = LogicalName from #tmpFicLogiques where Type = 'D'
select @vchLog = LogicalName from #tmpFicLogiques where Type = 'L'
drop table #tmpFicLogiques
if @vchData is null or @vchLog is null
begin
Print 'Erreur noms logiques invalides'
Return
end
-- recuperation des infos de fichier physiques
create table #tmpFichiersPhysiques
(
PhysicalName varchar(1000),
Status int
)
insert into #tmpFichiersPhysiques (PhysicalName, Status)
exec('select filename, usage = (case status & 0x40 when 0x40 then 0 else 1 end) from ' + @evchBase + '..sysfiles')
if @eintDebug = 1 select * from #tmpFichiersPhysiques
select @vchFichierData = LTrim(Rtrim(PhysicalName)) from #tmpFichiersPhysiques where Status = 1
select @vchFichierLog = LTrim(Rtrim(PhysicalName)) from #tmpFichiersPhysiques where Status = 0
if @vchFichierData is null or @vchFichierLog is null
begin
Print 'Erreur noms physiques invalides'
Return
end
drop table #tmpFichiersPhysiques
-- lance la restauration de la base
declare @vchSQL varchar(8000)
SET @vchSQL = 'RESTORE DATABASE [' + @evchBase + '] FROM DISK = N''' + @evchFichierBackup +
''' WITH FILE = 1, UNLOAD , STATS = 10, RECOVERY , REPLACE ,' +
' MOVE N'''+@vchData+''' TO N'''+@vchFichierData+''',
MOVE N'''+@vchLog+''' TO N'''+@vchFichierLog+''''
--select @vchSQL
if @eintDebug = 0 exec (@vchSQL)
else select @vchSQL |
restore filelistonly
Répondre avec citation
Envoyé par Gandalf Le Blanc
Partager