Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

10775: move .ldf files

Posted on 2014-10-27
3
Medium Priority
?
223 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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 2000 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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

572 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