title: SQL Server database reattachment error (not a permission issue)
body: <p>I'm stuck on trying to figure out this error, with absolutely no luck whatsoever. I've already went through the SO, found some bits and pieces to help me figure this out.</p>
  
  <p>I need to create a scheduled job that will copy and rename a database on 1st Jan so nobody needs to do it manually. </p>
  
  <p>This is the code that works perfectly:</p>
  
  <pre><code>EXEC sp_renamedb 'ugocity', 'ugocity1'
  GO
  
  EXEC sp_detach_db @dbname = 'ugocity1'
  GO
  
  EXEC sp_configure 'show advanced options' , 1
  RECONFIGURE
  GO
  EXEC sp_configure 'xp_cmdshell' , 1
  RECONFIGURE
  GO
  EXEC xp_cmdshell
      'md C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\'
  GO
  EXEC xp_cmdshell
      'RENAME "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ugocity2017.mdf", "ugocity1.mdf"'
  GO
  EXEC xp_cmdshell
      'RENAME "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ugocity2017_1.ldf", "ugocity1_log.ldf"'
  GO
  
  EXEC sp_configure 'xp_cmdshell' , 0
  RECONFIGURE
  GO
  EXEC sp_configure 'show advanced options' , 0
  RECONFIGURE
  GO
  
  EXEC sp_attach_db @dbname = 'ugocity1'
  , @filename1 = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ugocity1.mdf'
  , @filename2 = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ugocity1_log.ldf'
  
  GO
  </code></pre>
  
  <p>However, the new database name needs to contain year. So, it should be e.g. 'ugocity2017'. So, after searching through net for three hours I came up with this:</p>
  
  <pre><code>DECLARE @newname varchar(500), @newmdf varchar(500), @newldf varchar (500), @yearnum int, @prevyear int,
      @command1 varchar(max), @command2 varchar(max), @command3 varchar(max), @command4 varchar(max), @command5 varchar(max)
  
      SET @yearnum = YEAR(GETDATE())
      SET @prevyear = @yearnum - 2000
  
      SET @newname = 'ugocity' + CONVERT(varchar(4),@prevyear)
      SET @newmdf = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\' + @newname +'.mdf'
      SET @newldf = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ugocitylog.ldf'
  
      SET @command1 = 'sp_renamedb ''ugocity2017'', ''' + @newname + ''''
      SET @command2 = 'xp_cmdshell ''RENAME "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ugocity2017.mdf", "' + @newname + '.mdf"'''
      SET @command3 = 'xp_cmdshell ''RENAME "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ugocity1_log.mdf", "ugocitylog.ldf"'''
      SET @command4 = 'sp_detach_db @dbname = ''' + @newname + ''''
      SET @command5 = 'sp_attach_db @dbname = ''' + @newname + ''' , ' + ' @filename1 = ''' + @newmdf + ''' , ' + '@filename2 = ''C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ugocitylog.ldf'''
  
      EXECUTE (@command1)
  
      USE [master]
  
      EXECUTE (@command4)
  
      EXECUTE sp_configure 'show advanced options' , 1
      RECONFIGURE
  
      EXECUTE sp_configure 'xp_cmdshell' , 1
      RECONFIGURE
  
      EXECUTE (@command2)
  
      EXECUTE xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ugocity1_log.mdf", "ugocitylog.ldf"'
  
      EXECUTE sp_configure 'xp_cmdshell' , 0
      RECONFIGURE
  
      EXECUTE sp_configure 'show advanced options' , 0
      RECONFIGURE
  
      EXECUTE (@command5)
  </code></pre>
  
  <p>When I run the first one, the database and its physical files get renamed with no error. When I run the second code, the database and .mdf file get renamed, but .ldf remains as it was and  I get: </p>
  
  <blockquote>
    <p>Msg 5120, Level 16, State 101, Line 1<br>
    Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ugocitylog.ldf". Operating system error 2: "2(The system cannot find the file specified.)"</p>
  </blockquote>
  
  <p>Permissions are, literally, full control. </p>
  
  <p>Has anybody else encountered this error? I just can't figure it out.</p>