· 6 years ago · Jun 15, 2019, 03:06 PM
1restore filelistonly from disk='D:backupsmy_backup.bak'
2
3restore database my_db_name from disk='d:backupsmy_backups.bak' with file=1,
4move 'logical_data_file' to 'd:datamydb.mdf',
5move 'logical_log_file' to 'd:datamylog.ldf'
6
7CREATE PROC [dbo].[restoreDB]
8 @p_strDBNameTo SYSNAME,
9 @p_strDBNameFrom SYSNAME,
10 @p_strFQNRestoreFileName VARCHAR(255)
11AS
12 DECLARE
13 @v_strDBFilename VARCHAR(100),
14 @v_strDBLogFilename VARCHAR(100),
15 @v_strDBDataFile VARCHAR(100),
16 @v_strDBLogFile VARCHAR(100),
17 @v_strExecSQL NVARCHAR(1000),
18 @v_strExecSQL1 NVARCHAR(1000),
19 @v_strMoveSQL NVARCHAR(4000),
20 @v_strREPLACE NVARCHAR(50),
21 @v_strTEMP NVARCHAR(1000),
22 @v_strListSQL NVARCHAR(4000),
23 @v_strServerVersion NVARCHAR(20)
24
25 SET @v_strREPLACE = ''
26 IF exists (select name from sys.databases where name = @p_strDBNameTo)
27 SET @v_strREPLACE = ', REPLACE'
28
29 SET @v_strListSQL = ''
30 SET @v_strListSQL = @v_strListSQL + 'IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''##FILE_LIST''))'
31 SET @v_strListSQL = @v_strListSQL + 'BEGIN'
32 SET @v_strListSQL = @v_strListSQL + ' DROP TABLE ##FILE_LIST '
33 SET @v_strListSQL = @v_strListSQL + 'END '
34
35 SET @v_strListSQL = @v_strListSQL + 'CREATE TABLE ##FILE_LIST ('
36 SET @v_strListSQL = @v_strListSQL + ' LogicalName VARCHAR(64),'
37 SET @v_strListSQL = @v_strListSQL + ' PhysicalName VARCHAR(130),'
38 SET @v_strListSQL = @v_strListSQL + ' [Type] VARCHAR(1),'
39 SET @v_strListSQL = @v_strListSQL + ' FileGroupName VARCHAR(64),'
40 SET @v_strListSQL = @v_strListSQL + ' Size DECIMAL(20, 0),'
41 SET @v_strListSQL = @v_strListSQL + ' MaxSize DECIMAL(25,0),'
42 SET @v_strListSQL = @v_strListSQL + ' FileID bigint,'
43 SET @v_strListSQL = @v_strListSQL + ' CreateLSN DECIMAL(25,0),'
44 SET @v_strListSQL = @v_strListSQL + ' DropLSN DECIMAL(25,0),'
45 SET @v_strListSQL = @v_strListSQL + ' UniqueID UNIQUEIDENTIFIER,'
46 SET @v_strListSQL = @v_strListSQL + ' ReadOnlyLSN DECIMAL(25,0),'
47 SET @v_strListSQL = @v_strListSQL + ' ReadWriteLSN DECIMAL(25,0),'
48 SET @v_strListSQL = @v_strListSQL + ' BackupSizeInBytes DECIMAL(25,0),'
49 SET @v_strListSQL = @v_strListSQL + ' SourceBlockSize INT,'
50 SET @v_strListSQL = @v_strListSQL + ' filegroupid INT,'
51 SET @v_strListSQL = @v_strListSQL + ' loggroupguid UNIQUEIDENTIFIER,'
52 SET @v_strListSQL = @v_strListSQL + ' differentialbaseLSN DECIMAL(25,0),'
53 SET @v_strListSQL = @v_strListSQL + ' differentialbaseGUID UNIQUEIDENTIFIER,'
54 SET @v_strListSQL = @v_strListSQL + ' isreadonly BIT,'
55 SET @v_strListSQL = @v_strListSQL + ' ispresent BIT'
56
57 SELECT @v_strServerVersion = CAST(SERVERPROPERTY ('PRODUCTVERSION') AS NVARCHAR)
58
59 IF @v_strServerVersion LIKE '10.%'
60 BEGIN
61 SET @v_strListSQL = @v_strListSQL + ', TDEThumbpr DECIMAL'
62 --PRINT @v_strServerVersion
63 END
64
65 SET @v_strListSQL = @v_strListSQL + ')'
66
67 EXEC (@v_strListSQL)
68
69
70 INSERT INTO ##FILE_LIST EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @p_strFQNRestoreFileName + '''')
71
72 DECLARE curFileLIst CURSOR FOR
73 SELECT 'MOVE N''' + LogicalName + ''' TO N''' + replace(PhysicalName, @p_strDBNameFrom, @p_strDBNameTo) + ''''
74 FROM ##FILE_LIST
75
76 SET @v_strMoveSQL = ''
77
78 OPEN curFileList
79 FETCH NEXT FROM curFileList into @v_strTEMP
80 WHILE @@Fetch_Status = 0
81 BEGIN
82 SET @v_strMoveSQL = @v_strMoveSQL + @v_strTEMP + ', '
83 FETCH NEXT FROM curFileList into @v_strTEMP
84 END
85
86 CLOSE curFileList
87 DEALLOCATE curFileList
88
89 PRINT 'Killing active connections to the "' + @p_strDBNameTo + '" database'
90
91 -- Create the sql to kill the active database connections
92 SET @v_strExecSQL = ''
93 SELECT @v_strExecSQL = @v_strExecSQL + 'kill ' + CONVERT(CHAR(10), spid) + ' '
94 FROM master.dbo.sysprocesses
95 WHERE DB_NAME(dbid) = @p_strDBNameTo AND DBID <> 0 AND spid <> @@spid
96
97 EXEC (@v_strExecSQL)
98
99 PRINT 'Restoring "' + @p_strDBNameTo + '" database from "' + @p_strFQNRestoreFileName + '" with '
100 PRINT ' data file "' + @v_strDBDataFile + '" located at "' + @v_strDBFilename + '"'
101 PRINT ' log file "' + @v_strDBLogFile + '" located at "' + @v_strDBLogFilename + '"'
102
103 SET @v_strExecSQL = 'RESTORE DATABASE [' + @p_strDBNameTo + ']'
104 SET @v_strExecSQL = @v_strExecSQL + ' FROM DISK = ''' + @p_strFQNRestoreFileName + ''''
105 SET @v_strExecSQL = @v_strExecSQL + ' WITH FILE = 1,'
106 SET @v_strExecSQL = @v_strExecSQL + @v_strMoveSQL
107 SET @v_strExecSQL = @v_strExecSQL + ' NOREWIND, '
108 SET @v_strExecSQL = @v_strExecSQL + ' NOUNLOAD '
109 SET @v_strExecSQL = @v_strExecSQL + @v_strREPLACE
110
111
112 --PRINT '---------------------------'
113 --PRINT @v_strExecSQL
114 --PRINT '---------------------------'
115
116
117 EXEC sp_executesql @v_strExecSQL
118
119declare @filelist table (LogicalName nvarchar(128), PhysicalName nvarchar(260), Type char(1), FilegroupName varchar(10), size int, MaxSize bigint, field int, createlsn bit, droplsn bit, uniqueid uniqueidentifier, readonlylsn bit, readwritelsn bit, backupsizeinbytes bigint, sourceblocksize int, filegroupid int, loggroupguid uniqueidentifier, differentialbaselsn bit, differentialbaseguid uniqueidentifier, isreadonly bit, ispresent bit, tdethumbprint varchar(5));
120insert into @filelist exec sp_executesql N'restore filelistonly from disk=''D:backupsmy_backup.bak''';
121
122set @sql = N'RESTORE database my_database from disk ''D:backupsmy_backup.bak'' with ';
123select @sql = @sql + N' move ' + LogicalName + N' to ' udf_localFilePath(PhysicalName) + N','
124from @filelist;
125
126set @sql = substring(@sql, 1, len(@sql)-1); -- remove last ','
127exec sp_executesql @sql;
128
129DECLARE @FileList TABLE
130 (
131 LogicalName nvarchar(128) NOT NULL,
132 PhysicalName nvarchar(260) NOT NULL,
133 Type char(1) NOT NULL,
134 FileGroupName nvarchar(120) NULL,
135 Size numeric(20, 0) NOT NULL,
136 MaxSize numeric(20, 0) NOT NULL,
137 FileID bigint NULL,
138 CreateLSN numeric(25,0) NULL,
139 DropLSN numeric(25,0) NULL,
140 UniqueID uniqueidentifier NULL,
141 ReadOnlyLSN numeric(25,0) NULL ,
142 ReadWriteLSN numeric(25,0) NULL,
143 BackupSizeInBytes bigint NULL,
144 SourceBlockSize int NULL,
145 FileGroupID int NULL,
146 LogGroupGUID uniqueidentifier NULL,
147 DifferentialBaseLSN numeric(25,0)NULL,
148 DifferentialBaseGUID uniqueidentifier NULL,
149 IsReadOnly bit NULL,
150 IsPresent bit NULL,
151 TDEThumbprint varbinary(32) NULL
152 );
153
154 declare @RestoreStatement nvarchar(max), @BackupFile nvarchar(max);
155
156 set @BackupFile = 'D:mybackup.bak'
157
158 SET @RestoreStatement = N'RESTORE FILELISTONLY
159 FROM DISK=N''' + @BackupFile + ''''
160
161INSERT INTO @FileList
162 EXEC(@RestoreStatement);
163
164declare @logical_data nvarchar(max), @logical_log nvarchar(max);
165
166set @logical_data = (select LogicalName from @FileList where Type = 'D' and FileID = 1)
167set @logical_log = (select LogicalName from @FileList where Type = 'L' and FileID = 2)
168
169/*
170Automate restore w/o needing to know the logical file names.
171Specify destination database name, database backup source filename
172and .MDF, .LDF and .NDF directories.
173I do nightly automated database restores,
174and I've been using this code for about a month.
175Works for sql server 2008, might work for 2005.
176Created by wtm 5/27/2010
177*/
178
179 -- BEGIN - MODIFY THIS CODE - create a blank db
180if not exists(select * from master.sys.databases where [name]='sc')
181begin
182 create database sc
183end
184go
185-- END - MODIFY THIS CODE - create a blank db
186
187declare @strDatabase varchar(130)='sc' -- MODIFY THIS LINE - db name
188declare @strBackupFile varchar(500)='c:docsdb-backupssc.bak' -- MODIFY THIS LINE - source db backup file
189declare @strRestoreMDFFilesTo varchar(500)='c:docssqldata' -- MODIFY THIS LINE - destination restore directory for main files
190declare @strRestoreLDFFilesTo varchar(500)='c:docssqldata' -- MODIFY THIS LINE - destination restore directory for tlog files
191declare @strRestoreNDFFilesTo varchar(500)='c:docssqldata' -- MODIFY THIS LINE - destination restore directory for non-main files
192
193-- other variables used
194declare @strSQL nvarchar(max)
195declare @strOriginalPhysicalName varchar(150)
196declare @strPhysicalName varchar(150)
197declare @strLogicalName varchar(150)
198declare @intReturn int
199
200-- begin restoring
201begin try
202 drop table #tmpFilelist
203end try
204begin catch
205end catch
206create table #tmpFilelist (
207 LogicalName varchar(64), PhysicalName varchar(130), [Type] varchar(1), FileGroupName varchar(64), Size decimal(20, 0)
208 ,MaxSize decimal(25, 0), FileID bigint, CreateLSN decimal(25,0), DropLSN decimal(25,0), UniqueID uniqueidentifier
209 ,ReadOnlyLSN decimal(25,0), ReadWriteLSN decimal(25,0), BackSizeInBytes decimal(25,0), SourceBlockSize int
210 ,filegroupid int, loggroupguid uniqueidentifier, differentialbaseLSN decimal(25,0), differentialbaseGUID uniqueidentifier
211 ,isreadonly bit, ispresent bit, TDEThumbpr decimal
212)
213if not exists(select * from sc.sys.tables) or exists(select * from sc.sys.tables where [name]='not-an-original-table') -- MODIFY THIS LINE - business logic to see if we need to restore the database at all
214begin
215 print 'Restoring '+@strDatabase+' db ...'
216 use master
217 exec msdb.dbo.sp_delete_database_backuphistory @database_name = @strDatabase
218 use [master]
219 exec('alter database '+@strDatabase+' set single_user with rollback immediate')
220 use [master]
221 exec('drop database '+@strDatabase)
222 insert into #tmpFilelist
223 exec('restore filelistonly from disk = '''+@strBackupFile+'''')
224 set @strSQL='restore database ['+@strDatabase+'] from disk='''+@strBackupFile+''' with '
225 set @strSQL=@strSQL+ 'file=1 '
226 set @strSQL=@strSQL+ ',nounload '
227 set @strSQL=@strSQL+ ',replace '
228 set @strSQL=@strSQL+ ',stats=10 ' -- show restore status every 10%
229 while exists(select * from #tmpFilelist)
230 begin
231 select top 1 @strOriginalPhysicalName=PhysicalName, @strLogicalName=LogicalName from #tmpFilelist
232 set @strPhysicalName=@strOriginalPhysicalName
233 set @strPhysicalName=reverse(@strPhysicalName)
234 set @strPhysicalName=left(@strPhysicalName, charindex('', @strPhysicalName)-1)
235 set @strPhysicalName=reverse(@strPhysicalName)
236 set @strPhysicalName=replace(@strPhysicalName, '.', '_'+@strDatabase+'.')
237 if @strPhysicalName like '%.mdf'
238 set @strPhysicalName=@strRestoreMDFFilesTo+@strPhysicalName
239 else if @strPhysicalName like '%.ldf'
240 set @strPhysicalName=@strRestoreLDFFilesTo+@strPhysicalName
241 else
242 set @strPhysicalName=@strRestoreNDFFilesTo+@strPhysicalName
243 set @strSQL=@strSQL+ ',move '''+@strLogicalName+''' to '''+@strPhysicalName+''' '
244 delete from #tmpFilelist where PhysicalName=@strOriginalPhysicalName
245 end
246 execute @intReturn=sp_executesql @strSQL
247end
248
249SET NOCOUNT ON
250
251Declare @BackupFiles varchar(500), @data_file_path VARCHAR(512), @log_file_path VARCHAR(512), @RestoreFileList varchar(2000), @RestoreStatement varchar(3000), @MoveFiles varchar(2000), @DBName varchar(150)
252
253DECLARE @filelist TABLE (LogicalName NVARCHAR(128) NOT NULL, PhysicalName NVARCHAR(260) NOT NULL, [Type] CHAR(1) NOT NULL, FileGroupName NVARCHAR(120) NULL, Size NUMERIC(20, 0) NOT NULL, MaxSize NUMERIC(20, 0) NOT NULL, FileID BIGINT NULL, CreateLSN NUMERIC(25,0) NULL, DropLSN NUMERIC(25,0) NULL, UniqueID UNIQUEIDENTIFIER NULL, ReadOnlyLSN NUMERIC(25,0) NULL , ReadWriteLSN NUMERIC(25,0) NULL, BackupSizeInBytes BIGINT NULL, SourceBlockSize INT NULL, FileGroupID INT NULL, LogGroupGUID UNIQUEIDENTIFIER NULL, DfferentialBaseLSN NUMERIC(25,0)NULL, DifferentialBaseGUID UNIQUEIDENTIFIER NULL, IsReadOnly BIT NULL, IsPresent BIT NULL, TDEThumbprint VARBINARY(32) NULL)
254
255SET @data_file_path = 'E:SQLData'
256SET @log_file_path = 'E:SQLLog'
257SET @DBName = 'Adventureworks'
258
259--Get last full backup:
260SELECT @BackupFiles=Coalesce(@BackupFiles + ',', '') + 'DISK = N'''+physical_device_name+''''
261FROM msdb..backupset S
262JOIN msdb..backupmediafamily M ON M.media_set_id=S.media_set_id
263WHERE backup_set_id = ( SELECT max(backup_set_id)
264 FROM msdb..backupset S
265 JOIN msdb..backupmediafamily M ON M.media_set_id=S.media_set_id
266 WHERE S.database_name = @DBName and Type = 'D')
267
268SELECT @RestoreFileList= 'RESTORE FILELISTONLY FROM ' + @BackupFiles + ' WITH FILE = 1 '
269
270IF (@@microsoftversion / 0x1000000) & 0xff >= 10 --TDE capability
271Begin
272 INSERT into @filelist (LogicalName,PhysicalName,Type,FileGroupName,Size,MaxSize,FileID,CreateLSN,DropLSN,UniqueID,ReadOnlyLSN,ReadWriteLSN,BackupSizeInBytes,SourceBlockSize,FileGroupID,LogGroupGUID,DfferentialBaseLSN,DifferentialBaseGUID,IsReadOnly,IsPresent,TDEThumbprint)
273 EXEC (@RestoreFileList)
274End
275Else
276Begin
277 INSERT into @filelist (LogicalName,PhysicalName,Type,FileGroupName,Size,MaxSize,FileID,CreateLSN,DropLSN,UniqueID,ReadOnlyLSN,ReadWriteLSN,BackupSizeInBytes,SourceBlockSize,FileGroupID,LogGroupGUID,DfferentialBaseLSN,DifferentialBaseGUID,IsReadOnly,IsPresent)
278 EXEC (@RestoreFileList)
279End
280
281--next version, do a count on filename, any >1 put in alternate data/log location.
282SELECT @MoveFiles=Coalesce(@MoveFiles + ',' , '') + 'MOVE N''' + LogicalName + ''' to N''' +
283 Case When type = 'D' Then @data_file_path+Right(physicalname, charindex('',reverse(physicalname),1)-1)
284 when type = 'L' Then @log_file_path+Right(physicalname, charindex('',reverse(physicalname),1)-1)
285 Else 'Full Text - code not complete'
286 END
287 +''''
288From @filelist
289
290SELECT @RestoreStatement='RESTORE DATABASE [AuctionMain] FROM ' + @BackupFiles + ' WITH FILE = 1, ' + @MoveFiles + ', NOUNLOAD, REPLACE, STATS = 20'
291
292Print @RestoreStatement
293
294Exec(@RestoreStatement)
295
296use master
297
298--
299-- check SQL Server version
300DECLARE @sql_ver int;
301CREATE TABLE #tmp_sql_ver
302(
303 [Index] int,
304 [Name] nvarchar(100),
305 [iVal] int,
306 [cVal] nvarchar(100)
307)
308INSERT INTO #tmp_sql_ver EXEC('xp_msver ProductVersion');
309IF (SELECT cast(cVal as char(2)) FROM #tmp_sql_ver) = '8.'
310 SET @sql_ver = 8;
311ELSE
312 SET @sql_ver = 9;
313DROP TABLE #tmp_sql_ver;
314
315--
316-- get mdf/ldf names
317DECLARE @mdf_name varchar(50)
318DECLARE @ldf_name varchar(50)
319DECLARE @RestoreFileListOnly_columns varchar(2000)
320
321IF (@sql_ver = 8)
322BEGIN
323 SET @RestoreFileListOnly_columns = '
324 LogicalName nvarchar(128),
325 PhysicalName nvarchar(260),
326 [Type] char(1),
327 FileGroupName nvarchar(128),
328 [Size] numeric(20,0),
329 [MaxSize] numeric(20,0),
330 '
331END
332ELSE
333BEGIN
334 SET @RestoreFileListOnly_columns = '
335 LogicalName nvarchar(128),
336 PhysicalName nvarchar(260),
337 [Type] char(1),
338 FileGroupName nvarchar(128),
339 [Size] numeric(20,0),
340 [MaxSize] numeric(20,0),
341
342 FileID bigint,
343 CreateLSN numeric(25,0),
344 DropLSN numeric(25,0) NULL,
345 UniqueID uniqueidentifier,
346 ReadOnlyLSN numeric(25,0) NULL,
347 ReadWriteLSN numeric(25,0) NULL,
348 BackupSizeInBytes bigint,
349 SourceBlockSize int,
350 FileGroupID int,
351 LogGroupGUID uniqueidentifier NULL,
352 DifferentialBaseLSN numeric(25,0) NULL,
353 DifferentialBaseGUID uniqueidentifier,
354 IsReadOnly bit,
355 IsPresent bit
356 '
357 DECLARE @tmp_ver NVARCHAR(20)
358 SELECT @tmp_ver = CAST(SERVERPROPERTY ('PRODUCTVERSION') AS NVARCHAR)
359 IF @tmp_ver LIKE '1[01].%'
360 BEGIN
361 SET @RestoreFileListOnly_columns = @RestoreFileListOnly_columns + ', TDEThumbpr DECIMAL'
362 END
363END
364IF EXISTS (SELECT [table_name] FROM information_schema.tables WHERE [table_name] = 'tmp_RestoreFileListOnly')
365BEGIN
366 DROP TABLE [tmp_RestoreFileListOnly];
367END
368EXEC ('CREATE TABLE tmp_RestoreFileListOnly ('+@RestoreFileListOnly_columns+');');
369
370INSERT INTO tmp_RestoreFileListOnly EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @bkpfile + '''')
371PRINT 'RESTORE FILELISTONLY FROM DISK = ''' + @bkpfile + ''''
372--IF @@ROWCOUNT <> 2 RETURN
373SELECT @mdf_name = LogicalName FROM tmp_RestoreFileListOnly WHERE Type = 'D'
374SELECT @ldf_name = LogicalName FROM tmp_RestoreFileListOnly WHERE Type = 'L'
375DROP TABLE tmp_RestoreFileListOnly
376
377-- Use VARCHAR as the restore statement doesn't like NVARCHAR
378DECLARE @data_file_path VARCHAR(512), @log_file_path VARCHAR(512), @backup_path VARCHAR(512),
379 @backup_extension VARCHAR(4), @mdf_extension VARCHAR(4), @ldf_extension VARCHAR(4)
380
381-- ** VARIABLES THAT MUST BE SET **--
382SET @data_file_path = 'E:DataPath'
383SET @log_file_path = 'F:LogPath'
384SET @backup_path = 'B:BackUpPath'
385-- **----------------------------**--
386
387SET @backup_extension = '.bak'
388SET @mdf_extension = '.mdf'
389SET @ldf_extension = '.ldf'
390
391DECLARE @DATABASES_TO_RESTORE TABLE (rownum int IDENTITY (1, 1) PRIMARY KEY NOT NULL, backup_name VARCHAR(64), restore_as VARCHAR(64))
392
393-- ** Declare the Databases to be Restored ** --
394INSERT INTO @DATABASES_TO_RESTORE
395 SELECT 'Intranet', 'Intranet_Test'
396 UNION
397 SELECT 'TestAudit', 'TestAudit_Test'
398-- ** -------------------------------------** --
399
400DECLARE @max_rows INT, @row_count INT
401SET @row_count = 1
402SELECT @max_rows=count(*) FROM @DATABASES_TO_RESTORE
403
404WHILE @row_count <= @max_rows
405BEGIN
406 DECLARE @backup_name VARCHAR(32), @restore_as VARCHAR(32), @logical_data_name VARCHAR(64), @logical_log_name VARCHAR(64),
407 @data_file_full_path VARCHAR(512), @log_file_full_path VARCHAR(512), @full_backup_path VARCHAR(MAX)
408
409 SELECT @backup_name = backup_name, @restore_as = restore_as FROM @DATABASES_TO_RESTORE WHERE rownum = @row_count
410 SET @full_backup_path = @backup_path + @backup_name + @backup_extension
411
412 DECLARE @filelist TABLE (LogicalName NVARCHAR(128) NOT NULL, PhysicalName NVARCHAR(260) NOT NULL, [Type] CHAR(1) NOT NULL, FileGroupName NVARCHAR(120) NULL, Size NUMERIC(20, 0) NOT NULL, MaxSize NUMERIC(20, 0) NOT NULL, FileID BIGINT NULL, CreateLSN NUMERIC(25,0) NULL, DropLSN NUMERIC(25,0) NULL, UniqueID UNIQUEIDENTIFIER NULL, ReadOnlyLSN NUMERIC(25,0) NULL , ReadWriteLSN NUMERIC(25,0) NULL, BackupSizeInBytes BIGINT NULL, SourceBlockSize INT NULL, FileGroupID INT NULL, LogGroupGUID UNIQUEIDENTIFIER NULL, DfferentialBaseLSN NUMERIC(25,0)NULL, DifferentialBaseGUID UNIQUEIDENTIFIER NULL, IsReadOnly BIT NULL, IsPresent BIT NULL, TDEThumbprint VARBINARY(32) NULL)
413
414 INSERT into @filelist
415 EXEC ('RESTORE FilelistOnly FROM DISK = ''' + @full_backup_path + '''')
416
417 IF @@ROWCOUNT = 2
418 BEGIN
419 SELECT @logical_data_name = LogicalName FROM @filelist WHERE [Type] = 'D'
420 SELECT @logical_log_name = LogicalName FROM @filelist WHERE [Type] = 'L'
421
422 SET @data_file_full_path = @data_file_path + @restore_as + @mdf_extension
423 SET @log_file_full_path = @log_file_path + @restore_as + @ldf_extension
424
425 RESTORE DATABASE @restore_as
426 FROM DISK = @full_backup_path
427 WITH
428 FILE = 1,
429 MOVE @logical_data_name
430 TO @data_file_full_path,
431 MOVE @logical_log_name
432 TO @log_file_full_path
433 END
434 ELSE
435 PRINT 'CANNOT RESTORE DATABASE ' + @restore_as + ' THE BACKUP CONTAINS MORE THAN 1 BACKUP SET'
436
437 SELECT @row_count = @row_count + 1
438END
439
440SELECT top(1) @v_strRestorePath = physical_name FROM sys.master_files
441
442USE [master]
443GO
444SET ANSI_NULLS ON
445GO
446SET QUOTED_IDENTIFIER ON
447GO
448
449IF OBJECT_ID('[dbo].[restoreDB]') IS NOT NULL
450DROP PROC [dbo].[restoreDB]
451GO
452
453CREATE PROC [dbo].[restoreDB]
454 @p_strDBNameTo SYSNAME,
455 @p_strDBNameFrom SYSNAME,
456 @p_strFQNRestoreFileName VARCHAR(255)
457
458AS
459DECLARE
460 @v_strDBFilename VARCHAR(100),
461 @v_strDBLogFilename VARCHAR(100),
462 @v_strDBDataFile VARCHAR(100),
463 @v_strDBLogFile VARCHAR(100),
464 @v_strExecSQL NVARCHAR(1000),
465 @v_strExecSQL1 NVARCHAR(1000),
466 @v_strMoveSQL NVARCHAR(4000),
467 @v_strREPLACE NVARCHAR(50),
468 @v_strTEMP NVARCHAR(1000),
469 @v_strListSQL NVARCHAR(4000),
470 @v_strServerVersion NVARCHAR(20),
471 @v_strRestorePath varchar(500)
472
473SET @v_strREPLACE = ''
474IF exists (select name from sys.databases where name = @p_strDBNameTo)
475 SET @v_strREPLACE = ', REPLACE'
476
477SET @v_strListSQL = ''
478SET @v_strListSQL = @v_strListSQL + 'IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''##FILE_LIST''))'
479SET @v_strListSQL = @v_strListSQL + 'BEGIN'
480SET @v_strListSQL = @v_strListSQL + ' DROP TABLE ##FILE_LIST '
481SET @v_strListSQL = @v_strListSQL + 'END '
482
483SET @v_strListSQL = @v_strListSQL + 'CREATE TABLE ##FILE_LIST ('
484SET @v_strListSQL = @v_strListSQL + ' LogicalName VARCHAR(64),'
485SET @v_strListSQL = @v_strListSQL + ' PhysicalName VARCHAR(130),'
486SET @v_strListSQL = @v_strListSQL + ' [Type] VARCHAR(1),'
487SET @v_strListSQL = @v_strListSQL + ' FileGroupName VARCHAR(64),'
488SET @v_strListSQL = @v_strListSQL + ' Size DECIMAL(20, 0),'
489SET @v_strListSQL = @v_strListSQL + ' MaxSize DECIMAL(25,0),'
490SET @v_strListSQL = @v_strListSQL + ' FileID bigint,'
491SET @v_strListSQL = @v_strListSQL + ' CreateLSN DECIMAL(25,0),'
492SET @v_strListSQL = @v_strListSQL + ' DropLSN DECIMAL(25,0),'
493SET @v_strListSQL = @v_strListSQL + ' UniqueID UNIQUEIDENTIFIER,'
494SET @v_strListSQL = @v_strListSQL + ' ReadOnlyLSN DECIMAL(25,0),'
495SET @v_strListSQL = @v_strListSQL + ' ReadWriteLSN DECIMAL(25,0),'
496SET @v_strListSQL = @v_strListSQL + ' BackupSizeInBytes DECIMAL(25,0),'
497SET @v_strListSQL = @v_strListSQL + ' SourceBlockSize INT,'
498SET @v_strListSQL = @v_strListSQL + ' filegroupid INT,'
499SET @v_strListSQL = @v_strListSQL + ' loggroupguid UNIQUEIDENTIFIER,'
500SET @v_strListSQL = @v_strListSQL + ' differentialbaseLSN DECIMAL(25,0),'
501SET @v_strListSQL = @v_strListSQL + ' differentialbaseGUID UNIQUEIDENTIFIER,'
502SET @v_strListSQL = @v_strListSQL + ' isreadonly BIT,'
503SET @v_strListSQL = @v_strListSQL + ' ispresent BIT'
504
505SELECT @v_strServerVersion = CAST(SERVERPROPERTY ('PRODUCTVERSION') AS NVARCHAR)
506
507IF @v_strServerVersion LIKE '10.%'
508 BEGIN
509 SET @v_strListSQL = @v_strListSQL + ', TDEThumbpr DECIMAL'
510 --PRINT @v_strServerVersion
511 END
512
513SET @v_strListSQL = @v_strListSQL + ')'
514
515EXEC (@v_strListSQL)
516
517-- We want to get the current data path from this server as the backup file paths may not be the same on the server
518-- especially wehen switching between Express/Standard instances
519SELECT top(1) @v_strRestorePath = physical_name FROM sys.master_files;
520set @v_strRestorePath = REPLACE(@v_strRestorePath, RIGHT(@v_strRestorePath, CHARINDEX('', REVERSE(@v_strRestorePath))-1),'')
521--print @v_strRestorePath --'
522
523INSERT INTO ##FILE_LIST EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @p_strFQNRestoreFileName + '''')
524
525-- want to see whats in the fillist?
526--SELECT * FROM ##FILE_LIST
527
528DECLARE curFileLIst CURSOR FOR
529 -- Here we restore each file to the current server restore path. Right(...) grabs the file name from the back up
530 SELECT 'MOVE N''' + LogicalName + ''' TO N''' + @v_strRestorePath + Replace(RIGHT(PhysicalName, CHARINDEX('', REVERSE(PhysicalName))-1),@p_strDBNameFrom, @p_strDBNameTo) + '''' --'
531 FROM ##FILE_LIST
532
533SET @v_strMoveSQL = ''
534
535OPEN curFileList
536FETCH NEXT FROM curFileList into @v_strTEMP
537WHILE @@Fetch_Status = 0
538BEGIN
539 SET @v_strMoveSQL = @v_strMoveSQL + @v_strTEMP + ', '
540 FETCH NEXT FROM curFileList into @v_strTEMP
541END
542
543CLOSE curFileList
544DEALLOCATE curFileList
545
546PRINT 'Killing active connections to the "' + @p_strDBNameTo + '" database'
547
548-- Create the sql to kill the active database connections
549SET @v_strExecSQL = ''
550SELECT @v_strExecSQL = @v_strExecSQL + 'kill ' + CONVERT(CHAR(10), spid) + ' '
551FROM master.dbo.sysprocesses
552WHERE DB_NAME(dbid) = @p_strDBNameTo AND DBID <> 0 AND spid <> @@spid
553
554EXEC (@v_strExecSQL)
555
556PRINT 'Restoring "' + @p_strDBNameTo + '" database from "' + @p_strFQNRestoreFileName + '" with '
557PRINT ' data file "' + @v_strDBDataFile + '" located at "' + @v_strDBFilename + '"'
558PRINT ' log file "' + @v_strDBLogFile + '" located at "' + @v_strDBLogFilename + '"'
559
560SET @v_strExecSQL = 'RESTORE DATABASE [' + @p_strDBNameTo + ']'
561SET @v_strExecSQL = @v_strExecSQL + ' FROM DISK = ''' + @p_strFQNRestoreFileName + ''''
562SET @v_strExecSQL = @v_strExecSQL + ' WITH FILE = 1,'
563SET @v_strExecSQL = @v_strExecSQL + @v_strMoveSQL
564SET @v_strExecSQL = @v_strExecSQL + ' NOREWIND, '
565SET @v_strExecSQL = @v_strExecSQL + ' NOUNLOAD '
566SET @v_strExecSQL = @v_strExecSQL + @v_strREPLACE
567
568
569--PRINT '---------------------------'
570--PRINT @v_strExecSQL
571--PRINT '---------------------------'
572
573--For Some reason the file list hangs when I was debugging remove it.
574IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '##FILE_LIST'))
575BEGIN
576 DROP TABLE ##FILE_LIST
577END
578
579EXEC sp_executesql @v_strExecSQLter
580
581CREATE PROC [dbo].[restoreDB]
582 @p_strDBNameTo SYSNAME,
583 @p_strDBNameFrom SYSNAME,
584 @p_strBackupDirectory VARCHAR(255),
585 @p_strRestoreDirectory VARCHAR(255),
586 @p_strFQNBackupFileName VARCHAR(255)
587AS
588 DECLARE
589 @v_strDBFilename VARCHAR(200),
590 @v_strDBLogFilename VARCHAR(200),
591 @v_strDBDataFile VARCHAR(200),
592 @v_strDBLogFile VARCHAR(200),
593 @v_strExecSQL NVARCHAR(MAX),
594 @v_strMoveSQL NVARCHAR(MAX),
595 @v_strREPLACE NVARCHAR(50),
596 @v_strTEMP NVARCHAR(1000),
597 @v_strListSQL NVARCHAR(4000),
598 @v_strServerVersion NVARCHAR(20)
599
600 SET @v_strREPLACE = ''
601 IF exists (select name from sys.databases where name = @p_strDBNameTo)
602 SET @v_strREPLACE = ', REPLACE'
603
604 SET @v_strListSQL = ''
605 SET @v_strListSQL = @v_strListSQL + 'IF OBJECT_ID(''tempdb..##FILE_LIST'') IS NOT NULL DROP TABLE ##FILE_LIST '
606 SET @v_strListSQL = @v_strListSQL + 'CREATE TABLE ##FILE_LIST ('
607 SET @v_strListSQL = @v_strListSQL + ' LogicalName VARCHAR(64),'
608 SET @v_strListSQL = @v_strListSQL + ' PhysicalName VARCHAR(130),'
609 SET @v_strListSQL = @v_strListSQL + ' [Type] VARCHAR(1),'
610 SET @v_strListSQL = @v_strListSQL + ' FileGroupName VARCHAR(64),'
611 SET @v_strListSQL = @v_strListSQL + ' Size DECIMAL(20, 0),'
612 SET @v_strListSQL = @v_strListSQL + ' MaxSize DECIMAL(25,0),'
613 SET @v_strListSQL = @v_strListSQL + ' FileID bigint,'
614 SET @v_strListSQL = @v_strListSQL + ' CreateLSN DECIMAL(25,0),'
615 SET @v_strListSQL = @v_strListSQL + ' DropLSN DECIMAL(25,0),'
616 SET @v_strListSQL = @v_strListSQL + ' UniqueID UNIQUEIDENTIFIER,'
617 SET @v_strListSQL = @v_strListSQL + ' ReadOnlyLSN DECIMAL(25,0),'
618 SET @v_strListSQL = @v_strListSQL + ' ReadWriteLSN DECIMAL(25,0),'
619 SET @v_strListSQL = @v_strListSQL + ' BackupSizeInBytes DECIMAL(25,0),'
620 SET @v_strListSQL = @v_strListSQL + ' SourceBlockSize INT,'
621 SET @v_strListSQL = @v_strListSQL + ' filegroupid INT,'
622 SET @v_strListSQL = @v_strListSQL + ' loggroupguid UNIQUEIDENTIFIER,'
623 SET @v_strListSQL = @v_strListSQL + ' differentialbaseLSN DECIMAL(25,0),'
624 SET @v_strListSQL = @v_strListSQL + ' differentialbaseGUID UNIQUEIDENTIFIER,'
625 SET @v_strListSQL = @v_strListSQL + ' isreadonly BIT,'
626 SET @v_strListSQL = @v_strListSQL + ' ispresent BIT'
627
628 SELECT @v_strServerVersion = CAST(SERVERPROPERTY ('PRODUCTVERSION') AS NVARCHAR)
629
630 IF @v_strServerVersion LIKE '10.%'
631 BEGIN
632 SET @v_strListSQL = @v_strListSQL + ', TDEThumbpr DECIMAL'
633 --PRINT @v_strServerVersion
634 END
635
636 SET @v_strListSQL = @v_strListSQL + ')'
637
638 EXEC (@v_strListSQL)
639
640 INSERT INTO ##FILE_LIST EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @p_strFQNBackupFileName + '''')
641
642 DECLARE curFileLIst CURSOR FOR
643 SELECT 'MOVE N''' + LogicalName + ''' TO N''' + replace(replace(PhysicalName, @p_strDBNameFrom, @p_strDBNameTo), @p_strBackupDirectory, @p_strRestoreDirectory) + ''''
644 FROM ##FILE_LIST
645
646 SET @v_strMoveSQL = cast('' as nvarchar(max))
647
648 OPEN curFileList
649 FETCH NEXT FROM curFileList into @v_strTEMP
650 WHILE @@Fetch_Status = 0
651 BEGIN
652 SET @v_strMoveSQL = @v_strMoveSQL + cast(@v_strTEMP as nvarchar(max)) + cast(', ' as nvarchar(max))
653 FETCH NEXT FROM curFileList into @v_strTEMP
654 END
655
656 CLOSE curFileList
657 DEALLOCATE curFileList
658
659
660 PRINT 'Killing active connections to the "' + @p_strDBNameTo + '" database'
661
662 -- Create the sql to kill the active database connections
663 SET @v_strExecSQL = ''
664 SELECT @v_strExecSQL = @v_strExecSQL + 'kill ' + CONVERT(CHAR(10), spid) + ' '
665 FROM master.dbo.sysprocesses
666 WHERE DB_NAME(dbid) = @p_strDBNameTo AND DBID <> 0 AND spid <> @@spid
667
668 EXEC (@v_strExecSQL)
669
670 PRINT 'Restoring "' + @p_strDBNameTo + '" database from "' + @p_strFQNBackupFileName + '" with '
671 PRINT ' data file "' + @v_strDBDataFile + '" located at "' + @v_strDBFilename + '"'
672 PRINT ' log file "' + @v_strDBLogFile + '" located at "' + @v_strDBLogFilename + '"'
673
674 SET @v_strExecSQL = cast('RESTORE DATABASE [' as nvarchar(max)) + cast(@p_strDBNameTo as nvarchar(max)) + cast(']' as nvarchar(max))
675 SET @v_strExecSQL = @v_strExecSQL + cast(' FROM DISK = ''' as nvarchar(max)) + cast(@p_strFQNBackupFileName as nvarchar(max)) + cast('''' as nvarchar(max))
676 SET @v_strExecSQL = @v_strExecSQL + cast(' WITH FILE = 1,' as nvarchar(max))
677 SET @v_strExecSQL = @v_strExecSQL + @v_strMoveSQL
678 SET @v_strExecSQL = @v_strExecSQL + cast(' NOREWIND, ' as nvarchar(max))
679 SET @v_strExecSQL = @v_strExecSQL + cast(' NOUNLOAD ' as nvarchar(max))
680 SET @v_strExecSQL = @v_strExecSQL + cast(@v_strREPLACE as nvarchar(max))
681
682 --If want to print string need to do in sections due to limitation of print string length
683
684 PRINT 'Exec string: ' +cast(len(@v_strExecSQL) as nvarchar(max))+ ' ***:'
685 PRINT substring(@v_strExecSQL,0,3999)
686 PRINT substring(@v_strExecSQL,4000,7999)
687 PRINT substring(@v_strExecSQL,8000,11999)
688 PRINT substring(@v_strExecSQL,12000,15999)
689 PRINT substring(@v_strExecSQL,16000,19999)
690 PRINT substring(@v_strExecSQL,20000,23999)
691 PRINT substring(@v_strExecSQL,24000,27999)
692 PRINT substring(@v_strExecSQL,28000,31999)
693 PRINT substring(@v_strExecSQL,32000,35999)
694
695
696 EXEC sp_executesql @v_strExecSQL
697
698GO
699
700declare @databaseName nvarchar(max);
701declare @backUpDiskLocation nvarchar(max);
702declare @physicalMDFLocation nvarchar(max);
703declare @physicalLDFLocation nvarchar(max);
704
705set @databaseName = '[Event_Xdb.Collection.Shard1]';
706set @backUpDiskLocation = 'C:SQL-BACKUP<backUP-Name>.bak';
707set @physicalMDFLocation = 'C:SQLSQLData<MDF-Name>.mdf';
708set @physicalLDFLocation = 'C:SQLSQL-LOG<LDF-Name_log>.LDF'
709
710if (DB_ID(@databaseName)) is not null
711Begin
712DECLARE @kill varchar(8000); SET @kill = '';
713SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
714FROM master..sysprocesses
715WHERE dbid = DB_ID(@databaseName)
716EXEC(@kill);
717 DECLARE @Alter nvarchar(max); SET @Alter = 'ALTER DATABASE ' + @databaseName +' SET offline WITH Rollback Immediate'
718 EXEC (@Alter)
719END
720
721declare @sql nvarchar(max)
722set @sql = N'restore filelistonly from disk=''' + @backUpDiskLocation +'''';
723
724select @sql
725create table #filelist (LogicalName nvarchar(128), PhysicalName nvarchar(260), Type char(1), FilegroupName varchar(10), size int, MaxSize bigint, field int, createlsn bit, droplsn bit, uniqueid uniqueidentifier, readonlylsn bit, readwritelsn bit, backupsizeinbytes bigint, sourceblocksize int, filegroupid int, loggroupguid uniqueidentifier, differentialbaselsn bit, differentialbaseguid uniqueidentifier, isreadonly bit, ispresent bit, tdethumbprint varchar(5), SnapshotUrl nvarchar(128));
726insert into #filelist exec sp_executesql @sql;
727ALTER TABLE #filelist add id int identity(1,1)
728update #filelist set PhysicalName = @physicalMDFLocation where [id]= 1
729update #filelist set PhysicalName = @physicalLDFLocation where [id]= 2
730select * from #filelist
731
732set @sql = N'RESTORE database '+ @databaseName +' from disk = '''+ @backUpDiskLocation +''' with replace, ';
733select @sql
734select @sql = @sql + N' move ''' + LogicalName + N''' to ''' + PhysicalName + N''',' from #filelist;
735
736set @sql = substring(@sql, 1, len(@sql)-1); -- remove last ','
737
738select @sql
739exec sp_executesql @sql;
740
741drop table #filelist