Problems moving data files 
Author Message
 Problems moving data files
WIN2KSP3 SQL2KSP3.

I'm attempting to move the physical files comprising a database
and need some advice on the best way of doing this.

I've tried the following, which doesn't work.
ALTER DATABASE Northwind
  MODIFY FILE (name = 'Northwind', filename = 'c:\temp\n.mdf')

Do I need to create a new file, move the data into it and delete the
original?

Any suggestions most welcome.



Mon, 15 Aug 2005 22:23:39 GMT
 Problems moving data files

You can detach the database, then physically move the files, then re-attach
from the new location.

--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/

Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.


Quote:
> WIN2KSP3 SQL2KSP3.

> I'm attempting to move the physical files comprising a database
> and need some advice on the best way of doing this.

> I've tried the following, which doesn't work.
> ALTER DATABASE Northwind
>   MODIFY FILE (name = 'Northwind', filename = 'c:\temp\n.mdf')

> Do I need to create a new file, move the data into it and delete the
> original?

> Any suggestions most welcome.



Mon, 15 Aug 2005 22:32:24 GMT
 Problems moving data files
Thanks for the reply.



Quote:
> You can detach the database, then physically move the files, then
re-attach
> from the new location.

> --
> Aaron Bertrand, SQL Server MVP
> http://www.aspfaq.com/

> Please reply in the newsgroups, but if you absolutely
> must reply via e-mail, please take out the TRASH.



> > WIN2KSP3 SQL2KSP3.

> > I'm attempting to move the physical files comprising a database
> > and need some advice on the best way of doing this.

> > I've tried the following, which doesn't work.
> > ALTER DATABASE Northwind
> >   MODIFY FILE (name = 'Northwind', filename = 'c:\temp\n.mdf')

> > Do I need to create a new file, move the data into it and delete the
> > original?

> > Any suggestions most welcome.



Mon, 15 Aug 2005 22:44:35 GMT
 Problems moving data files
Kevin,

Quote:
> WIN2KSP3 SQL2KSP3.

> I'm attempting to move the physical files comprising a database
> and need some advice on the best way of doing this.

> I've tried the following, which doesn't work.
> ALTER DATABASE Northwind
>   MODIFY FILE (name = 'Northwind', filename = 'c:\temp\n.mdf')

> Do I need to create a new file, move the data into it and delete the
> original?

Use sp_detach_db to detach the database. Move the database files
to the new location, rename if needed. Then use sp_attach_db to
reattach the database, specifying the new paths for the files.

Linda



Mon, 15 Aug 2005 23:12:35 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Move data and log files directories and files

2. moving data files...

3. Finding Indexes - Moving Between Filegroups/Data Files

4. Move all tables from a .ndf file to the .mdf file and eliminate the .ndf file

5. Moving SQL 7 Data Files from C: to D:

6. Moving SYSTEM data files

7. data files moved ?

8. Moving Data Files

9. Moving Data files in SQL 7

10. Moving TempDB data file

11. Move a single data file

12. move data files...


 
Powered by phpBB® Forum Software