November 4, 2008 at 11:19 am
I manage a JDE Database and periodically need to copy our production data into a test database.
The difficulty arises in that the table owner name in Production database is different from those in the test database, i.e. production table owners are proddta and test table owner is crpdta (std names from manufacturer).
I have a script that will copy all of the tables from production to test and works like this:
(Basic structure... some detail removed)
declare @i as int
declare @FromUser sysname
declare @ToUser sysname
declare @ToSt varchar(50)
declare @FromSt varchar(50)
Declare @Table sysname
declare @vsql as nvarchar(1000)
Declare @FromDB varchar(25)
Declare @todb varchar(25)
select @Fromuser='PRODDTA'
select @FromDB='PS_PRODUCTION'
select @ToUser='CRPDTA'
select @todb='JDE_CRP'
Select @i = Min(id) FROM sysobjects WHERE type = 'U' and
uid=(select uid from sysusers where name=@FromUser)
set nocount on
if @i is null
begin
print '@i is null, terminating prematurly'
end
While @i is not null
Begin
Select @Table = Name from sysobjects where id = @i
set @FromSt = @Fromdb + '.' + @Fromuser + '.' + @Table
set @ToSt = @todb + '.' + @Touser + '.' + @Table
exec ('Truncate Table ' + @todb + '.' + @Touser + '.' + @Table)
exec ('Insert Into ' + @todb + '.' + @Touser + '.' + @Table + ' Select * from ' + @Fromdb + '.' + @Fromuser + '.' + @Table)
Select @i = Min(id) from sysobjects where id > @i and type = 'U' and uid=(select uid from sysusers where name=@Fromuser)
if @i is null
begin
print '@i is null, finished copying PROD to CRP'
end
end
This works nicely (albeit slow) but it has a serious drawback. Since the source DB is +55G, this script creates a huge log file.
Is there a way to turn off, or limit log file activity during this process? Or, is there a better way to do this?
SQL2k, WIN2K
November 4, 2008 at 11:49 am
You can't disable logging. It's not an optional component of the database.
What you can do is set the database you're doing the transfer into simple recovery. Since it's not a production system, it's not a risk. Then between each insert statement run a checkpoint in that database.
So, after each insert, run something like this
exec ('Use ' + @todb + '; checkpoint;')
In simple recovery, checkpoint will truncate the inactive portion of the log, allowing the log space to be reused and hopefully, reduce the overall amount of space required.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 4, 2008 at 11:50 am
Before transfering the data, change the recovery model of your test database to use the simple recovery model.
November 4, 2008 at 11:54 am
This will work, that is the command I was looking for.
Since the database must be in full recovery mode for our backup software to work, is there a way to set the DB to simple mode via an SQL command and then reset it after the script finishes?
Probably not, but can't hurt to ask.
November 4, 2008 at 11:58 am
ALTER DATABASE. Look it up in BOL.
November 4, 2008 at 12:00 pm
Gotcha, thanks to all for the help.
November 4, 2008 at 12:07 pm
Once you've set it back to full, you'll have to take a full database backup or your backup software, which I assume does log backups, will fail.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 4, 2008 at 12:16 pm
USE Master
GO
ALTER DATABASE databasename
SET RECOVERY SIMPLE
GO
November 4, 2008 at 12:24 pm
If I got all of your excellent comments, the final code should be something like this:
use master
ALTER DATABASE JDE_CRP
set RECOVERY SIMPLE
declare @i as int
declare @FromUser sysname
declare @ToUser sysname
declare @ToSt varchar(50)
declare @FromSt varchar(50)
Declare @Table sysname
declare @vsql as nvarchar(1000)
Declare @FromDB varchar(25)
Declare @todb varchar(25)
select @Fromuser='PRODDTA'
select @FromDB='PS_PRODUCTION'
select @ToUser='CRPDTA'
select @todb='JDE_CRP'
Select @i = Min(id) FROM sysobjects WHERE type = 'U' and
uid=(select uid from sysusers where name=@FromUser)
set nocount on
if @i is null
begin
print '@i is null, terminating prematurly'
end
While @i is not null
Begin
Select @Table = Name from sysobjects where id = @i
set @FromSt = @Fromdb + '.' + @Fromuser + '.' + @Table
set @ToSt = @todb + '.' + @Touser + '.' + @Table
exec ('Truncate Table ' + @todb + '.' + @Touser + '.' + @Table)
exec ('Insert Into ' + @todb + '.' + @Touser + '.' + @Table + ' Select * from ' + @Fromdb + '.' + @Fromuser + '.' + @Table)
--> BEGIN ADDED CODE HERE (Good stuff here)<--
exec ('Use ' + @todb + ';CHECKPOINT;')
exec ('Use ' + @FromDB)
--> END ADDED CODE HERE <--
Select @i = Min(id) from sysobjects where id > @i and type = 'U' and uid=(select uid from sysusers where name=@Fromuser)
if @i is null
begin
print '@i is null, finished copying PROD to CRP'
end
end
go
use master
ALTER DATABASE JDE_CRP
set RECOVERY FULL
GO
-- Create a logical backup device for the full CRPbackup.
USE master
EXEC sp_addumpdevice 'disk', 'mydiskdump', 'E:\SQL2K\MSSQL\backup\CRP.bak'
-- Back up the full CRPdatabase.
BACKUP DATABASE JDE_CRP TO mydiskdump
go
I think that adds all the comments you provided.
Thanks you all for the excellent help.
November 4, 2008 at 12:30 pm
Looks about right. All you have to do now is test it.
November 4, 2008 at 12:54 pm
I wouldn't bother creating a backup device for a once-off backup, and if you run that more than once, you may get an error saying the backup device already exists.
Just backup to disk
BACKUP DATABASE JDE_CRP TO DISK = 'E:\SQL2K\MSSQL\backup\CRP.bak'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply