Monthly Archives: January 2015

How to move log shipped secondary database files

One of our clients wanted to consolidate files on one of their servers.  In doing this I realized that I was going to have to move the files for a database that is actually log shipped.  This is a fairly easy task that is pretty well documented but since I had to do it I thought “What the hey!  I should blog about this.” so here we are.

The main problem is how to do this without breaking log shipping in some way.  If you have a huge database that can take forever to restore and reset log shipping you will want to do this the right way so you don’t have to slink to your IT Manager to say that reporting or whatever will not be available while you fix things.  Not that I would have any experience with that….

Whatever you do, don’t  think you can move the log shipped database by simply detaching then attaching again.  This will not work and you will be making the walk or call of shame to your IT Manager.  You can move the files after doing a few more steps that I have outlined below.  Now go have at it!

1. Get the location of the secondary database files in case you don’t already know where they are.  You will need the logical name later on.

SELECT name AS DBLogicalName,
physical_name AS Location,
CASE type WHEN 0 THEN ‘Database File’ WHEN 1 THEN ‘Transaction Log’ END AS FileType,
state_desc AS [State]
FROM sys.master_files
WHERE database_id = DB_ID(‘LS_DB_NAME’)

2. Disable the LS_RESTORE job for the database so that no logs are restored until after the files are moved.

USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N’LSRestore_??_??’, @enabled=0
GO

3. Make sure the database is in NORECOVERY state if it is currently in STANDBY state.  If the database is already in NORECOVERY state then you don’t need to run the below code.

USE [master]
GO
RESTORE LOG LS_DB_NAME WITH NORECOVERY
GO

4. Execute the ALTER DATABASE command to tell SQL where the files will be located soon.

USE [master]
GO
ALTER DATABASE LS_DB_NAME
MODIFY FILE ( NAME = DBLogicalName, FILENAME = ‘Path to new location’)
GO

5. Stop SQL Server services including the SQL Agent service.

6. Use Windows Explorer to move the files to the new location.

7. Restart the SQL Server services including the SQL Agent service.

8. Enable the LS_RESTORE job for the database.

USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N’LSRestore_??_??’, @enabled=1
GO

9. Verify that all log shipping jobs are still working.

For those of you who have log shipped secondary databases in STANDBY and are wondering if it will go from NORECOVERY to STANDBY again….the answer is that it will as soon as the LS_RESTORE job runs and restores transaction logs again. Continue reading

Rebuild lost or deleted transaction log files

SQL Server Administration: Every so often someone will call with a terrible problem because a drive was lost or someone mistakenly deleted transaction log files.  When this happens, the affected databases are not useable and are usually marked as SUSPECT or IN RECOVERY in Management Studio.  Usually the best option is to restore the database from a backup however, sometimes backup files may be lost or simply not set up and therefore do not exist.  If the transaction log files cannot be recovered and a database backup is not available a last ditch option is to force SQL Server to rebuild the lost transaction log files.  This option should be the last to be considered but there are cases where doing this is justified.  Since the transaction log file is built as a brand new file, any transactions that were in the old transaction log file would be lost so it would be possible that data corruption would occur.  You will obviously need to create folders for the transaction logs on a drive with enough space to hold the new log files.

Force a new transaction log to be built

Use this statement to get the names to substitute in the appropriate places in the ALTER DATABASE statements that will follow:

select d.name as dbname, m.name as logicalname, m.physical_name

from sys.master_files m

join sys.databases d on (d.database_id = m.database_id)

where m.type = 1 — Log

order by 1, 2

You may want to use the actual file name from the physical_name column in the FILENAME value below.  Use the new folder path as the path in the FILENAME value.  Run each statement one-by-one and especially pay attention to what the CHECKDB output returns if there are errors.  If it says you need to run REPAIR_ALLOW_DATA_LOSS then uncomment the command to do so and run that.  When you get to no errors from the DBCC CHECKDB then you can put the database in MULTI_USER and can use the database then.

USE master

GO

ALTER DATABASE [dbname] SET EMERGENCY

GO

ALTER DATABASE [dbname] SET SINGLE_USER

GO

ALTER DATABASE [dbname] REBUILD LOG ON

(NAME= logicalname, FILENAME=’Drive path to new file location\Log file name.ldf’) — Like ‘E:\MSSQL\LOGS\DB_Log.ldf’

GO

DBCC CHECKDB ([dbname])

GO

— DBCC CHECKDB ([dbname], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

— GO

ALTER DATABASE [dbname] SET MULTI_USER

GO