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.

you are trying to rename `ugocity1_log.mdf` to `ugocitylog.ldf` (?!) Maybe file with that name already exists — Nenad Zivkovic, Dec 28 '17 at 15:17

score 1 · Answer 1 · answered Dec 28 '17 at 16:42

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.

answered Dec 28 '17 at 16:42

SMor

2,830
4
11
14

Because it has to be done automatically, I know I can do it through restore but then someone would have to log on to server and do it every month. And nobody wants to do that so.. – Dec 28 '17 at 19:18
And you can do it automatically - you just aren't thinking about it correctly. The restore command is as programmable and "automatible" as your convoluted script - with far fewer ways to mess up the process. The suggestion to use SSMS was to simplify your writing of the required code. – SMor Dec 28 '17 at 22:08
Ok, ok, I found what you were talking about. The first time I tried to do it with restore it kept returning errors because I had no idea I had to put 'WITH FILE = 2' before MOVE. This is like the first time ever I'm working with SQL server so I know it's messy and complicated. Thank you, it actually works now. – Dec 29 '17 at 07:34

SQL Server database reattachment error (not a permission issue)

1 Answers1