Announcement

Collapse
No announcement yet.

Script for auto backup restore on SQL server 2000?

Collapse

Ads on showthread pages

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Script for auto backup restore on SQL server 2000?

    WordPress Hosting on SSD Servers
    Hello All,

    One can use below mentioned script to restore all databases from available backup:

    ----------------------------------------------
    USE Master;
    GO
    SET NOCOUNT ON

    -- 1 - Variable declaration
    DECLARE @dbName sysname
    DECLARE @backupPath NVARCHAR(500)
    DECLARE @cmd NVARCHAR(500)
    DECLARE @fileList TABLE (backupFile NVARCHAR(255))
    DECLARE @lastFullBackup NVARCHAR(500)
    DECLARE @lastDiffBackup NVARCHAR(500)
    DECLARE @backupFile NVARCHAR(500)

    -- 2 - Initialize variables
    SET @dbName = 'Customer'
    SET @backupPath = 'D:\SQLBackups\'

    -- 3 - get list of files
    SET @cmd = 'DIR /b ' + @backupPath

    INSERT INTO @fileList(backupFile)
    EXEC master.sys.xp_cmdshell @cmd

    -- 4 - Find latest full backup
    SELECT @lastFullBackup = MAX(backupFile)
    FROM @fileList
    WHERE backupFile LIKE '%.BAK'
    AND backupFile LIKE @dbName + '%'

    SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
    + @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE'
    PRINT @cmd

    -- 4 - Find latest diff backup
    SELECT @lastDiffBackup = MAX(backupFile)
    FROM @fileList
    WHERE backupFile LIKE '%.DIF'
    AND backupFile LIKE @dbName + '%'
    AND backupFile > @lastFullBackup

    -- check to make sure there is a diff backup
    IF @lastDiffBackup IS NOT NULL
    BEGIN
    SET
    @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
    + @backupPath + @lastDiffBackup + ''' WITH NORECOVERY'
    PRINT @cmd
    SET @lastFullBackup = @lastDiffBackup
    END

    -- 5 - check for log backups
    DECLARE backupFiles CURSOR FOR
    SELECT
    backupFile
    FROM @fileList
    WHERE backupFile LIKE '%.TRN'
    AND backupFile LIKE @dbName + '%'
    AND backupFile > @lastFullBackup

    OPEN backupFiles

    -- Loop through all the files for the database
    FETCH NEXT FROM backupFiles INTO @backupFile

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET
    @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''
    + @backupPath + @backupFile + ''' WITH NORECOVERY'
    PRINT @cmd
    FETCH NEXT FROM backupFiles INTO @backupFile
    END

    CLOSE
    backupFiles
    DEALLOCATE backupFiles

    -- 6 - put database in a useable state
    SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY'
    PRINT @cmd
    ----------------------------------------------

    Thanks,

    Shane G.
    AccuWebHosting.Com

  • #2
    ASP.Net Hosting starts @ $3.18/Mo.
    Re: Script for auto backup restore on SQL server 2000?

    Well, We dont see a point why you are doing this, when you see the data is being lost in the Temporary Database and there is sure some mistake in the process. I have also done these things in Windows 2000 and the data stays there in the Temporary Database and you were asking in SQL Server 2005 and that is the topic of this thread and I told you its not the way and you can just use Backup & Restore option in SQL Server 2005 and its straight forward.

    If you dont plan to find whats the problem and say you are doing the same mistake for so many years, you are free to go with it

    Thanks.

    Comment

    Working...
    X
    Sign up for a News Letter Click here to sign up