Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / GP Database Restore
Microsoft Dynamics GP (Archived)

GP Database Restore

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hi Everyone,

I am trying to restore the GP backup database using the following steps below, but I am getting this error regarding the .ldf file

I stopped at Step #19 due to the error I got.

      Restore-DB-Error.jpg



If you are using SQL Server Management Studio, follow these steps:

  1. Click Start, and then click Programs.
  2. Point to Microsoft SQL Server 2005 or Microsoft SQL Server 2008, and then click SQL Server Management Studio. The Connect to Server window opens.
  3. In the Server name box, type the name of the instance of SQL Server.
  4. In the Authentication list, click SQL Authentication.
  5. In the User name box, type sa.
  6. In the Password box, type the password for the sa user, and then click Connect.
  7. In the Object Explorer section, expand Databases.
  8. Right-click the test company database, point to Tasks, point to Restore, and then click Database.
  9. In the Source for Restore area, click From Device, and then click the ellipsis button.
  10. In the Backup Location area, click Add.
  11. Find the location where saved the backup file, select LIVE.bak file, and then click OK.
  12. Click OK. You return to the Restore Database window.
  13. In the Select the Backup Sets to Restore section, click the backup file that you want to restore.
  14. In the Select a Page area, click Options.
  15. In the Restore Database Files as area, you will need to change the location of these two files from the Live database to the test database's .mdf and .ldf files. By default, these will be selected on the Live database's .mdf and .ldf files.

    Note The logical file name reflects the name of the live database. Do not change the logical file name.
  16. To change these locations, click on the Ellipse (…) next to the file location field.
  17. Navigate to the path that you noted in step 1, where the test database was created.
  18. Highlight the respective .mdf file, and then click OK.
  19. Repeat steps p through r, select the .ldf file, and then click OK.
  20. Click to select the Overwrite existing database check box.
  21. Click OK to return to the Restore Database window

*This post is locked for comments

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: GP Database Restore

    Leslie, I have never tried this with MR. Do you think the MR services will try to restart and then lock the databases again?

  • L Vail Profile Picture
    L Vail 65,271 on at
    RE: GP Database Restore

    Hi,

    A couple of things I might try is to make sure I have marked the 'close existing connections' checkbox and change the names of the .mdf and .ldf files to something you haven't already used.

    Kind regards,

    Leslie

  • Suggested answer
    Bill Campbell Profile Picture
    Bill Campbell 22,647 on at
    RE: GP Database Restore

    I am with Richard on this one - it killed me for a long time trying to figure out who the heck was using GP when I was attempting to restore databases.

    Management Report has an 'always reading' relationship with the databases - really nice - but . . . .

    So if MR is set up to have access to the TEST company, then you are going to have to 'stop' the MR Service while you make the RESTORE.

    Good luck.

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: GP Database Restore

    1-Run Utilities.

    2-make a sample company.

    3-open sql take backup from live data base.

    4-restore the backup in test company.

    5-change the destination in general.

    6-change restore as in file.

    7-check overwrite on options.

    8-back to general and choose the source file check on radio button device.

    9-after restore run this script for not dupliacate the database ID.

    if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'SY00100') begin

     declare @Statement varchar(850)

     select @Statement = 'declare @cStatement varchar(255)

    declare G_cursor CURSOR for

    select case when UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'')

     then ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''+ cast(b.CMPANYID as char(3))

     else ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''''''+ db_name()+'''''''' end

    from INFORMATION_SCHEMA.COLUMNS a, '+rtrim(DBNAME)+'.dbo.SY01500 b

     where UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'',''INTERID'',''DB_NAME'',''DBNAME'')

       and b.INTERID = db_name() and COLUMN_DEFAULT is not null

    and rtrim(a.TABLE_NAME)+''-''+rtrim(a.COLUMN_NAME) <> ''SY00100-DBNAME''

     order by a.TABLE_NAME

    set nocount on

    OPEN G_cursor

    FETCH NEXT FROM G_cursor INTO @cStatement

    WHILE (@@FETCH_STATUS <> -1)

    begin

     exec (@cStatement)

     FETCH NEXT FROM G_cursor INTO @cStatement

    end

    close G_cursor

    DEALLOCATE G_cursor

    set nocount off'

     from SY00100

     exec (@Statement)

    end

    else begin

     declare @cStatement varchar(255)

     declare G_cursor CURSOR for

     select case when UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID')

       then 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '+ cast(b.CMPANYID as char(3))

       else 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '''+ db_name()+'''' end

     from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b

       where UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME')

         and b.INTERID = db_name() and COLUMN_DEFAULT is not null

       order by a.TABLE_NAME

     set nocount on

     OPEN G_cursor

     FETCH NEXT FROM G_cursor INTO @cStatement

     WHILE (@@FETCH_STATUS <> -1)

     begin

       exec (@cStatement)

       FETCH NEXT FROM G_cursor INTO @cStatement

     end

     close G_cursor

     DEALLOCATE G_cursor

     set nocount off

    end

    10-go now and open gp test company all data we be there.

    thank you

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: GP Database Restore

    Are you running Management Reporter and has this Test company been added to MR. Something is using your TEST database and you cannot do a restore of a database that is in use.  If you go into SQL Activity Monitor and then Processes it will show you what databases are in use.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: GP Database Restore

    Hi Richard,

    These what I got from running this script: RESTORE DATABASE [TEST2] FILE = N'GPSGDSIDat.mdf',  FILE = N'GPSGDSILog.ldf' FROM  DISK = N'G:\GDS\IT\Backup\Database Backup\2015\GDSI_db_201512130000.BAK' WITH  FILE = 1,  MOVE N'GPSGDSIDat.mdf' TO N'C:\GPSGDSIDat.mdf',  MOVE N'GPSGDSILog.ldf' TO N'C:\GPSGDSILog.ldf',  NOUNLOAD,  REPLACE,  STATS = 10

    GO

    Results:

    Msg 3102, Level 16, State 1, Line 1

    RESTORE cannot process database 'TEST2' because it is in use by this session. It is recommended that the master database be used when performing this operation.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

    Many thanks,

    Rotchine

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: GP Database Restore

    After the restore fails go down to the error log and get more information as to why the restore is failing. The error logs are located under SQL Server agent. Let us know what that error message is.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: GP Database Restore

    Hi Richard,

    I just want to restore to the Test company the GP Live database backup of a certain date.

    But I'm getting this error on ldf file. From the screenshot above, I changed the default location of both mdf & ldf files to another drive in the computer server.

    Before, I successfully restored the live database backup to the test company. The location of both mdf & ldf files of successfully restored database were also located on the drive that I'm trying to do again the restoration.

    Thanks,

    Rotchine

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: GP Database Restore

    If It is working now that is good. Another approach is to detach and attach the databases but let's hope that is not needed. Sounds like you were trying to overwrite a file that was in use.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: GP Database Restore

    Hi Richard,

    Yes, actually both mdf & ldf files do exists.

    I just changed the location of mdf & ldf files.

    Thanks,

    Rotchine

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans