Archive for the ‘SQL’ Category

SQL Server – Restore database backup script

Summary:

T-SQL Script, using variables and dynamic SQL.

It actually prints SQL code for you to review before execution.

Proceed with caution.

Here it is:

DECLARE @DATABASE_NAME VARCHAR(3000)
SET @DATABASE_NAME='MyDB'

DECLARE @BACKUP_FILE VARCHAR(3000)
SET @BACKUP_FILE='E:\my_db_backup.bak'

DECLARE @RESTORE_PATH VARCHAR(3000)
SET @RESTORE_PATH='E:\SQLDATA\'

DECLARE @DATA_FILE VARCHAR(3000)
SET @DATA_FILE=@RESTORE_PATH + @DATABASE_NAME + '.mdf'

DECLARE @LOG_FILE VARCHAR(3000)
SET @LOG_FILE=@RESTORE_PATH + @DATABASE_NAME + '_log.ldf'

--PRINT @DATABASE_NAME
--PRINT @BACKUP_FILE
--PRINT @RESTORE_PATH
--PRINT @DATA_FILE
--PRINT @LOG_FILE

-- Retrieve the Logical file name of the database from backup
-- gives you 'db_data_file_logical_name' and 'db_log_file_logical_name' to be used on the restore script
RESTORE FILELISTONLY
FROM DISK = @BACKUP_FILE
DECLARE @SQLString nvarchar(3000)

----Make Database to single user Mode
SET @SQLString =
'ALTER DATABASE [' + @DATABASE_NAME + '] ' +
'SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
--EXECUTE( @SQLString )
PRINT @SQLString

----Restore Database
SET @SQLString =
'RESTORE DATABASE [' + @DATABASE_NAME + '] FROM DISK = ''' + @BACKUP_FILE + '''' +
' WITH MOVE ''db_data_file_logical_name'' TO ''' + @DATA_FILE + ''', ' +
' MOVE ''db_log_file_logical_name'' TO ''' + @LOG_FILE + ''', ' +
' REPLACE'
--EXECUTE( @SQLString )
PRINT @SQLString

/*If there is no error in statement before, database will be in multiuser mode.
If error occurs, please execute following command, it will convert database in multi user.*/

SET @SQLString =
'ALTER DATABASE [' + @DATABASE_NAME + '] ' +
'SET MULTI_USER'
--EXECUTE( @SQLString )
PRINT @SQLString

GO

References:

SQL SERVER – Restore Database Backup using SQL Script (T-SQL)
EASY ONE: ALTER DATABASE with Variable
SQL SERVER – FIX : Error 3154: The backup set holds a backup of a database other than the existing database
SQL SERVER – FIX : ERROR : Msg 3159, Level 16, State 1, Line 1 – Msg 3013, Level 16, State 1, Line 1

Parsing HTML to SQL using SQLDOM

Parsing HTML to SQL using SQLDOM

SQL query build calendar

CREATE      procedure [dbo].[CalMonthByYearMonth] (@YEAR INT, @MONTH INT) AS
DECLARE @INPUTDATE DATETIME
DECLARE @DATE DATETIME
DECLARE @LASTDATE DATETIME
DECLARE @MONTHDAYCOUNT INT
DECLARE @COUNT INT
DECLARE @DAY VARCHAR(10)
DECLARE @STARTWEEK INT
DECLARE @CURWEEK INT
DECLARE @STARTMONTH INT
SET @INPUTDATE='01/01/' + CAST(@YEAR AS CHAR(4))
PRINT @INPUTDATE
SET @STARTMONTH=@MONTH
SET @INPUTDATE=DATEADD(MM,@MONTH - 1,@INPUTDATE)

SET @COUNT=1
SET @DATE = DATEADD(d, -(DATEPART(dd, @INPUTDATE) - 1), @INPUTDATE)
SET @LASTDATE=DATEADD(DD,-1,DATEADD(MM,1,@DATE))
SET @MONTHDAYCOUNT=datediff(d, @date, dateadd(m, 1, @date))
SET @STARTWEEK=DATEPART(WEEK,@INPUTDATE)
DECLARE @CURRWEEK INT
DECLARE @CUR INT
CREATE TABLE #TEMP(
WEEK VARCHAR(10),
SUNDAY VARCHAR(10),
MONDAY VARCHAR(10),
TUESDAY VARCHAR(10),
WEDNESDAY VARCHAR(10),
THURSDAY VARCHAR(10),
FRIDAY VARCHAR(10),
SATURDAY VARCHAR(10),
YEARWEEK VARCHAR(10))
DECLARE @wkcount int
DECLARE @weeksinmonth int
DECLARE @EXEC NVARCHAR(2000)
SET @WKCOUNT=1
SET @weeksinmonth=datediff(week, @date, @lastdate) + 1
WHILE @wkcount<= @weeksinmonth
begin
INSERT INTO #TEMP VALUES(@wkcount,'SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', @STARTWEEK + @WKCOUNT - 1)
SET @WKCOUNT=@WKCOUNT + 1
end
WHILE @COUNT<=@MONTHDAYCOUNT
BEGIN
SET @DAY=DATENAME(WEEKDAY,@DATE)
IF @STARTWEEK=DATENAME(WEEK,@DATE)
SET @CURRWEEK=1
ELSE
BEGIN
SET @CUR=DATENAME(WEEK,@DATE)
SET @CURRWEEK=(@CUR-@STARTWEEK)+1
END

SET @EXEC='UPDATE #TEMP SET ' + @DAY + ' =' + CAST(@COUNT AS CHAR(2)) + ' WHERE WEEK=' + CAST(@CURRWEEK AS CHAR(2))+ 'AND WEEK IS NOT NULL'
EXEC SP_EXECUTESQL @EXEC
SET @DATE=DATEADD(DD,1,@DATE)
SET @COUNT=@COUNT + 1
END
UPDATE #TEMP SET SUNDAY=' ' WHERE SUNDAY='SUNDAY'
UPDATE #TEMP SET MONDAY=' ' WHERE MONDAY='MONDAY'
UPDATE #TEMP SET TUESDAY=' ' WHERE TUESDAY='TUESDAY'
UPDATE #TEMP SET WEDNESDAY=' ' WHERE WEDNESDAY='WEDNESDAY'
UPDATE #TEMP SET THURSDAY=' ' WHERE THURSDAY='THURSDAY'
UPDATE #TEMP SET FRIDAY=' ' WHERE FRIDAY='FRIDAY'
UPDATE #TEMP SET SATURDAY=' ' WHERE SATURDAY='SATURDAY'
SELECT Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday --, YEARWEEK
--SELECT Monday, Tuesday, Wednesday, Thursday, Friday, YEARWEEK
FROM #TEMP order by week
DROP TABLE #TEMP

 

References:

 

What is a good (cheap/free) calendar/schedule control for asp.net?

Creating a Calendar in a single SQL statement

T-SQL: Generate Sequences in a Loop Query

T-SQL Query with calendar table

How to create a Calender table for 100 years in Sql

SQL Server memory usage

I read this excellent post about SQL server memory usage: Determine SQL Server memory use by database and object

Just wanted to write it down here so I don’t forget later.

This query shows buffer pool memory usage per database.

-- Note: querying sys.dm_os_buffer_descriptors
-- requires the VIEW_SERVER_STATE permission.

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Total Pages';

;WITH src AS
(
SELECT
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
--WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[db_name] = CASE [database_id] WHEN 32767
THEN 'Resource DB'
ELSE DB_NAME([database_id]) END,
db_buffer_pages,
db_buffer_MB = db_buffer_pages / 128,
db_buffer_percent = CONVERT(DECIMAL(6,3),
db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;

And this query shows buffer pool memory usage per obejcts for a specific database.

USE BOVEDA;
GO

;WITH src AS
(
   SELECT
       [Object] = o.name,
       [Type] = o.type_desc,
       [Index] = COALESCE(i.name, ''),
       [Index_Type] = i.type_desc,
       p.[object_id],
       p.index_id,
       au.allocation_unit_id
   FROM
       sys.partitions AS p
   INNER JOIN
       sys.allocation_units AS au
       ON p.hobt_id = au.container_id
   INNER JOIN
       sys.objects AS o
       ON p.[object_id] = o.[object_id]
   INNER JOIN
       sys.indexes AS i
       ON o.[object_id] = i.[object_id]
       AND p.index_id = i.index_id
   WHERE
       au.[type] IN (1,2,3)
       AND o.is_ms_shipped = 0
)
SELECT
   src.[Object],
   src.[Type],
   src.[Index],
   src.Index_Type,
   buffer_pages = COUNT_BIG(b.page_id),
   buffer_mb = COUNT_BIG(b.page_id) / 128
FROM
   src
INNER JOIN
   sys.dm_os_buffer_descriptors AS b
   ON src.allocation_unit_id = b.allocation_unit_id
WHERE
   b.database_id = DB_ID()
GROUP BY
   src.[Object],
   src.[Type],
   src.[Index],
   src.Index_Type
ORDER BY
   buffer_pages DESC;

Other references:

How to Identify Microsoft SQL Server Memory Bottlenecks

SSRS: Format number with 2 decimals points

=Format(Sum(Fields!TOTALHABERES.Value, "Detalle"),"#,##0.00")

How to do number formatting to 1 decimal point in SSRS?
SSRS Expressions Tips and Tricks
Formatting Numbers and Dates

Recover SQL Server sa password

Response on StackOverflow.com

You may have forgotten “sa” password, you never got it, the provider didn’t give it to you, whatever.

But, You have Administrator privileges on Windows.

The steps are the following:

  1. Go to SQL Server Configuration Manager.
  2. Select SQL Server and STOP the service.
  3. Right click on it, go to Properties.
  4. On “Startup parameters”, add the following at the beginning “-m;”
  5. Start SQL Server service.
  6. Now, you’re on Single User Mode.
  7. Open a Command window (on Win7 make sure to Run as Administrator)
  8. Type “SQLCMD” and hi ENTER.
  9. Now you’re logged on SQL Server in Single User Mode, without any password.
  10. Type: “CREATE LOGIN mynewuser WITH PASSWORD = ‘mynewpassword′;”
  11. Type: “GO;”
  12. Type: “sp_addsrvrolemember ‘mynewuser’, ‘sysadmin’;”
  13. Type: “GO;”
  14. Stop SQL Server service.
  15. Right click it, go to Properties.
  16. On “Startup parameters”, remove the following from the beginning “-m;”
  17. Start SQL Server service.
  18. Open SQL Server Management Studio, login as “mynewuser” user.
  19. Go to Security, Logins, and change “sa” user password.

References:

How to recover SA password on Microsoft SQL Server 2008 R2
Disaster Recovery: How to regain your lost sysadmin access
Starting SQL Server in Single-User Mode
Tips & Tricks: YOU HAVE LOST ACCESS TO SQL SERVER. NOW WHAT?
sqlcmd Utility

Fixed row headers on SSRS 2008 R2

As you would have noticed, this doesn’t work out of the box in SSRS 2008 R2.

This is a little tricky.

So, the needed steps are the following:

  1. Select your Tablix.
  2. On your Tablix Properties General tab, uncheck “Keep header visible while scrolling” (This was supposed to work out of the box)
  3. On the panel below, you will see Row and Columns Groups.
  4. On the upper right corner, click and Select “Advanced Mode”.
  5. Now, on Row Groups, click on the first item “(Static”).
  6. On its Properties, set “Keep with group”, “Fixed data”  and “Repeat on new page” to TRUE.

Done!

Further references:

Freezing column headers while scrolling in SSRS

Keep Column Headers Visible while scrolling in SSRS 2008

Keep Column Headers Visible while scrolling in SSRS 2008

Fixed Row and Column Headers in Matrix in SSRS 2008 R2

Controlling Row and Column Headings (Report Builder 3.0 and SSRS)

Fixed row headers on SSRS 2008 R2

Repeating Tablix Headers In SSRS 2008