sql server STRING_SPLIT

--select * from STRING_SPLIT('\dir1\d2\test.txt','\')


 
DECLARE @Val as nvarchar(4000)
DECLARE curInserted CURSOR LOCAL FOR  (select * from STRING_SPLIT('\dir1\d2\test.txt','\'))
OPEN curInserted
FETCH NEXT FROM curInserted INTO @Val
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT @Val
  FETCH NEXT FROM curInserted INTO @Val
END
CLOSE curInserted

Sql Server Datetime Offset SWITCHOFFSET AT TIME ZONE

--AT TIME ZONE: time and offset are calculated for given timezone
--SWITCHOFFSET: only offset is changed, time remains the same


--create example table _dto for datetimeoffset demo
CREATE TABLE [dbo].[_dto](
[id] [int] IDENTITY(1,1) NOT NULL,
[dtoUtc] [datetimeoffset](7) NULL, --utc time
[dtoLocal] [datetimeoffset](7) NULL --local time
) ON [PRIMARY]
GO

--insert winter and sommertime demo test record
INSERT [dbo].[_dto] ( [dtoUtc]) VALUES (CAST(N'2018-01-23T10:00:00.0000000+00:00' AS DateTimeOffset)) -- winter time
GO
INSERT [dbo].[_dto] ([dtoUtc]) VALUES (CAST(N'2017-07-23T10:00:00.0000000+00:00' AS DateTimeOffset)) -- summer time
GO

--get string values for available sql server time zones:
select * from sys.time_zone_info;

--show result of SWITCHOFFSET and AT TIME ZONE
select dtoUtc ,  --original Utc Time (offset 00)
   SWITCHOFFSET(dtoUtc,1) as switch, -- time remains the same, only offset changes
   dtoUtc AT TIME ZONE 'Central Europe Standard Time' as AtTimeZone -- time and offset are transfered to destination time zone, winter and summer time are calculated correct
from _dto order by dto

--update _dto set dtoLocal= dtoUtc AT TIME ZONE 'Central Europe Standard Time' -- calculating local time from utc and store it
select * from  _dto

--drop table _dto; --clean up

SQL SERVER RECOVERY PENDING

cause / Ursache: sql server can't access mdf or ldf file - permissions changed ? enough free space ? / Sql server kann nicht auf das mdf oder ldf file Zugreifen - haben sich die BErechtigungen geändert oder ist zuwenig Speicherplatz frei ?

after file Access is ok / nachdem der Zugriff auf die Files wieder möglich ist:

ALTER DATABASE mydb SET ONLINE;
DBCC CHECKDB('mydb')

or for all pending databases:

select 'DBCC CHECKDB(' + name + ')',* from sys.databases where state=3 -- name like 'mydb%'
select 'ALTER DATABASE ' + name + ' SET ONLINE',* from sys.databases where state=3

change sql server hostname

select @@servername -- view servername

EXEC sp_DROPSERVER 'oldservername'

EXEC sp_ADDSERVER 'newservername', 'local'

restart server

get Backup sets of a Backup file including more than one backup

declare @backupFile varchar(max) = 'C:\SqlBackup\test.BAK';
-- THIS IS SPECIFIC TO SQL SERVER 2012 and 2014
--
declare @headers table
(
    BackupName varchar(256),
    BackupDescription varchar(256),
    BackupType varchar(256),       
    ExpirationDate varchar(256),
    Compressed varchar(256),
    Position varchar(256),
    DeviceType varchar(256),       
    UserName varchar(256),
    ServerName varchar(256),
    DatabaseName varchar(256),
    DatabaseVersion varchar(256),       
    DatabaseCreationDate varchar(256),
    BackupSize varchar(256),
    FirstLSN varchar(256),
    LastLSN varchar(256),       
    CheckpointLSN varchar(256),
    DatabaseBackupLSN varchar(256),
    BackupStartDate varchar(256),
    BackupFinishDate varchar(256),       
    SortOrder varchar(256),
    CodePage varchar(256),
    UnicodeLocaleId varchar(256),
    UnicodeComparisonStyle varchar(256),       
    CompatibilityLevel varchar(256),
    SoftwareVendorId varchar(256),
    SoftwareVersionMajor varchar(256),       
    SoftwareVersionMinor varchar(256),
    SoftwareVersionBuild varchar(256),
    MachineName varchar(256),
    Flags varchar(256),       
    BindingID varchar(256),
    RecoveryForkID varchar(256),
    Collation varchar(256),
    FamilyGUID varchar(256),       
    HasBulkLoggedData varchar(256),
    IsSnapshot varchar(256),
    IsReadOnly varchar(256),
    IsSingleUser varchar(256),       
    HasBackupChecksums varchar(256),
    IsDamaged varchar(256),
    BeginsLogChain varchar(256),
    HasIncompleteMetaData varchar(256),       
    IsForceOffline varchar(256),
    IsCopyOnly varchar(256),
    FirstRecoveryForkID varchar(256),
    ForkPointLSN varchar(256),       
    RecoveryModel varchar(256),
    DifferentialBaseLSN varchar(256),
    DifferentialBaseGUID varchar(256),       
    BackupTypeDescription varchar(256),
    BackupSetGUID varchar(256),
    CompressedBackupSize varchar(256),       
    Containment varchar(256),
    --
    -- This field added to retain order by
    --
    Seq int NOT NULL identity(1,1)
);
insert into @headers exec('restore headeronly from disk = '''+ @backupFile +'''');
select * from @headers

SSRS Ampeln (KPI) in VS2008 BI

Manchmal "leuchten" alle Ampeln (trotz Gegenteiliger Bedingungen) eines SSRS Reports im Preview des VS2008 Business Intelligence Studios, dann hilft es das Visual Studio zu schießen und neu zu öffnen.

sql server: restore many transactionlogs

example of restoring sql server transaction logs to a full backup: beispiel einiger Befehlszielen um transaktionslogs wiederherzustellen:
RESTORE LOG [CrmBak] FROM DISK = N'E:\SqlServerBackup\CRM\CRM_backup_2014_01_01_230431_6398282.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
RESTORE LOG [CrmBak] FROM DISK = N'E:\SqlServerBackup\CRM\CRM_backup_2014_01_02_230438_1759387.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
RESTORE LOG [CrmBak] FROM DISK = N'E:\SqlServerBackup\CRM\CRM_backup_2014_01_03_230429_5963993.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
to generate the restore command lines I used commandline: dir >dir.txt then with notepad++ I removed the date /time columns by pressing alt+marking with mouse, then used edit/Column editor to insert restore.... before the filename and ' WITH FILE=1 ... after the filename
um die Restore Befehle zu erzeugen, verwendetete ich den DOS BEfehel DIR: dir> dir.txt dann mit notepadd++ mit gedrückter alt taste mit der maus die unerwünschten spalten markeiren und löschen, dann mit edit / Column editor fügte ich vor dem dateinamen restore ... und nach dem DAteinamen 'WITH FILE=1 ... hinzu

wenn ein transaktionslog fehlt oder das zugrundeliegende full backup nicht in den Zeitraum des transaktionslogs fällt erhält man: Meldung 4305, Ebene 16, Status 1, Zeile 1 Das Protokoll in diesem Sicherungssatz beginnt bei LSN 29111000000034100001. Diese liegt für eine Anwendung auf die Datenbank nicht weit genug zurück. Eine frühere Protokollsicherung, die LSN 29015000000200600001 einschließt, kann wiederhergestellt werden. Meldung 3013, Ebene 16, Status 1, Zeile 1 RESTORE LOG wird fehlerbedingt beendet.

Sql Server TSQL Script: Backup ALL Databases (foult tolreant / Fehlertolerant)

--TODO: Enter Profilename and Email ADress at last statement DECLARE @name VARCHAR(50) -- 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 @FEHLER VARCHAR(MAX) DECLARE @MSG VARCHAR(MAX) SET @path = 'F:\Backup\' SET @Msg='' SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR FOR SELECT name FROM MASTER.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '_' + @fileDate + '.BAK' BEGIN TRY print 'BACKUP DATABASE '+@name+' TO DISK = '+@fileName; BACKUP DATABASE @name TO DISK = @fileName END TRY BEGIN CATCH set @Fehler= 'Error: BACKUP DATABASE '+@name+' TO DISK = '+@fileName print @Fehler set @MSG=@MSG+@Fehler END CATCH FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor --SEND Mail - use SSMS Management DatabaseMAil right click Send Testmail to get Profilename and test mail (and set up if needed EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ProfileName', @recipients = 'x@y.z', @body = 'Backup Bericht SQLServer (wenn leer keine Fehler)', @subject = @MSG ;

create sql server login when not exists

if (SELECT SUSER_ID('test')) is null begin create login test with password='pwd' end