Eric Barro
ebarro at verizon.net
Thu Mar 27 09:38:31 CDT 2008
John/Gustav,
You can't marry parameters and SQL commands in the same sentence as per your
code example . Therefore...
BACKUP DATABASE @strDBNameFrom TO DISK = @strDBDir + @strDBNameFrom + '.bak'
Will not work because SQL thinks of parameters in terms of, well criteria
for select, insert and update type queries.
You need to assign the SQL commands to a SQL variable that you have to
declare as such...
DECLARE @sql varchar(1000) -- I believe 4000 is the max.
SET @sql = 'BACKUP DATABASE ' + CHR(39) + @strDBNameFrom ' + CHR(39) + ' TO
DISK = ' + CHR(39) + @strDBDir + @strDBNameFrom + '.bak' + CHR(39)
And then you need to execute the SQL command using another SQL command...
EXEC (@sql) -- this will run the dynamic parametized SQL query to accomplish
the task of backing up the database.
--Now to the restore portion...
--note the use of CHR(39) ; single apostrophe as delimeters
SET @sql = 'RESTORE DATABASE MLV FROM DISK = ' + CHR(39) +
'C:\temp\MLVBak.BAK' + CHR(39)
SET @sql = @sql + ' WITH MOVE ' + CHR(39) + 'MLV_Data' + CHR(39) + ' TO '
+ CHR(39) + 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MLV_Data.mdf'
+ ', '
SET @sql = @sql + 'MOVE ' + CHR(39) + 'MLV_log' + CHR(39) + ' TO ' +
CHR(39) + 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MLV_log.LDF' +
CHR(39)
--To debug uncomment the line below (i.e remove the --; -- and /* */ are
treated as comment delimiters in SQL syntax)
--PRINT @sql --this should give you exactly the following command commented
below
/*
RESTORE DATABASE MLV
FROM DISK = 'C:\inetpub\wwwroot\pvp\MLVBak.BAK'
WITH MOVE 'MLV_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\MLV_Data.mdf',
MOVE 'MLV_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\MLV_log.LDF'
*/
--To run it use EXEC command
EXEC (@sql)
That's it in a nutshell...
NOTES: for John
1. Robert's sproc is spot on. What he didn't provide were comments such as
why it needs to be like this and not like that, etc...
2. Robert DID provide comments. Simple, yes but you are wrong in saying he
didn't provide comments. Take a look at his code again and wherever you see
the double dash (--), those are comments.
3. (including Gustav)...it doesn't matter what you name your physical MDF or
LDF files, whether you append _Data or _Log to them. What matters is when
you search for them in the file system you can easily spot them and
associate them with each other because SQL server data files ALWAYS come
with SQL server log files (unless of course you specify the no logs option).
4. For your non-relational application John, you can simply do away with
TRANSCATION LOG files unless you need to rollback changes. It makes the
processing (CRUD operations) go faster since SQL doesn't have to log
everything that takes place.
5. Additional tips...if you simply want to "refresh" a table (meaning wipe
the data out), use
TRUNCATE TABLE <table name> (< > are not needed of course when you type the
command)
...it skips the transaction logs and sets the identity (autonumber for you
Access nubees) field back to 1. It is way faster than DELETE FROM <table
name>.
Finally...let's just all lighten up and quit being childish. This is not a
my thingy is bigger than your thingy deal. It's just SQL server...and by the
way for this interested in SQL server 2008, go to a Microsoft event and get
a FREE copy of SQL server 2008, Visual Studio 2008 and Windows Server 2008.
That's what I'm gonna do later on today. NYAH, NYAH, NYAH, NA...NA...NA
LOL!
Eric
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, March 27, 2008 6:34 AM
To: 'Discussion concerning MS SQL Server'
Subject: [dba-SQLServer] Copy a database using Elizabeth's suggestion
plusparameters
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
_______________________________________________
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