MSSQL: Unterschied zwischen den Versionen

Aktuelle Version vom 6. November 2018, 08:30 Uhr

MSSQL

Backupscript (Procedure)

Backupscript sichert alle Datenbanken.

      ALTER PROCEDURE BackupDatabase
      AS
      DECLARE @name VARCHAR(256) -- database name  
      DECLARE @path VARCHAR(256) -- path for backup files  
      DECLARE @fileName VARCHAR(256) -- filename for backup  
      DECLARE @fileDate VARCHAR(20) -- used for file name
      DECLARE @recovery_model_desc VARCHAR(50) -- check recovery model
      
      -- specify database backup directory
      SET @path = '\\servername.domain\BackupMSSQL2\I0\' 
      
      -- specify filename format
      SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
      
      DECLARE db_cursor CURSOR FOR  
      SELECT name 
      FROM master.dbo.sysdatabases 
      WHERE name NOT IN ('tempdb')  -- exclude these databases
      
      OPEN db_cursor
      
      FETCH NEXT FROM db_cursor INTO @name   
      WHILE @@FETCH_STATUS = 0   
      BEGIN   
             SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
             BACKUP DATABASE @name TO DISK = @fileName
      
             SELECT @recovery_model_desc = recovery_model_desc FROM sys.databases WHERE name = @name
             IF @recovery_model_desc = 'FULL'
             BEGIN        
                    SET @fileName = @path + @name + '_' + @fileDate + '.LOG'  
                    BACKUP LOG @name TO DISK = @fileName 
             END
             FETCH NEXT FROM db_cursor INTO @name   
      END   
      
      CLOSE db_cursor   
      DEALLOCATE db_cursor

• Backup with Logfile.

Table for Logentry.

   GO
   CREATE TABLE dbo.BackupLog
      (
       ID bigint NOT NULL IDENTITY (1, 1),
       TYPE nchar(10) NOT NULL,
       LOGDATE datetime NOT NULL,
       DBNAME nchar(256) NOT NULL,
       FILENAME nchar(512) NOT NULL
      )  ON [PRIMARY]
   GO
   ALTER TABLE dbo.BackupLog ADD CONSTRAINT
       PK_BackupLog PRIMARY KEY CLUSTERED
       (
        ID
       ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Stored Procedures

   ALTER PROCEDURE [dbo].[BackupDatabase]
   AS
   DECLARE @subDirectory VARCHAR(2)-- Subdirectory for Backup !! must change!!
   DECLARE @dbName VARCHAR(256) -- database name
   DECLARE @countName INT  -- Count of database
   DECLARE @filePath VARCHAR(256) -- path for backup files  
   DECLARE @fileName VARCHAR(256) -- filename for backup  
   DECLARE @fileDate VARCHAR(20) -- used for file name
   DECLARE @recovery_model_desc VARCHAR(50) -- check recovery model
   -- specify database backup directory
   SET @subDirectory  = 'I0'  
   SET @filePath = '\\ssta-netap5.paricorp.net\BackupMSSQL2\' + @subDirectory + '\'  
   -- specify filename format
   SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
   SELECT @countName = Count(name) 
   FROM master.dbo.sysdatabases 
   WHERE name NOT IN ('tempdb')  -- exclude these databases
   --Logfile
   INSERT INTO BackupLog (TYPE, LOGDATE, DBNAME, FILENAME)
       VALUES ('START-SC', GETDATE(), 'Backup to ' + @subDirectory, 'Countdatabase = ' + Cast(@countName as VARCHAR(10)) )
   DECLARE db_cursor CURSOR FOR  
   SELECT name 
   FROM master.dbo.sysdatabases 
   WHERE name NOT IN ('tempdb')  -- exclude these databases
   OPEN db_cursor   
   FETCH NEXT FROM db_cursor INTO @dbName   
   WHILE @@FETCH_STATUS = 0   
   BEGIN
      SET @fileName = @filePath + @dbName + '_' + @fileDate + '.BAK'
      INSERT INTO BackupLog (TYPE, LOGDATE, DBNAME, FILENAME)
             VALUES ('START', GETDATE(), @dbName, @fileName)
      BACKUP DATABASE @dbName TO DISK = @fileName 
      INSERT INTO BackupLog (TYPE, LOGDATE, DBNAME, FILENAME)
             VALUES ('STOP', GETDATE(), @dbName, @fileName)
      SELECT @recovery_model_desc = recovery_model_desc FROM sys.databases WHERE name = @dbName
      IF @recovery_model_desc = 'FULL'
      BEGIN
         SET @fileName = @filePath + @dbName + '_' + @fileDate + '.LOG'
         INSERT INTO BackupLog (TYPE, LOGDATE, DBNAME, FILENAME)
             VALUES ('START', GETDATE(), @dbName, @fileName)
         BACKUP LOG @dbName TO DISK = @fileName 
         INSERT INTO BackupLog (TYPE, LOGDATE, DBNAME, FILENAME)
             VALUES ('STOP', GETDATE(), @dbName, @fileName)
      END
      FETCH NEXT FROM db_cursor INTO @dbName
   END
   CLOSE db_cursor
   DEALLOCATE db_cursor
   --Logfile
   INSERT INTO BackupLog (TYPE, LOGDATE, DBNAME, FILENAME)
        VALUES ('STOP-SC', GETDATE(), 'Backup to ' + @subDirectory, 'Countdatabase = ')
   --Cean Logfile
   delete BackupLog where Logdate < (select GETDATE() -30);

Truncate Logfile (Procedure)

      DECLARE c CURSOR FOR SELECT database_id, name, recovery_model_desc FROM sys.databases -- WHERE name='sharepoint_config'
      DECLARE @dbname VARCHAR(1024)
      DECLARE @rmod VARCHAR(1024)
      DECLARE @id INT
      DECLARE @lfile VARCHAR(1024) 
      OPEN c
      
      FETCH NEXT FROM c INTO @id, @dbname, @rmod WHILE @@FETCH_STATUS = 0
      BEGIN
             IF @rmod = 'FULL'
             BEGIN
                    SET @lfile = (SELECT name FROM sys.master_files WHERE database_id = @id AND type=1)
                    PRINT @lfile
                    EXEC('ALTER DATABASE [' + @dbname + '] SET RECOVERY SIMPLE')
                    EXEC('USE ['+@dbname+']; DBCC SHRINKFILE(['+@lfile+'], 1)')
                    EXEC('ALTER DATABASE [' + @dbname + '] SET RECOVERY FULL	')
             END ELSE
             IF @rmod = 'SIMPLE'
             BEGIN
                    SET @lfile = (SELECT name FROM sys.master_files WHERE database_id = @id AND type=1)
                    PRINT @lfile
                    EXEC('USE ['+@dbname+']; DBCC SHRINKFILE(['+@lfile+'], 1)')
             END
             FETCH NEXT FROM c INTO @id, @dbname,@rmod
      END
      
      CLOSE c
      DEALLOCATE c

Sonstig

   Select s.Name As DatabaseName, Coalesce(Convert(VarChar(12), Max(b.backup_finish_date),101),'-') AS LastBackupTime from sysdatabases s left outer join msdb.dbo.backupset b on b.database_name = s.name  Group by s.name

Suspect Database

• Anzeigen der Suspect Database

use master
go
select name, state_desc from sys.databases where state_desc='SUSPECT'
go

• In Datenbank in Emergency Modus setzen.

alter database "SuspectDatabase" set emergency

• Check Database

dbcc checkdb("SuspectDatabase")

• Datenbank in Single_User Modus setzen

alter database "SuspectDatabase" set single_user with rollback immediate

• Check Database

dbcc checkdb("SuspectDatabase", repair_allow_data_loss)

• Datenbank Zugriff für alle erteilen.

alter database "SuspectDatabase" ser multi_user

Transfer User

• Abfrage auf den Sourcesystem

use master
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
sys.server_principals p LEFT JOIN sys.syslogins l
     ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'

• Create User

use master
CREATE LOGIN [user] WITH PASSWORD = 'password', SID = user-id, 
DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Prozesse

• Anzahl der Session

SELECT 
   DB_NAME(dbid) as DBName, 
   COUNT(dbid) as NumberOfConnections,
   loginame as LoginName
FROM
   sys.sysprocesses
WHERE 
   dbid > 0
GROUP BY 
   dbid, loginame