Db Crash happened,Log backup issue,Pls help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Db Crash happened,Log backup issue,Pls help

Author

Topic

Ravikumarc
Yak Posting Veteran

51 Posts

Posted - 2009-12-08 : 19:18:20

Dear All,

Db Crash has happened at mid day, here i have to take full backup and Log backup...
Issue Is
********
We are taking log back up for every 15 mins..Db crashes at mid day at a particular time..So here i have to take 5 hrs of log backup (i.e.) 20 log backups with full backup. Can you please help me out by providing the scripts " how to take Log backup scripts from bottom (old time) to top (recent time).
I’m not using Mirroring, Logshipping, Clustering concepts..

Example log backup which I have taken

G:\Backup\Prods\Sam\CBH_tlog_200912051545.TRN
G:\Backup\Prods\Sam\CBH_tlog_200912051530.TRN
G:\Backup\Prods\Sam\CBH_tlog_200912051515.TRN
G:\Backup\Prods\Sam\CBH_tlog_200912051500.TRN
G:\Backup\Prods\Sam\CBH_tlog_200912051445.TRN
G:\Backup\Prods\Sam\CBH_tlog_200912051430.TRN
G:\Backup\Prods\Sam\CBH_tlog_200912051415.TRN
G:\Backup\Prods\Sam\CBH_tlog_200912051400.TRN
G:\Backup\Prods\Sam\CBH_tlog_200912051345.TRN
G:\Backup\Prods\Sam\CBH_tlog_200912051330.TRN
G:\Backup\Prods\Sam\CBH_tlog_200912051315.TRN
G:\Backup\Prods\Sam\CBH_tlog_200912051300.TRN
G:\Backup\Prods\Sam\CBH_tlog_200912051245.TRN
G:\Backup\Prods\Sam\CBH_tlog_200912051230.TRN
G:\Backup\Prods\Sam\CBH_tlog_200912051215.TRN
G:\Backup\Prods\Sam\CBH_tlog_200912051200.TRN

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-08 : 21:53:07

Here's what I use:


DECLARE @localPath varchar(100), @cmd nvarchar(4000)

SELECT @localPath = 'G:\Backup\dbName\'

CREATE TABLE #files(DirInfo VARCHAR(7000))

SELECT @cmd = 'dir "' + @localPath + '*.TRN" /OD'

INSERT INTO #files
EXEC master..xp_cmdshell @cmd

SELECT 'RESTORE LOG dbName FROM DISK = ''' + @localPath + bkpFileName + ''' WITH NORECOVERY'
FROM
(
	SELECT bkpFileName = SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) 
	FROM #files
	WHERE 
		ISDATE(SUBSTRING(DirInfo, 1, 10)) = 1 AND 
		DirInfo NOT LIKE '%<DIR>%'
) t

DROP TABLE #files

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Ravikumarc
Yak Posting Veteran

51 Posts

Posted - 2009-12-10 : 14:53:13

Dear tkizer,

Yep it works fine for me,Thanks a lot for yr timely help

Regards
Ravi

quote:
Originally posted by tkizer

Here's what I use:

DECLARE @localPath varchar(100), @cmd nvarchar(4000)

SELECT @localPath = 'G:\Backup\dbName\'

CREATE TABLE #files(DirInfo VARCHAR(7000))

SELECT @cmd = 'dir "' + @localPath + '*.TRN" /OD'

INSERT INTO #files
EXEC master..xp_cmdshell @cmd

SELECT 'RESTORE LOG dbName FROM DISK = ''' + @localPath + bkpFileName + ''' WITH NORECOVERY'
FROM
(
SELECT bkpFileName = SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))
FROM #files
WHERE
ISDATE(SUBSTRING(DirInfo, 1, 10)) = 1 AND
DirInfo NOT LIKE '%<DIR>%'
) t

DROP TABLE #files

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-10 : 15:13:27

You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources