Job - Scheduled update table in another databaes nightly - please review and comment on script / process

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Job - Scheduled update table in another...

Job - Scheduled update table in another databaes nightly - please review and comment on script / process

Rate Topic

Display Mode

Topic Options

Author

Message

Mile Higher Than Sea Level

Posted Monday, October 15, 2012 4:47 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 11:59 AM
Points: 72, Visits: 223

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.

USE DBT;
GO
IF OBJECT_ID('dbo.GIS.GISDaily', 'U') IS NOT NULL
DROP TABLE dbo.GIS.GISDaily;
GO
ALTER DATABASE GIS SET RECOVERY BULK_LOGGED;
GO
SELECT * INTO GIS.dbo.GISDaily
FROM GIS_Info
GO
ALTER DATABASE GIS SET RECOVERY Simple;
GO

-- 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.

Post #1372989

Sean Pearce

Posted Wednesday, October 17, 2012 2:21 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 2:27 AM
Points: 350, Visits: 1,340

Firstly, it should be GIS.dbo.GISDaily and not dbo.GIS.GISDaily.
Secondly you can leave the database in simple recovery.

USE DBT;
GO

IF OBJECT_ID('GIS.dbo.GISDaily', 'U') IS NOT NULL
 DROP TABLE GIS.dbo.GISDaily;
GO

SELECT *
INTO GIS.dbo.GISDaily
FROM GIS_Info
GO

http://thesqlguy.blogspot.com/

Post #1373668

« Prev Topic | Next Topic »

Permissions