Tag: Restore backup

Error : MDF file cannot be overwritten in SQL Server

Today one of my colleague asked me that he was trying to replace an existing database with another database, while trying to do so , he has encountered the below error , though he was using same restore scripts which were working with out any error  in another machine of same configuration.

SQL Scripts used:

USE master
BACKUP DATABASE [Test_master] TO DISK = N'C:\Backups\Test_master.bak' WITH NOFORMAT, INIT, NAME = N'Test_master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,STATS = 10

ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE [Test] FROM DISK = N'C:\Backups\Test_master.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

Error Message:


Msg 1834, Level 16, State 1, Line 6 
The file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test_master.mdf' cannot be overwritten. It is being used by database 'Test_master'.

Msg 3156, Level 16, State 4, Line 6 File 'Test_master' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test_master.mdf'. 
Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 6
The file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test_master_log.ldf' cannot be overwritten. It is being used by database 'Test_master'.
Msg 3156, Level 16, State 4, Line 6 File 'Test_master_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test_master_log.ldf'.
 Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 6 Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 6
RESTORE DATABASE is terminating abnormally.

Solution:

As the Error message clearly says that restore should happen WITH MOVE. We were able to restore Successfully by using WITH MOVE.

RESTORE DATABASE [Test] FROM DISK = N'C:\Backups\Test_master.bak' 
WITH MOVE 'Test_master' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test.mdf', 
MOVE 'Test_master_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test_log.ldf', REPLACE, STATS = 10;

Output of this query:


14 percent processed.
23 percent processed. 
32 percent processed. 
42 percent processed. 
51 percent processed. 
61 percent processed. 
70 percent processed. 
80 percent processed. 
94 percent processed. 
100 percent processed. 
Processed 168 pages for database 'Test', file 'Test_master' on file 1. Processed 2 pages for database 'Test', file 'Test_master_log' on file 1. RESTORE DATABASE successfully processed 170 pages in 0.023 seconds (57.659 MB/sec).
To answer the question of my colleague on why he didn’t receive the same error on another machine with same set of scripts.

lets try with same scripts which he has used. i.e. WITH OUT using MOVE

USE master BACKUP DATABASE [Test_master] TO DISK = N'C:\Backups\Test_master.bak' WITH NOFORMAT, INIT, NAME = N'Test_master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,STATS = 10 

ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

RESTORE DATABASE [Test] FROM DISK = N'C:\Backups\Test_master.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

As we could see that we are able to restore with No Error 🙂


4 percent processed.
23 percent processed. 
33 percent processed. 
42 percent processed.
52 percent processed.
61 percent processed.
71 percent processed.
80 percent processed. 
94 percent processed.
Processed 168 pages for database 'Test_master', 
file 'Test_master' on file 1. 100 percent processed.
Processed 1 pages for database 'Test_master', file 'Test_master_log' on file 1. BACKUP DATABASE successfully processed 169 pages in 0.021 seconds (62.872 MB/sec). 
14 percent processed. 
23 percent processed. 
33 percent processed. 
42 percent processed. 
52 percent processed. 
61 percent processed. 
80 percent processed. 
94 percent processed. 
100 percent processed. 
Processed 168 pages for database 'Test', file 'Test_master' on file 1. Processed 1 pages for database 'Test', file 'Test_master_log' on file 1. RESTORE DATABASE successfully processed 169 pages in 0.021 seconds (62.872 MB/sec).
Conclusion:

As long as the database being restored already has the same number and type of files to match those contained in the backup set being restored, the MOVE clause is not required.

T-SQL Script to restore backup files from a folder in SQL Server

Very often, myself or my friends were in need of a script to restore the backups from a predefined folder. It was hard to write the script in the hour of need. Hence, I thought I will share the script that has been created recently. Hope this script would be useful for you as well.



DECLARE @BACKUPFILENAME VARCHAR(MAX) = ''
DECLARE @MDFLOGICALNAME NVARCHAR(100)
DECLARE @LDFLOGICALNAME NVARCHAR(100)
DECLARE @DEST_PATH NVARCHAR(100)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @DATABASENAME SYSNAME

DROP TABLE IF EXISTS #TFILELISTONLY,#TRAILUPGRADEBACKUPFILES

CREATE TABLE #TRAILUPGRADEBACKUPFILES (ID INT IDENTITY(1,1),FILENAME SYSNAME,RESTORE_DRIVE CHAR(1))

/*********************************************************************************************************************************/
--The below code fetches the file list(backup file names) to be restored from the pre-defined folder
DECLARE @CommandShell TABLE ( Line VARCHAR(512)) 
DECLARE  @CMD VARCHAR(512) ,@BackupFilePath NVARCHAR(256)
SET @BackupFilePath = 'C:\' 
SET @CMD = 'DIR /B ' + @BackupFilePath +  ' /TC' 
				
INSERT INTO @CommandShell EXEC MASTER..xp_cmdshell   @CMD 
    
-- Delete lines not containing filename
DELETE 
FROM   @CommandShell 
WHERE  Line is null

If((Select Top 1 Line From @CommandShell) = 'Access is denied.') 
    PRINT 'Folder access is not provided for network path'
				
INSERT INTO #TRAILUPGRADEBACKUPFILES (FILENAME)
	Select * From @CommandShell A WHERE CHARINDEX('.BAK',LINE) > 0

	
--Cleanup of system databases
Delete From #TRAILUPGRADEBACKUPFILES where Filename in( 'master.bak','msdb.bak','model.bak','tempdb.bak','resource.bak'
--Add if you have any special list to be ignored (examples as below)
--,'DBAMaintenance.bak','DBATools.bak'
)

/*********************************************************************************************************************************/

CREATE TABLE #TFILELISTONLY 
(
    TLOGINNAME SYSNAME,TPHYSICALNAME VARCHAR(MAX),
    TTYPE VARCHAR(1),TFILEGROUPNAME  VARCHAR(MAX),
    TSIZE BIGINT,TMAXSIZE BIGINT,TFIELD  VARCHAR(MAX),
    TCREATELSN VARCHAR(MAX),TDROPLSN VARCHAR(MAX),
    TUNIQUEID VARCHAR(MAX),READONLYLSN VARCHAR(MAX),
    READWRITELSN VARCHAR(MAX),BACKUPSIZEINBYTES VARCHAR(MAX),
    SOURCEBLOCKSIZE VARCHAR(MAX),FILEGROUPID VARCHAR(MAX),
    LOGGROUPGUID VARCHAR(MAX),DIFFERENTIALLSN VARCHAR(MAX),
    DIFFERENTIALBASEGUID VARCHAR(MAX),ISREADONLY VARCHAR(MAX),
    ISPRESENT VARCHAR(MAX),TDEHUMBPRINT VARCHAR(MAX),
    SNAPSHORTURL VARCHAR(MAX) -- Add this for higher version of SQL Server
)

WHILE EXISTS(SELECT 1 FROM #TRAILUPGRADEBACKUPFILES)
BEGIN
	
	SET @BACKUPFILENAME = (SELECT TOP 1 FILENAME FROM #TRAILUPGRADEBACKUPFILES ORDER BY FILENAME ASC)
	--Restore Path needs to be provided in the below
	SET @DEST_PATH = 'C:\DATA\'
		
	SET @DATABASENAME = (SUBSTRING(@BACKUPFILENAME,0,CHARINDEX('.',@BACKUPFILENAME)))
				
	--To check if the database is already present in the environment, If present, Drop the database
	IF Exists(SELECT 1 FROM master.sys.databases WHERE name = @DATABASENAME)
	BEGIN
	
		EXEC('DROP Database '+@DATABASENAME)
		Print @BACKUPFILENAME + ' has been successfully dropped.'
		
	END

	INSERT INTO #TFILELISTONLY
		EXEC ('RESTORE FILELISTONLY FROM DISK ='''+ @BackupFilePath +  @BACKUPFILENAME+'''')

	SET @MDFLOGICALNAME = (SELECT TLOGINNAME FROM #TFILELISTONLY WHERE TTYPE ='D')
	SELECT @LDFLOGICALNAME = (SELECT TLOGINNAME FROM #TFILELISTONLY WHERE TTYPE ='L')
        
	SELECT @SQL ='RESTORE DATABASE ' + @DATABASENAME + ' FROM DISK = ''' +  @BackupFilePath + @BACKUPFILENAME + ''' 
		WITH MOVE ''' + @MDFLOGICALNAME + ''' TO ''' + @DEST_PATH + @DATABASENAME + '.MDF'', 
		MOVE '''  + +  @LDFLOGICALNAME + ''' TO ''' + @DEST_PATH + @DATABASENAME + '.LDF'''
	
	EXEC (@SQL)
	
	Print @BACKUPFILENAME + ' has been successfully restored.'
		
	EXEC ('ALTER DATABASE [' + @DATABASENAME + '] SET RECOVERY SIMPLE;')

	DELETE FROM #TFILELISTONLY


	DELETE FROM #TRAILUPGRADEBACKUPFILES WHERE FILENAME =@BACKUPFILENAME	

END

How to identify Restore details in SQL Server

Problem Statement:

Today, one of my colleague was looking for a way to identify the backup file path for one his restored databases. The below script would help you in similar situation.

Script:


DECLARE @dbname sysname, @days int
SET @dbname = NULL --provide database name you want
--number of days since need to check, script will default to 25
SET @days = -25 
SELECT
     rs.destination_database_name AS [Database],
     rs.user_name AS [Restored By],
     CASE WHEN rs.restore_type = 'D' THEN 'Database'
      WHEN rs.restore_type = 'F' THEN 'File'
      WHEN rs.restore_type = 'G' THEN 'Filegroup'
      WHEN rs.restore_type = 'I' THEN 'Differential'
      WHEN rs.restore_type = 'L' THEN 'Log'
      WHEN rs.restore_type = 'V' THEN 'Verifyonly'
      WHEN rs.restore_type = 'R' THEN 'Revert'
     ELSE rs.restore_type 
     END AS [Restore Type],
     rs.restore_date AS [Restore Started],
     bmf.physical_device_name AS [Restored From], 
     rf.destination_phys_name AS [Restored To]
FROM msdb.dbo.restorehistory rs
 INNER JOIN msdb.dbo.backupset bs 
     ON rs.backup_set_id = bs.backup_set_id
 INNER JOIN msdb.dbo.restorefile rf 
     ON rs.restore_history_id = rf.restore_history_id
 INNER JOIN msdb.dbo.backupmediafamily bmf 
     ON bmf.media_set_id = bs.media_set_id
WHERE rs.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) 
     AND destination_database_name = ISNULL(@dbname, destination_database_name) 
ORDER BY rs.restore_history_id DESC