Move sql ldf files

Nupur Dave is a social media enthusiast and an independent consultant. She primarily focuses on the database domain, helping clients build short and long term multi-channel campaigns to drive leads for their sales pipeline.

Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check , we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.

Once you learn my business secrets, you will fix the majority of problems in the future. Step 2: Detach Database To detach the database you will need to move it first in a single user mode. Leave new Bill Nye. Leave a Reply Cancel reply. Exclusive Newsletter Website. Go to mobile version.

However, it is an old method and it is not necessary to follow the same method now. I personally use the following method to move the data and log files for my customers. Let us learn that step by step. Before continuing to this blog post, you should read the following two blog posts as they will be useful to get logical and physical file names. First, we will get the location of the physical files using the following command.

Now in this step, you will have to move your database files from the original location to the new location. Running following command will change the location of the file for SQL Server.

You do not have use older method of Detach or Attach anymore if you want to move your database. Note to Team: For your review. Stop the instance of SQL Server or shut down the system to perform maintenance. Move the file or files to the new location and verify the SQL Server service account still has permission to access it. Restart the instance of SQL Server or the server. If the msdb database is moved and the instance of SQL Server is configured for Database Mail , complete these additional steps.

If a file must be moved because of a hardware failure, follow these steps to relocate the file to a new location. If the database cannot be started, that is it is in suspect mode or in an unrecovered state, only members of the sysadmin fixed role can move the file. Start the instance of SQL Server in master-only recovery mode by entering one of the following commands at the command prompt. The parameters specified in these commands are case sensitive. The commands fail when the parameters are not specified as shown.

For more information about using the sqlcmd utility, see Use the sqlcmd Utility. In the Existing parameters box, select the -d parameter. In the Specify a startup parameter box, change the parameter to the new path of the master data file. Click Update to save the change. In the Existing parameters box, select the -l parameter.

In the Specify a startup parameter box, change the parameter to the new path of the master log file. The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data file.

At this point SQL Server should run normally. Failure to update the registry can cause patching and upgrading to fail. The database cannot be moved.