Post reply

Script to restore all the Transaction log

  • April 10, 2008 at 7:53 am

    #189005

    Hi ,

    How can i restore bunch of Transaction log backup with out restoring one at a time. Which

    is very time consuming.

    Few time after the full database restore i have to manually applied all the transaction log backup

    manually.Any help will be appreciated.

    Regards

    Hammad khan

  • April 10, 2008 at 8:31 am

    #801009

    Since you're using SQL 2000 you could potentially use a combination of xp_dirtree and xp_getfiledetails to automate this. Here's a basic template for what you'd need to do.

    It's dependant on several things but it's possible. Hope this helps.

    --create a temp table called #files with 3 columns

    --insert contents of backup directory into table

    insert into #files exec exec xp_dirtree 'c:\sqlbackup\log\', 1, 1

    --delete rows from table that aren't files

    delete #files where [file] <> 1

    --now cursor through the rows in #files and for each file, use master.dbo.xp_getfiledetails 'file name' to find out when the file was created.

    --if you store the timestamp in the filename then you won't need to use xp_getfiledetails

    Karl
    source control for SQL Server

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply