Modificar ruta de archivos

Hola,

esto te servira.

Moving User Databases

The following example moves a database named mydb, which contains one data
file, Mydb.mdf, and one log file, Mydblog.ldf. If the database you are
moving has additional data or log files, specify all of them in a
comma-delimited list in the sp_attach_db stored procedure. The sp_detach_db
procedure does not change no matter how many files the database contains
because it does not list them.



1. Detach the database as follows:



use master

go

sp_detach_db 'mydb'

go

2. Next, copy the data and log files from the current location
(D:\Mssql7\Data) to the new location (E:\Sqldata).



3. Re-attach the database pointing to the files in the new location as
follows:



use master

go

sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'

go



Verify the change in file locations using sp_helpfile:

use mydb

go

sp_helpfile

go



The filename column values should reflect the new locations.



Moving MSDB, Pubs, and Northwind

Follow the same procedure for moving user databases. For MSDB, make sure the
SQL Server Agent is not currently running. Otherwise, the sp_detach_db
stored procedure fails with the following message:

Server: Msg 3702, Level 16, State 1, Line 0
Cannot drop the database 'msdb' because it is currently in use.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

Moving the Master Database

1. Change the path for the master data and log files in
SQL Server Enterprise Manager.

NOTE: You may optionally change the location of the error log here as well.



2. Right-click the SQL Server in Enterprise Manager and
click Properties on the shortcut menu.



3. Click the Startup Parameters button and you see the
following entries:

-dD:\MSSQL7\data\master.mdf

-eD:\MSSQL7\log\ErrorLog

-lD:\MSSQL7\data\mastlog.ldf

-d is the fully qualified path for the master database data file.

-e is the fully qualified path for the error log file.

-l is the fully qualified path for the master database log file.



4. Change these values as follows:



a. Remove the current entries for the Master.mdf and Mastlog.ldf
files.



b. Add new entries specifying the new location:



-dE:\SQLDATA\master.mdf

-lE:\SQLDATA\mastlog.ldf

5. Stop SQL Server.



6. Copy the Master.mdf and Mastlog.ldf files to the new
location (E:\Sqldata).



7. Restart SQL Server.



Moving the Model Database

To move the model database, SQL Server must be started with trace flag 3608
so that it does not recover any database except the master.



NOTE: You will not be able to access any user databases at this time. You
should not perform any operations other than the steps below while using
this trace flag. To add trace flag 3608 as a SQL Server startup parameter,
perform the following steps:



1. In SQL Server Enterprise Manager, right-click the server name and
click Properties on the shortcut menu.

2. On the General tab, click Startup Parameters.

3. Add a new parameter as -T3608.



After adding trace flag 3608, perform the following steps:

1. Stop and restart SQL Server.

2. Detach the model database as follows:

use master

go

sp_detach_db 'model'

go

3. Move the Model.mdf and Modellog.ldf files from D:\Mssql7\Data to
E:\Sqldata.

4. Reattach the model database as follows:



use master

go

sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'

go

5. Remove the -T3608 trace flag from the startup parameters box in the
Enterprise Manager.

6. Stop and restart SQL Server.



You can verify the change in file locations using sp_helpfile:

use model

go

sp_helpfile

go

Moving Tempdb

You can move tempdb files by using the ALTER DATABASE statement.

1. Determine the logical file names for the tempdb
database by using sp_helpfile as follows:



use tempdb

go

sp_helpfile

go

The logical name for each file is contained in the name column. This example
uses the default file names of tempdev and templog.



2. Use the ALTER DATABASE statement, specifying the
logical file name as follows:



use master

go

Alter database tempdb modify file (name = tempdev, filename 'E:\Sqldata\tempdb.mdf')

go

Alter database tempdb modify file (name = templog, filename 'E:\Sqldata\templog.ldf')

go

You should receive the following messages confirming the change:

File 'tempdev' modified in sysaltfiles. Delete old file after restarting SQL
Server.

File 'templog' modified in sysaltfiles. Delete old file after restarting SQL
Server.

3. Using sp_helpfile in tempdb will not confirm these
changes until you restart SQL Server.

4. Stop and restart SQL Server.





Ademas, esta listita de links te va a resultar interesante ...

Moving DB's between Servers
http://www.support.microsoft.com/?id14546

Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id"4071

Using WITH MOVE in a Restore
http://support.microsoft.com/?id"1465

How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id$6133

Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id)8897

Utility to map logins to users
http://www.dbmaint.com/SyncSqlLogins.asp

User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id8001

How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id$0872

Restoring a .mdf
http://www.sqlservercentral.com/scr...scriptidY9

Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id07775



Saludos !

Emilio Boucau
Buenos Aires - Argentina
http://www.portalsql.com