Author Archives: Jugal Shah

Unknown's avatar

About Jugal Shah

Jugal Shah has 19 plus years of experience in leading and managing the data and analytics practices. He has done the significant work in databases, analytics and generative AI projects. You can check his profile on http://sqldbpool.com/certificationawards/ URL.

LiteSpeed – Restore Script

3 Replies

Script to check the backup file – data and log file information

exec master.dbo.xp_restore_filelistonly
@filename ='<BackupFilePath>\<BackupFileName.bak>'

Script to check the backup file header information

exec master.dbo.xp_restore_headeronly
@filename ='<BackupFilePath>\<BackupFileName.bak>'

Script to verify the backup file if the backup file is valid or not

EXEC master.dbo.xp_restore_verifyonly @filename ='<BackupFilePath>\<BackupFileName.bak>'

Script to restore database using Full backup with the default options

exec master.dbo.xp_restore_database
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.bak>'

Script to restore database using Full backup with file move option

exec master.dbo.xp_restore_database
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.bak>',
@with = 'move "logical filename" to "physical file location.mdf"',
@with = 'move "logical filename" to "physical file location.ldf"'

Script to restore database using Full backup with replace option

exec master.dbo.xp_restore_database
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.bak>',
@with = 'replace',
@with = 'move "logical filename" to "physical file location.mdf"',
@with = 'move "logical filename" to "physical file location.ldf"'

Script to restore Full backup with no recovery

exec master.dbo.xp_restore_database
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.bak>',
@with = 'replace',
@with = 'move "logical filename" to "physical file location.mdf"',
@with = 'move "logical filename" to "physical file location.ldf"',
@with='NORECOVERY'

Script to restore log backup with no recovery

EXEC master.dbo.xp_restore_log
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.trn>',
@with ='NORecovery'

Script to restore log backup with recovery

EXEC master.dbo.xp_restore_log
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.trn>',
@with ='Recovery'

Script to do point in time recovery

EXEC master.dbo.xp_restore_log
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.trn>',
@with ='Recovery',
@with = 'STOPBEFOREMARK = <LogMark>'

LiteSpeed – Backup Scripts

Leave a reply

full or complete database backup

execute master.dbo.xp_backup_database
  @database = '<database name>',
  @filename = '<path>\<backup file name>.bak',
  @init = 1,
  @compressionlevel = 4

differential backup

 
 execute master.dbo.xp_backup_database
  @database = '<database name>',
  @filename = '<path>\<backup file name>.bak',
  @init = 1,
  @compressionlevel = 4,
  @with =  differential

transaction log backup

 
 execute master.dbo.xp_backup_log
  @database = '<database name>',
  @filename = '<path>\<backup file name>.trn',
  @init = 1,
  @compressionlevel = 4

filegroup backup

   
 execute master.dbo.xp_backup_database 
  @database = '<database name>',
  @filename = '<path>\<backup file name>.bck',
  @init = 1,
  @compressionlevel = 4,
  @filegroup = 'filegroupname'

Table Hints – NoLock vs ReadPast

Leave a reply

When any data in a database is read or modified, the database engine uses special type of mechanism, called locks, to maintain integrity in the database. Locks will be used to make sure the transaction consistency.

NoLock Table Hint
– Will allow you to read the uncommited data
– Only used with SELECT statement
– Blocking will not occur
– Will reduce the concurrency and improve the performance at some extent
– Risk of doing Phantom reads

Let’s create table for the NOLOCK and READPAST hint demo

create table tranDemo
(
			id int identity(1,1),
			name varchar(10)
)

insert into tranDemo values ('Jugal')
insert into tranDemo values ('Nehal')

–Now let’s update the values by specifying the explicit transaction and don’t commit/rollback the transaction

begin transaction
   update tranDemo
   set name = 'DJ'
   where name = 'Jugal'

Now open new query window and execute the below query and you will notice query will not return data and will continue running as it is blocked

select * from trandemo

Now open new query window and execute the below query to check the blocking, you can see the blocking SPID in the result set

sp_who2 active

Now open new query window and execute the below query using NOLOCK hint and it will return data, yet transaction is not committed but it will return the updated value.

select * from trandemo(NOLOCK)

READPast Table Hint: Less commonly used table hint than NOLOCK. This hint specifies that the database engine not consider any locked rows or data pages when returning results.
– Will only read the commited rows which are not locked
– Blocking will not occur
– Only used with SELECT statement
– Will reduce the concurrency and improve the performance at some extent
– Result set returned by this hint is not perfect as it will not retun the locked rows or pages, so you can not make any decision based on data

Now open new query window and run the below query it will return only one record (“Nehal”) which is not locked or modified

select * from trandemo(ReadPast)

XP_LogEvent and RaiseError

2 Replies

xp_logevent
xp_logevent extended stored procedure is used to log the message into the SQL Server error log and event viewer application log.

Syntax
xp_logevent { error_number , ‘message’ } [ , ‘severity’ ]
Parameter Description
Error number Error number should be greater than 50000
Message Text you want to record in the error log and event viewer log
Severity Optional parameter (warning, informational or error) used to determine the type of error that will be logged in the NT application event log.

Than the question will arise in your mind what is the use of RAISERROR,  here is the difference between XP_LOGEVENT and RAISEERROR

XP_LOGEVENT RAISEERROR
Logs a user-defined message in the SQL Server log file and in the Windows Event Viewer. xp_logevent can be used to send an alert without sending a message to the client. Generates an error message. RAISERROR can either reference a user-defined message stored in the sys.messages view or build a message dynamically
Syntax
xp_logevent { error_number , ‘message’ } [ , ‘severity’ ]		 Syntax
RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,…n ] ] )
[ WITH option [ ,…n ] ]
Does not return the error to the client application Does return the error to the client application
Does not change the value of @@error Does change the value of @@error
Can only be used to log the messages in the SQL Server error log and event viewer log Can use for error handling and RaiseError with log can perform the similar functionality to XP_LOGEVENT
Permission
Requires membership in the db_owner fixed database role in the master database, or membership in the sysadmin fixed server role.		 Permission
Severity levels from 0 through 18 can be specified by any user. Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions

Example

EXEC master..xp_logevent 50003, ‘Stored Procedure executed successfully’
Exec xp_readerrorlog –check the error message in error log or event viewer log

How to find out the SQL Server installation date?

2 Replies

Problem
How to find out the SQL Server installation date?

Solution:
To get the exact SQL Server installation date we have to check for the object which is created at the time of installation. NT Authority\System login is getting created at the time of SQL Server installation. You can check the SQL Server installation date by querying the sys.syslogins or sys.server_principals view against the login NT Authority\System name.

NT Authority\System login which has unrestricted access to all local system resources and it is a member of the Windows Administrators group on the local computer with the sysadmin fixed SQL Server role.NT Authority\System login get created at the time of installation of SQL Server.

First we will check the sys.syslogins or sys.server_principals views
sys.syslogins
This SQL Server 2000 system table is included as a view for backward compatibility which shows all logins, its metadata and access.

sys.server_principals
Contains a row for every server-level principal

We can query one of the views to get the installation date. If your SQL Server is English Language compatible you can directly query by login name or for the other languages we will use the neutral language (hexadecimal code) which is same on every instance.

-- work with only English language installations
SELECT  createdate as 'SQL Server Installation Date'
FROM    sys.syslogins 
where   name = 'NT AUTHORITY\SYSTEM'

--neutral language 
SELECT  createdate as 'SQL Server Installation Date'
FROM    sys.syslogins 
where   sid = 0x010100000000000512000000

--Using sys.server_principals 
SELECT create_date as 'SQL Server Installation Date'
FROM sys.server_principals 
WHERE name='NT AUTHORITY\SYSTEM'

--Sample CMDB Query
SELECT SERVERPROPERTY('productversion') as ProductVersion
      ,SERVERPROPERTY ('productlevel') as ProductLevel
      ,SERVERPROPERTY ('edition') as Edition
      ,SERVERPROPERTY ('MachineName') as MachineName
      ,SERVERPROPERTY ('LicenseType') as LicenseType
      ,SERVERPROPERTY ('NumLicenses') as NumLicenses
      ,create_date as 'SQL Server Installation Date'
FROM sys.server_principals 
WHERE name='NT AUTHORITY\SYSTEM'

Query to check the SQL Evaluation Version Expire Date
You can check the SQL Server evaluation version expire date as well using below query and enter the product key to activate the SQL Server license.

-- Evaluation version expire date
SELECT create_date as 'SQL Server Installation Date',
DATEADD(dd,180,create_date) as 'Expiration Date'
FROM sys.server_principals WHERE name='NT AUTHORITY\SYSTEM'