SQL Server database reattachment error (not a permission issue)

Question

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.

I need to create a scheduled job that will copy and rename a database on 1st Jan so nobody needs to do it manually.

This is the code that works perfectly:

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

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:

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)

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:

Msg 5120, Level 16, State 101, Line 1
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.)"

Permissions are, literally, full control.

Has anybody else encountered this error? I just can't figure it out.

Answer 1

Yes - the problem cannot possibly be in your code so look elsewhere. You have 5 different command strings. Which ones do you actually execute? But why bother with complicated scripting that you are too impatient to debug. You can accomplish everything you need to do using the restore command. That includes changing the database name and renaming the physical files. SSMS will do this for you and you can review/use the script it generates.

In the general page, choose "device" and select the backup file. Specify a new name for the database in the destination. In the files page, change the physical file names for all of the actual files in the restore as list. Then click on the Script button at the top of the dialog rather than the OK button at the bottom.