· 7 years ago · Oct 17, 2018, 06:10 PM
1SET QUOTED_IDENTIFIER OFF
2GO
3SET ANSI_NULLS ON
4GO
5
6if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CSS_RestoreDir]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
7drop procedure [dbo].[sp_CSS_RestoreDir]
8GO
9
10exec sp_CSS_RestoreDir 'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLBackup'
11
12
13
14
15/***************************************************************************************/
16-- Procedure Name: sp_CSS_RestoreDir
17-- Purpose: Restore one or many database backups from a single directory. This script reads all
18-- database backups that are found in the @restoreFromDir parameter.
19-- Any database backup that matches the form %_db_% will be restored to
20-- the file locations specified in the RestoreTo... parameter(s). The database
21-- will be restored to a database name that is based on the database backup
22-- file name. For example Insurance_db_200305212302.BAK will be restored to
23-- a database named Insurance. The characters preceeding the '_db_' text determines
24-- the name.
25--
26-- Input Parameters: @restoreFromDir - The directory where the database backups are located
27-- @restoreToDataDir - The directory where the data files (i.e. MDF) will be restored to
28-- @restoreToLogDir - The directory where the log files (i.e. LDF) will be restored to. If
29-- this parameter is not provided then the log files are restored to @restoreToDataDir.
30-- @MatchFileList - set to 'Y' to restore to same directory structure contained in the backup,
31-- also allows for secondary data files 'ndf' to to be in a different dir than mdf files
32-- @DBName - restore just this one database - selects the latest bak file
33--
34-- Output Parameters: None
35--
36-- Return Values:
37--
38-- Written By: Chris Gallelli -- 8/22/03
39-- Modified By:
40-- Modifications: Bruce Canaday -- 10/20/2003
41-- Added optional parameters @MatchFileList and @DBName
42-- Bruce Canaday -- 10/24/2003
43-- Get the db name as the characters LEFT of the right most '_db_' in the bak filenaame
44-- This is to handle databases such as ALIS_DB
45-- Bruce Canaday -- 10/28/2003
46-- When using @MatchFileList = 'Y' attempt to create any directory that doesn't exist
47-- Bruce Canaday -- 11/04/2003
48-- Allow spaces in the @restoreFromDir directory name
49-- paul Wegmann -- 07/11/2012
50-- Chnaged the create table to allow more feilds to support SQL Server 2008r2 and SQl Server 2012
51-- create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20),
52-- FileId int,CreateLSN bit, DropLSN bit, UniqueID varchar(255),ReadOnlyLSn bit, ReadWriteLSN bit, backupSizeInBytes varchar(50), SourceBlockSize int,
53-- FileGroupid Int, LogGroupGUID varchar(255),DifferentialBaseLSN varchar(255),DifferentialBaseGUID varchar(255),isReadOnly bit, IsPresent bit,TDEThumbprint varchar(255) )
54-- Paul Wegmann -- 07/11/2012 changed from stored proc to set
55-- declare @restoreFromDir varchar(255),
56-- @restoreToDataDir varchar(255),
57-- @restoreToLogDir varchar(255) ,
58-- @MatchFileList char(1) ,
59-- @OneDBName varchar(255)
60--
61-- set @restoreFromDir = 'location of directory where your backup exist'
62-- set @restoreToDataDir = 'location where your data files will be restored too'
63-- set @restoreToLogDir = 'location of LDF files needs to be restored too'
64-- set @MatchFileList = 'N'
65-- set @OneDBName = null
66--
67-- Sample Execution: exec sp_CSS_RestoreDir 'C:sqldbsql_backup', 'C:sqldbsql_data', 'C:sqldbsql_log' (if you use declare/set option then you don't have to use this command to restore)
68--
69-- Alternate Execution: exec sp_CSS_RestoreDir 'C:sqldbsql_backup', @MatchFileList = 'Y' (if you use declare/set option then you don't have to use this command to restore)
70--
71-- Reviewed By: Anoar Hassan
72--
73/***************************************************************************************/
74
75CREATE proc sp_CSS_RestoreDir
76 @restoreFromDir varchar(255),
77 @restoreToDataDir varchar(255)= null,
78 @restoreToLogDir varchar(255) = null,
79 @MatchFileList char(1) = 'N',
80 @OneDBName varchar(255) = null
81as
82
83-- to use delare/set option, use the following code and commond -- the create proc SP_CSS_RestoreDir
84-- declare @restoreFromDir varchar(255),
85-- @restoreToDataDir varchar(255),
86-- @restoreToLogDir varchar(255) ,
87-- @MatchFileList char(1) ,
88-- @OneDBName varchar(255)
89--
90
91
92 set @restoreFromDir = 'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLBackup'
93 set @restoreToDataDir = 'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA'
94 set @restoreToLogDir = 'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA'
95 set @MatchFileList = 'N'
96 set @OneDBName = 'PL_SampleData,ReportServer '
97
98--If a directory for the Log file is not supplied then use the data directory
99If @restoreToLogDir is null
100 set @restoreToLogDir = @restoreToDataDir
101
102set nocount on
103
104declare @filename varchar(40),
105 @cmd varchar(500),
106 @cmd2 varchar(500),
107 @DataName varchar (255),
108 @LogName varchar (255),
109 @LogicalName varchar(255),
110 @PhysicalName varchar(255),
111 @Type varchar(20),
112 @FileGroupName varchar(255),
113 @Size varchar(20),
114 @MaxSize varchar(20),
115 @restoreToDir varchar(255),
116 @searchName varchar(255),
117 @DBName varchar(255),
118 @PhysicalFileName varchar(255)
119
120create table #dirList (filename varchar(100))
121--edited by Anoar
122create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20),
123 FileId int,CreateLSN bit, DropLSN bit, UniqueID varchar(255),ReadOnlyLSn bit, ReadWriteLSN bit, backupSizeInBytes varchar(50), SourceBlockSize int,
124 FileGroupid Int, LogGroupGUID varchar(255),DifferentialBaseLSN varchar(255),DifferentialBaseGUID varchar(255),isReadOnly bit, IsPresent bit,TDEThumbprint varchar(255) )
125
126
127--Get the list of database backups that are in the restoreFromDir directory
128if @OneDBName is null
129 select @cmd = 'dir /b /on "' +@restoreFromDir+ '"'
130else
131 select @cmd = 'dir /b /o-d /o-g "' +@restoreFromDir+ '"'
132
133insert #dirList exec master..xp_cmdshell @cmd
134
135select * from #dirList where filename like '%_db_%' --order by filename
136
137if @OneDBName is null
138 declare BakFile_csr cursor for
139 select * from #dirList where filename like '%_db_%bak' order by filename
140else
141 begin -- single db, don't order by filename, take default latest date /o-d parm in dir command above
142 select @searchName = @OneDBName + '_db_%bak'
143 declare BakFile_csr cursor for
144 select top 1 * from #dirList where filename like @searchName
145 end
146
147open BakFile_csr
148fetch BakFile_csr into @filename
149
150while @@fetch_status = 0
151 begin
152 select @cmd = "RESTORE FILELISTONLY FROM disk = '" + @restoreFromDir + "" + @filename + "'"
153
154 insert #filelist exec ( @cmd )
155
156 if @OneDBName is null
157 select @dbName = left(@filename,datalength(@filename) - patindex('%_bd_%',reverse(@filename))-3)
158 else
159 select @dbName = @OneDBName
160
161 select @cmd = "RESTORE DATABASE " + @dbName +
162 " FROM DISK = '" + @restoreFromDir + "" + @filename + "' WITH "
163
164 PRINT ''
165 PRINT 'RESTORING DATABASE ' + @dbName
166
167 declare DataFileCursor cursor for
168 select LogicalName, PhysicalName, Type, FileGroupName, Size, MaxSize
169 from #filelist
170
171 open DataFileCursor
172 fetch DataFileCursor into @LogicalName, @PhysicalName, @Type, @FileGroupName, @Size, @MaxSize
173
174 while @@fetch_status = 0
175 begin
176 if @MatchFileList != 'Y'
177 begin -- RESTORE with MOVE option
178 select @PhysicalFileName = reverse(substring(reverse(rtrim(@PhysicalName)),1,patindex('%%',reverse(rtrim(@PhysicalName)))-1 ))
179
180 if @Type = 'L'
181 select @restoreToDir = @restoreToLogDir
182 else
183 select @restoreToDir = @restoreToDataDir
184
185 select @cmd = @cmd +
186 " MOVE '" + @LogicalName + "' TO '" + @restoreToDir + "" + @PhysicalFileName + "', "
187 end
188 else
189 begin -- Match the file list, attempt to create any missing directory
190 select @restoreToDir = left(@PhysicalName,datalength(@PhysicalName) - patindex('%%',reverse(@PhysicalName)) )
191 select @cmd2 = "if not exist " +@restoreToDir+ " md " +@restoreToDir
192 exec master..xp_cmdshell @cmd2
193 end
194
195 fetch DataFileCursor into @LogicalName, @PhysicalName, @Type, @FileGroupName, @Size, @MaxSize
196
197 end -- DataFileCursor loop
198
199 close DataFileCursor
200 deallocate DataFileCursor
201
202 select @cmd = @cmd + ' REPLACE'
203 --select @cmd 'command'
204 EXEC (@CMD)
205
206 truncate table #filelist
207
208 fetch BakFile_csr into @filename
209
210 end -- BakFile_csr loop
211
212close BakFile_csr
213deallocate BakFile_csr
214
215drop table #dirList
216
217return
218GO
219
220SET QUOTED_IDENTIFIER OFF
221GO
222SET ANSI_NULLS ON
223GO
224
225
226
227--to bulk backup dbs in a single instance:
228DECLARE @name VARCHAR(50) -- database name
229DECLARE @path VARCHAR(256) -- path for backup files
230DECLARE @fileName VARCHAR(256) -- filename for backup
231DECLARE @fileDate VARCHAR(20) -- used for file name
232
233SET @path = 'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLBackup'
234
235SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
236
237DECLARE db_cursor CURSOR FOR
238SELECT name
239FROM master.dbo.sysdatabases
240WHERE name NOT IN ('master','model','msdb','tempdb','ReportServerTempDB')
241
242OPEN db_cursor
243FETCH NEXT FROM db_cursor INTO @name
244
245WHILE @@FETCH_STATUS = 0
246BEGIN
247 SET @fileName = @path + @name + '_db_' + @fileDate + '.BAK'
248 BACKUP DATABASE @name TO DISK = @fileName
249
250 FETCH NEXT FROM db_cursor INTO @name `enter code here`
251END
252
253CLOSE db_cursor
254DEALLOCATE db_cursor