How to Copy a SQL Database With Partition and restore it in a Test Database

I have a stored procedure which will backup the SQL data base and restore it in a test Database. It was working fine, But When ever a database with Partition Its failing , Showing the follwing Error.

cannot be overwritten. It is being used by database 'OriginalDb'. Use WITH MOVE to identify a valid location for the file.

So on... Its having many transaction logs files So each partitions it showing the errors messages

Below is the working stored Procedure for Other DBs

***********************************************************
DECLARE   @SourceDatabaseName AS SYSNAME = 'orginalDb',

    @TargetDatabaseName AS SYSNAME = 'demo'
    declare @Disksize as bigint
    declare @Dbsize as bigint 
--========Checking the  C drive Size================
--==================================================
DBCC SHRINKDATABASE ('orginalDb', 10);
SELECT  @Disksize= available_bytes/1048576
FROM sys.master_files AS f CROSS APPLY 
  sys.dm_os_volume_stats(f.database_id, f.file_id)
group by volume_mount_point, total_bytes/1048576, 
  available_bytes/1048576 
--===============END=================================
--================DBSIZE=============================
SELECT @Dbsize = CONVERT(VARCHAR,SUM(size)*8/1024)
FROM        sys.databases   
JOIN        sys.master_files  
ON          sys.databases.database_id=sys.master_files.database_id   where sys.databases.name ='orginalDb'
GROUP BY    sys.databases.name  
ORDER BY    sys.databases.name  
--================= END =============================
--========================Condition to disk c drive is more than 10 GB size==========================================
IF @Disksize >@Dbsize 
begin 
-- ==================================================
-- Define path where backup will be saved
-- ==================================================
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = @SourceDatabaseName)
    RAISERROR ('Variable @SourceDatabaseName is not set correctly !', 20, 1) WITH LOG       
DECLARE @SourceBackupFilePath varchar(2000)
SELECT @SourceBackupFilePath = BMF.physical_device_name
FROM
    msdb.dbo.backupset B
    JOIN msdb.dbo.backupmediafamily BMF ON B.media_set_id = BMF.media_set_id
WHERE B.database_name = @SourceDatabaseName
ORDER BY B.backup_finish_date DESC
SET @SourceBackupFilePath = REPLACE(@SourceBackupFilePath, '.bak', '_clone.bak')
-- ==================================================
-- Backup source database
-- ==================================================
DECLARE @Sql NVARCHAR(MAX) 
SET @Sql = 'BACKUP DATABASE @SourceDatabaseName TO DISK = ''@SourceBackupFilePath'''
SET @Sql = REPLACE(@Sql, '@SourceDatabaseName', @SourceDatabaseName)
SET @Sql = REPLACE(@Sql, '@SourceBackupFilePath', @SourceBackupFilePath)
SELECT 'Performing backup...', @Sql as ExecutedSql
EXEC (@Sql)
-- ==================================================
-- Automatically compose database files (.mdf and .ldf) paths
-- ==================================================
DECLARE
          @LogicalDataFileName as NVARCHAR(MAX)
        , @LogicalLogFileName as NVARCHAR(MAX)
        , @TargetDataFilePath as NVARCHAR(MAX)
        , @TargetLogFilePath as NVARCHAR(MAX)
SELECT
    @LogicalDataFileName = name,
    @TargetDataFilePath = SUBSTRING(physical_name,1,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))) + '\' + @TargetDatabaseName + '.mdf'
FROM sys.master_files
WHERE
    database_id = DB_ID(@SourceDatabaseName)        
    AND type = 0            -- datafile file
SELECT
    @LogicalLogFileName = name,
    @TargetLogFilePath = SUBSTRING(physical_name,1,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))) + '\' + @TargetDatabaseName + '.ldf'
FROM sys.master_files
WHERE
    database_id = DB_ID(@SourceDatabaseName)        
    AND type = 1            -- log file     
-- ============================================
-- Restore target database
-- ============================================
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @TargetDatabaseName)
    RAISERROR ('A database with the same name already exists!', 20, 1) WITH LOG        
SET @Sql = 'RESTORE DATABASE @TargetDatabaseName
FROM DISK = ''@SourceBackupFilePath'' 
WITH MOVE ''@LogicalDataFileName'' TO ''@TargetDataFilePath'',
MOVE ''@LogicalLogFileName'' TO ''@TargetLogFilePath''' 
SET @Sql = REPLACE(@Sql, '@TargetDatabaseName', @TargetDatabaseName)
SET @Sql = REPLACE(@Sql, '@SourceBackupFilePath', @SourceBackupFilePath)
SET @Sql = REPLACE(@Sql, '@LogicalDataFileName', @LogicalDataFileName)
SET @Sql = REPLACE(@Sql, '@TargetDataFilePath', @TargetDataFilePath)
SET @Sql = REPLACE(@Sql, '@LogicalLogFileName', @LogicalLogFileName)
SET @Sql = REPLACE(@Sql, '@TargetLogFilePath', @TargetLogFilePath)
SELECT 'Restoring...', @Sql as ExecutedSql
EXEC (@Sql)
end
else
print 'Restoring failed';

************************************************************************

Please copy this storedprocedure and check with partitioned Databases, (it wont work ) But working with other dbs .
Where I need to Make changes? Does it possible to restore a partioned DB to another DB ? Thanks in advance!

Solution

Your finally code should look like this:

RESTORE DATABASE TargetDatabaseName FROM DISK = '...' 
WITH MOVE 'LogicalDataFileName' TO 'D:\WINCAP\demo.mdf', 
     MOVE 'LogicalLogFileName' TO 'D:\WINCAP\demo.ldf', 
     MOVE 'Boces_ss2014_Part' TO 'D:\WINCAP\dem.ndf', 
     MOVE 'Boces_ss2015_Part' TO 'D:\WINCAP\demccc.ndf', 
     MOVE 'Boces_ss2016_Part' TO 'D:\WINCAP\demccdc.ndf', 
     MOVE 'Boces_ss2017_Part' TO 'D:\WINCAP\dems.ndf', 
     MOVE 'Boces_ss2018_Part' TO 'D:\WINCAP\de.ndf', 
     MOVE 'Boces_ssFinal_Part' TO 'D:\WINCAP\dems.ndf'

Your code is incorrect in some place but I have no idea where.

Maybe you miss a comma here before move if you pasted exactly the same code you executed:

''D:\WINCAP\dems.ndf'' MOVE

To find out where you should PRINT your code and check it before execution:

print @Sql

Once printed your code, copy-paste it in SSMS query window and parse it, if there is any error it will be evidenced by SSMS.

Only then do your exec(@Sql)