move DB location

[elibb]

hi, i have SQL server 7.0 and SQL Server 2000, and i have installed in the drive c:, but im running out of space. is there a way to move the location of my database to another disk drive? without having to create a new one and import all the data??

thank you very much

Eli

 

[TitleistDBA]

You can backup your database and then do a restore with move option. Here is an example from BOL.

RESTORE DATABASE { database_name | @database_name_var }
[ FROM < backup_device > [ ,...n ] ]
[ WITH
[ RESTRICTED_USER ]
[ [ , ] FILE = { file_number | @file_number } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]
[ ,...n ]
[ [ , ] KEEP_REPLICATION ]
[ [ , ] { NORECOVERY | RECOVERY | STANDBY = {undo_file_name|@undo_file_name_var} } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] REPLACE ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]

MOVE 'logical_file_name' TO 'operating_system_file_name'

Specifies that the given logical_file_name should be moved to operating_system_file_name. By default, the logical_file_name is restored to its original location. If the RESTORE statement is used to copy a database to the same or different server, the MOVE option may be needed to relocate the database files and to avoid collisions with existing files. Each logical file in the database can be specified in different MOVE statements.

http://www.acushnetcompany.com

 

[JayKusch]

you can also detach the DB, move it to another location and then re-attach it. this can be done through enterprise manager or query analyzer w/ the sp_detachdb and sp_attachdb

Thanks

J. Kusch

 

[TitleistDBA]

That will work for the SQL 2000 version but I don't think you can detach a SQL Server 7 db.

http://www.acushnetcompany.com

 

[JayKusch]

examples:

EXEC sp_detach_db 'pubs'

now move your your MDF and LDF files to the new drive THEN

EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ld

Make sure the new path is entered where the files were moved to.

Thanks

J. Kusch

 

[Corran007]

you can detach and attach sql 7 dbs with t-sql, but not with enterprise manager