0

i am new as DBA. i want to migrate db, can i do one time alter for all data ?. or must one alter to one data ?

this my query.

DECLARE @name VARCHAR(50) 

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('db1','db2')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
    BEGIN
        
        --PRINT (
        EXECUTE(
        'ALTER DATABASE ' + @name + '
        MODIFY FILE (
        NAME = PSS4WMASTER , FILENAME = ''S:\Jubey\'+ @name +'.mdf'',
        NAME = PSS4WMASTER_log , FILENAME = ''S:\Jubey\'+ @name +'_64.ldf'',
        NAME = PSS4WMASTER10 , FILENAME = ''S:\Jubey\'+ @name +'_1.ndf'',
        NAME = PSS4WMASTER11 , FILENAME = ''S:\Jubey\'+ @name +'_2.ndf'',
        NAME = PSS4WMASTER12, FILENAME = ''S:\Jubey\'+ @name +'_3.ndf'',
        NAME = PSS4WMASTER13, FILENAME = ''S:\Jubey\'+ @name +'_4.ndf'',
        NAME = PSS4WMASTER14, FILENAME = ''S:\Jubey\'+ @name +'_5.ndf'',
        NAME = PSS4WMASTER15, FILENAME = ''S:\Jubey\'+ @name +'_6.ndf'',
        NAME = PSS4WMASTER16, FILENAME = ''S:\Jubey\'+ @name +'_7.ndf'',
        NAME = PSS4WMASTER17, FILENAME = ''S:\Jubey\'+ @name +'_8.ndf'',
        NAME = PSS4WMASTER18, FILENAME = ''S:\Jubey\'+ @name +'_9.ndf'',
        NAME = PSS4WMASTER19, FILENAME = ''S:\Jubey\'+ @name +'_10.ndf'',
        NAME = PSS4WMASTER2, FILENAME = ''S:\Jubey\'+ @name +'_11.ndf'',
        NAME = PSS4WMASTER20, FILENAME = ''S:\Jubey\'+ @name +'_12.ndf'',
        NAME = PSS4WMASTER21, FILENAME = ''S:\Jubey\'+ @name +'_13.ndf'',
        NAME = PSS4WMASTER22, FILENAME = ''S:\Jubey\'+ @name +'_14.ndf'',
        NAME = PSS4WMASTER23, FILENAME = ''S:\Jubey\'+ @name +'_15.ndf'',
        NAME = PSS4WMASTER24, FILENAME = ''S:\Jubey\'+ @name +'_16.ndf'',
        NAME = PSS4WMASTER25, FILENAME = ''S:\Jubey\'+ @name +'_17.ndf'',
        NAME = PSS4WMASTER26, FILENAME = ''S:\Jubey\'+ @name +'_18.ndf'',
        NAME = PSS4WMASTER27, FILENAME = ''S:\Jubey\'+ @name +'_19.ndf'',
        NAME = PSS4WMASTER28, FILENAME = ''S:\Jubey\'+ @name +'_20.ndf'',
        NAME = PSS4WMASTER29, FILENAME = ''S:\Jubey\'+ @name +'_21.ndf'',
        NAME = PSS4WMASTER3, FILENAME = ''S:\Jubey\'+ @name +'_22.ndf'',
        NAME = PSS4WMASTER30, FILENAME = ''S:\Jubey\'+ @name +'_23.ndf'',
        NAME = PSS4WMASTER31, FILENAME = ''S:\Jubey\'+ @name +'_24.ndf'',
        NAME = PSS4WMASTER32, FILENAME = ''S:\Jubey\'+ @name +'_25.ndf'',
        NAME = PSS4WMASTER33, FILENAME = ''S:\Jubey\'+ @name +'_26.ndf'',
        NAME = PSS4WMASTER34, FILENAME = ''S:\Jubey\'+ @name +'_27.ndf'',
        NAME = PSS4WMASTER35, FILENAME = ''S:\Jubey\'+ @name +'_28.ndf'',
        NAME = PSS4WMASTER36, FILENAME = ''S:\Jubey\'+ @name +'_29.ndf'',
        NAME = PSS4WMASTER37, FILENAME = ''S:\Jubey\'+ @name +'_30.ndf'',
        NAME = PSS4WMASTER38, FILENAME = ''S:\Jubey\'+ @name +'_31.ndf'',
        NAME = PSS4WMASTER39, FILENAME = ''S:\Jubey\'+ @name +'_32.ndf'',
        NAME = PSS4WMASTER4, FILENAME = ''S:\Jubey\'+ @name +'_33.ndf'',
        NAME = PSS4WMASTER40, FILENAME = ''S:\Jubey\'+ @name +'_34.ndf'',
        NAME = PSS4WMASTER41, FILENAME = ''S:\Jubey\'+ @name +'_35.ndf'',
        NAME = PSS4WMASTER42, FILENAME = ''S:\Jubey\'+ @name +'_36.ndf'',
        NAME = PSS4WMASTER43, FILENAME = ''S:\Jubey\'+ @name +'_37.ndf'',
        NAME = PSS4WMASTER44, FILENAME = ''S:\Jubey\'+ @name +'_38.ndf'',
        NAME = PSS4WMASTER45, FILENAME = ''S:\Jubey\'+ @name +'_39.ndf'',
        NAME = PSS4WMASTER46, FILENAME = ''S:\Jubey\'+ @name +'_40.ndf'',
        NAME = PSS4WMASTER47, FILENAME = ''S:\Jubey\'+ @name +'_41.ndf'',
        NAME = PSS4WMASTER48, FILENAME = ''S:\Jubey\'+ @name +'_42.ndf'',
        NAME = PSS4WMASTER49, FILENAME = ''S:\Jubey\'+ @name +'_43.ndf'',
        NAME = PSS4WMASTER5, FILENAME = ''S:\Jubey\'+ @name +'_44.ndf'',
        NAME = PSS4WMASTER50, FILENAME = ''S:\Jubey\'+ @name +'_45.ndf'',
        NAME = PSS4WMASTER51, FILENAME = ''S:\Jubey\'+ @name +'_46.ndf'',
        NAME = PSS4WMASTER52, FILENAME = ''S:\Jubey\'+ @name +'_47.ndf'',
        NAME = PSS4WMASTER53, FILENAME = ''S:\Jubey\'+ @name +'_48.ndf'',
        NAME = PSS4WMASTER54, FILENAME = ''S:\Jubey\'+ @name +'_49.ndf'',
        NAME = PSS4WMASTER55, FILENAME = ''S:\Jubey\'+ @name +'_50.ndf'',
        NAME = PSS4WMASTER56, FILENAME = ''S:\Jubey\'+ @name +'_51.ndf'',
        NAME = PSS4WMASTER57, FILENAME = ''S:\Jubey\'+ @name +'_52.ndf'',
        NAME = PSS4WMASTER58, FILENAME = ''S:\Jubey\'+ @name +'_53.ndf'',
        NAME = PSS4WMASTER59, FILENAME = ''S:\Jubey\'+ @name +'_54.ndf'',
        NAME = PSS4WMASTER6, FILENAME = ''S:\Jubey\'+ @name +'_55.ndf'',
        NAME = PSS4WMASTER60, FILENAME = ''S:\Jubey\'+ @name +'_56.ndf'',
        NAME = PSS4WMASTER61, FILENAME = ''S:\Jubey\'+ @name +'_57.ndf'',
        NAME = PSS4WMASTER62, FILENAME = ''S:\Jubey\'+ @name +'_58.ndf'',
        NAME = PSS4WMASTER63, FILENAME = ''S:\Jubey\'+ @name +'_59.ndf'',
        NAME = PSS4WMASTER64, FILENAME = ''S:\Jubey\'+ @name +'_60.ndf'',
        NAME = PSS4WMASTER7, FILENAME = ''S:\Jubey\'+ @name +'_61.ndf'',
        NAME = PSS4WMASTER8, FILENAME = ''S:\Jubey\'+ @name +'_62.ndf'',
        NAME = PSS4WMASTER9, FILENAME = ''S:\Jubey\'+ @name +'_63.ndf'')')
    
    FETCH NEXT FROM db_cursor INTO @name
    END

CLOSE db_cursor
DEALLOCATE db_cursor

i am sorry if my question is not complete. Thanks you.

1
  • Please, edit the question to add the error message.
    – Ronaldo
    Jun 23 '21 at 10:21
2

You can do it the way you suggest, but note that running the ALTER DATABASE... MODIFY FILE statements does not actually move the physical file, it simply updates the file location in the master database. To complete the process you need to take the database offline, physically move the files in the file system yourself, then bring the database back online.

This article is a good reference.

For this kind of task I'd prefer to use BACKUP followed by RESTORE .. WITH MOVE. But that's just a personal preference.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.