title: Batch file SQLCMD Restore Loop - Files Disappear
body: <p>So I've searched and found a lot of info in similar areas, but nothing that quite hits the issue I'm having.</p>
  
  <p>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.</p>
  
  <p>I played around with different ideas, but a batch file using sqlcmd seems to be the best suited for the job.</p>
  
  <p>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.</p>
  
  <p>When it runs the sqlcmd appears to work fine. At the end it prints out</p>
  
  <pre><code>RESTORE DATABASE successfully processed X pages in Y seconds 
  </code></pre>
  
  <p>Also the files (.mdf, .ndf, .ldf) are in the directory as expected and then it moves on to the next. </p>
  
  <p>The problem is that when it moves on to the next, the files that were just restored disappear from the directory.</p>
  
  <p>If anyone has any ideas it would certainly be appreciated.</p>
  
  <p>Here's the batch...</p>
  
  <pre><code>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
  </code></pre>
  
  <p>=========================================</p>
  
  <p>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).</p>
  
  <pre><code>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 &lt; 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'
  </code></pre>
  
  <p>===================================================================</p>
  
  <p>This is an example of what I ended up using in c#.....</p>
  
  <pre><code>///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();
  </code></pre>