[dba-SQLServer] SQL Server 2005 - Copying a database

jwcolby jwcolby at colbyconsulting.com
Thu Sep 10 11:24:56 CDT 2009


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.
> 
> 
> 
> 
> 
> -Francisco
> http://sqlthis.blogspot.com | Tsql and More...




More information about the dba-SQLServer mailing list