/****** 2017-12-17 Version 11 ******/ /****** This script will create a backup database and copy old data to it ******/ /****** WARNING: Use caution as the script can delete data that you want to keep ******/ /****** If unsure create a separate backup of the database first ******/ /****** EXISTINGDATABASE is an existing database ******/ /****** BACKUPDATABASE will be created and data will be copied from EXISTINGDATABASE ******/ /****** Search and replace BACKUPDATABASE with the name of new backup database ******/ /****** Search and replace EXISTINGDATABASE with the name of the existing database ******/ /****** Search and replace YYYY-MM-DD with the date where data older will be ******/ /****** backed up to BACKUPDATABASE and deleted from EXISTINGDATABASE ******/ /****** Example of date is 2013-01-01 and ******/ /****** data from the day before will be backed up ******/ /****** If the BACKUPDATABASE is already existing the script will not run ******/ IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'EXISTINGDATABASE') BEGIN print 'The database EXISTINGDATABASE is not existing. The script will not run.' RETURN END GO IF EXISTS (SELECT * FROM sys.databases WHERE name = 'BACKUPDATABASE') BEGIN print 'The database BACKUPDATABASE is already existing. The script will not run.' RETURN END GO CREATE DATABASE [BACKUPDATABASE] COLLATE SQL_Latin1_General_CP1_CI_AS; GO USE [BACKUPDATABASE] GO SET ANSI_PADDING ON GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[UserDetailedLogTemp]( [ServerName] [int] NULL, [UserName] [int] NULL, [UserHost] [int] NULL, [AppName] [int] NULL, [DateField] [datetime] NULL, [LingerValue] [int] NULL, [Licenses] [int] NULL, [ID] [int] NULL, [ProjectID] [int] NULL ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [groupindex] ON [dbo].[UserDetailedLogTemp] ( [DateField] ASC ) INCLUDE ( [ServerName], [UserName], [UserHost], [AppName], [LingerValue], [Licenses], [ProjectID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [IDIndex] ON [dbo].[UserDetailedLogTemp] ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO SET ANSI_PADDING ON GO CREATE NONCLUSTERED INDEX [IX_UserDetailedLogTemp] ON [dbo].[UserDetailedLogTemp] ( [ServerName] ASC, [AppName] ASC, [DateField] ASC, [Licenses] ASC, [LingerValue] ASC, [UserHost] ASC, [UserName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO SET ANSI_PADDING ON GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[SamplingInterval]( [SamplingInterval] [int] NULL ) ON [PRIMARY] GO SET ANSI_PADDING ON GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblUserNameHostGroup]( [HostName] [int] NOT NULL, [HostGroupId] [int] NOT NULL, [FromDate] [datetime] NULL, [ToDate] [datetime] NULL ) ON [PRIMARY] GO CREATE UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[tblUserNameHostGroup] ( [HostName] ASC, [HostGroupId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO SET ANSI_PADDING ON GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblUserNameGroup]( [UserName] [int] NOT NULL, [GroupId] [int] NOT NULL, [FromDate] [datetime] NULL, [ToDate] [datetime] NULL ) ON [PRIMARY] GO CREATE UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[tblUserNameGroup] ( [UserName] ASC, [GroupId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO SET ANSI_PADDING ON GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblUserDetails]( [UserName] [nvarchar](100) NOT NULL, [UserDescription1] [nvarchar](255) NULL, [UserDescription2] [nvarchar](255) NULL, [UserDescription3] [nvarchar](255) NULL, [UserID] [int] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_tblUserDetails_1] PRIMARY KEY CLUSTERED ( [UserID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_tblUserDetails] UNIQUE NONCLUSTERED ( [UserName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING ON GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblUserDetailedLogPrev]( [ServerName] [nvarchar](100) NULL, [UserName] [nvarchar](100) NULL, [UserHost] [nvarchar](100) NULL, [AppName] [nvarchar](100) NULL, [DateField] [datetime] NULL, [LingerDate] [datetime] NULL, [CheckedOutDate] [datetime] NULL, [LicHandle] [int] NULL, [Licenses] [int] NULL, [CheckedOutDateOrg] [datetime] NULL, [LicenseSystem] [nvarchar](100) NULL, [ProjectID] [int] NULL ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [dt] ON [dbo].[tblUserDetailedLogPrev] ( [CheckedOutDate] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO SET ANSI_PADDING ON GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblUserDetailedLog]( [ServerName] [nvarchar](100) NOT NULL, [UserName] [nvarchar](100) NOT NULL, [UserHost] [nvarchar](100) NOT NULL, [AppName] [nvarchar](100) NOT NULL, [DateField] [datetime] NULL, [LingerDate] [datetime] NULL, [CheckedOutDate] [datetime] NULL, [LicHandle] [int] NULL, [Licenses] [int] NULL, [ID] [int] IDENTITY(1,1) NOT NULL, [ProjectID] [int] NULL ) ON [PRIMARY] GO CREATE UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[tblUserDetailedLog] ( [ServerName] ASC, [UserName] ASC, [UserHost] ASC, [AppName] ASC, [DateField] ASC, [CheckedOutDate] ASC, [LicHandle] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [dt] ON [dbo].[tblUserDetailedLog] ( [CheckedOutDate] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO SET ANSI_PADDING ON GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblUserCompleteDetailedLog]( [ServerName] [nvarchar](100) NULL, [UserName] [nvarchar](100) NULL, [UserHost] [nvarchar](100) NULL, [AppName] [nvarchar](100) NULL, [DateField] [datetime] NULL, [LingerDate] [datetime] NULL, [CheckedOutDate] [datetime] NULL, [LicHandle] [int] NULL, [Licenses] [int] NULL ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [defindex] ON [dbo].[tblUserCompleteDetailedLog] ( [DateField] ASC, [ServerName] ASC, [UserName] ASC, [UserHost] ASC, [AppName] ASC, [CheckedOutDate] ASC, [LicHandle] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO SET ANSI_PADDING ON GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblServerNames]( [ServerName] [nvarchar](100) NOT NULL, [Description] [nvarchar](255) NULL, [MaxLic] [int] NULL, [EmailNotificationPing] [bit] NULL, [ServerID] [int] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_tblServerNames_1] PRIMARY KEY CLUSTERED ( [ServerID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_tblServerNames] UNIQUE NONCLUSTERED ( [ServerName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING ON GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblServerGroupsJunction]( [ServerGroupName] [nvarchar](100) NOT NULL, [ServerName] [int] NOT NULL ) ON [PRIMARY] GO CREATE UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[tblServerGroupsJunction] ( [ServerGroupName] ASC, [ServerName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO SET ANSI_PADDING ON GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblServerGroups]( [ServerGroupName] [nvarchar](100) NOT NULL, [Description] [nvarchar](255) NULL, [MaxLic] [int] NULL, [ServerGroupID] [int] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_tblServerGroups_1] PRIMARY KEY CLUSTERED ( [ServerGroupID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_tblServerGroups] UNIQUE NONCLUSTERED ( [ServerGroupName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING ON GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblPingedHosts]( [ServerName] [nvarchar](100) NOT NULL, [UserName] [nvarchar](100) NOT NULL, [UserHost] [nvarchar](100) NOT NULL, [AppName] [nvarchar](100) NOT NULL, [CheckedOutDate] [datetime] NULL, [LingerDate] [datetime] NULL, [LicHandle] [int] NULL, [PingDate] [datetime] NULL, [PingResult] [nvarchar](255) NULL ) ON [PRIMARY] GO SET ANSI_PADDING ON GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblHostGroups]( [HostGroupId] [int] IDENTITY(1,1) NOT NULL, [HostGroupName] [nvarchar](100) NOT NULL, [HostGroupDescription] [nvarchar](255) NULL, [MaxLic] [int] NULL, CONSTRAINT [PK_tblHostGroups] PRIMARY KEY CLUSTERED ( [HostGroupId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING ON GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblHostDetails]( [HostName] [nvarchar](100) NOT NULL, [HostDescription1] [nvarchar](255) NULL, [HostDescription2] [nvarchar](255) NULL, [HostIPv4] [nvarchar](15) NULL, [HostIPv6] [nvarchar](39) NULL, [HostID] [int] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_tblHostDetails_1] PRIMARY KEY CLUSTERED ( [HostID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_tblHostDetails] UNIQUE NONCLUSTERED ( [HostName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING ON GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblGroups]( [GroupId] [int] IDENTITY(1,1) NOT NULL, [GroupName] [nvarchar](100) NOT NULL, [GroupDescription] [nvarchar](255) NULL, [MaxLic] [int] NULL, CONSTRAINT [PK_tblGroups] PRIMARY KEY CLUSTERED ( [GroupId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING ON GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblDeniedUserLog]( [UserName] [nvarchar](100) NOT NULL, [HostName] [nvarchar](100) NOT NULL, [AppName] [nvarchar](100) NOT NULL, [FirstDenial] [datetime] NULL, [LastDenial] [datetime] NULL, [OUT] [datetime] NULL, [Denials] [int] NULL ) ON [PRIMARY] GO CREATE UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[tblDeniedUserLog] ( [UserName] ASC, [HostName] ASC, [AppName] ASC, [FirstDenial] ASC, [LastDenial] ASC, [OUT] ASC, [Denials] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO SET ANSI_PADDING ON GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblAppName]( [AppName] [nvarchar](255) NOT NULL, [Description] [nvarchar](255) NULL, [NoLog] [bit] NOT NULL, [EmailNotificationPercentageLevel] [int] NULL, [EmailNotificationLicensesLeft] [int] NULL, [EmailNotificationLicensesMax] [int] NULL, [EmailNotificationDenials] [bit] NOT NULL, [MaxLic] [int] NULL, [PriceFactorPerHour] [real] NULL, [DisplayAsUser] [bit] NOT NULL, [AppId] [int] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_tblAppName] PRIMARY KEY CLUSTERED ( [AppId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_tblAppName] UNIQUE NONCLUSTERED ( [AppName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[tblAppName] ADD CONSTRAINT [DF_tblAppName_NoLog] DEFAULT ((0)) FOR [NoLog] GO ALTER TABLE [dbo].[tblAppName] ADD CONSTRAINT [DF_tblAppName_EmailNotificationDenials] DEFAULT ((0)) FOR [EmailNotificationDenials] GO ALTER TABLE [dbo].[tblAppName] ADD CONSTRAINT [DF_tblAppName_DisplayAsUser] DEFAULT ((0)) FOR [DisplayAsUser] GO SET ANSI_PADDING ON GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblAppGroupsJunction]( [AppGroupName] [nvarchar](100) NOT NULL, [AppName] [int] NOT NULL ) ON [PRIMARY] GO CREATE UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[tblAppGroupsJunction] ( [AppGroupName] ASC, [AppName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO SET ANSI_PADDING ON GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblAppGroups]( [AppGroupName] [nvarchar](255) NOT NULL, [Description] [nvarchar](255) NULL, [MaxLic] [int] NULL, [AppGroupID] [int] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_tblAppGroups_1] PRIMARY KEY CLUSTERED ( [AppGroupID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_tblAppGroups] UNIQUE NONCLUSTERED ( [AppGroupName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING ON GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[log]( [ServerName] [nvarchar](100) NOT NULL, [DateTime] [datetime] NOT NULL, [AppName] [nvarchar](100) NOT NULL, [MaxLic] [int] NULL, [UsedLic] [int] NULL, [Linger] [int] NULL ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [dt] ON [dbo].[log] ( [DateTime] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[log] ( [ServerName] ASC, [DateTime] ASC, [AppName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO SET ANSI_PADDING ON GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblWildcard]( [ItemType] [nvarchar](10) NOT NULL, [Wildcard] [nvarchar](100) NOT NULL, [ItemGroup] [nvarchar](100) NOT NULL ) ON [PRIMARY] GO SET ANSI_PADDING ON GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblProjects]( [ProjectID] [int] IDENTITY(1,1) NOT NULL, [Project] [nvarchar](50) NOT NULL, [IsActive] [smallint] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[tblProjects] ADD CONSTRAINT [DF_tblProjects_IsActive] DEFAULT ((1)) FOR [IsActive] GO CREATE UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[tblProjects] ( [Project] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO PRINT 'Backup table log' INSERT INTO [BACKUPDATABASE].[dbo].[log] SELECT * FROM [EXISTINGDATABASE].[dbo].[log] WHERE [DateTime]<'YYYY-MM-DD' IF @@ROWCOUNT > 0 DELETE FROM [EXISTINGDATABASE].[dbo].[log] WHERE [DateTime]<'YYYY-MM-DD' PRINT 'Backup table tblAppGroups' GO INSERT INTO [BACKUPDATABASE].[dbo].[tblAppGroups] SELECT * FROM [EXISTINGDATABASE].[dbo].[tblAppGroups] GO PRINT 'Backup table tblAppGroupsJunction' INSERT INTO [BACKUPDATABASE].[dbo].[tblAppGroupsJunction] SELECT * FROM [EXISTINGDATABASE].[dbo].[tblAppGroupsJunction] GO PRINT 'Backup table tblAppName' INSERT INTO [BACKUPDATABASE].[dbo].[tblAppName] SELECT * FROM [EXISTINGDATABASE].[dbo].[tblAppName] GO PRINT 'Backup table tblDeniedUserLog' INSERT INTO [BACKUPDATABASE].[dbo].[tblDeniedUserLog] SELECT * FROM [EXISTINGDATABASE].[dbo].[tblDeniedUserLog] WHERE [LastDenial]<'YYYY-MM-DD' IF @@ROWCOUNT > 0 DELETE FROM [EXISTINGDATABASE].[dbo].[tblDeniedUserLog] WHERE [LastDenial]<'YYYY-MM-DD' GO PRINT 'Backup table tblGroups' SET IDENTITY_INSERT [BACKUPDATABASE].[dbo].[tblGroups] ON GO INSERT INTO [BACKUPDATABASE].[dbo].[tblGroups] ([GroupName],[GroupDescription],[MaxLic],[GroupId]) SELECT [GroupName],[GroupDescription],[MaxLic],[GroupId] FROM [EXISTINGDATABASE].[dbo].[tblGroups] GO SET IDENTITY_INSERT [BACKUPDATABASE].[dbo].[tblGroups] OFF GO PRINT 'Backup table tblHostDetails' INSERT INTO [BACKUPDATABASE].[dbo].[tblHostDetails] SELECT * FROM [EXISTINGDATABASE].[dbo].[tblHostDetails] GO PRINT 'Backup table tblHostGroups' SET IDENTITY_INSERT [BACKUPDATABASE].[dbo].[tblHostGroups] ON GO INSERT INTO [BACKUPDATABASE].[dbo].[tblHostGroups] ([HostGroupName],[HostGroupDescription],[MaxLic],[HostGroupId]) SELECT [HostGroupName],[HostGroupDescription],[MaxLic],[HostGroupId] FROM [EXISTINGDATABASE].[dbo].[tblHostGroups] GO SET IDENTITY_INSERT [BACKUPDATABASE].[dbo].[tblHostGroups] OFF GO PRINT 'Backup table tblPingedHosts' INSERT INTO [BACKUPDATABASE].[dbo].[tblPingedHosts] SELECT * FROM [EXISTINGDATABASE].[dbo].[tblPingedHosts] WHERE [PingDate]<'YYYY-MM-DD' IF @@ROWCOUNT > 0 DELETE FROM [EXISTINGDATABASE].[dbo].[tblPingedHosts] WHERE [PingDate]<'YYYY-MM-DD' GO PRINT 'Backup table tblProjects' SET IDENTITY_INSERT [BACKUPDATABASE].[dbo].[tblProjects] ON GO INSERT INTO [BACKUPDATABASE].[dbo].[tblProjects] ([ProjectID],[Project],[IsActive]) SELECT [ProjectID],[Project],[IsActive] FROM [EXISTINGDATABASE].[dbo].[tblProjects] GO SET IDENTITY_INSERT [BACKUPDATABASE].[dbo].[tblProjects] OFF GO PRINT 'Backup table tblServerGroups' INSERT INTO [BACKUPDATABASE].[dbo].[tblServerGroups] SELECT * FROM [EXISTINGDATABASE].[dbo].[tblServerGroups] GO PRINT 'Backup table tblServerGroupsJunction' INSERT INTO [BACKUPDATABASE].[dbo].[tblServerGroupsJunction] SELECT * FROM [EXISTINGDATABASE].[dbo].[tblServerGroupsJunction] GO PRINT 'Backup table tblServerNames' INSERT INTO [BACKUPDATABASE].[dbo].[tblServerNames] SELECT * FROM [EXISTINGDATABASE].[dbo].[tblServerNames] GO PRINT 'Backup table tblUserCompleteDetailedLog' INSERT INTO [BACKUPDATABASE].[dbo].[tblUserCompleteDetailedLog] SELECT * FROM [EXISTINGDATABASE].[dbo].[tblUserCompleteDetailedLog] WHERE [DateField]<'YYYY-MM-DD' IF @@ROWCOUNT > 0 DELETE FROM [EXISTINGDATABASE].[dbo].[tblUserCompleteDetailedLog] WHERE [DateField]<'YYYY-MM-DD' GO PRINT 'Backup table tblUserDetailedLog' SET IDENTITY_INSERT [BACKUPDATABASE].[dbo].[tblUserDetailedLog] ON GO INSERT INTO [BACKUPDATABASE].[dbo].[tblUserDetailedLog] ([ServerName],[UserName],[UserHost],[AppName],[DateField],[LingerDate],[CheckedOutDate],[LicHandle],[Licenses],[ID],[ProjectID]) SELECT [ServerName],[UserName],[UserHost],[AppName],[DateField],[LingerDate],[CheckedOutDate],[LicHandle],[Licenses],[ID],[ProjectID] FROM [EXISTINGDATABASE].[dbo].[tblUserDetailedLog] WHERE [DateField]<'YYYY-MM-DD' IF @@ROWCOUNT > 0 DELETE FROM [EXISTINGDATABASE].[dbo].[tblUserDetailedLog] WHERE [DateField]<'YYYY-MM-DD' GO SET IDENTITY_INSERT [BACKUPDATABASE].[dbo].[tblUserDetailedLog] OFF GO PRINT 'Backup table tblUserDetailedLogPrev' INSERT INTO [BACKUPDATABASE].[dbo].[tblUserDetailedLogPrev] SELECT * FROM [EXISTINGDATABASE].[dbo].[tblUserDetailedLogPrev] WHERE [DateField]<'YYYY-MM-DD' IF @@ROWCOUNT > 0 DELETE FROM [EXISTINGDATABASE].[dbo].[tblUserDetailedLogPrev] WHERE [DateField]<'YYYY-MM-DD' GO PRINT 'Backup table tblUserDetails' INSERT INTO [BACKUPDATABASE].[dbo].[tblUserDetails] SELECT * FROM [EXISTINGDATABASE].[dbo].[tblUserDetails] GO PRINT 'Backup table tblUserNameGroup' INSERT INTO [BACKUPDATABASE].[dbo].[tblUserNameGroup] SELECT * FROM [EXISTINGDATABASE].[dbo].[tblUserNameGroup] GO PRINT 'Backup table tblUserNameHostGroup' INSERT INTO [BACKUPDATABASE].[dbo].[tblUserNameHostGroup] SELECT * FROM [EXISTINGDATABASE].[dbo].[tblUserNameHostGroup] GO PRINT 'Backup table tblWildcard' INSERT INTO [BACKUPDATABASE].[dbo].[tblWildcard] SELECT * FROM [EXISTINGDATABASE].[dbo].[tblWildcard] GO PRINT 'Backup table UserDetailedLogTemp' INSERT INTO [BACKUPDATABASE].[dbo].[UserDetailedLogTemp] SELECT * FROM [EXISTINGDATABASE].[dbo].[UserDetailedLogTemp] WHERE [DateField]<'YYYY-MM-DD' IF @@ROWCOUNT > 0 DELETE FROM [EXISTINGDATABASE].[dbo].[UserDetailedLogTemp] WHERE [DateField]<'YYYY-MM-DD' GO PRINT 'Ready' GO