Solved

10775: move .ldf files

Posted on 2014-10-27
3
205 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
  • 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

807 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