To move the SQL databases MDF and LDF files to a new location, you will need to use the SQL Server Management Studio (SSMS). SQL Server Management Studio (SSMS) provides a graphical interface for managing and configuring SQL Server databases.
Here are the steps you can follow to move the SQL databases MDF and LDF files to a new location using SQL Server Management Studio:
- Before doing anything, make sure no one is using the venio project associated to the SQL database you are planning on moving.
- It is highly recommended (but optional) to use the Backup/Restore module in the Administration tab of the Venio Console and follow the steps in the Backup wizard to store the Venio project database backup in case anything goes wrong. As there is no need to backup all project data (we are only moving database files, not project data) it is highly recommended to choose the Backup Option "Backup project database only". Disabling the Compression Option 'Compress and archive' is also recommended. If you run into any problems creating the backup, please contact Venio support.
Make sure your backup options selected match the above screenshot. Follow the rest of the steps in the backup wizard until you receive a prompt that the backup was successful. If you run into any problems creating the backup, please contact Venio support. -
Start by launching SQL Server Management Studio and connecting to the SQL Server instance that contains the databases you want to move.
-
In the Object Explorer panel on the left side of the screen, expand the "Databases" node and then right-click on the database that you want to move. Select the "Properties" option from the context menu.
-
In the "Database Properties" window, select the "Files" page. This page shows the current locations of the database MDF and LDF files. Note these locations as we will need to browse to these locations in Windows Explorer later.
- In the Object Explorer panel, right-click on the database again and select the "Tasks" > "Detach" option from the context menu. This will detach the database from the SQL Server instance.
- In the "Detach Database" window, select the "Drop Connections" option and then click on the "OK" button. This will disconnect any users who are currently connected to the database and allow the database to be moved to the new location.
- After the database has been detached, copy the MDF and LDF files from the old location to the new location. Browse to the locations of the MDF and LDF files we noted in step 3 in Windows Explorer. Copy and paste the MDF and LDF to the proper locations on the different drive(s). Make sure to copy both files and to keep the same file names.
- Once the files have been copied to the new location, attach the database to the SQL Server instance again. In the Object Explorer panel, right-click on the "Databases" node and select the "Attach" option from the context menu.
- In the "Attach Databases" window, click on the "Add" button and navigate to the new location where the MDF files are stored. Select the MDF file and click on the "OK" button to attach the database.
-
Repeat the previous steps for the LDF file, using the "Browse" button next to the "Path" column for the LDF file.
-
After updating the file paths for the MDF and LDF files, click on the "OK" button in the "Attach Databases" window to save the changes.
-
After the database has been attached, it will be available for use with the SQL Server instance, and the MDF and LDF files will be stored in the new location.
- It is important to make sure that the SQL service account has access to the new location. If SSMS can't 'see' the directory you want, it is likely that SQL Server does not have access to it.
- Test accessing and ingesting data in the associated Venio project of the database you just moved and make sure that any backup scripts running in the SQL server are properly creating backups of the database which is now located in the new drive.
Overall, moving the SQL databases MDF and LDF files to a new location is a process that involves using the SQL Server Management Studio tool and detaching and attaching the database. By following these steps, you can migrate your SQL data files to a different drive or location.
Deleting the old MDF and LDF
Once you are certain the databases have been moved successfully and you have tested ingesting data in the Venio project, verify that the database is using the new path. In the Object Explorer panel on the left side of the screen, expand the "Databases" node and then right-click on the database that you want to verify. Select the "Properties" option from the context menu. In the "Database Properties" window, select the "Files" page. This page shows the current locations of the database MDF and LDF files. Note these and make sure they are pointing to the new location. Make sure that any backup scripts running in the SQL server are properly creating backups of the database which is now located in the new drive. It should now be safe to delete the old \ original MDF and LDF files from the old drive. Make sure you ONLY delete the MDF and LDF files of projects you are sure have been moved and re-attached successfully. As always, you should not delete anything unless you are absolutely certain.
If you have any doubts, questions, or concerns, confirm with your organization's DBA (DataBase Administrator) and\or Information Technology department.
Configure the SQL Server to automatically use the new drive for new Databases
Follow these steps if you need all new databases to store their database and log files (mdf, ndf, and ldf) to the new location. The following steps will only apply to new databases. If you need to move existing databases, you will still have to follow the previous steps to move them.
1. In SSMS, right click on the server and choose "Properties".
2. On the "Database Settings" page of the Server Properties window, specify your new locations for data, log and backup files.
Comments
0 comments
Please sign in to leave a comment.