Batch file SQLCMD Restore Loop - Files Disappear

Question

So I've searched and found a lot of info in similar areas, but nothing that quite hits the issue I'm having.

I'm working in a test environment and need to restore from the same SQL database backup (.bak) to many named instances of SQL server. All the sql instances are pre-installed and running.

I played around with different ideas, but a batch file using sqlcmd seems to be the best suited for the job.

So I created a batch file (.bat) that asks for the starting and stopping instance numbers and then should restore from the backup to each SQL named instance incrementing the instance number along the way.

When it runs the sqlcmd appears to work fine. At the end it prints out

RESTORE DATABASE successfully processed X pages in Y seconds 

Also the files (.mdf, .ndf, .ldf) are in the directory as expected and then it moves on to the next.

The problem is that when it moves on to the next, the files that were just restored disappear from the directory.

If anyone has any ideas it would certainly be appreciated.

Here's the batch...

ECHO OFF
ECHO Enter starting instance number for restore db
SET /P _IntStart=


ECHO Enter number of last instance for restore db
SET /P _IntStop=
SET /a _IntStop=_IntStop+1



:RestoreDb
If %_IntStart% GEQ %_IntStop% goto:EOF

ECHO Display Instance Number... IntStart = %_IntStart%

sqlcmd -e -s localhost\instance%_IntStart% -d master -U user -P password -Q "Use [master]; RESTORE DATABASE DBName1 FROM DISK = 'C:\DBName1.bak'WITH REPLACE, MOVE 'DBName1' TO 'E:\Microsoft SQL Server\MSSQL10_50.INSTANCE%_IntStart%\MSSQL\DATA\DBName1.mdf', MOVE 'DBName1_log' TO 'E:\Microsoft SQL Server\MSSQL10_50.INSTANCE%_IntStart%\MSSQL\DATA\DBName1_log.LDF', MOVE 'ftrow_DBName1Catalog' TO 'E:\Microsoft SQL Server\MSSQL10_50.INSTANCE%_IntStart%\MSSQL\DATA\DBName1_1.ndf';"

SET /a _IntStart=_IntStart+1


GOTO:RestoreDb

PAUSE
EXIT

=========================================

From SQL Mgmt. Studio I've also tried the below. It works if I comment out the loop and run it each time manually bumping the instance number. It will create separate copies of the db and files. The problem here is SQLCMD doesn't appear to like concatenation in Mgmt. Studio so I can't increment the instance number in the :CONNECT. It ends up trying to connect to localhost\instance$(SCintnum).

Declare @intnum int
Set @intnum = 1

Declare @intstr NVARCHAR(255)
Set @intstr = @intnum

Declare @PathName1 NVARCHAR(255)
Declare @PathName2 NVARCHAR(255)
Declare @PathName3 NVARCHAR(255)

Set @PathName1 = 'E:\Microsoft SQL Server\MSSQL10_50.INSTANCE' + @intstr + '\MSSQL\DATA\DBName1.mdf'
Set @PathName2 = 'E:\Microsoft SQL Server\MSSQL10_50.INSTANCE' + @intstr + '\MSSQL\DATA\DBName1_log.LDF'
Set @PathName3 = 'E:\Microsoft SQL Server\MSSQL10_50.INSTANCE' + @intstr + '\MSSQL\DATA\DBName1_1.ndf'

 While @intnum < 51

:SETVAR SCintnum 1
:CONNECT localhost\instance$(SCintnum) -U user -P password

Use [master];
RESTORE DATABASE DBName1 FROM DISK = 'C:\DBName1.bak'
WITH REPLACE, 
MOVE 'DBName1'              TO @PathName1,
MOVE 'DBName1_log'          TO @PathName2,
MOVE 'ftrow_DBName1Catalog' TO @PathName3;

:SETVAR SCintnum $(SCintum)+1
Set @intnum = @intnum+1
Set @intstr = @intnum
Set @PathName1 = 'E:\Microsoft SQL Server\MSSQL10_50.INSTANCE' + @intstr + '\MSSQL\DATA\DBName1.mdf'
Set @PathName2 = 'E:\Microsoft SQL Server\MSSQL10_50.INSTANCE' + @intstr + '\MSSQL\DATA\DBName1_log.LDF'
Set @PathName3 = 'E:\Microsoft SQL Server\MSSQL10_50.INSTANCE' + @intstr + '\MSSQL\DATA\DBName1_1.ndf'

===================================================================

This is an example of what I ended up using in c#.....

///Set SQL Connection
SqlConnection myConnection = new SqlConnection("user id=sa;" + 
                                   "password="+ sapassword+";server="+servername+"\\instance"+currentinstancenum+";" + 
                                   "Trusted_Connection=yes;" + 
                                   "database=master; " + 
                                   "connection LifeTime=0; connection Timeout=30");
///Set SQL Command

string thesqlcommand = "USE [master]; RESTORE DATABASE " + dbname + " FROM DISK = '" + backuplocation + "' WITH REPLACE, MOVE '" + dbname + "' TO " + @PathName1 + ", MOVE '" + dbname + "_log' TO " + @PathName2 + ", MOVE 'ftrow_" + dbname + "Catalog' TO " + @PathName3 + ";";



SqlCommand myCommand = new SqlCommand(thesqlcommand, myConnection);



///Set SQL Command TimeOut, open connection, execute command, close command
myCommand.CommandTimeout = 180;
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();

Answer 1

Ended up creating a little utility in C# to do this. Wish I had started there as it was far simpler. I added an example to the bottom of the original post.