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 …