Smart Arab

This advanced SQL Query will help to restore any SQL 2005 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 @BackUpPath VARCHAR(255)
DECLARE @DataBaseName VARCHAR(128)
DECLARE @ApplicationName VARCHAR(128)

SET @BackUpPath = '%BackupPath%'
SET @DataBaseName = '%DBName%'

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

CREATE TABLE [#tempHearder]
    (
        LogicalName nvarchar (128) ,
        PhysicalName nvarchar(260),
        Type char(1),
        FileGroupName nvarchar(128),
        Size numeric(20,0),
        MaxSize numeric(20,0),
        FileID bigint,
        CreateLSN numeric(25,0),
        DropLSN numeric(25,0) NULL,
        UniqueID uniqueidentifier,
        ReadOnlyLSN numeric(25,0) NULL,
        ReadWriteLSN numeric(25,0) NULL,
        BackupSizeInBytes bigint,
        SourceBlockSize int,
        FileGroupID int,
        LogGroupGUID uniqueidentifier NULL,
        DifferentialBaseLSN numeric(25,0) NULL,
        DifferentialBaseGUID uniqueidentifier,
        IsReadOnly bit,
        IsPresent bit
    )

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

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

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) COLLATE ARABIC_CI_AI,
    )

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

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 = @BackUpPath
WITH MOVE @DBName
TO @dbPath  ,
MOVE @DBLog
TO @LogPath ,
REPLACE

By using this simple class you will be able to Export or Import DataTable to or from a Plain Text file or CSV , with delimited option .

using System;
using System.Data;
using System.IO;

class DataTable_ExportImport
{
    /// <summary>
    /// Export DataTable Columns , Rows to file
    /// </summary>
    /// <param name="datatable">The datatable to exported from.</param>
    /// <param name="delimited">string for delimited exported row items</param>
    /// <param name="exportcolumnsheader">Including columns header with exporting</param>
    /// <param name="file">The file path to export to.</param>
    public static void ExportDataTabletoFile(DataTable datatable, string delimited, bool exportcolumnsheader, string file)
    {
        StreamWriter strFile = new StreamWriter(file, false, System.Text.Encoding.Default);
        if (exportcolumnsheader)
        {
            string Columns = string.Empty;
            foreach (DataColumn column in datatable.Columns)
            {
                Columns += column.ColumnName + delimited;
            }
            strFile.WriteLine(Columns.Remove(Columns.Length - 1, 1));
        }

        foreach (DataRow datarow in datatable.Rows)
        {
            string row = string.Empty;
            foreach (object items in datarow.ItemArray)
            {
                row += items.ToString() + delimited;
            }
            strFile.WriteLine(row.Remove(row.Length - 1, 1));
        }
        strFile.Flush();
        strFile.Close();
    }

    /// <summary>
    /// Import file with delimited rows, columns to datatable
    /// </summary>
    /// <param name="file">The file path to imported from.</param>
    /// <param name="delimited">string for delimited imported row items</param>
    /// <param name="exportcolumnsheader">Including columns header with importing , (if true, the first row will be added as DataColumns) , (if false, DataColumns will be numbers)</param>
    /// <param name="datatable">The datatable to imported to.</param>
    public static void ImportDataTableFromFile(string file, string delimited, bool importcolumnsheader, DataTable datatable)
    {
        StreamReader strFile = new StreamReader(file, System.Text.Encoding.Default);

        if (importcolumnsheader)
        {
            string[] ColumnsArray = strFile.ReadLine().Split(delimited.ToCharArray());
            foreach (string strCol in ColumnsArray)
            {
                datatable.Columns.Add(strCol);
            }
        }
        else
        {
            int ColumnsCount = strFile.ReadLine().Split(delimited.ToCharArray()).Length;
            for (int iCol = 1; iCol <= ColumnsCount; iCol++)
            {
                datatable.Columns.Add(iCol.ToString());
            }

            strFile = new StreamReader(file, System.Text.Encoding.Default);
        }

        while (strFile.Peek() > 0)
        {
            datatable.Rows.Add(strFile.ReadLine().Split(delimited.ToCharArray()));
        }
        strFile.Close();
    }
}