Monday, December 22, 2014

Defrag Exchange Server 2010 with eseutil

Step by step guide to defrag Exchange 2010 server

1. Check first how much white space there is in your Exchange databases by running the following in Exchange Management Shell.
Get-MailboxDatabase -Status | ft name,databasesize,availablenewmailboxspace -auto
2. Check you have a backup of the Exchange Server (just in case!)

3. Disable any reboots of the Exchange Server or Domain Controllers if they are going to clash with the work.

4. You will need 110% of the largest database size as free space, either on the same server or elsewhere on the network.

5. Stop the Microsoft Exchange Information Store service.

6. Open command prompt and run
eseutil /d "d:\Program Files\Microsoft\Exchange Server\V14\Mailbox\DB01\DB01.edb" /t  "e:\DB01temp.edb"(The first path points to the Exchange database - edb file - that you wish to run the eseutil /d against, the other path is the the temp location used for the defragmentation process)

This can take a few hours. The command window will keep you updated of the progress, which is in two stages - the defragmentation and then the moving of the new edb file from the temp to the original location.

Run for all databases

7. When the process is finished, you can now restart the Microsoft Exchange Information Store service.

8. In the Exchange Management Console, check all databases have mounted. Check you can send and receive internal and external email.

9. In my case, I then shutdown the VM and removed the temp VHD from Hyper-V Manager. (I also deleted this VHD to reclaim space on the Cluster Storage Volume)..

10. Power on the server. Final test of inbound and outbound email.

11. Re-enable any server reboots you disabled earlier.

12. Run Get-MailboxDatabase -Status | ft name,databasesize,availablenewmailboxspace -auto in Exchange Management Shell and see your databases are now much smaller from when you started.

Tuesday, December 16, 2014

Importing media to Backup Exec

Importing media to Backup Exec

You can import media to a robotic library to add tapes to Backup Exec, or to import media that is required for a restore job. When you insert media into a robotic library, you must create an import storage operation job. The import storage operation updates the Backup Exec database with the information about the media. Backup Exec associates the media that you import with a system media set.
Note:
You should not associate scratch media with a media set that you create. Backup Exec automatically moves the media to the required media set as needed.
Before you import media, note the following:
  • If the media does not have a barcode, you must run the Inventory after import operation so that the current media's label appears in the properties. You can only select this operation after you select Import media now.
  • If the robotic library uses a media magazine, ensure that no jobs are currently running. Before you swap the magazine, ensure that all media are ejected from the drive and are back in the magazine slots.
You can select any number of slots to import media to.
The import storage operation supports robotic libraries with portals. When this storage operation job runs, Backup Exec checks the selected slots for media. If media is found, it is exported to the portals. After all of the media is exported, you are prompted to insert new media into the portal so it can be imported. This process continues until all of the requested media have been imported into the robotic library.
You can also run a scan operation to update the slot information when you insert new media in a robotic library. The scan job log reports the barcoded media that are in the drives and portals.
To import media now
  1. On the Storage tab, do one of the following
    • Expand the robotic library, right-click Slots, and then click Import media now.
    • Right-click the robotic library, and then click Import media now.
  2. (Optional) View the job history or click the Job Monitor tab for details about the job.
To schedule an import media job
  1. On the Storage tab, do one of the following:
    • Expand the robotic library, right-click Slots, and then click Import media now.
    • Right-click the robotic library, and then click Import media now, and then click Schedule.
  2. In the left pane, click Storage operations.
  3. Click the drop-down menu, and select storage operation that you want to schedule:
    ImportUpdates the Backup Exec database with information about the media.
    Inventory after importMounts the media in the drive, reads the media label, and updates the Backup Exec database. This operation is necessary for media that do not have barcodes.
  4. To send a notification when the job completes, in the left pane, click Notification and select the appropriate options:
    Recipient nameShow the names of the individual and group recipients.
    Recipient typeIndicate Recipient for an individual recipient or Group for a group recipient.
    Manage RecipientsAdd, edit, or delete recipients.
    PropertiesView or change the properties of a selected recipient.
  5. To schedule the job, in the left pane, click Schedule and select the appropriate options.
    RecurrenceSpecify a recurrence schedule for the job.
    HoursCreate a recurrence pattern that is measured in hours or minutes.
    When you select Hours, you can configure the following options:
    • Every X hour/minute
      Indicates the number of hours or minutes between the start time of a job and the start time of the next job instance.
    • From
      Designates the starting time for a job to run.
    • Between
      Restricts the job to certain hours and days. For example, if you only want the job to run during business hours, you can select 9:00 AM to 5:00 PM on Monday, Tuesday, Wednesday, Thursday, and Friday.
      The start time and end time can span a maximum of 24 hours, however they can cross over midnight into the following day.
    DaysCreate a recurrence pattern that is measured in days.
    When you select Days, you must choose between the following options:
    • Every X day
      Indicates the number of days between the start time of a job and the start time of the next job instance.
    • Every weekday
      Specifies that the job should run on Mondays, Tuesdays, Wednesdays, Thursdays, and Fridays.
    WeeksCreate a recurrence pattern that is measured in weeks.
    When you create a recurrence pattern that is measured in weeks, you must configure the Every X week on field. The Every X week on specifies the number of weeks between the start time of a job and the start time of the next job instance. It also specifies the days of the week on which the job should run.
    MonthsCreate a recurrence pattern that is measured in months.
    When you select Months, you must choose between the following options:
    • Day X of every X month
      Specifies the day on which the job should run. It also indicates the number of months between the start time of a job and the start time of the next job instance.
    • Every X X of every X month
      Specifies the day on which the job should run. It also indicates the number of months between the start time of a job and the start time of the next job instance.
    • Selected days of the month
      Specifies the weeks and days of the month on which Backup Exec runs the job. You select the days and weeks on a grid. The recurrence pattern that you select repeats itself every month.
      The default setting is for the job to run every month on the current week and day of the month. For example, if you create the job on the third Monday of the month, the default setting is for the job to run once a month on the third Monday.
      You can change the default or select additional days on which the job should run. Any additional days that you select are added to the monthly recurrence pattern.
    • Selected dates of the month
      Specifies the dates of the month on which Backup Exec runs the job. The recurrence pattern that you select repeats itself every month.
      The default setting is for the job to run every month on the current date of the month. For example, if you create the job on the 15th, the default setting is for the job to run once a month on the 15th.
      You can change the default or select additional days on which the job should run. Any additional days that you select are added to the monthly recurrence pattern.
      If you select the 31st, the job runs on the last day of the month in months that do not have 31 days. For example, if you configure the job to run on the 31st, in September the job runs on the 30th instead.
    YearsCreate a recurrence pattern that is measured in years.
    When you select Years, you can configure the following options:
    • Every X year
      Specifies the number of years between the start time of a job and the start time of the next job instance.
    • On X
      Specifies the date on which Backup Exec runs the job. The date that you select in this field corresponds to the number of years that you selected in the Every X year field. So if you selected to run the job every 2 years and you selected June 28th in this field, the job runs every 2 years on June 28th.
    • On the X of X
      Specifies the day and month on which Backup Exec runs the job. The date that you select in this field corresponds to the number of years that you selected in the Every X year field. So if you selected to run the job every 2 years and you selected the fourth Thursday of June in this field, the job runs every 2 years on the fourth Thursday of June.
    atDesignate the starting time for the first job in the recurrence pattern.
    CalendarView all scheduled jobs on a calendar to check for scheduling conflicts.
    Reschedule the job if it does not start x hours after its scheduled startSpecify the amount of time past the job's scheduled start time at which Backup Exec changes the job completion status to Missed. The job is rescheduled to run based on the time window that you configured.
    Cancel the job if it is still running x hours after it scheduled start timeSpecify the amount of time after the job's scheduled start time at which you want to cancel the job if it is still running. Backup Exec changes the job completion status to Canceled, timed out.
    Include dates with the schedule of this jobSpecify dates to include with the job schedule. The job runs on all of the dates that you select using this option, in addition to the dates that are part of its normal schedule recurrence. The job resumes its normal schedule on the next day that it is scheduled after an include date.
    Exclude dates from the schedule for this jobSpecify dates to exclude from the job schedule. The job does not run on any of the dates that you select using this option. It resumes its normal schedule on the next day that a job is scheduled after an excluded date.
    Run now with no recurring scheduleRun the job immediately without scheduling any more instances of it for the future.
    Run onRun the job at the time and date that you specify.
    Create without a scheduleCreate a job without scheduling it. When you use this option, the job does not run at the time of creation and it does not have a recurring schedule. The job remains unscheduled until you choose to run it. You can use a third-party job automation or task scheduling tool to run the job later.
    If you use this option to create a job, you cannot place the job on hold. You cannot place jobs on hold unless they are scheduled.
  6. Click OK
  7. (Optional) View the job history or click the Job Monitor tab for details about the job.


Event ID 58061: "Please insert overwritable media into the drive. Overwritable media includes scratch, blank, and recyclable media. Please note that depending on the current Media Overwrite Protection setting, imported and allocated media may be overwritable as well" is reported in the Event Viewer Application Log.

Issue



Event ID 58061: "Please insert overwritable media into the drive. Overwritable media includes scratch, blank, and recyclable media. Please note that depending on the current Media Overwrite Protection setting, imported and allocated media may be overwritable as well" is reported in the Event Viewer Application Log.


Error



Event ID: 58061
Source: Backup Exec
Type: Information
Description: Event ID 58061: "Please insert overwritable media into the drive.

Overwritable media includes scratch, blank, and recyclable media.

Error - Unable to mount specified medium: 0xa000810f.


Cause



The alert was reported because a "backup" job was performed in Backup Exec (tm) and there are no tapes in the tape drive that Backup Exec can use to continue the backup. Backup Exec is waiting for tapes that can be overwritten (including scratch, blank, and recyclable media) to be placed in the tape drive.  
This error can also be detected from the SGMon log. This event will register the following error in the SGMon log:
Error - Unable to mount specified medium: 0xa000810f.


Solution



Solution 1:
Place tapes in the tape drive, respond to the alert in Backup Exec.
 
Solution 2:
Check if the tapes are in 'End marker Unreadable' state and if the tape drive has multiple errors as shown below.
If the tapes are  'End marker unredable' and have multiple errors then follow the steps below.
1.Right click on the tape and run long erase.Please note that all data on the tape will be lost.
2.Run a clean job on the tape drive.
3.Run the backup job again to the tape.



Supplemental Materials

SourceEvent ID
Value58061
DescriptionEvent ID 58061: "Please insert overwritable media into the drive.

SourceError Code
Value0xa000810f
Description Error - Unable to mount specified medium: 0xa000810f.

The alert "Please insert overwritable media" is displayed during a backup operation

Issue



The alert "Please insert overwritable media" is displayed during a backup operation.


Error



Please insert overwritable media.
UMI code : 
  • V-275-601
  • V-275-439 
  • V-275-433
  • V-275-432


Cause



If the media in the drive is not overwritable, the above alert is displayed requesting the user to insert overwritable media in to a tape drive. Another reason for this alert is when the backup is spanning onto another tape. When the backup spans to another media,it prompts for an Overwritable Media to continue backing up the resource(s).


Solution



Providing an overwritable tape and responding OK to the alert will allow the job to continue. The media in the drive can be overwritten if the media is scratch or recyclable (its overwrite protection period has expired). If allocated or imported media are in the drive, they may also be overwritten depending on the Media Overwrite Protection Level that is set.

Backup Exec can also be configured to respond to these alerts automatically. Refer to the Related Documents section for more information on configuring automated alert response in Backup Exec.

There are different alert messages associated with the alert "Please insert overwritable media". Please refer to the following technotes for more information on specific alerts:
  1. Alert description: "Please insert overwritable media into the drive":
    • When performing a backup operation with Backup Exec, the alert message "Please insert overwritable media" is displayed. www.symantec.com/docs/TECH6377

       
    • Event ID 58061: "Please insert overwritable media into the drive. Overwritable media includes scratch, blank, and recyclable media. Please note that depending on the current Media Overwrite Protection setting, imported and allocated media may be overwritable as well" is reported in the Event Viewer Application Log. www.symantec.com/docs/TECH23606

     
  2. Alert description: "Please insert overwritable media into the cascaded drive group."
     
  3. Alert description: "Please insert overwritable media into the robotic library using the import command."
    • Event ID 58064: "Backup Exec Alert: Library Insert (Server: "%server%") (Job: "%job name%") Please insert overwritable media into the robotic library using the import command. Overwritable media includes scratch, blank, and recyclable media" is reported in the Event Viewer Application Log. www.symantec.com/docs/TECH23219

       
    • Event ID 58064: "Please insert media '%s' into the robotic library using the import command" in the Event Viewer Application Log. www.symantec.com/docs/TECH23616

       
    • Using Backup Exec for Windows Servers, the message "Please insert overwritable media into the robotic library using the import command." is displayed and the job status is "Queued" during a backup job and overwritable media is available in the library and media sets. www.symantec.com/docs/TECH60241

       
  4. Alert description: "Please insert overwritable media into the Backup-to-Disk device"
    • Event ID 58061: "Please insert overwritable media into the Backup-to-Disk device. Overwritable media includes scratch, blank, and recyclable media. Please note that depending on the current Media Overwrite Protection setting, imported and allocated media may be overwritable as well" is reported when performing a backup operation. www.symantec.com/docs/TECH23610
       



Supplemental Materials

SourceUMI
ValueV-275-601
DescriptionPlease insert overwritable media into the Backup-to-Disk device.

SourceUMI
ValueV-275-439
DescriptionPlease insert overwritable media into the robotic library using the import command.

SourceUMI
ValueV-275-433
DescriptionPlease insert overwritable media into the cascaded drive group.

SourceUMI
ValueV-275-432
DescriptionPlease insert overwritable media into the drive.

SourceEvent ID
Value58061

SourceEvent ID
Value58064

How to transfer an existing Microsoft Dynamics GP, Microsoft Small Business Financials, or Microsoft Small Business Manager installation to a new server that is running Microsoft SQL Server

Transfer instructions

  1. On the old server, copy the following Capture_Logins.sql script to the local hard disk. Click the following link to obtain the Capture_Logins.sql script:

    https://mbs.microsoft.com/Files/customer/GP/Downloads/ServicePacks/KB878449_Capture_Logins.sql
  2. On the old server, run the Capture_Logins.sql script to capture all SQL Server logins and password information. All SQL Server logins that are used by the financial application, by Microsoft Business Solutions - FRx, by Personal Data Keeper, or by any other application that is using the SQL Server installation on the old server will be captured. Follow these steps, based on the SQL Server that tools you use:
    • If you use SQL Server Management Studio, follow these steps:
      1. Click Start, point to All Programs, point to Microsoft SQL Server 2005, Microsoft SQL Server 2008, or Microsoft SQL Server 2012 and then click SQL Server Management Studio.
      2. In the Connect to Server window, follow these steps:
        1. In the Server name box, type the name of the server that is running SQL Server.
        2. In the Authentication box, click SQL Authentication.
        3. In the Login box, type sa.
        4. In the Password box, type the password for the sa user, and then click Connect.
      3. Click File, point to Open, and then click File.
      4. In the Look In list, click the Capture_Logins.sql script that you copied to the local hard disk in step 1, and then click Open.
      5. In the Connect to Database Engine window, follow these steps:
        1. In the Server Name box, type the name of the old server that is running SQL Server.
        2. In the Authentication box, click SQL Authentication.
        3. In the Login box, type sa.
        4. In the Password box, type the password for the sa user, and then click Connect.
      6. Click Query, point to Results to, and then click Results to File.
      7. Click Query, and then click Execute.
      8. In the Save Results window, follow these steps:
        1. In the Save in list, click the location where you want to save the results of the script.
        2. In the File name box, type SQLLOGINS.sql, and then click Save.
    • If you use Query Analyzer, follow these steps:
      1. Click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
      2. In the Connect to SQL Server window, follow these steps:
        1. In the SQL Server box, type the name of the old server that is running SQL Server.
        2. In the Connect using area, click SQL Server Authentication.
        3. In the Login name box, type sa.
        4. In the Password box, type the password for the sa user, and then click OK.
      3. Click File, and then click Open.
      4. In the Open Query File window, in the Look In list, click the Capture_Logins.sql script that you copied to the local hard disk of the old server in step 1, and then click Open.
      5. Click Query, and then click Results to File.
      6. Click Query, and then click Execute.
      7. In the Save Results window, follow these steps:
        1. In the Save in list, click the location where you want to save the results of the script.
        2. In the File name box, type SQLLOGINS.sql, and then click Save.
    • If you use MSDE 2000 and if no SQL Server tools are available, follow these steps:
      1. On the server that is running MSDE, click Start, click Run, type cmd, and then click OK.
      2. At the command prompt, type the following script, and then press ENTER to run the script. Replace SERVERNAME with the name of the server that is running MSDE 2000. Replace SAPASSWORD with the password for the sa user.
        OSQL –S SERVERNAME –U sa –P SAPASSWORD –i 
        “C:\Capture_Logins.sql” –o “c:\SQLLOGINS.sql” -n -w 500
      3. Type exit, and then press ENTER.
      4. Right-click Start, and then click Explore.
      5. On drive C, open the SQLLOGINS.sql file to make sure that the script was created successfully.
  3. Make a full backup of the DYNAMICS database and all company databases on the old server.

    Note On the server that is running SQL Server, MSDE 2000, or SQL Server 2005 Express, start the financial application, click File, and then click Backup to make a backup of each database.

    You can also create a backup by using SQL Server Management Studio, Enterprise Manager, Query Analyzer, or the Support Administrator Console.
  4. On the old server, generate a SQL script for each SQL Server Agent Job that is currently scheduled and for each SQL Server Agent Operator that is currently set up. Follow these steps, based on the SQL Server tools that you use.

    Note These steps apply to SQL Server Standard, to SQL Server Enterprise, or to SQL Server Workgroup Edition.
    • If you use SQL Server Management Studio, follow these steps:
      1. Click Start, point to All Programs, point to Microsoft SQL Server 2005, Microsoft SQL Server 2008, or Microsoft SQL Server 2012, and then click SQL Server Management Studio.
      2. In the Connect to Server window, follow these steps:
        1. In the Server name box, type the name of the server that is running SQL Server.
        2. In the Authentication box, click SQL Authentication.
        3. In the Login box, type sa.
        4. In the Password box, type the password for the sa user, and then click Connect.
      3. In the Object Explorer pane, expand SQL Server Agent, and then expand Jobs to view all available jobs.

        Note If the SQL Server Agent is not started, right-click SQL Server Agent, and then click Start.
      4. Right-click a job, point to Script Job as, point to Create To, and then click File.
      5. In the Select a File window, select the folder where you want to save the script, and then type a file name. Click OK.
      6. Repeat steps c through e for all jobs.
      7. In the Object Explorer pane, expand SQL Server Agent , and then expand Operators to view all Operators that are currently set up.
      8. Right-click an operator, point to Script Operator as, point to Create To, and then click File.
      9. In the Select a File Window, select the folder where you want to save the script, and then type a file name. Click OK.
      10. Repeat steps g through i for all operators.
    • If you use Enterprise Manager, follow these steps:
      1. Click Start, point to All Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
      2. Expand Microsoft SQL Servers, expand SQL Server Group, and then expand the name of the server that is running SQL Server.
      3. Expand Databases, expand Management, and then expand SQL Server Agent.
      4. Right-click Jobs, point to All Tasks, and then click Generate SQL Script.
      5. In the Generate SQL Script window, select the folder where you want to save the script in the Save In list.
      6. In the File name box, type a name for the script, and then click Save.
      7. Click OK to generate the script.
      8. Right-click Operators, point to All Tasks, and then click Generate SQL Script.
      9. In the Generate SQL Script window, select the folder where you want to save the script in the Save In list.
      10. In the File name box, enter a name for the script, and then click Save.
      11. Click OK to generate the script.
  5. In Windows Explorer, copy the SQLLOGINS.sql script that you created in step 2, the backup files that you created in step 3, and the SQL Server Agent Job and SQL Server Agent Operator scripts that you created in step 4 from the old server to the hard disk on the new server.

    Note If you are using the same server, you do not have to complete this step.
  6. Install SQL Server on the new server if it is not already installed.

    Notes
    • Make sure that you use the same sort order that was used on the old server. To obtain the sort order that was used on the old server, run the following script against the master database in the SQL Server Management Studio, in Query Analyzer, or in the Support Administrator Console:
      sp_helpsort 
      The following list shows the SQL Server sort orders that the financial applications support:
      • Column to verify: Server Collation Default
        Column contents: Latin1-General, binary sort
        Column meaning: Binary Sort Order 50
      • Column to verify: Server Collation Default
        Column contents: Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data
        Column meaning: Dictionary Order Case Insensitive (DOCI) Sort Order 52
    • If you are using the same server, install a new instance of SQL Server on the same computer. In the rest of this article, the term "new server" is used to refer to the new server that is running SQL Server or to the new instance of SQL Server on the old computer.
    • If you restore a database that was installed on the computer that is running SQL Server 7.0 or SQL Server 2000 and if you are moving the database to a computer that is running SQL Server 2005, you will have to update the database compatibility level for each database after the restore. To do this, follow these steps on the new server in SQL Server Management Studio:
      1. In the Object Explorer area, expand Databases, right-click the database, and then click Options.
      2. In the Compatibility box, click to select the SQL Server 2005 (90) check box.
  7. On the new server, restore the DYNAMICS database from the backup file that you created in step 3. Follow these steps, based on the SQL Server tools that you use.

    Note If you are using the same server, restore the databases on the new instance of SQL Server on the same computer.
    • If you use SQL Server Management Studio, follow these steps:
      1. Click Start, point to All Programs, point to Microsoft SQL Server 2005, Microsoft SQL Server 2008, or Microsoft SQL Server 2012, and then click SQL Server Management Studio.
      2. In the Connect to Server window, follow these steps:
        1. In the Server Name box, type the name of the new server that is running SQL Server.
        2. In the Authentication box, click SQL Authentication.
        3. In the Login box, type sa.
        4. In the Password box, type the password for the sa user, and then click Connect.
      3. In the Object Explorer area, right-click Databases, and then click Restore Database.
      4. In the Destination for restore area, type DYNAMICS in the To database box.
      5. In the Source for restore area, click From Device, and then click the ellipsis button to open the Specify Backup window.
      6. In the Backup Media list, click File, and then click Add to open the Locate Backup Files window.
      7. In the Select the file area, click the backup file for the DYNAMICS database that you backed up in step 3, click OK, and then click OK.
      8. In the Select the backup sets to restore area, click to select the Restore check box next to the backup that you want to restore.
      9. In the Select a Page area, click Options, and then click to select the Overwrite the existing database check box.
      10. In the Restore the database files as area, change the Restore As column so that the data file and the log file use the correct paths on the new server.

        Note The default paths for SQL Server 2005 or SQL Server 2008 are the following.
        %systemroot%\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\_Data.mdf
        %systemroot%\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\_Log.ldf
        You can find these files by using Windows Explorer.
      11. Click OK.
    • If you use Enterprise Manager, follow these steps:
      1. Click Start, point to All Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
      2. Expand Microsoft SQL Servers, expand SQL Server Group, expand the name of the new server.
      3. Right-click Databases, point to All Tasks, and then click Restore Database.
      4. In the Restore as database box, type DYNAMICS.
      5. In the Restore area, click to check From device, and then click Select Devices.
      6. In the Choose Restore Devices window, click Add.
      7. In the Choose Restore Destination Window, click the ellipsis button, locate and then click the backup file of the DYNAMICS database that you backed up in step 3, click OK, click OK, and then click OK again.
      8. Click the Options tab, and then click to select the Force restore over existing database check box.
      9. In the Restore the database files as area, change the Restore As column so that the data file and the log file use the correct paths on the new server.

        Note The default paths for SQL Server 2000 are as follows:
        %systemroot%\Program Files\Microsoft SQL Server\MSSQL\Data\_Data.mdf
        %systemroot%\Program Files\Microsoft SQL Server\MSSQL\Data\_Log.ldf
        You can find these files by using Windows Explorer.
      10. Click OK.
    • If you use the Support Administrator Console, follow these steps:
      1. Click Start, point to All Programs, point to Microsoft Support Administrator Console, and then click Support Administrator Console.
      2. In the Connect to SQL Server window, follow these steps:
        1. In the SQL Server box, type the name of the new server.
        2. In the Login Name box, type sa.
        3. In the Password box, type the password for the sa user, and then click OK.
      3. Copy the following script to the New Query 1 window:
        RESTORE DATABASE [TEST] 
         FROM  DISK = N'C:\Program Files\Dynamics\Backup\TEST.bak'
                   WITH  FILE = 1, NOUNLOAD, STATS = 10, RECOVERY, REPLACE,
                   MOVE N'GPSTESTDat.mdf' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\GPSTESTDat.mdf', 
                   MOVE N'GPSTESTLog.ldf' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\GPSTESTLog.ldf'
        
        Note Make the following changes to the script to apply to your environment:
        • Replace TEST with the name of your company database on the new server.
        • Replace C:\Program Files\Dynamics\Backup\TEST.bak with the correct path of the backup file.
        • Replace GPSTESTDat.mdf with the correct name of the file.
        • Replace C:\Program Files\Microsoft SQL Server\MSSQL\Data\GPSTESTDat.mdf with the correct path of the .mdf file for the database on the new server.
        • Replace GPSTESTLog.ldf with the correct name of the file.
        • Replace C:\Program Files\Microsoft SQL Server\MSSQL\Data\GPSTESTLog.mdf with the correct path of the .ldf file for the database on the new server.
      4. Click the green arrow to run the query.
  8. Repeat step 7 for each company database.

    Note If you are restoring a Microsoft SQL Server 2000 database to Microsoft SQL Server 2005 or Microsoft SQL Server 2008, you must perform the following tasks:
    • Change the database compatibility level for each database. To do this in SQL Management Studio, right-click the database, click Properties, click Options, and then change the database compatibility to SQL Server(90) for Microsoft SQL Server 2005 or SQL Server 2008 (100) for Microsoft SQL Server 2008.
    • Remove the schemas that have the Microsoft Dynamics GP user names that were created for each financial application user. The default schema is dbo, and the user schemas are not needed. To remove the user schemas, click the following link, and then run the script that is in the linked document in SQL Management Studio:
  9. Create an Open Database Connectivity (ODBC) connection at the new server and at all client workstations that use the financial application. For more information about how to set up an Open Database Connectivity connection on Microsoft SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:
    870416 ODBC setup on SQL Server 2005, SQL Server 2000, SQL Server 7.0, and SQL Server Desktop Engine 2000 (MSDE)
  10. On the new computer, install a Server and Client installation of the financial application. Then, install any third-party products or additional products that you use on the new server. Verify that the third-party and additional products are functional.
  11. Run the SQLLOGINS.sql script that you created in step 2 to create all the SQL Server logins. You can use SQL Server Management Studio or Query Analyzer to run the script. If you use MSDE 2000, the SQLLOGINS.sql script must be run by using OSQL. See the instructions in step 2 for the process.

    Note If the old server was running Microsoft Dynamics GP and does not have the same name as the new server, the passwords for the users will no longer be valid. To reset the password, follow these steps:
    1. Log on to Microsoft Dynamics GP as the sa user.
    2. On the Tools menu, point to Setup, point to System, and then click User.
    3. Click the Lookup button next to User ID and select the appropriate user.
    4. In the password field, enter a new password, and then click Save.
    Notes
    •  You must follow these steps if the old server was running Microsoft Business Solutions-Great Plains 7.5 or Microsoft Business Solutions - Great Plains 8.0 and the new server will be running Microsoft Dynamics GP 10.0 or Microsoft Dynamics GP 2010.0.
  12. Run the scripts that you created in step 4 to create the SQL Server Agent jobs and the SQL Server Agent Operators on the new server. You can use SQL Server Management Studio or Query Analyzer to run the script.

    Note If you use MSDE 2000, there is one additional script that you must run to create the PJOURNAL jobs and to truncate the PJOURNAL table every half hour. The script must be run by using OSQL. See the instructions in step 2 for the process. To obtain the Create_PJOURNAL.sql script that performs this process, click the following link:
  13. Obtain the Dex_Req.sql script and the Grant.sql script, and then run the scripts. To obtain the scripts, visit the following Microsoft Web sites: Notes
    • Run the Dex_Req.sql script against the Master database. Run the Grant.sql script against the Dynamics database and against all company databases.
    • You can use SQL Server Management Studio or Query Analyzer to run the scripts.
    • If you use MSDE 2000, the Dex_Req.sql script must be run by using OSQL. See the instructions in step 2 for the process. You can run the Grant.sql script by using the Support Administrator Console.
    • The Dex_Req.sql script creates the DEX_LOCK and DEX_SESSION tables in the tempdb database. The script also creates the smDEX_Build_Locks stored procedure in the master database. The Grant.sql script grants select, update, insert, and delete permissions to all tables, views, and stored procedures for all users in the DYNGRP database role. These are the permissions that you must have to use the financial application.
  14. Run the following script against each financial application database to set the database owner to DYNSA.
    sp_changedbowner 'DYNSA'
  15. If the Reports and Forms dictionary files are shared on the old server, copy the files to the new server.

    Note To verify whether the Reports and Forms dictionary files are shared, view the Dynamics.set file on a client workstation where the financial application is installed. To view the Dynamics.set file, right-click the Dynamics.set file, and then click Edit to open the file.
  16. If the OLE Notes files are shared on the old server, copy the files to the new server.

    Note To verify whether the OLE Notes files are shared, view the OLENotes path in the Dex.ini file on a client workstation where the financial application is installed. To view the Dex.ini file, double-click the Dex.ini file to open the file in Notepad.
  17. If the Automatic Updates feature has been used and has entries that point to a share on the old server, the files must be copied to a share on the new server. The entries in the SYUPDATE table in the System DYNAMICS database needed to be adjusted. For more information, see Microsoft Knowledge Base article 916679.
  18. If you're moving your Microsoft Dynamics GP 2010 databases and you use the drilldown functionality in the SQL Server Reporting Services or Excel integrated reports you need to do the following to update your server links so the drilldowns work after the server move:
    • Ensure that everyone has logged out of Microsoft Dynamics GP 2010 and close all instances of SQL Server Management Studio
    • On a machine where Dynamics GP 2010 is installed click on Start, then point to All Programs. Click on Microsoft Dynamics, then GP 2010 and click on Database Maintenance
    • When the utility opens select or enter the SQL Server instance where the Dynamics GP 2010 databases are stored. If you are logged in as a domain account with rights to this SQL Server instance you can select that option. Otherwise select SQL Authentication and enter a n appropriate user name and password. Then click Next >>
    • Select Mark All to choose each of the Dynamics GP 2010 databases and click Next >>
    • Select the Microsoft Dynamics GP product, then click Next >>
    • Select Functions and Stored Procedures, then click Next >>
    • Review the confirmation window, then click Next >> to begin the process.

    This can take some time, depending on the number of products installed and the number of databases that need to be addressed. Once it has completed your external report drilldowns will work in the new SQL Server instance you've moved to.

Sunday, December 7, 2014

Rebuild System Databases

System databases must be rebuilt to fix corruption problems in the master, model, msdb, or resource system databases or to modify the default server-level collation. This topic provides step-by-step instructions to rebuild system databases in SQL Server 2014.

Limitations and Restrictions


When the master, model, msdb, and tempdb system databases are rebuilt, the databases are dropped and re-created in their original location. If a new collation is specified in the rebuild statement, the system databases are created using that collation setting. Any user modifications to these databases are lost. For example, you may have user-defined objects in the master database, scheduled jobs in msdb, or changes to the default database settings in the model database.

Prerequisites


Perform the following tasks before you rebuild the system databases to ensure that you can restore the system databases to their current settings.
  1. Record all server-wide configuration values.
    SELECT * FROM sys.configurations;
    
  2. Record all service packs and hotfixes applied to the instance of SQL Server and the current collation. You must reapply these updates after rebuilding the system databases.
    SELECT
    SERVERPROPERTY('ProductVersion ') AS ProductVersion,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('ResourceVersion') AS ResourceVersion,
    SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,
    SERVERPROPERTY('Collation') AS Collation;
    
  3. Record the current location of all data and log files for the system databases. Rebuilding the system databases installs all system databases to their original location. If you have moved system database data or log files to a different location, you must move the files again.
    SELECT name, physical_name AS current_file_location
    FROM sys.master_files
    WHERE database_id IN (DB_ID('master'), DB_ID('model'), DB_ID('msdb'), DB_ID('tempdb'));
    
  4. Locate the current backup of the master, model, and msdb databases.
  5. If the instance of SQL Server is configured as a replication Distributor, locate the current backup of the distribution database.
  6. Ensure you have appropriate permissions to rebuild the system databases. To perform this operation, you must be a member of the sysadmin fixed server role. For more information, see Server-Level Roles.
  7. Verify that copies of the master, model, msdb data and log template files exist on the local server. The default location for the template files is C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\Templates. These files are used during the rebuild process and must be present for Setup to succeed. If they are missing, run the Repair feature of Setup, or manually copy the files from your installation media. To locate the files on the installation media, navigate to the appropriate platform directory (x86 or x64) and then navigate to setup\sql_engine_core_inst_msi\Pfiles\SqlServr\MSSQL.X\MSSQL\Binn\Templates.  

To rebuild system databases for an instance of SQL Server:

  1. Insert the SQL Server 2014 installation media into the disk drive, or, from a command prompt, change directories to the location of the setup.exe file on the local server. The default location on the server is C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Release.
  2. From a command prompt window, enter the following command. Square brackets are used to indicate optional parameters. Do not enter the brackets. When using a Windows operating system that has User Account Control (UAC) enabled, running Setup requires elevated privileges. The command prompt must be run as Administrator.
    Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]
    Parameter name
    Description
    /QUIET or /Q
    Specifies that Setup run without any user interface.
    /ACTION=REBUILDDATABASE
    Specifies that Setup re-create the system databases.
    /INSTANCENAME=InstanceName
    Is the name of the instance of SQL Server. For the default instance, enter MSSQLSERVER.
    /SQLSYSADMINACCOUNTS=accounts
    Specifies the Windows groups or individual accounts to add to the sysadmin fixed server role. When specifying more than one account, separate the accounts with a blank space. For example, enter BUILTIN\Administrators MyDomain\MyUser. When you are specifying an account that contains a blank space within the account name, enclose the account in double quotation marks. For example, enter NT AUTHORITY\SYSTEM.
    [ /SAPWD=StrongPassword ]
    Specifies the password for the SQL Server sa account. This parameter is required if the instance uses Mixed Authentication (SQL Server and Windows Authentication) mode.
    Security note Security Note
    The sa account is a well-known SQL Server account and it is often targeted by malicious users. It is very important that you use a strong password for the sa login.
    Do not specify this parameter for Windows Authentication mode.
    [ /SQLCOLLATION=CollationName ]
    Specifies a new server-level collation. This parameter is optional. When not specified, the current collation of the server is used.
    Important note Important
    Changing the server-level collation does not change the collation of existing user databases. All newly created user databases will use the new collation by default.
    For more information, see Set or Change the Server Collation.
  3. When Setup has completed rebuilding the system databases, it returns to the command prompt with no messages. Examine the Summary.txt log file to verify that the process completed successfully. This file is located at C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Logs.

After rebuilding the database you may need to perform the following additional tasks:
  • Restore your most recent full backups of the master, model, and msdb databases. For more information, see Back Up and Restore of System Databases (SQL Server).
    Important note Important
    If you have changed the server collation, do not restore the system databases. Doing so will replace the new collation with the previous collation setting.
    If a backup is not available or if the restored backup is not current, re-create any missing entries. For example, re-create all missing entries for your user databases, backup devices, SQL Server logins, end points, and so on. The best way to re-create entries is to run the original scripts that created them.
Security note Security Note
We recommend that you secure your scripts to prevent their being altered by unauthorized by individuals.
  • If the instance of SQL Server is configured as a replication Distributor, you must restore the distribution database. For more information, see Back Up and Restore Replicated Databases.
  • Move the system databases to the locations you recorded previously. For more information, see Move System Databases.
  • Verify the server-wide configuration values match the values you recorded previously.

To rebuild the resource system database:

  1. Launch the SQL Server 2014 Setup program (setup.exe) from the distribution media.
  2. In the left navigation area, click Maintenance, and then click Repair.
  3. Setup support rule and file routines run to ensure that your system has prerequisites installed and that the computer passes Setup validation rules. Click OK or Install to continue.
  4. On the Select Instance page, select the instance to repair, and then click Next.
  5. The repair rules will run to validate the operation. To continue, click Next.
  6. From the Ready to Repair page, click Repair. The Complete page indicates that the operation is finished.


If the msdb database is damaged and you do not have a backup of the msdb database, you can create a new msdb by using the instmsdb script.
Caution note Caution
Rebuilding the msdb database using the instmsdb script will eliminate all the information stored in msdb such as jobs, alert, operators, maintenance plans, backup history, Policy-Based Management settings, Database Mail, Performance Data Warehouse, etc.
  1. Stop all services connecting to the Database Engine, including SQL Server Agent, SSRS, SSIS, and all applications using SQL Server as data store.
  2. Start SQL Server from the command line using the command: NET START MSSQLSERVER /T3608
    For more information, see Start, Stop, Pause, Resume, Restart the Database Engine, SQL Server Agent, or SQL Server Browser Service.
  3. In another command line window, detach the msdb database by executing the following command, replacing with the instance of SQL Server: SQLCMD -E -S -dmaster -Q"EXEC sp_detach_db msdb"
  4. Using the Windows Explorer, rename the msdb database files. By default these are in the DATA sub-folder for the SQL Server instance.
  5. Using SQL Server Configuration Manager, stop and restart the Database Engine service normally.
  6. In a command line window, connect to SQL Server and execute the command: SQLCMD -E -S -i"C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Install\instmsdb.sql" -o" C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Install\instmsdb.out"
    Replace with the instance of the Database Engine. Use the file system path of the instance of SQL Server.
  7. Using the Windows Notepad, open the instmsdb.out file and check the output for any errors.
  8. Re-apply any service packs or hotfix installed on the instance.
  9. Recreate the user content stored in the msdb database, such as jobs, alert, etc.
  10. Backup the msdb database.


Syntax and other run-time errors are displayed in the command prompt window. Examine the Setup statement for the following syntax errors:
  • Missing slash mark (/) in front of each parameter name.
  • Missing equal sign (=) between the parameter name and the parameter value.
  • Presence of blank spaces between the parameter name and the equal sign.
  • Presence of commas (,) or other characters that are not specified in the syntax.
After the rebuild operation is complete, examine the SQL Server logs for any errors. The default log location is C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Logs. To locate the log file that contains the results of the rebuild process, change directories to the Logs folder from a command prompt, and then run findstr /s RebuildDatabase summary*.*. This search will point you to any log files that contain the results of rebuilding system databases. Open the log files and examine them for relevant error messages.