Solved

10775: move .ldf files

Posted on 2014-10-27
3
200 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:
ScottPletcher 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:ScottPletcher
ScottPletcher 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

867 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now