Drop Primary Transaction Log File

Hi All,
I attemping to move a transaction log file online to another location. I performed the following steps;
- Created a second(log_name2) log file.
- ran DBCC Shrinkfile(log_name1,emptyfile)
- alter database mydb
remove file log_name1
* However, I receive the following error 5020 "The primary data or log file cannot be removed from a database".
Is it possible to remove the original log file?
Mike,
You might have to take the database offline by doing:
1.sp_detach_db
2.Then attach the database back using sp_attach_db but this time mentioning
the new path for ldf file.Refer BooksOnLine for syntax of the two commands.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"MikeDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:94423849-18A7-4E5F-9296-ED3EBDD3CCD3@.microsoft.com...
> Hi All,
> I attemping to move a transaction log file online to another location. I
performed the following steps;
> - Created a second(log_name2) log file.
> - ran DBCC Shrinkfile(log_name1,emptyfile)
> - alter database mydb
> remove file log_name1
> * However, I receive the following error 5020 "The primary data or log
file cannot be removed from a database".
> Is it possible to remove the original log file?
|||Hi,
In your case , I feel that the only solution is detach and attach the
databases.
Steps:
1. detach the database using sp_detach_db
2. copy the LDF to new location
3. Attach it back using sp_attach_db
Have a look into the below link:
http://msdn.microsoft.com/library/de...us/createdb/cm
_8_des_03_9dbn.asp
Thanks
Hari
MCDBA
"MikeDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:94423849-18A7-4E5F-9296-ED3EBDD3CCD3@.microsoft.com...
> Hi All,
> I attemping to move a transaction log file online to another location. I
performed the following steps;
> - Created a second(log_name2) log file.
> - ran DBCC Shrinkfile(log_name1,emptyfile)
> - alter database mydb
> remove file log_name1
> * However, I receive the following error 5020 "The primary data or log
file cannot be removed from a database".
> Is it possible to remove the original log file?

Drop Primary Transaction Log File

Hi All,
I attemping to move a transaction log file online to another location. I per
formed the following steps;
- Created a second(log_name2) log file.
- ran DBCC Shrinkfile(log_name1,emptyfile)
- alter database mydb
remove file log_name1
* However, I receive the following error 5020 "The primary data or log file
cannot be removed from a database".
Is it possible to remove the original log file?Mike,
You might have to take the database offline by doing:
1.sp_detach_db
2.Then attach the database back using sp_attach_db but this time mentioning
the new path for ldf file.Refer BooksOnLine for syntax of the two commands.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"MikeDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:94423849-18A7-4E5F-9296-ED3EBDD3CCD3@.microsoft.com...
> Hi All,
> I attemping to move a transaction log file online to another location. I
performed the following steps;
> - Created a second(log_name2) log file.
> - ran DBCC Shrinkfile(log_name1,emptyfile)
> - alter database mydb
> remove file log_name1
> * However, I receive the following error 5020 "The primary data or log
file cannot be removed from a database".
> Is it possible to remove the original log file?|||Hi,
In your case , I feel that the only solution is detach and attach the
databases.
Steps:
1. detach the database using sp_detach_db
2. copy the LDF to new location
3. Attach it back using sp_attach_db
Have a look into the below link:
http://msdn.microsoft.com/library/d...-us/createdb/cm
_8_des_03_9dbn.asp
Thanks
Hari
MCDBA
"MikeDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:94423849-18A7-4E5F-9296-ED3EBDD3CCD3@.microsoft.com...
> Hi All,
> I attemping to move a transaction log file online to another location. I
performed the following steps;
> - Created a second(log_name2) log file.
> - ran DBCC Shrinkfile(log_name1,emptyfile)
> - alter database mydb
> remove file log_name1
> * However, I receive the following error 5020 "The primary data or log
file cannot be removed from a database".
> Is it possible to remove the original log file?