Please review Newbie's code and plan: Comments welcome
SQL 2008 - Database name FOO FOO has 2 databases DBT and GIS Only Table in GIS is GISDaily
Database GIS has SQL Server Security for the GIS dept username/Password set to DBO
The GISDaily will be set to a scheduled task to run. The GIS_Info is a Table (or view) that will be modified (e.g. new fields) from time-to-time
Each night - the GISDaily will be destroyed and rebuilt. The GIS Database will have GISDaily re-built each night.
IF OBJECT_ID('dbo.GIS.GISDaily', 'U') IS NOT NULL
DROP TABLE dbo.GIS.GISDaily;
ALTER DATABASE GIS SET RECOVERY BULK_LOGGED;
SELECT * INTO GIS.dbo.GISDaily
ALTER DATABASE GIS SET RECOVERY Simple;
-- the idea is to rebuild GISDaily from a view in DBT. The GIS dept will provide changes in the DBT view.
-- The UN/PW for the SQL server is only used for the GIS DB. So, the rebuilt table will be available to them.