How to extract Litespeed file and convert it into native backup file

Please find the extractor.exe file on the system and then go to the path using CMD, once you have reached to the location, use the below command.


Extractor.exe -F Sourcepath\filename.BAK -E Destinationpath\filename_native.bak

If you don't have extractor.exe then copy the same from any server to the destination server and then try to extract it.

How to restore database using Litespeed backup file and TSQL command

Below is TSQL script to restore database in MSSQL using Litespeed backup file.

exec master.dbo.xp_restore_database @database = N'databasename' ,

@filename = N'Full path of the backup file',
@filenumber = 1,
@with = N'STATS = 10',
@with = N'MOVE N''LogicalFileNameOf MDF file'' TO N''Destinationpath\Logicalfilename.mdf''',
@with = N'MOVE N''LogicalFileNameOf LDF file'' TO N''Destinationpath\Logicalfilename.ldf''',
@affinity = 0,
@logging = 0
GO

Example :-


exec master.dbo.xp_restore_database @database = N'Test' ,
@filename = N'Q:\Backup\Test_20130308.bak',
@filenumber = 1,
@with = N'STATS = 10',
@with = N'MOVE N''Test'' TO N''M:\data\Test.mdf''',
@with = N'MOVE N''Test_log'' TO N''H:\logs\Test_log.ldf''',
@affinity = 0,
@logging = 0
GO

Script to shrink all DB's with in SQL server 2005.

SET NOCOUNT ON

DECLARE @DBName varchar(100)
DECLARE @FileName varchar(100)
DECLARE @TempTxt varchar(1000)
DECLARE @Cnt smallint

SET @TempTxt = 'USE Master;' + CHAR(13) +
'EXEC xp_fixeddrives;'

DECLARE cmdtxt_cursor CURSOR FOR SELECT  RTRIM(master..Sysdatabases.Name) as [DBName],
         RTRIM(master..Sysaltfiles.Name) as [FileName]
FROM  master..Sysdatabases
Inner join master..Sysaltfiles on master..Sysaltfiles.DBID = master..Sysdatabases.DBID
WHERE   master..Sysdatabases.DBID not in (1,2,3,4) and Patindex('%log%', master..Sysaltfiles.Name) > 0
and ((master..Sysaltfiles.Size)*8/1024) > 0 and Patindex ('% %', master..Sysdatabases.Name) = 0
ORDER BY Left(master..Sysaltfiles.FileName, 1)

SET @TempTxt = 'USE Master;' + CHAR(13) +
'EXEC xp_fixeddrives;'
--PRINT @TempTxt
EXEC (@TempTxt)

OPEN cmdtxt_cursor
FETCH NEXT FROM cmdtxt_cursor INTO @DBName, @FileName
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT 'Processing : ' + @DBName
If Exists(Select master..Sysdatabases.[Name] From master..Sysdatabases
           Where master..Sysdatabases.[Name] = @DBName)
BEGIN
SET @TempTxt = 'USE [' + @DBName + '];' + CHAR(13) +
'BACKUP LOG [' + @DBName + '] WITH TRUNCATE_ONLY ;' + CHAR(13) +
'DBCC SHRINKFILE (''' + @FileName + ''', 1);'
--PRINT @TempTxt
EXEC (@TempTxt)
FETCH NEXT FROM cmdtxt_cursor INTO @DBName, @FileName
END
END

CLOSE cmdtxt_cursor
DEALLOCATE cmdtxt_cursor
SET @TempTxt = 'USE Master;' + CHAR(13) +
'EXEC xp_fixeddrives;'
--PRINT @TempTxt
EXEC (@TempTxt)
GO

Error Code 1326 - Not able to execute xp_cmdshell

Issue :-  A domain SQL service account password has been changes and after that the user is complaining that he is not able to access xp_cmdshell.

Error 1326 was seen.

Resolution :- We checked the SQL server properties and found that the same account was being used as a proxy account in SQL server. Updated the new password of the service account at proxy account and then it was able to access the xp_cmdshell.

Script to grant database level role to a login on all user database

Below script will grant db_owner role to a newly created login on all user databases. We can use the same script to grant any database level role to the login on all user database.

Script 1 :- For Windows Login

create login loginname from windows

DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')
BEGIN USE ? EXEC(''create user username for login loginname'') END '

EXEC sp_MSforeachdb @command

DECLARE @command1 varchar(1000)

Select @command1 = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')
BEGIN USE ? EXEC sp_addrolemember ''db_owner'', ''username'' END'

EXEC sp_MSforeachdb @command1


For SQL Login

create login loginname with password = ' Enter the password for the login'

DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')
BEGIN USE ? EXEC(''create user username for login loginname'') END '

EXEC sp_MSforeachdb @command

DECLARE @command1 varchar(1000)

Select @command1 = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')
BEGIN USE ? EXEC sp_addrolemember ''db_owner'', ''username'' END'

EXEC sp_MSforeachdb @command1