Solved

10775: move .ldf files

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

708 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

16 Experts available now in Live!

Get 1:1 Help Now