SQL 2000 Script for Backup and Restore database.

 

This simple SQL Query will help to take a full backup of any SQL 2000 database , to your hard disk .

 

how to use ? Copy and paste the code below to SQL Query Analyzer and replace ‘Databasename’ and ‘backupfile’ with your values , then press F5

DECLARE @DataBaseName VARCHAR(128)
DECLARE @Backupfile VARCHAR(255)

SET @DataBaseName = 'DatabaseName'  -- your database name goes here
SET @Backupfile = 'Backupfile'        -- your backup file path goes here


BACKUP DATABASE @DataBaseName TO DISK = @Backupfile WITH INIT 

 

 

This advanced SQL Query will help to restore any SQL 2000 database , from a backup file.

how to use ? Copy and paste the code below to SQL Query Analyzer and replace ‘Databasename’ and ‘backupfile’ with your values , then press F5

USE MASTER

DECLARE @BackupFile VARCHAR(255)
DECLARE @DataBaseName VARCHAR(128)
DECLARE @ApplicationName VARCHAR(128)

SET @DataBaseName = 'DataBaseName'    -- your database name goes here
SET @BackupFile = 'BackupFile'        -- your backup file path goes here


SET @ApplicationName = NEWID() -- Not Is use 

CREATE TABLE [#tempHearder]
    (
        [LogicalName] [VARCHAR](128) ,
        [PysicalName] [VARCHAR](255) ,
        [Type] [VARCHAR](5) ,
        [FileGroupName][VARCHAR](15) ,
        [Size] [INT],
        [MaxSize] [Float]
    )

DECLARE @ListFiles VARCHAR (128)
DECLARE @DBName VARCHAR (128)
DECLARE @DBLog VARCHAR (128)

SET @ListFiles = 'RESTORE FILELISTONLY FROM DISK = ''' + @BackupFile + ''''

INSERT INTO #tempHearder EXEC (@ListFiles)

SET @DBName = (SELECT RTRIM(LogicalName) FROM #tempHearder WHERE Type = 'D')
SET @DBLog = (SELECT RTRIM(LogicalName) FROM #tempHearder WHERE Type = 'L')

DROP TABLE #tempHearder


CREATE TABLE [#tempDBPath]
    (
        [DbPath] [VARCHAR](128) ,
    )

CREATE TABLE [#tempLogPath]
    (
        [LogPath] [VARCHAR](128) ,
    )

DECLARE @DbPath VARCHAR(128)
DECLARE @LogPath VARCHAR(128)

SET @DBPath = 'SELECT filename FROM '  + @DataBaseName + '..sysfiles WHERE fileid = 1'
INSERT INTO #tempDBPath EXEC(@DbPath)

SET @LogPath = 'SELECT filename FROM ' + @DataBaseName + '..sysfiles WHERE fileid = 2'
INSERT INTO #tempLogPath EXEC(@LogPath)

SET @DBPath = (Select RTRIM(DbPath) from #tempDBPath)
SET @LogPath = (Select RTRIM(LogPath) from #tempLogPath)

DROP TABLE #tempDBPath
DROP TABLE #tempLogPath

-- Kill other users 
DECLARE @PID CURSOR

DECLARE @GetSPID INT DECLARE @KillPID VARCHAR (15)

SET @PID = CURSOR FOR SELECT spid FROM master..sysprocesses 
JOIN master..sysdatabases ON master..sysprocesses.dbid = master..sysdatabases.dbid 
WHERE Name = @DataBaseName AND program_name <> @ApplicationName

OPEN @PID 
FETCH NEXT FROM @PID into @GetSPID 

WHILE @@FETCH_STATUS = 0 
BEGIN 
    SET @KILLPID = 'KILL ' + cast(@GetSPID as varchar(5)) 
    EXEC(@KILLPID) 
FETCH NEXT FROM @PID into @GetSPID 
END 

CLOSE @PID DEALLOCATE @PID

-- Start restoring 

RESTORE DATABASE @DataBaseName FROM DISK = @BackupFile
WITH MOVE @DBName
TO @dbPath  ,
MOVE @DBLog                                                                                                                   
TO @LogPath ,                                                                                                                                                                                          
REPLACE


Enjoy …

Comments

Leave a Reply

Your email address will not be published.