· 8 years ago · Feb 15, 2018, 10:50 AM
1USE master;
2GO
3-- the original database (use 'SET @DB = NULL' to disable backup)
4DECLARE @SourceDatabaseName varchar(200)
5DECLARE @SourceDatabaseLogicalName varchar(200)
6DECLARE @SourceDatabaseLogicalNameForLog varchar(200)
7DECLARE @query varchar(2000)
8DECLARE @DataFile varchar(2000)
9DECLARE @LogFile varchar(2000)
10DECLARE @BackupFile varchar(2000)
11DECLARE @TargetDatabaseName varchar(200)
12DECLARE @TargetDatbaseFolder varchar(2000)
13
14-- ****************************************************************
15
16SET @SourceDatabaseName = '[Source.DB]' -- Name of the source database
17SET @SourceDatabaseLogicalName = 'Source_DB' -- Logical name of the DB ( check DB properties / Files tab )
18SET @SourceDatabaseLogicalNameForLog = 'Source_DB_log' -- Logical name of the DB ( check DB properties / Files tab )
19SET @BackupFile = 'C:Tempbackup.dat' -- FileName of the backup file
20SET @TargetDatabaseName = 'TargetDBName' -- Name of the target database
21SET @TargetDatbaseFolder = 'C:Temp'
22
23-- ****************************************************************
24
25SET @DataFile = @TargetDatbaseFolder + @TargetDatabaseName + '.mdf';
26SET @LogFile = @TargetDatbaseFolder + @TargetDatabaseName + '.ldf';
27
28-- Backup the @SourceDatabase to @BackupFile location
29IF @SourceDatabaseName IS NOT NULL
30BEGIN
31SET @query = 'BACKUP DATABASE ' + @SourceDatabaseName + ' TO DISK = ' + QUOTENAME(@BackupFile,'''')
32PRINT 'Executing query : ' + @query;
33EXEC (@query)
34END
35PRINT 'OK!';
36
37-- Drop @TargetDatabaseName if exists
38IF EXISTS(SELECT * FROM sysdatabases WHERE name = @TargetDatabaseName)
39BEGIN
40SET @query = 'DROP DATABASE ' + @TargetDatabaseName
41PRINT 'Executing query : ' + @query;
42EXEC (@query)
43END
44PRINT 'OK!'
45
46-- Restore database from @BackupFile into @DataFile and @LogFile
47SET @query = 'RESTORE DATABASE ' + @TargetDatabaseName + ' FROM DISK = ' + QUOTENAME(@BackupFile,'''')
48SET @query = @query + ' WITH MOVE ' + QUOTENAME(@SourceDatabaseLogicalName,'''') + ' TO ' + QUOTENAME(@DataFile ,'''')
49SET @query = @query + ' , MOVE ' + QUOTENAME(@SourceDatabaseLogicalNameForLog,'''') + ' TO ' + QUOTENAME(@LogFile,'''')
50PRINT 'Executing query : ' + @query
51EXEC (@query)
52PRINT 'OK!'
53
54USE master;
55
56DECLARE
57 @SourceDatabaseName AS SYSNAME = '<SourceDB>',
58 @TargetDatabaseName AS SYSNAME = '<TargetDB>'
59
60
61
62-- ============================================
63-- Define path where backup will be saved
64-- ============================================
65IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = @SourceDatabaseName)
66 RAISERROR ('Variable @SourceDatabaseName is not set correctly !', 20, 1) WITH LOG
67
68DECLARE @SourceBackupFilePath varchar(2000)
69SELECT @SourceBackupFilePath = BMF.physical_device_name
70FROM
71 msdb.dbo.backupset B
72 JOIN msdb.dbo.backupmediafamily BMF ON B.media_set_id = BMF.media_set_id
73WHERE B.database_name = @SourceDatabaseName
74ORDER BY B.backup_finish_date DESC
75
76SET @SourceBackupFilePath = REPLACE(@SourceBackupFilePath, '.bak', '_clone.bak')
77
78
79
80-- ============================================
81-- Backup source database
82-- ============================================
83DECLARE @Sql NVARCHAR(MAX)
84SET @Sql = 'BACKUP DATABASE @SourceDatabaseName TO DISK = ''@SourceBackupFilePath'''
85SET @Sql = REPLACE(@Sql, '@SourceDatabaseName', @SourceDatabaseName)
86SET @Sql = REPLACE(@Sql, '@SourceBackupFilePath', @SourceBackupFilePath)
87SELECT 'Performing backup...', @Sql as ExecutedSql
88EXEC (@Sql)
89
90
91
92-- ============================================
93-- Automatically compose database files (.mdf and .ldf) paths
94-- ============================================
95DECLARE
96 @LogicalDataFileName as NVARCHAR(MAX)
97 , @LogicalLogFileName as NVARCHAR(MAX)
98 , @TargetDataFilePath as NVARCHAR(MAX)
99 , @TargetLogFilePath as NVARCHAR(MAX)
100
101SELECT
102 @LogicalDataFileName = name,
103 @TargetDataFilePath = SUBSTRING(physical_name,1,LEN(physical_name)-CHARINDEX('',REVERSE(physical_name))) + '' + @TargetDatabaseName + '.mdf'
104FROM sys.master_files
105WHERE
106 database_id = DB_ID(@SourceDatabaseName)
107 AND type = 0 -- datafile file
108
109SELECT
110 @LogicalLogFileName = name,
111 @TargetLogFilePath = SUBSTRING(physical_name,1,LEN(physical_name)-CHARINDEX('',REVERSE(physical_name))) + '' + @TargetDatabaseName + '.ldf'
112FROM sys.master_files
113WHERE
114 database_id = DB_ID(@SourceDatabaseName)
115 AND type = 1 -- log file
116
117
118
119-- ============================================
120-- Restore target database
121-- ============================================
122IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @TargetDatabaseName)
123 RAISERROR ('A database with the same name already exists!', 20, 1) WITH LOG
124
125SET @Sql = 'RESTORE DATABASE @TargetDatabaseName
126FROM DISK = ''@SourceBackupFilePath''
127WITH MOVE ''@LogicalDataFileName'' TO ''@TargetDataFilePath'',
128MOVE ''@LogicalLogFileName'' TO ''@TargetLogFilePath'''
129SET @Sql = REPLACE(@Sql, '@TargetDatabaseName', @TargetDatabaseName)
130SET @Sql = REPLACE(@Sql, '@SourceBackupFilePath', @SourceBackupFilePath)
131SET @Sql = REPLACE(@Sql, '@LogicalDataFileName', @LogicalDataFileName)
132SET @Sql = REPLACE(@Sql, '@TargetDataFilePath', @TargetDataFilePath)
133SET @Sql = REPLACE(@Sql, '@LogicalLogFileName', @LogicalLogFileName)
134SET @Sql = REPLACE(@Sql, '@TargetLogFilePath', @TargetLogFilePath)
135SELECT 'Restoring...', @Sql as ExecutedSql
136EXEC (@Sql)
137
138USE master
139GO
140-- the original database (use 'SET @DB = NULL' to disable backup)
141DECLARE @DB varchar(200)
142SET @DB = 'PcTopp'
143-- the backup filename
144DECLARE @BackupFile varchar(2000)
145SET @BackupFile = 'c:pctoppsqlserverbackup.dat'
146-- the new database name
147DECLARE @TestDB varchar(200)
148SET @TestDB = 'TestDB'
149-- the new database files without .mdf/.ldf
150DECLARE @RestoreFile varchar(2000)
151SET @RestoreFile = 'c:pctoppsqlserverbackup'
152-- ****************************************************************
153-- no change below this line
154-- ****************************************************************
155
156DECLARE @query varchar(2000)
157DECLARE @DataFile varchar(2000)
158SET @DataFile = @RestoreFile + '.mdf'
159DECLARE @LogFile varchar(2000)
160SET @LogFile = @RestoreFile + '.ldf'
161IF @DB IS NOT NULL
162BEGIN
163SET @query = 'BACKUP DATABASE ' + @DB + ' TO DISK = ' + QUOTENAME(@BackupFile, '''')
164EXEC (@query)
165END
166-- RESTORE FILELISTONLY FROM DISK = 'C:tempbackup.dat'
167-- RESTORE HEADERONLY FROM DISK = 'C:tempbackup.dat'
168-- RESTORE LABELONLY FROM DISK = 'C:tempbackup.dat'
169-- RESTORE VERIFYONLY FROM DISK = 'C:tempbackup.dat'
170IF EXISTS(SELECT * FROM sysdatabases WHERE name = @TestDB)
171BEGIN
172SET @query = 'DROP DATABASE ' + @TestDB
173EXEC (@query)
174END
175RESTORE HEADERONLY FROM DISK = @BackupFile
176DECLARE @File int
177SET @File = @@ROWCOUNT
178DECLARE @Data varchar(500)
179DECLARE @Log varchar(500)
180SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''')
181CREATE TABLE #restoretemp
182(
183LogicalName varchar(500),
184PhysicalName varchar(500),
185type varchar(10),
186FilegroupName varchar(200),
187size int,
188maxsize bigint
189)
190INSERT #restoretemp EXEC (@query)
191SELECT @Data = LogicalName FROM #restoretemp WHERE type = 'D'
192SELECT @Log = LogicalName FROM #restoretemp WHERE type = 'L'
193PRINT @Data
194PRINT @Log
195TRUNCATE TABLE #restoretemp
196DROP TABLE #restoretemp
197IF @File > 0
198BEGIN
199SET @query = 'RESTORE DATABASE ' + @TestDB + ' FROM DISK = ' + QUOTENAME(@BackupFile, '''') +
200' WITH MOVE ' + QUOTENAME(@Data, '''') + ' TO ' + QUOTENAME(@DataFile, '''') + ', MOVE ' +
201QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE = ' + CONVERT(varchar, @File)
202EXEC (@query)
203END
204GO
205
206USE master
207GO
208-- the original database (use 'SET @DB = NULL' to disable backup)
209DECLARE @DB varchar(200)
210SET @DB = 'GMSSDB'
211-- the backup filename
212DECLARE @BackupFile varchar(2000)
213SET @BackupFile = 'c:tempbackup.dat'
214-- the new database name
215DECLARE @TestDB varchar(200)
216SET @TestDB = 'GMSSDBArchive'
217-- the new database files without .mdf/.ldf
218DECLARE @RestoreFile varchar(2000)
219SET @RestoreFile = 'c:tempbackup'
220-- ****************************************************************
221-- no change below this line
222-- ****************************************************************
223
224DECLARE @query varchar(2000)
225DECLARE @DataFile varchar(2000)
226SET @DataFile = @RestoreFile + '.mdf'
227DECLARE @LogFile varchar(2000)
228SET @LogFile = @RestoreFile + '.ldf'
229IF @DB IS NOT NULL
230BEGIN
231SET @query = 'BACKUP DATABASE ' + @DB + ' TO DISK = ' + QUOTENAME(@BackupFile, '''')
232EXEC (@query)
233END
234-- RESTORE FILELISTONLY FROM DISK = 'C:tempbackup.dat'
235-- RESTORE HEADERONLY FROM DISK = 'C:tempbackup.dat'
236-- RESTORE LABELONLY FROM DISK = 'C:tempbackup.dat'
237-- RESTORE VERIFYONLY FROM DISK = 'C:tempbackup.dat'
238IF EXISTS(SELECT * FROM sysdatabases WHERE name = @TestDB)
239BEGIN
240SET @query = 'DROP DATABASE ' + @TestDB
241EXEC (@query)
242END
243
244CREATE TABLE #headeronly
245(
246BackupName nvarchar(128) null,
247BackupDescription nvarchar(255) null,
248BackupType smallint,
249ExpirationDate datetime null,
250Compressed bit,
251Position smallint,
252DeviceType tinyint,
253UserName nvarchar(128),
254ServerName nvarchar(128),
255DatabaseName nvarchar(128),
256DatabaseVersion int,
257DatabaseCreationDate datetime,
258BackupSize numeric(20,0),
259FirstLSN numeric(25,0),
260LastLSN numeric(25,0),
261CheckpointLSN numeric(25,0),
262DatabaseBackupLSN numeric(25,0),
263BackupStartDate datetime,
264BackupFinishDate datetime,
265SortOrder smallint,
266CodePage smallint,
267UnicodeLocaleId int,
268UnicodeComparisonStyle int,
269CompatibilityLevel tinyint,
270SoftwareVendorId int,
271SoftwareVersionMajor int,
272SoftwareVersionMinor int,
273SoftwareVersionBuild int,
274MachineName nvarchar(128),
275Flags int,
276BindingID uniqueidentifier,
277RecoveryForkID uniqueidentifier,
278Collation nvarchar(128),
279FamilyGUID uniqueidentifier,
280HasBulkLoggedData bit,
281IsSnapshot bit,
282IsReadOnly bit,
283IsSingleUser bit,
284HasBackupChecksums bit,
285IsDamaged bit,
286BeginsLogChain bit,
287HasIncompleteMetaData bit,
288IsForceOffline bit,
289IsCopyOnly bit,
290FirstRecoveryForkID uniqueidentifier,
291ForkPointLSN numeric(25,0) NULL,
292RecoveryModel nvarchar(60),
293DifferentialBaseLSN numeric(25,0) NULL,
294DifferentialBaseGUID uniqueidentifier,
295BackupTypeDescription nvarchar(60),
296BackupSetGUID uniqueidentifier NULL
297)
298--RESTORE HEADERONLY FROM DISK = @BackupFile
299SET @query = 'RESTORE HEADERONLY FROM DISK = ' + QUOTENAME(@BackupFile, '''')
300INSERT #headeronly exec(@query)
301
302
303DECLARE @File int
304select @File = count(1) from #headeronly
305print CONVERT(varchar, @File)
306DROP TABLE #headeronly
307
308
309DECLARE @Data varchar(500)
310DECLARE @Log varchar(500)
311SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''')
312
313--RESTORE FILELISTONLY FROM DISK = 'c:tempbackup.dat'
314
315CREATE TABLE #restoretemp
316(
317LogicalName nvarchar(128),
318PhysicalName nvarchar(260),
319type char(1),
320FilegroupName nvarchar(128),
321size numeric(20,0),
322maxsize numeric(20,0),
323FileID bigint,
324CreateLSN numeric(25,0),
325DropLSN numeric(25,0 )NULL,
326UniqueID uniqueidentifier,
327ReadOnlyLSN numeric(25,0) NULL,
328ReadWriteLSN numeric(25,0) NULL,
329BackupSizeInBytes bigint,
330SourceBlockSize int,
331FileGroupID int,
332LogGroupGUID uniqueidentifier NULL,
333DifferentialBaseLSN numeric(25,0) NULL,
334DifferentialBaseGUID uniqueidentifier,
335IsReadOnly bit,
336
337IsPresent bit
338
339)
340--select * from EXEC (@query)
341INSERT #restoretemp EXEC (@query)
342SELECT @Data = LogicalName FROM #restoretemp WHERE type = 'D'
343SELECT @Log = LogicalName FROM #restoretemp WHERE type = 'L'
344PRINT @Data
345PRINT @Log
346TRUNCATE TABLE #restoretemp
347DROP TABLE #restoretemp
348print CONVERT(varchar, @File)
349IF @File > 0
350BEGIN
351
352SET @query = 'RESTORE DATABASE ' + @TestDB + ' FROM DISK = ' + QUOTENAME(@BackupFile, '''') +
353' WITH MOVE ' + QUOTENAME(@Data, '''') + ' TO ' + QUOTENAME(@DataFile, '''') + ', MOVE ' +
354QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE = ' + CONVERT(varchar, @File)
355print 'starting restore'
356EXEC (@query)
357print 'finished restore'
358END
359GO
360
361BACKUP DATABASE MyDB TO DISK='D:MyDB.bak'