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

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


On Thu, Sep 10, 2009 at 5:53 AM, jwcolby <jwcolby at colbyconsulting.com>wrote:

> Interesting Arthur.
>
> So from the gui I right click on the db of interest, /tasks / copy
> database.  A wizard opens and I
> do my thing, naming the files, where they should go etc.
>
> Are you saying that SQL Server saves that somewhere as TSQL and I can go
> look at it?
>
> John W. Colby
> www.ColbyConsulting.com
>
>
> Arthur Fuller wrote:
> > I've never had to do this, but I think that this approach would work. The
> > whole Management Studio UI (and Enterprise Manager before it) are nothing
> > but front ends that hide T-SQL commands. Whenever I want to know what
> SSMS
> > is doing under the covers, I execute the command of interest using the
> GUI
> > and then view the query syntax (include query with results).
> > Arthur
> >
> > On Thu, Sep 10, 2009 at 8:24 AM, jwcolby <jwcolby at colbyconsulting.com
> >wrote:
> >
> >> I need to programmatically copy a database to a new name (make a copy),
> and
> >> specify where the data
> >> file and log file goes.  Can anyone give me help in figuring that out?
>  I
> >> want to write a stored
> >> procedure to do this and call that from Access.
> >>
> >>
> > _______________________________________________
> > 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
>
>