Asger Blond
ab-mi at post3.tele.dk
Thu Sep 10 17:19:52 CDT 2009
Just one more point:
Remember to make a full database backup after switching the recovery model.
Otherwise you won't be able to make log-backups.
You could create a main-procedure like this:
CREATE PROCEDURE spMain
AS
ALTER DATABASE <dbname> SET RECOVERY SIMPLE
EXEC sp1
EXEC sp2
EXEC sp3 (etc)
ALTER DATABASE <dbname> SET RECOVERY FULL
BACKUP DATABASE <dbname> TO DISK = 'X:\mybackups\mydb.bak'
GO
Asger
-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Asger Blond
Sendt: 10. september 2009 23:28
Til: 'Discussion concerning MS SQL Server'
Emne: Re: [dba-SQLServer] SQL Server 2005 - Copying a database
To prevent the log swelling you could just set the recovery model to simple
before executing the stored procedures then set it back to full:
ALTER DATABASE <dbname> SET RECOVERY SIMPLE
Run stored procedures
ALTER DATABASE <dbname> SET RECOVERY FULL
Asger
-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Robert
Stewart
Sendt: 10. september 2009 22:42
Til: dba-sqlserver at databaseadvisors.com
Emne: Re: [dba-SQLServer] SQL Server 2005 - Copying a database
John,
What you might want to do is shrink the log file by truncating it
between each of the 10 SPs that you are running. This will keep the
bloat down to a minimum and in the end, drop it down to it's smallest size.
The simplest way of doing it is to do a backup/restore. Then make the
changes oyu want to in it.
Robert
At 12:00 PM 9/10/2009, you wrote:
>Date: Thu, 10 Sep 2009 12:24:56 -0400
>From: jwcolby <jwcolby at colbyconsulting.com>
>Subject: Re: [dba-SQLServer] SQL Server 2005 - Copying a database
>To: Discussion concerning MS SQL Server
> <dba-sqlserver at databaseadvisors.com>
>Message-ID: <4AA92858.3010907 at colbyconsulting.com>
>Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
>Francisco,
>
>Thanks for the response. The situation is that I have a small
>"template" database. The total thing
>is only a few megabytes, but it has a couple of empty "template
>tables" as well as about 30 views
>and about 15 stored procedures.
>
>I then copy that database to a new name, the name of an order such
>as XYZ1209. I go in and tweak a
>couple of pre-existing views and run a stored procedure which in
>turns runs about 10 other stored
>procedures. Those stored procedures build tblOrderData up from
>scratch with field names specific to
>that order, and populates tblOrderData with data pulled from the
>"database from hell".
>
>It is that process of populating tblOrderData, and later doing
>updates to a couple of fields in
>tblOrderData that can cause the log file to swell.
>
>When I am done with this whole process I pretty much just leave that
>order database alone, in fact
>after a couple of months I usually detach the db. I MIGHT have to
>reference tblOrderData in a
>subsequent order to not use records used in a previous order, stuff
>like that. But nothing that
>would ever swell the log file again.
>
>So I need to:
>
>1) Copy the template to a new name
>2) Get the log file in a different drive ("set of spindles") for
>performance reasons.
>3) Shrink the log file at the very end when I am done with the order.
>
>I need to do this from Access - and eventually from C#.Net. Right
>now I have an Access database
>that does all this stuff for me, allows me to execute stored
>procedures in these order databases
>etc. Right now I do those three pieces manually, and it would be
>nice to automate those pieces as well.
>
>I will look at your code to get this done.
>
>Much appreciated.
>
>John W. Colby
>www.ColbyConsulting.com
>
>
>Francisco Tapia wrote:
> > John,
> > It seems that to copy a database it might be easier if you simply
backup
> > and restore a database and it could be much quicker and you avoid the
log
> > issue, I'm wondering if the log file grows to a ridiculous size because
you
> > literally copy data from the existing database to the new
> database. If this
> > is the case doing a restore would allow you to simply restore the
> last known
> > good backup + differential, if you don't to this now, you might consider
> > having your sproc perform a simple step to backup your db as a
DIFFERENTIAL
> > backup, then simply run a restore
> >
> > --DIFFERENTIAL BACKUP
> >
> > Declare @Disk AS VARCHAR(1000)
> > SET @Disk = 'E:\DBBAckups\DIFF_DBName_'+ CAST(YEAR(GETDATE()) AS
> > VARCHAR(4)) + RIGHT('0'+ CAST(MONTH(GETDATE()) AS VARCHAR(4)), 2) +
> > RIGHT('0' +CAST(DAY(GETDATE()) AS VARCHAR(4)) ,2)+ '.Bak'
> > BACKUP DATABASE [DBName] TO DISK = @Disk WITH INIT , NAME =
N'DBNameDB
> > bkup', NOSKIP WITH DIFFERENTIAL
> >
> >
> > --Then to restore the database you'll need to Restore the FULL
> database with
> > NORECOVERY which puts it into a restore state so you can also apply your
> > DIFFERENTIAL RESTORE.
> >
> >
> > CREATE TABLE #tmpFile (PID INT IDENTITY(1,1)NOT NULL, Result
VARCHAR(255))
> > INSERT INTO #tmpFile (Result)
> > EXEC master.dbo.xp_cmdshell 'DIR E:\DBBackups\\FULL*.sqb /B /O-d'
> >
> > Declare @RestoreDate AS DateTime
> > Declare @Disk AS NVARCHAR(1000)
> > SELECT TOP 1 @Disk = 'E:\DBBackups\' + Result FROM #tmpFile
> >
> > --FULL DB RESTORE WITH NORECOVERY
> > SET @Disk = '-SQL "RESTORE DATABASE [DBNAME] FROM DISK = '''+ at Disk+'''
> > WITH NORECOVERY, MOVE ''DataBaseName1'' TO ''D:\Program
> Files\Microsoft SQL
> > Server\MSSQL\Data\DatabaseName1.MDF'', MOVE ''DatabaseName2'' TO
> > ''d:\Program Files\Microsoft SQL
> Server\MSSQL\Data\DataBaseName2.mdf'', MOVE
> > ''DatabaseName_log'' TO ''d:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\DatabaseName_log.ldf'', REPLACE"'
> > EXEC master..sqlbackup @Disk
> >
> > DROP TABLE #tmpFile
> >
> > --Now Restore the DIFFERENTIAL BACKUP
> >
> > CREATE TABLE #tmpFile (PID INT IDENTITY(1,1)NOT NULL, Result
VARCHAR(255))
> > INSERT INTO #tmpFile (Result)
> > EXEC master.dbo.xp_cmdshell 'DIR E:\DBBackups\DIFF*.sqb /B /O-d'
> >
> > Declare @RestoreDate AS DateTime
> > Declare @Disk AS NVARCHAR(1000)
> > SELECT TOP 1 @Disk = 'E:\DBBackups\' + Result FROM #tmpFile
> >
> > --DIFF DB RESTORE WITH RECOVERY
> > SET @Disk = '-SQL "RESTORE DATABASE [DBNAME] FROM DISK = '''+ at Disk+'''
> > WITH RECOVERY, MOVE ''DataBaseName1'' TO ''D:\Program Files\Microsoft
SQL
> > Server\MSSQL\Data\DatabaseName1.MDF'', MOVE ''DatabaseName2'' TO
> > ''d:\Program Files\Microsoft SQL
> Server\MSSQL\Data\DataBaseName2.mdf'', MOVE
> > ''DatabaseName_log'' TO ''d:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\DatabaseName_log.ldf'', REPLACE"'
> > EXEC master..sqlbackup @Disk
> >
> > DROP TABLE #tmpFile
> >
> >
> >
> >
> > To shrink your log files:
> > BACKUP LOG DATABASENAME WITH TRUNCATE_ONLY ---<<< this just clears the
> > checkpoints from your log file
> > DBCC SHRINKFILE ('Data_LogFile', 1) ---- performs the actual shrinking
of
> > the log file which won't happen if you have uncommitted checkpoints in
your
> > log file.
> >
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com