ict.ken.be

To enable remote connection on SQL Server 2008 Express, see the step below:

• Start SQL Server Browser service if it’s not started yet. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer.
• Enable TCP/IP protocol for SQL Server 2008 Express to accept remote connection.
• Change Server Authentication to SQL Server and Windows Authentication. By default, SQL Server 2008 Express allows only Windows Authentication mode so you can connect to the SQL Server with current user log-on credential. If you want to specify user for connect to the SQL Server, you have to change Server Authentication to SQL Server and Windows Authentication.

Use the following steps to configure the Windows Firewall:

• Open the Windows Firewall dialog box and click the Exceptions tab.
• Click Add Program and Browse to find sqlbrowser.exe and click OK.
• Click Add Program and Browse to find sqlservr.exe. Click OK.
• Click Add Port and enter "SQL Service" for Name, 1433 for Port number, and select the TCP radio button.
• Click OK on the Windows Firewall dialog.

--table "xyz" cannot be reorganized because page level locking is disabled.

--ALTER INDEX IDX_NAME ON TABLE_NAME SET (ALLOW_PAGE_LOCKS = ON)

select A.Name as InName,ob.Name as DBName from sys.indexes A
left outer join sys.objects ob on ob.object_id=A.Object_id
where allow_page_locks=0 and ob.type='U'

/*
SET NOCOUNT ON
DECLARE @DBName nvarchar(50), @INName nvarchar(50)
DECLARE @ODBName nvarchar(50), @OINName nvarchar(50)
Declare @execstr nvarchar(200)
--PRINT '-------- Vendor Products Report --------'
DECLARE Index_cursor CURSOR FOR
Select A.Name as InName,ob.Name as DBName from sys.indexes A
left outer join sys.objects ob on ob.object_id=A.Object_id
where allow_page_locks=0 and ob.type='U'
-- Select only allow_page_locks 0 and User Tables
OPEN Index_cursor
FETCH NEXT FROM Index_cursor
INTO @INName, @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @DBName +' ' + @INName
--PRINT @INName
SET @ODBName = ltrim(rtrim(@DBName))
SET @OINName = ltrim(rtrim(@INName))
SELECT @execstr = 'ALTER INDEX '+@OINName+ ' ON '+
@ODBName+' SET (ALLOW_PAGE_LOCKS = ON)';
EXEC (@execstr);
FETCH NEXT FROM Index_cursor
INTO @INName, @DBName
END
CLOSE Index_cursor
DEALLOCATE Index_cursor
*/

--create in master
CREATE PROCEDURE [dbo].[backup_db]
@db            varchar(50),
@backup_path   varchar(254) = 'C:\Databases.Backups\'
AS
DECLARE @backup_device nvarchar(1024);
DECLARE @backup_name nvarchar(127);
DECLARE @device_type nvarchar(50);
SET @backup_device = @backup_path + @db + '_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '.bak';
SET @backup_name = @db +'-Full Database Backup';
SET @device_type = 'disk';
 
EXEC master.dbo.sp_addumpdevice
@devtype = @device_type,
@logicalname = @db,
@physicalname = @backup_device;
 
BACKUP DATABASE @db TO  @db WITH NOFORMAT, INIT,
NAME = @backup_name , SKIP, NOREWIND, NOUNLOAD,  STATS = 10;
 
EXEC sp_dropdevice @db;
GO

--create in master
CREATE PROCEDURE [dbo].[backup_db_all]
AS
	exec backup_db 'Ken_Databasename1'
	exec backup_db 'Ken_Databasename1'
GO
 
--exec backup_db_all

Call stored procedure from batch file:

• sqlcmd -E -S .\SQLEXPRESS -d master -Q "backup_db 'databaseName','C:\TEMP\'"
• sqlcmd -E -S .\SQLEXPRESS -d master -Q "backup_db_all"

1. Stop the sql server within services
2. Delete the physical files (.mdf , .ldf)
3. Start the sql server
4. Right click the offline database and click delete

USE MASTER 

Declare @dbid int,
        @spid int,
        @str nvarchar(128)
select @dbid = dbid from master..sysdatabases
  where name = 'DB_MESSAGENT'
declare spidcurs cursor for
   select spid from master..sysprocesses where dbid = @dbid
open spidcurs
fetch next from spidcurs into @spid
While @@fetch_status = 0
  Begin
    Select @str = 'Kill '+ convert(nvarchar(30),@spid)
    --exec(@str)
    select @str
    fetch next from spidcurs into @spid
End
Deallocate spidcurs