Solved

10775: move .ldf files

Posted on 2014-10-27
3
207 Views
Last Modified: 2014-10-28
I'm thinking of using this procedure below to move the * .ldf of the data bases, could validating and whether this right, your will need something extra to make after moving the databases remain operational or if it has other procedure that I recommend:

Estoy pensando usar este procedimiento descrito a continuación para mover los archivos *.ldf de las bases de Datos, podría validarme e indicar si esta bien, su se  necesita algo adicional para que luego del movimiento la bases de datos queden operativas o si es que tiene otro procedimiento que me recomiende:
- The 1st valid path where files are databases:
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'NombreBaseDatos');

- 2nd setting the database to single user mode
Use MASTER
GO
ALTER DATABASE adventureWorks
SET SINGLE_USER
GO

- 3rd We perform Detach the database
USE master
GO
sp_detach_db 'NombreBaseDatos'
GO

- 4th physically move the files to their new locations according corresponds with Windows Explorer
- 5th Attacheo the database to their new locations
USE master
GO
sp_attach_DB 'AdventureWorks',
'E:\SQLDATA\NombreBaseDatos.mdf',
'F:\SQLOG\NombreBaseDatos_Log.ldf'
GO

 Applies the new path of the database and its status as if online
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'NombreBaseDatos');


It is operating as valid?
Thanks in advance for your kind help and collaboration.
0
Comment
Question by:enrique_aeo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40408463
You should not detach the db unless you need to move the db to a different instance.  To move a file(s) to a different drive on the same server, you can:
1) ALTER the db file(s) to reflect the new physical location
2) Take the db offline
3) Move the file(s) to the new location
4) Put the db back online.
0
 

Author Comment

by:enrique_aeo
ID: 40408594
What is the problem if dettach use?
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 total points
ID: 40408650
It's just an obsolete method, but it might still work OK ... then again, it might not.

1) Detach changes the security on the file.  Only *the* id that detached it can reattach it.  This can be a real pain in certain cases.
2) If you detach a db that has errors (CHECKDB wouldn't be clean), it most likely will not reattach, and those db files are now useless.
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question