Moving Sql data files to different location

It is always good to have database files to be placed on a non-system partition (system partition is where default program files and windows folder resides)

Last week, unfortunately my PC got corrupted, had to restore to 3 days old system image of windows, and I lost my sql data. Now for future, I have taken a preventive measure, and moved my sql database files to non-system drive.

Here are the steps,

Database name is testDB,

Note: make sure new location is not read only, has sufficient privileges to Administrator account, and the drive is uncompressed 

Step 1 : Create a new location 

New location is D:\MSSQL\DATA\

Step 2 : Run these commands in master database

ALTER DATABASE testDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE testDB SET OFFLINE;

ALTER DATABASE testDB MODIFY FILE

(

   Name = testDB,

   Filename = 'D:\MSSQL\DATA\testDB.MDF'

);

ALTER DATABASE my MODIFY FILE

(

   Name = testDB_log,

   Filename ='D:\MSSQL\DATA\testDB_log.LDF'

);

Step 3 : Move original sql data files from C:\program files\mssql\data to newly created location

Make sure the newly pasted files are not read-only, remove the read-only or compression settings.

Step 4: Move original sql data files from C:\program files\mssql\data to newly created location

Bring the database to online mode

ALTER DATABASE testDB SET ONLINE;

ALTER DATABASE testDB SET MULTI_USER;

Another way of pagination in sql server

Today, I wrote a paginating query using ranking function row_number(), with help of my team mate Neha. Initial idea was from her, and later I hijacked it to post it here. :). Poor Neha..

Ceiling function seems to be crux of it.  First 3 columns are data columns, rest are informational for paging data.

select

          *

from

(

          SELECT                                                                         

          ID                                                      --Data Columns

          ,GrandSlamLocation                          --Data Columns    

          ,GrandSlamLogin                               --Data Columns

          ,ROW_NUMBER() OVER(ORDER BY ID) [S.No]                                             

          , ceiling(ROW_NUMBER() OVER(ORDER BY ID)/ 10.00)  as Page

          ,count(*) over() as TotalRecords

          ,ceiling(count(*) over()/10.00) as TotalPages

          from

          (

                   --Base Query Start

                   select

                             ID,                                  --Data Columns

                             GrandSlamLocation,      --Data Columns    

                             GrandSlamLogin            --Data Columns

                   from  

                             Ticket

                   --Base Query End

          )A

           

)B where Page =  1

Tally table - equivalent of "select * from dual" in oracle

This sql query genrates unlimited sequential rows/virtual rows, using CTE

; WITH

Table1 AS (SELECT 1 N UNION ALL SELECT 1 N),    -- 2            ,              2 raise to power 1

Table2 AS (SELECT 1 N FROM Table1 x, Table1 y),            -- 8            ,   2 raise to power 3

Table3 AS (SELECT 1 N FROM Table2 x, Table2 y),            -- 128         ,   2 raise to power 7

Table4 AS (SELECT 1 N FROM Table3 x, Table3 y),            -- 32768        , 2 raise to power 15

Table5 AS (SELECT 1 N FROM Table4 x, Table4 y),           -- 2147483648 , 2 raise to power 31

tally as

(

                   SELECT

                             ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N                      

                   FROM

                             Table1 , Table2 , Table3, Table4 --, Table5

                             -- you can delete or add tables here to increase decrease the rows, or

)

select * from tally where n < 5000

-- you can control number of rows here also

Truncate your database log file

CREATE PROC [dbo].[PROC_TruncateLog]

AS BEGIN

      DECLARE @DBName sysname,

                  @logName sysname

      --

      SET @DBName = DB_NAME()

      --

      BACKUP LOG @DBName WITH NO_LOG

      --

      DECLARE logCursor CURSOR FOR

      SELECT name

      FROM sysfiles

      WHERE groupid = 0

      --

      OPEN logCursor

      FETCH NEXT FROM logCursor

      INTO @logName

      WHILE @@FETCH_STATUS = 0 BEGIN

            SELECT @logName = RTRIM(@logName)

            DBCC SHRINKFILE (@logName)

            FETCH NEXT FROM logCursor

            INTO @logName

      END

      CLOSE logCursor

      DEALLOCATE logCursor

END

Code snippet for querying excel files as DataTable

public static DataSet ImportExcelXLS(string FileName, bool hasHeaders)
    {
        string HDR = hasHeaders ? "Yes" : "No";

        string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                            FileName +
                                    ";Extended Properties='Excel 12.0;HDR=" +
                         HDR + ";IMEX=1'";


        DataSet output = new DataSet();

        using (OleDbConnection conn = new OleDbConnection(strConn))
        {
            conn.Open();

            DataTable schemaTable = conn.GetOleDbSchemaTable(
              OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

            foreach (DataRow schemaRow in schemaTable.Rows)
            {
                string sheet = Convert.ToString(schemaRow["TABLE_NAME"]);

                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
                cmd.CommandType = CommandType.Text;

                DataTable outputTable = new DataTable(sheet);
                outputTable.TableName = sheet;
                output.Tables.Add(outputTable);
                new OleDbDataAdapter(cmd).Fill(outputTable);
            }
        }
        return output;
    }