Removing Logshipping infomation from Primary and secondary servers

Many times, there is a need to log ship a database to a second server for upgrading or testing.

Once the logshipping is stopped and the database brought online, there can often be settings left behind.  This will cause reports, or checks to show errors.  (You all have checks in place to look for logshipped databases that are out of sync, right?)

So how can it be removed?

Here goes:-

On the primary (must be run in master)

EXEC master.dbo.sp_delete_log_shipping_primary_secondary
@primary_database = N'AdventureWorks'
,@secondary_server = N'PRIMARY\STANDBY'
,@secondary_database = N'AdventureWorks'

On the secondary (also in master)



sp_delete_log_shipping_secondary_databaseN'AdventureWorks'


THIS WILL NOT DELETE THE DATABASE, its just bad wording on Microsoft's part.

Then if needed delete the secondary database.

When you absolutely positively got to kill every mother ****er in the room ACCEPT NO SUBSTITUTES!!!!

Ever been in the situation where you need to restore a database, delete, take off line or detach, but "exclusive access cannot be obtained as the database is in use"?

Then here is the script for you.  Use with caution, it will end any open transactions, and is not recommended for any thing but a database that's now junk, ie to be deleted or restored over.


declare @SQLText varchar(8000) ,@spid int
select @spid = min(spid) from master..sysprocesses where dbid = db_id('<Databasename>')
while @spid is not null
begin
 select @SQLText = 'kill '+convert(varchar(5),@spid)
 select @SQLText
 execute (@SQLText)
 -- inc counter
 select @spid = min(spid) from master..sysprocesses where dbid = db_id('<databasename>') and spid > @spid
end


Remember when restoring a database over the top, its always best to not drop said database first. As this will cause the restore to have to recreate file's ect. And will make it take longer.


http://www.youtube.com/watch?v=yxQNYPYFq1c