[dba-SQLServer] Copy a database using Elizabeth's suggestion plus parameters

Thu Mar 27 10:45:38 CDT 2008

Hi Eric

I know, but here we just wish to create a copy of an existing template database. No "backup to a safe place", no volume data, no speed issue, but - and that's the big difference from a normal backup/restore job - a new command every time it is called. 

So instead of all these parameters just build the command string that is needed while you have VB.Net or Access or whatever running - as a master controller copying files, sending e-mails, and creating Excel files - and push that command to the server engine. I know most of it can be done within SQL Server, but this is where I understand John: why not use the environment and languages you are familiar with? You will, anyway, need some simple GUI to type in start parameters and to read or print result codes or the like and pop a messagebox in case of an  error.

/gustav

>>> ebarro at verizon.net 27-03-2008 16:06:38 >>>
Gustav,

.NET is not the tool to use for backing up and restoring SQL databases.
Granted, you may find solutions out there that do that it won't be optimal.
Besides, the tool is already there for you to use. Just use it.

I would use .NET as a UI to call the sprocs but not everything can be done
using .NET. It's just as bad as when the *priests* insist that SQL is the
end all and be all and that you'll go to HELL if you don't do it the TSQL
way.

Eric

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com 
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Gustav
Brock
Sent: Thursday, March 27, 2008 6:49 AM
To: dba-sqlserver at databaseadvisors.com 
Subject: Re: [dba-SQLServer] Copy a database using Elizabeth's suggestion
plus parameters

Hi John

I think you are trapped by the single/double quote syntax for embedded
strings just like when you build string expressions form strings in VBA.
Look in Robert's code how he used Chr(34) to wrap string.

But your trouble is why I suggested to lift this whole thing out of T-SQL
into VB.Net (from where you control the complete process anyway, right?).

/gustav

>>> jwcolby at colbyconsulting.com 27-03-2008 14:34 >>>
Thanks Elizabeth and Gustav.

I tried to get it working using passed in parameters, but promptly got
bogged down in the ever helpful "error near ." error messages.

I need a "FromDbName", a "ToDbName", and a "DbLocationPath" parameter.  Just
to show the kinds of issues that more advanced users never even think about
anymore, but which stop me cold...

I created these parameters in a stored procedure, copied the code in and
started replacing the various pieces with @ParamThis and @paramThat.  I
immediately got the one and only error message that SQL Server knows "error
near @".  

I got out BOL "the ever helpful.." ... Oh, different rant.

I immediately commented out the code pasted in to the SP template and voila,
it "ran".  Uncommented just the first line and get "error near +"

ALTER PROCEDURE [dbo].[usp_CopyDatabase]
	-- Add the parameters for the stored procedure here
	@strDBNameFrom varchar(100),
	@strDBNameTo varchar(100),
	@strDBDir  varchar(255)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here --BACKUP DATABASE @strDBNameFrom

--   TO DISK = @strDBDir + @strDBNameFrom + '.bak'

--RESTORE FILELISTONLY 
--   FROM DISK = @strDBDir + @strDBNameFrom + '.bak'

--RESTORE DATABASE @strDBNameTo 
--   FROM DISK = @strDBDir + @strDBNameFrom + '.bak'
--   WITH MOVE strDBNameFrom + '_Data' TO  @strDBDir + @strDBNameTo +
'.mdf',
--   MOVE strDBNameFrom + '_Log' TO @strDBDir + @strDBNameTo + '.ldf'
END

My uneducated guess is that as soon as I start actually trying to run this
thing, the parameters have nothing in them so they will not run.  But...

Running it is the only way to "SAVE" the changes right?  So I am kind of
stuck.  So do I have to make my parameters have default values just so the
changes to the code will save?  Or is it even that?  

I can't imagine why I would get frustrated when I get the ever helpful
"error near +".  

John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com 
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Gustav
Brock
Sent: Thursday, March 27, 2008 4:57 AM
To: dba-sqlserver at databaseadvisors.com 
Subject: Re: [dba-SQLServer] Copy a database using a store procedureor
function

Hi Elizabeth - and John

The gun smoke distracted me so much from the original question that I had
lost the request of John - to be able to perform this parameterized from
code.

But it seems like your suggestion could be the key of a solution. I found a
minor error ('AdventureWorks_Data' should read just 'AdventureWorks'). With
that corrected I  simply moved your code (skipping the GO) into a
pass-through query in Access:

BACKUP DATABASE somedb 
   TO DISK = 'c:\template2.bak'

RESTORE FILELISTONLY 
   FROM DISK = 'c:\template2.bak'

RESTORE DATABASE newfromtemplate2 
   FROM DISK = 'c:\template2.bak'
   WITH MOVE 'somedb' TO 'c:\newfromtemplate2.mdf',
   MOVE 'somedb_log' TO 'c:\newfromtemplate2.ldf'

Wow! In a few seconds you have a brand new database.
Of course, once a template has been created you can skip the first part with
the backup.

Now, for this to work as to fulfill John's request, you will have to rewrite
the SQL above to reflect the name of the new database but that can be done
with a simple replace of "newfromtemplate2" to the actual new database name
and then execute the query. This John can code with his left hand in Access
and it shouldn't take much to write a small class in VB.net to perform the
same two actions: replace the database name and send the SQL command to the
server.

/gustav

>>> Elizabeth.J.Doering at wellsfargo.com 25-03-2008 21:10 >>>
I haven't used this (being pretty new to this myself), but I'm sure some one
else here can speak to it:

E. Make a copy of a database using BACKUP and RESTORE This example uses both
the BACKUP and RESTORE statements to make a copy of the AdventureWorks
database. The MOVE statement causes the data and log file to be restored to
the specified locations. The RESTORE FILELISTONLY statement is used to
determine the number and names of the files in the database being restored.
The new copy of the database is named TestDB. For more information, see
RESTORE FILELISTONLY (Transact-SQL). 

 Copy Code
BACKUP DATABASE AdventureWorks 
   TO DISK = 'C:\AdventureWorks.bak'

RESTORE FILELISTONLY 
   FROM DISK = 'C:\AdventureWorks.bak'

RESTORE DATABASE TestDB 
   FROM DISK = 'C:\AdventureWorks.bak'
   WITH MOVE 'AdventureWorks_Data' TO 'C:\testdb.mdf',
   MOVE 'AdventureWorks_Log' TO 'C:\testdb.ldf'
GO

This is from BOL for SQL Server 2005, which is often amazingly helpful.

HTH,

Liz 

Liz Doering
elizabeth.j.doering at wellsfargo.com 
612.667.2447 