Restore database with move

  • Hi everybody,
    I have a script to restore a database with move. I do not have a database yet for this backup I want to restore so I want to create it with the restore. I keep getting a "RESTORE DATABASE is terminating abnormally." message. Can someone please help!


    RESTORE DATABASE N'MyDb' FROM DISK = N'C:\SomeFolder\SomeBackup.bak'
    WITH FILE = 1,
    MOVE N'MyDb_dat' TO N'C:\SomeFolder\MyDb.mdf',
    MOVE N'MyDb_log' TO N'C:\SomeFolder\MyDb_log.ldf,
    NOUNLOAD, NORECOVERY

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

  • Manie Verster - Monday, January 23, 2017 2:15 AM

    Hi everybody,
    I have a script to restore a database with move. I do not have a database yet for this backup I want to restore so I want to create it with the restore. I keep getting a "RESTORE DATABASE is terminating abnormally." message. Can someone please help!


    RESTORE DATABASE N'MyDb' FROM DISK = N'C:\SomeFolder\SomeBackup.bak'
    WITH FILE = 1,
    MOVE N'MyDb_dat' TO N'C:\SomeFolder\MyDb.mdf',
    MOVE N'MyDb_log' TO N'C:\SomeFolder\MyDb_log.ldf,
    NOUNLOAD, NORECOVERY

    Can you post the full error message please?
    😎

    First thought would be that either the "MyDB" or the actual files ( C:\SomeFolder\MyDb.mdf or C:\SomeFolder\MyDb_log.ldf ) already exists on the server, another possibility would be that the path C:\SomeFolder doesn't exist.

  • Is the backup file good ?
    Can you try
    RESTORE VERIFYONLY  FROM DISK = N'C:\SomeFolder\SomeBackup.bak'?

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Eirikur Eiriksson - Monday, January 23, 2017 2:25 AM

    Manie Verster - Monday, January 23, 2017 2:15 AM

    Hi everybody,
    I have a script to restore a database with move. I do not have a database yet for this backup I want to restore so I want to create it with the restore. I keep getting a "RESTORE DATABASE is terminating abnormally." message. Can someone please help!


    RESTORE DATABASE N'MyDb' FROM DISK = N'C:\SomeFolder\SomeBackup.bak'
    WITH FILE = 1,
    MOVE N'MyDb_dat' TO N'C:\SomeFolder\MyDb.mdf',
    MOVE N'MyDb_log' TO N'C:\SomeFolder\MyDb_log.ldf,
    NOUNLOAD, NORECOVERY

    Can you post the full error message please?
    😎

    First thought would be that either the "MyDB" or the actual files ( C:\SomeFolder\MyDb.mdf or C:\SomeFolder\MyDb_log.ldf ) already exists on the server, another possibility would be that the path C:\SomeFolder doesn't exist.

    Msg 50000, Level 16, State 1, Line 74

    RESTORE DATABASE is terminating abnormally.

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

  • joeroshan - Monday, January 23, 2017 2:27 AM

    Is the backup file good ?
    Can you try
    RESTORE VERIFYONLY  FROM DISK = N'C:\SomeFolder\SomeBackup.bak'?

    I will, thank you. Keep you posted.

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

  • Quick question, is there enough free space on the drive for the database?
    😎

  • Eirikur Eiriksson - Monday, January 23, 2017 2:36 AM

    Quick question, is there enough free space on the drive for the database?
    😎

    Yes, way enough!

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

  • joeroshan - Monday, January 23, 2017 2:27 AM

    Is the backup file good ?
    Can you try
    RESTORE VERIFYONLY  FROM DISK = N'C:\SomeFolder\SomeBackup.bak'?

    I did run the verify script and got the following message back:

    Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.

    The path specified by "C:\SQL\MSSQL11.ORISYS\MSSQL\DATA\MyDb.mdf" is not in a valid directory.

    Directory lookup for the file "C:\SQL\MSSQL11.ORISYS\MSSQL\DATA\MyDb_log.ldf" failed with the operating system error 3(The system cannot find the path specified.).

    The backup set on file 1 is valid.

    I do not understand the concern for storage space. I have enough disk space but maybe it refers to something else? I did copy the backup from the server hence the path not found. This is the first time I am actually restoring a database I T-SQL but I have done this many times in SSMS. Why would it be different here?

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

  • Manie Verster - Monday, January 23, 2017 2:51 AM

    joeroshan - Monday, January 23, 2017 2:27 AM

    Is the backup file good ?
    Can you try
    RESTORE VERIFYONLY  FROM DISK = N'C:\SomeFolder\SomeBackup.bak'?

    I did run the verify script and got the following message back:

    Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.

    The path specified by "C:\SQL\MSSQL11.ORISYS\MSSQL\DATA\MyDb.mdf" is not in a valid directory.

    Directory lookup for the file "C:\SQL\MSSQL11.ORISYS\MSSQL\DATA\MyDb_log.ldf" failed with the operating system error 3(The system cannot find the path specified.).

    The backup set on file 1 is valid.

    I do not understand the concern for storage space. I have enough disk space but maybe it refers to something else? I did copy the backup from the server hence the path not found. This is the first time I am actually restoring a database I T-SQL but I have done this many times in SSMS. Why would it be different here?

    Add the move clause to the restore, otherwise the original paths are used.
    😎

    RESTORE VERIFYONLY
    FROM DISK = N'C:\SomeFolder\SomeBackup.bak'
    WITH FILE = 1,
    MOVE N'MyDb_dat' TO N'C:\SomeFolder\MyDb.mdf',
    MOVE N'MyDb_log' TO N'C:\SomeFolder\MyDb_log.ldf'

  • Eirikur Eiriksson - Monday, January 23, 2017 7:40 AM

    Manie Verster - Monday, January 23, 2017 2:51 AM

    joeroshan - Monday, January 23, 2017 2:27 AM

    Is the backup file good ?
    Can you try
    RESTORE VERIFYONLY  FROM DISK = N'C:\SomeFolder\SomeBackup.bak'?

    I did run the verify script and got the following message back:

    Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.

    The path specified by "C:\SQL\MSSQL11.ORISYS\MSSQL\DATA\MyDb.mdf" is not in a valid directory.

    Directory lookup for the file "C:\SQL\MSSQL11.ORISYS\MSSQL\DATA\MyDb_log.ldf" failed with the operating system error 3(The system cannot find the path specified.).

    The backup set on file 1 is valid.

    I do not understand the concern for storage space. I have enough disk space but maybe it refers to something else? I did copy the backup from the server hence the path not found. This is the first time I am actually restoring a database I T-SQL but I have done this many times in SSMS. Why would it be different here?

    Add the move clause to the restore, otherwise the original paths are used.
    😎


    RESTORE VERIFYONLY
    FROM DISK = N'C:\SomeFolder\SomeBackup.bak'
    WITH FILE = 1,
    MOVE N'MyDb_dat' TO N'C:\SomeFolder\MyDb.mdf',
    MOVE N'MyDb_log' TO N'C:\SomeFolder\MyDb_log.ldf'

    Thank you for this. I did run it and the message: The backup set on file 1 is valid. Sadly though, my restore still give me the same error. Eirikur, would you mind if I sent you an attachment in a private message?

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

  • Manie Verster - Tuesday, January 24, 2017 12:05 AM

    Eirikur Eiriksson - Monday, January 23, 2017 7:40 AM

    Manie Verster - Monday, January 23, 2017 2:51 AM

    joeroshan - Monday, January 23, 2017 2:27 AM

    Is the backup file good ?
    Can you try
    RESTORE VERIFYONLY  FROM DISK = N'C:\SomeFolder\SomeBackup.bak'?

    I did run the verify script and got the following message back:

    Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.

    The path specified by "C:\SQL\MSSQL11.ORISYS\MSSQL\DATA\MyDb.mdf" is not in a valid directory.

    Directory lookup for the file "C:\SQL\MSSQL11.ORISYS\MSSQL\DATA\MyDb_log.ldf" failed with the operating system error 3(The system cannot find the path specified.).

    The backup set on file 1 is valid.

    I do not understand the concern for storage space. I have enough disk space but maybe it refers to something else? I did copy the backup from the server hence the path not found. This is the first time I am actually restoring a database I T-SQL but I have done this many times in SSMS. Why would it be different here?

    Add the move clause to the restore, otherwise the original paths are used.
    😎


    RESTORE VERIFYONLY
    FROM DISK = N'C:\SomeFolder\SomeBackup.bak'
    WITH FILE = 1,
    MOVE N'MyDb_dat' TO N'C:\SomeFolder\MyDb.mdf',
    MOVE N'MyDb_log' TO N'C:\SomeFolder\MyDb_log.ldf'

    Thank you for this. I did run it and the message: The backup set on file 1 is valid. Sadly though, my restore still give me the same error. Eirikur, would you mind if I sent you an attachment in a private message?

    No problem
    😎

  • Here is my script as I created it with variables. This, as I said before, is a learning curve for me and maybe I am trying to reinvent the wheel here but I am trying.

    --This is basically a copy of my original t-sql file that I just changed to remove real names. I am not sure if I declared
    --all the variables correctly. If you can just check it because if I do not use variables but hardcoding in the restore script
    --it works fine.

    --I have a plan to create a stored proc to do a point in time restore but must still do the rest. If I can just come past this part

    DECLARE @basepath nvarchar(1000) -- parameter in sp
    DECLARE @fullbak nvarchar(4000), @errmes nvarchar(4000)
    DECLARE @datalogicname sysname = N'MyDb_dat' -- parameter in sp
    DECLARE @loglogicname sysname = N'MyDb_log' --
    DECLARE @dbname sysname = N'MyDb' -- parameter in sp
    DECLARE @dblogname sysname = N'MyDb_log' -- parameter in sp
    DECLARE @dbdatafile nvarchar(1000) = N'C:\DB\MyDb.mdf' -- parameter in sp
    DECLARE @dblogfile nvarchar(1000) = N'C:\DB\MyDb.ldf' -- parameter in sp

    BEGIN TRANSACTION
        BEGIN TRY
            --these sets will be replaced with actual stored proc parameters
            SET @basepath = N'C:\DB\Backup\'
            SET @fullbak = @basepath+N'MyDb_backup_2017_01_18_170005_1286319.bak'
            SET @datalogicname = N'MyDb_dat'
            SET @loglogicname = N'MyDb_log'
            SET @dbname = N'MyDb'
            SET @dblogname = N'MyDb_log'
            SET @dbdatafile = N'C:\DB\MyDb.mdf'
            SET @dblogfile = N'C:\DB\MyDb.ldf'

            RESTORE DATABASE @dbname FROM DISK = @fullbak
            WITH
            FILE = 1,
            MOVE @datalogicname TO @dbdatafile,
            MOVE @loglogicname TO @dblogfile,
            NORECOVERY

            --RESTORE VERIFYONLY
            --FROM DISK = @fullbak
            --WITH FILE = 1,
            --MOVE @datalogicname TO @dbdatafile,
            --MOVE @loglogicname TO @dblogfile

        END TRY
        BEGIN CATCH
            set @errmes = 'Failed: ' + ERROR_MESSAGE()
            RAISERROR(@errmes,16,1)
            ROLLBACK TRANSACTION
            RETURN
        END CATCH
    COMMIT TRANSACTION

    I will appreciate any help I can get and will mention anyone that helped in the code.

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

  • Manie Verster - Monday, January 23, 2017 2:51 AM

    joeroshan - Monday, January 23, 2017 2:27 AM

    Is the backup file good ?
    Can you try
    RESTORE VERIFYONLY  FROM DISK = N'C:\SomeFolder\SomeBackup.bak'?

    I did run the verify script and got the following message back:

    Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.

    The path specified by "C:\SQL\MSSQL11.ORISYS\MSSQL\DATA\MyDb.mdf" is not in a valid directory.

    Directory lookup for the file "C:\SQL\MSSQL11.ORISYS\MSSQL\DATA\MyDb_log.ldf" failed with the operating system error 3(The system cannot find the path specified.).

    The backup set on file 1 is valid.

    I do not understand the concern for storage space. I have enough disk space but maybe it refers to something else? I did copy the backup from the server hence the path not found. This is the first time I am actually restoring a database I T-SQL but I have done this many times in SSMS. Why would it be different here?

    I think the issue isn't space....I believe that may just be a warning for you to check based on the size of the backup file.  More concerning is the actual error. The system cannot find the path specified.

    That can mean one of two things (that I can think of at least).
    1. The path is wrong. Check this by opening a Run window (Start>Run) and typing in the path (with double slants \\ before the path). If it can't open it, there's your issue.  
    2. Permissions.  It is possible the process doesn't have the proper permissions to access the path.  I would expect an error showing Access Denied, but sometimes I have seen the "cannot find path specified" if there is a permissions issue.

    Even if you can restore a database using SSMS's GUI, doesn't mean it will work via code.  You could have introduced a typo when you typed the path.  Also, SSMS does the restore using a different login (I believe it uses the service account that SQL Agent runs as), whereas running it by code it is running with your access.

    -SQLBill

  • SQLBill - Thursday, January 26, 2017 9:38 AM

    Manie Verster - Monday, January 23, 2017 2:51 AM

    I did run the verify script and got the following message back:

    Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.

    The path specified by "C:\SQL\MSSQL11.ORISYS\MSSQL\DATA\MyDb.mdf" is not in a valid directory.

    Directory lookup for the file "C:\SQL\MSSQL11.ORISYS\MSSQL\DATA\MyDb_log.ldf" failed with the operating system error 3(The system cannot find the path specified.).

    The backup set on file 1 is valid.

    I do not understand the concern for storage space. I have enough disk space but maybe it refers to something else? I did copy the backup from the server hence the path not found. This is the first time I am actually restoring a database I T-SQL but I have done this many times in SSMS. Why would it be different here?

    I think the issue isn't space....I believe that may just be a warning for you to check based on the size of the backup file.  More concerning is the actual error. The system cannot find the path specified.

    That can mean one of two things (that I can think of at least).
    1. The path is wrong. Check this by opening a Run window (Start>Run) and typing in the path (with double slants \\ before the path). If it can't open it, there's your issue.  
    2. Permissions.  It is possible the process doesn't have the proper permissions to access the path.  I would expect an error showing Access Denied, but sometimes I have seen the "cannot find path specified" if there is a permissions issue.

    Even if you can restore a database using SSMS's GUI, doesn't mean it will work via code.  You could have introduced a typo when you typed the path.  Also, SSMS does the restore using a different login (I believe it uses the service account that SQL Agent runs as), whereas running it by code it is running with your access.

    -SQLBill

    SQL Bill,
    First of all thank you for your post. The error message you saw was from my first restore verify only but I was helped right by Eirikur and told to use the with move and then my backup was verified good. I thought at first it might be access rights but I hardcoded everything and then ran the script and everything was fine. My path is definitely correct as well al the backup file name. I checked everything and could not find a fault but I thought maybe I did not declare my variables correctly meaning the data types. The other thing is the backup was made on the server where the original database also is and I am trying to restore it to my local computer.

    Manie Verster
    Developer
    Johannesburg
    South Africa

    I am happy because I choose to be happy.
    I just love my job!!!

  • Manie Verster - Thursday, January 26, 2017 6:39 AM

    Here is my script as I created it with variables. This, as I said before, is a learning curve for me and maybe I am trying to reinvent the wheel here but I am trying.

    --This is basically a copy of my original t-sql file that I just changed to remove real names. I am not sure if I declared
    --all the variables correctly. If you can just check it because if I do not use variables but hardcoding in the restore script
    --it works fine.

    --I have a plan to create a stored proc to do a point in time restore but must still do the rest. If I can just come past this part

    DECLARE @basepath nvarchar(1000) -- parameter in sp
    DECLARE @fullbak nvarchar(4000), @errmes nvarchar(4000)
    DECLARE @datalogicname sysname = N'MyDb_dat' -- parameter in sp
    DECLARE @loglogicname sysname = N'MyDb_log' --
    DECLARE @dbname sysname = N'MyDb' -- parameter in sp
    DECLARE @dblogname sysname = N'MyDb_log' -- parameter in sp
    DECLARE @dbdatafile nvarchar(1000) = N'C:\DB\MyDb.mdf' -- parameter in sp
    DECLARE @dblogfile nvarchar(1000) = N'C:\DB\MyDb.ldf' -- parameter in sp

    BEGIN TRANSACTION
        BEGIN TRY
            --these sets will be replaced with actual stored proc parameters
            SET @basepath = N'C:\DB\Backup\'
            SET @fullbak = @basepath+N'MyDb_backup_2017_01_18_170005_1286319.bak'
            SET @datalogicname = N'MyDb_dat'
            SET @loglogicname = N'MyDb_log'
            SET @dbname = N'MyDb'
            SET @dblogname = N'MyDb_log'
            SET @dbdatafile = N'C:\DB\MyDb.mdf'
            SET @dblogfile = N'C:\DB\MyDb.ldf'

            RESTORE DATABASE @dbname FROM DISK = @fullbak
            WITH
            FILE = 1,
            MOVE @datalogicname TO @dbdatafile,
            MOVE @loglogicname TO @dblogfile,
            NORECOVERY

            --RESTORE VERIFYONLY
            --FROM DISK = @fullbak
            --WITH FILE = 1,
            --MOVE @datalogicname TO @dbdatafile,
            --MOVE @loglogicname TO @dblogfile

        END TRY
        BEGIN CATCH
            set @errmes = 'Failed: ' + ERROR_MESSAGE()
            RAISERROR(@errmes,16,1)
            ROLLBACK TRANSACTION
            RETURN
        END CATCH
    COMMIT TRANSACTION

    I will appreciate any help I can get and will mention anyone that helped in the code.

    Hi guys,

    I found the answer and just want to say thank you for those who tried to assist. A restore cannot be done within a transaction so the begin, rollback and commit transaction statements must be removed. I am currently running my script and it is going!

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply