June 4, 2003 at 3:20 pm
I'm not sure if this is the right place for this, but here goes. I currently have a database defined, and my boss wants to move the transaction logs to another drive. Can anyone help?
June 4, 2003 at 3:31 pm
You can use sp_detach/sp_attach. Read more about it in the KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
June 4, 2003 at 3:33 pm
First you are going to have to detach the db using <b>sp_detach_db</b>. Then move the log to the new location and use <b>sp_attach_db</b>.
The easiest way to do this is to open up SQL Query Analyzer and run these procedures. Look up on technet.com and/or MSDN for a description on how to use these procs.
Thanks,
Anthony Mendoza, Jr., MCSD, MCDBA
Micro-Systems, Inc.
Application Developer
June 5, 2003 at 9:18 am
I appreciate the help. I've tried what you said but I keep getting an error message 9003 stating that the LSN in the log scan is incorrect. Any solutions, ideas, ....?
June 5, 2003 at 9:42 am
Can you post the errorlog of SQL Server and the statement that you used to re-attach the database back.
June 5, 2003 at 2:18 pm
I don't have access to the log but I have the error message:
error 9003: The LSN (519:13819:1) passed to log scan in database 'Testjes' is invalid..
I used the following statement:
sp_attatch_db 'H:\testjes.mdf' 'H:\testjestblParent.ndf' 'F:\testjes.log'
This the best I can do. I'm still learning the ins and outs of MSSQL and my boss is into micro managing, so I don't get alot of hands stuff, unless I'm working with him
June 5, 2003 at 7:43 pm
quote:
sp_attatch_db 'H:\testjes.mdf' 'H:\testjestblParent.ndf' 'F:\testjes.log'
Your statemnet is incorrect. There is no database name.
Try following statement.
sp_attach_db @dbname = 'TestJes',
filename1 = 'H:\testjes.mdf''
filename2 = 'H:\testjestblParent.ndf',
filename3 = 'F:\testjes.log'
How did you get above MDF, NDF and LDF files? sp_attach_db should only be executed on databases that were previously detached from the database server using an explicit sp_detach_db operation. Or get copy of these files when SQL Server is shutdown.
June 11, 2003 at 9:40 am
I'm sorry, I re-typed the command and left out the database name. I got the file names by looking at the properties of the database before I detatched it using the sp_detatch command
June 11, 2003 at 12:19 pm
Is the new location of the file on the same box(dif. drive)?
June 12, 2003 at 1:14 pm
Yes
June 12, 2003 at 2:00 pm
below are two items copied from MSDN, maybe attach mdf, ldf files first then after that is complete use single file attach to attach the log file.
sp_attach_single_file_db
Attaches a database having only one data file to the current server.
Syntax
sp_attach_single_file_db [ @dbname = ] 'dbname'
, [ @physname = ] 'physical_name'
Arguments
[@dbname =] 'dbname'
Is the name of the database to be attached to the server. dbname is sysname, with a default of NULL.
[@physname =] 'phsyical_name'
Is the physical name, including path, of the database file. physical_name is nvarchar(260), with a default of NULL.
Return Code Values
0 (success) or 1 (failure)
Result Sets
None
Remarks
When sp_attach_single_file_db attaches the database to the server, it builds a new log file and performs additional cleanup work to remove replication from the newly attached database.
Used sp_attach_single_file_db only on databases that were previously detached from the server using an explicit sp_detach_db operation.
Permissions
Only members of the sysadmin and dbcreator fixed server roles can execute this procedure.
Examples
This example detaches pubs and then attaches one file from pubs to the current server.
EXEC sp_detach_db @dbname = 'pubs'
EXEC sp_attach_single_file_db @dbname = 'pubs',
@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
sp_attach_db
Attaches a database to a server.
Syntax
sp_attach_db [ @dbname = ] 'dbname'
, [ @filename1 = ] 'filename_n' [ ,...16 ]
Arguments
[@dbname =] 'dbname'
Is the name of the database to be attached to the server. The name must be unique. dbname is sysname, with a default of NULL.
[@filename1 =] 'filename_n'
Is the physical name, including path, of a database file. filename_n is nvarchar(260), with a default of NULL. There can be up to 16 file names specified. The parameter names start at @filename1 and increment to @filename16. The file name list must include at least the primary file, which contains the system tables that point to other files in the database. The list must also include any files that were moved after the database was detached.
Return Code Values
0 (success) or 1 (failure)
Result Sets
None
Remarks
sp_attach_db should only be executed on databases that were previously detached from the database server using an explicit sp_detach_db operation. If more than 16 files must be specified, use CREATE DATABASE with the FOR ATTACH clause.
If you attach a database to a server other than the server from which the database was detached, and the detached database was enabled for replication, you should run sp_removedbreplication to remove replication from the database.
Permissions
Only members of the sysadmin and dbcreator fixed server roles can execute this procedure.
Examples
This example attaches two files from pubs to the current server.
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.ldf'
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply