Changing the location of your SQL databases

[G.W.Green]

If you want to change the location of your SQL data, follow these steps:

1. Create a directory in the location you want the data.
2. Open SQL Server Management Studio and make backups of your current databases
3. In SQL Server Management Studio, right click on the directory at the top of the tree in the column on the left. Choose Properties.
4. In the properties window, choose ‘Database Settings’
   
   
   
   
5. Now change the ‘Database default locations’ for both Data and Log fields
6. This will mean that any new databases will be created here.
7. Now detach the current databases so that you can move them:
   - Right click on the database and go to ‘Tasks | Detach…’
   
   
   
   
   - Tick ‘Drop Connections’
   
   
   
   
   - You will notice that the database has disappeared from the database tree. Do this for the Catalog and the Shipping databases.
   
   
   
   
8. Now the databases are detached, they need to be moved. You firstly need to stop the SQL Server service from running, otherwise the databases are still ‘in use’ and therefore cannot be moved.
   - Open SQL Server Configuration Manager by going to ‘Start | Programs | Microsoft SQL Server [version] | Configuration Tools’
   
   
   
   
   - Right click on the SQL Server service and stop it. The name of the SQL Server service will be the service name then the instance (install) name in brackets
   
   
   
   
9. Now browse to the current SQL data directory. The default location will be similar to this:
   ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLSERVER2008_R2\MSSQL\DATA’
   
10. Locate the database files you wish to move. There will be an MDF and an LDF file for each database. For a SellerDeck site, that means you need to find at least four files. Two for Catalog and two for Shipping
    
    
    
    
11. Cut these files and paste them into the new directory you set up at the start.
12. Now start the SQL Server service back up, via SQL Server Configuration Manager
    
    
    
    
13. Now its time to re-attach those databases:
    - In SQL Server Management Studio, right click on ‘Databases’ at the top of the tree and choose ‘Attach…’
    
    
    
    
    - In the attach databases window, click ‘Add’. This will open another window where you choose the databases in your new location. Because we changed the default paths earlier, the new location should already be chosen.
    
    
    
    
    Select the Catalog.mdf file and press ok, and ok again. Do the same again for the Shipping.mdf file.
    
    - Now right click on ‘Databases’ and choose refresh. You will see the databases reappear in the tree.
    
    
    
    
14. This completes the move of your SQL databases. You can now launch the site SellerDeck as normal.