November 10, 2016 at 9:16 am
Can anyone please give some assistance, I keep getting Logical file 'LIVE_OPERATIONS_BA' is not part of database 'DupilcateErrorTest'. as an error when running this, not sure how to solve it. SQL2012
declare @chkdirectory as nvarchar(4000)
declare @folder_exists as int
DECLARE @RestorePoint smalldatetime = '1900-01-01 00:00:00'
DECLARE @DEBUG BIT = 1
BEGIN
SET NOCOUNT ON;
DECLARE @DBNameVARCHAR(200)
DECLARE @NewDBNameVARCHAR(200)
DECLARE @filenameVARCHAR(200)
DECLARE @TSQLNVARCHAR(2000)
DECLARE @Startdatetime
DECLARE @TimeStampVARCHAR(15)
DECLARE @BodyVARCHAR(1000)
DECLARE @SubjectVARCHAR(200)
DECLARE @backup_filenameVARCHAR(200)
DECLARE @BUTypeVARCHAR(5)
DECLARE @ToAddressVARCHAR(500)
SET @ToAddress = 'admin@email.com'
SET @DBName = 'LIVE_OPERATIONS_BA'
SET @NewDBName = 'DupilcateErrorTest'
declare @file_results table
(file_exists int,
file_is_a_directory int,
parent_directory_exists int
)
/* Added for testing */
IF @DEBUG = 1
BEGIN
SET @ToAddress = 'admin@email.com'
SET @NewDBName = 'DupilcateErrorTest'
END
SET @Start = GETDATE()
SET @TimeStamp = (SELECT REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(30),@Start,120),':',''),'-',''),' ','_'))
IF @RestorePoint = '1900-01-01 00:00:00'
BEGIN
SET @TSQL = 'BACKUP DATABASE ['+@DBName+']
TO DISK = N''\\vbackups\sqltest$\SQLBackups\DEVELOPMENT_REFRESH\'+@DBName+'_'+@TimeStamp+'.bak''
WITH COMPRESSION
, COPY_ONLY
, NOFORMAT
, NOINIT
, NAME = N'''+@DBName+'-Full Database Backup''
, SKIP
, NOREWIND
, NOUNLOAD
, STATS = 10;'
EXEC sp_executesql @TSQL
PRINT @TSQL
PRINT 'Backup ' + @DBName + ' database completed in approximately ' + CAST(DATEDIFF(mi,@Start,GETDATE()) as VARCHAR(5)) + ' minutes.'
RESTORE DATABASE LIVE_OPS_CBRAM_TEST
FROM DISK = 'C:\cbrammer_test_bak/locbramm.bak'
WITH REPLACE,
MOVE 'LIVE_OPS_CBRAM' TO 'C:\cbrammer_test_bak\files\locbramm.mdf',
MOVE 'LIVE_OPS_CBRAM_log' TO 'C:\cbrammer_test_bak\files\locbramm_log.ldf';
*/
SET @filename = @DBName
SET @Start = GETDATE()
SET @TSQL = 'ALTER DATABASE ['+@NewDBName+'] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE ['+@NewDBName+']
FROM DISK = N''\\vbackups\sqltest$\SQLBackups\DEVELOPMENT_REFRESH\'+@DBName+'_'+@TimeStamp+'.bak''
WITH MOVE N''' + @filename + ''' TO N''\\vbackups\sqltest$\SQLBackups\DEVELOPMENT_REFRESH\DB_Data\'+@NewDBName+'.mdf''
, MOVE N''' + @filename + '_log'' TO N''\\vbackups\sqltest$\SQLBackups\DEVELOPMENT_REFRESH\DB_Log\'+@NewDBName+'.ldf''
, NOUNLOAD
, REPLACE
, STATS = 5;'
EXEC sp_executesql @TSQL
PRINT @TSQL
WAITFOR DELAY '00:00:10'
PRINT 'Restore new copy of '+@DBName+' on completed in approximately ' + CAST(DATEDIFF(mi,@Start,GETDATE()) as VARCHAR(5)) + ' minutes.'
SET @Body = @NewDBName + ' DB on devsql01 has been refreshed from a backup of the production ' + @DBName + ' database at ' + CAST(@Start as VARCHAR(50)) + '. The entire process completed in approximately ' + CAST(DATEDIFF(mi,@Start,GETDATE()) as VARCHAR(5)) + ' minutes.'
END
ELSE
BEGIN
/* Drop TempTableRestore Table if it exists. */
IF OBJECT_ID('tempdb..#refreshDEV_DB') IS NULL
BEGIN
CREATE TABLE #refreshDEV_DB (
AutoID INT IDENTITY(1,1)
, database_name varchar(100)
, backup_filename varchar(200)
, last_lsn numeric(25,0)
, BUType Varchar(1)
, backup_finish_date datetime);
END
INSERT INTO #refreshDEV_DB
/* Tlog Backups */
SELECT bs.[database_name], bmf.[physical_device_name], bs.[last_lsn], bs.[type], bs.[backup_finish_date]
FROM [msdb].[dbo].[backupset] bs
JOIN [msdb].[dbo].[backupmediafamily] bmf
ON bs.[media_set_id] = bmf.[media_set_id]
WHERE bs.[database_name] = @DBName
AND (bs.[type] = 'L')
AND bs.[backup_start_date] > (
SELECT max([backup_start_date])
FROM [msdb].[dbo].[backupset] bs
WHERE [database_name] = @DBName
AND ([type] = 'D' OR [type] = 'I')
AND [backup_start_date] < @RestorePoint)
AND [backup_start_date] < @RestorePoint
OR (bs.[backup_finish_date] = (
SELECT Top 1 bs.[backup_finish_date]
FROM [msdb].[dbo].[backupset] bs
JOIN [msdb].[dbo].[backupmediafamily] bmf
ON bs.[media_set_id] = bmf.[media_set_id]
WHERE bs.[database_name] = @DBName
AND (bs.[type] = 'L')
AND bs.[backup_start_date] > (
SELECT max([backup_start_date])
FROM [msdb].[dbo].[backupset] bs
WHERE bs.[database_name] = @DBName
AND ([type] = 'D' OR [type] = 'I')
AND [backup_start_date] < @RestorePoint)
AND [backup_start_date] > @RestorePoint
ORDER BY [backup_start_date]) AND bs.[database_name] = @DBName)
UNION
/* Full Backup */
SELECT bs.[database_name], bmf.[physical_device_name], bs.[last_lsn], bs.[type], bs.[backup_finish_date]
FROM [msdb].[dbo].[backupset] bs
JOIN [msdb].[dbo].[backupmediafamily] bmf
ON bs.[media_set_id] = bmf.[media_set_id]
WHERE bs.[database_name] = @DBName
AND (bs.[type] = 'D')
AND bs.[backup_start_date] = (
SELECT max([backup_start_date])
FROM [msdb].[dbo].[backupset] bs
WHERE [database_name] = @DBName
AND [type] = 'D'
AND [is_copy_only] = 0
AND [backup_start_date] < @RestorePoint)
UNION
/* Diff Backup */
SELECT bs.[database_name], bmf.[physical_device_name], bs.[last_lsn], bs.[type], bs.[backup_finish_date]
FROM [msdb].[dbo].[backupset] bs
JOIN [msdb].[dbo].[backupmediafamily] bmf
ON bs.[media_set_id] = bmf.[media_set_id]
WHERE bs.[database_name] = @DBName
AND (bs.[type] = 'I')
AND bs.[backup_start_date] = (
SELECT max([backup_start_date])
FROM [msdb].[dbo].[backupset] bs
WHERE [database_name] = @DBName
AND [type] = 'I'
AND [backup_start_date] < @RestorePoint)
AND bs.[backup_start_date] > (
SELECT max([backup_start_date])
FROM [msdb].[dbo].[backupset] bs
WHERE [database_name] = @DBName
AND [type] = 'D'
AND [backup_start_date] < @RestorePoint)
ORDER BY bs.[backup_finish_date]
/* Delete any Tlogs between Full and Diff backup */
IF EXISTS (
SELECT 1
FROM #refreshDEV_DB
WHERE BUType='L'
AND backup_finish_date
BETWEEN (select backup_finish_date from #refreshDEV_DB WHERE BUType='D')
AND (select backup_finish_date from #refreshDEV_DB WHERE BUType='I'))
BEGIN
DELETE FROM #refreshDEV_DB
WHERE BUType='L'
AND backup_finish_date
BETWEEN (select backup_finish_date from #refreshDEV_DB WHERE BUType='D')
AND (select backup_finish_date from #refreshDEV_DB WHERE BUType='I')
END
/* Restore Database from backups */
DECLARE c1 CURSOR FOR SELECT [backup_filename], [BUType] FROM #refreshDEV_DB order by backup_finish_date
OPEN c1
FETCH NEXT FROM c1 INTO @backup_filename, @BUType
WHILE @@fetch_status <> -1
BEGIN
IF @BUType = 'D'
SET @TSQL = 'ALTER DATABASE ['+@NewDBName+'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE ['+@NewDBName+']
FROM DISK = N'''+@backup_filename+'''
WITH FILE = 1
, MOVE N''' + @filename + ''' TO N''\\vbackups\sqltest$\SQLBackups\DEVELOPMENT_REFRESH\DB_Data\'+@NewDBName+'.mdf''
, MOVE N''' + @filename + '_log'' TO N''\\vbackups\sqltest$\SQLBackups\DEVELOPMENT_REFRESH\DB_Log\'+@NewDBName+'.ldf''
, NORECOVERY
, NOUNLOAD
, REPLACE
, STATS = 5;'
IF @BUType = 'I'
SET @TSQL = 'RESTORE DATABASE ['+@NewDBName+']
FROM DISK = N'''+@backup_filename+'''
WITH FILE = 1
, NORECOVERY
, NOUNLOAD
, STATS = 5;'
IF @BUType = 'L'
SET @TSQL = 'RESTORE LOG ['+@NewDBName+']
FROM DISK = N'''+@backup_filename+'''
WITH FILE = 1
, NOUNLOAD
, STATS = 5
, STOPAT = N'''+CONVERT(NVARCHAR(30),@RestorePoint,9)+''';'
EXEC sp_executesql @TSQL
PRINT @TSQL
FETCH NEXT FROM c1 INTO @backup_filename, @BUType
END
CLOSE c1
DEALLOCATE c1
IF OBJECT_ID('tempdb..#refreshDEV_DB') IS NOT NULL
BEGIN
DROP TABLE #refreshDEV_DB
END
SET @Body = @NewDBName + ' DB on wv-sql03 has been refreshed from a backup of the production ' + @DBName + ' database at ' + CAST(@RestorePoint as VARCHAR(50)) + '. The entire process completed in approximately ' + CAST(DATEDIFF(mi,@Start,GETDATE()) as VARCHAR(5)) + ' minutes.'
END
/* Backup new database to start backup chain */
DECLARE @BackupShare NVARCHAR(200)
SET @BackupShare = (SELECT ItemValue FROM DBATools.dbo.Settings WHERE ItemName='BackupShare' and UseIt=1)
EXECUTE [DBATools].[dbo].[DatabaseBackup]
@databases = @NewDBName
, @Directory = @BackupShare
, @BackupType = 'FULL' /* FULL,DIFF,LOG */
, @verify = 'Y'
, @checksum = 'Y'
, @LogToTable = 'Y'
, @Compress = 'Y'
, @ChangeBackupType = 'Y' /* Will change the backup type if a Diff or Tlog cannot be performed */
, @CopyOnly = 'N' /* will use WITH COPY_ONLY in backup command keeping the backup chain in tact */
END
November 10, 2016 at 9:57 am
my initial guess is that the problem is in this part of your code:
SET @filename = @DBName
SET @Start = GETDATE()
SET @TSQL = 'ALTER DATABASE ['+@NewDBName+'] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE ['+@NewDBName+']
FROM DISK = N''\\vbackups\sqltest$\SQLBackups\DEVELOPMENT_REFRESH\'+@DBName+'_'+@TimeStamp+'.bak''
WITH MOVE N''' + @filename + ''' TO N''\\vbackups\sqltest$\SQLBackups\DEVELOPMENT_REFRESH\DB_Data\'+@NewDBName+'.mdf''
, MOVE N''' + @filename + '_log'' TO N''\\vbackups\sqltest$\SQLBackups\DEVELOPMENT_REFRESH\DB_Log\'+@NewDBName+'.ldf''
, NOUNLOAD
, REPLACE
, STATS = 5;'
you set @filename to @DBName which was 'LIVE_OPERATIONS_BA' above. Then in building your RESTORE command, you're esentially saying:
MOVE 'LIVE_OPERATIONS_BA' TO DupilcateErrorTest.mdf
MOVE 'LIVE_OPERATIONS_BA_log' TO DupilcateErrorTest.ldf
the MOVE part of the RESTORE command needs the logical name of the file. Run this in your LIVE_OPERATIONS_BA database and use its Name column:
SELECT * FROM sys.database_files
there's also an extra */ after the RESTORE DATABASE LIVE_OPS_CBRAM_TEST command
November 10, 2016 at 10:11 am
That fixed it, I was trying to use a copy of LIVE_OPERATIONS, which is used to refresh data on LIVE_OPERATIONS_BA. So I thought I could use the LIVE_OPERATIONS_BA to use that back to create the restores. But since it is using the mdf and ldf of LIVE_OPERATIONS, which it's mdf,ldf are imbedded in the bak file it can't do that? So then how do you use, prod to Dev then dev-test? This is what I'm trying to do, is use this to refresh data throughout the environments.
November 10, 2016 at 11:32 am
the MDF and LDF files aren't the problem those are the physical names, the problem is that each file in SQL Server also has a logical name. These logical names will be the same even if you restore them to a different database with different physical file names.
If you don't know what the logical names are within a backup and want to see them, you can do something like:
RESTORE FILELISTONLY FROM DISK = N'\\vbackups\sqltest$\SQLBackups\DEVELOPMENT_REFRESH\LIVE_OPERATIONS_BA.bak'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply