[dba-SQLServer] Copy a database using James example code

jwcolby jwcolby at colbyconsulting.com
Thu Mar 27 17:43:56 CDT 2008


It failed the second time though, even when run locally.  Something to do
with the log file. 


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 James
Barash
Sent: Thursday, March 27, 2008 5:53 PM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Copy a database using James example code

I've only tried to run it from the server using a local path since SQL
Server can only create database files on a local drive. I use Remote Desktop
to connect to the server and run everything from there. 

James

-----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 5:14 PM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Copy a database using James example code

James, that does indeed execute properly if it is executed from the server
computer, using local paths (E:\SQLServerData instead of
\\Stonehenge\SQLServerData\).  If I try to run it from my laptop it gives
errors:

Processed 192 pages for database 'IRGeneric', file 'IRGeneric' on file 2.
Processed 1 pages for database 'IRGeneric', file 'IRGeneric_log' on file 2.
BACKUP DATABASE successfully processed 193 pages in 0.007 seconds (225.865
MB/sec).
Msg 5110, Level 16, State 2, Procedure usp_CopyDatabase, Line 33 The file
"\\Stonehenge\SQLServerData\IRTest.mdf" is on a network path that is not
supported for database files.
Msg 3156, Level 16, State 3, Procedure usp_CopyDatabase, Line 33 File
'IRGeneric' cannot be restored to '\\Stonehenge\SQLServerData\IRTest.mdf'.
Use WITH MOVE to identify a valid location for the file.
Msg 5110, Level 16, State 2, Procedure usp_CopyDatabase, Line 33 The file
"\\Stonehenge\SQLServerData\IRTest.ldf" is on a network path that is not
supported for database files.
Msg 3156, Level 16, State 3, Procedure usp_CopyDatabase, Line 33 File
'IRGeneric_log' cannot be restored to
'\\Stonehenge\SQLServerData\IRTest.ldf'. Use WITH MOVE to identify a valid
location for the file.
Msg 3119, Level 16, State 1, Procedure usp_CopyDatabase, Line 33 Problems
were identified while planning for the RESTORE statement. Previous messages
provide details.
Msg 3013, Level 16, State 1, Procedure usp_CopyDatabase, Line 33 RESTORE
DATABASE is terminating abnormally.

AFAIK the \\ directories are read / write.  Perhaps SQL Server will not
perform some operations to network paths?  If I run it from my Server
machine substituting e:\SQLServerData\ it runs.

If I try to execute the SP from my laptop using E:\ (which exists on the
server but not on my laptop) it throws an error:

Processed 192 pages for database 'IRGeneric', file 'IRGeneric' on file 4.
Processed 1 pages for database 'IRGeneric', file 'IRGeneric_log' on file 4.
BACKUP DATABASE successfully processed 193 pages in 0.024 seconds (65.877
MB/sec).
Msg 3159, Level 16, State 1, Procedure usp_CopyDatabase, Line 33 The tail of
the log for the database "IRTest" has not been backed up. Use BACKUP LOG
WITH NORECOVERY to backup the log if it contains work you do not want to
lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to
just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Procedure usp_CopyDatabase, Line 33 RESTORE
DATABASE is terminating abnormally.

I suspect that this is a "something fails the second time" kind of thing
since it is throwing the same error when executed again directly on the
server.

I tried the following:

RESTORE DATABASE @strDBNameTo 
   FROM DISK = @FileName
   WITH MOVE @strDBNameData  TO  @MDFName,
   MOVE @strDBNameLog TO @NDFName
   WITH REPLACE

But it now gives the infamous "incorrect syntax near WITH" error.

RESTORE DATABASE @strDBNameTo 
   FROM DISK = @FileName
   WITH REPLACE
   WITH MOVE @strDBNameData  TO  @MDFName,
   MOVE @strDBNameLog TO @NDFName

Gives me the same thing.

GETTING CLOSE!!  8-)

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 James
Barash
Sent: Thursday, March 27, 2008 4:51 PM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Copy a database using James example code

John:

It is failing because the stored procedure makes certain assumptions about
the logical and physical names of the mdf and ldf files, which is seems do
not apply to your test database. It assumes the data file is called
IRGeneric_Data.mdf and the log file is called IRGeneric_Log.ndf. According
to the output, it looks as though your files are called IRGeneric.mdf and
IRGeneric_Log.ndf. 
If you change the line: 

Select @strDBNameData = @strDBNameFrom + '_Data'

To

Select @strDBNameData = @strDBNameFrom

then it should work.

James
-----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 2:47 PM
To: 'Discussion concerning MS SQL Server'
Subject: [dba-SQLServer] Copy a database using James example code

James,

I created the SP, and executed it from the query window.

execute usp_CopyDatabase "IRGeneric", "IRTest",
"\\Stonehenge\SQLServerData\"

And received the following:

Processed 192 pages for database 'IRGeneric', file 'IRGeneric' on file 1.
Processed 2 pages for database 'IRGeneric', file 'IRGeneric_log' on file 1.
BACKUP DATABASE successfully processed 194 pages in 0.061 seconds (25.935
MB/sec).
Msg 3234, Level 16, State 2, Procedure usp_CopyDatabase, Line 30 Logical
file 'IRGeneric_Data' is not part of database 'IRTest'. Use RESTORE
FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Procedure usp_CopyDatabase, Line 30 RESTORE
DATABASE is terminating abnormally.

It certainly sounds like the backup happened, and if I go to the
\\Stonehenge\SQLServerData\ directory there is a file IRGeneric.BAK.

It would seem that restoring the backup file to a different file name is
causing an issue?

One obvious question, how do I compute the line number referenced?  Is that
"line 30 from the top of the file" or line 30 from the procedure
declaration?  My uneducated guess is from the function declaration line.

After that, any clue on what the failure is actually saying.

Thanks, 


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 James
Barash
Sent: Thursday, March 27, 2008 10:14 AM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Copy a database using Elizabeth's
suggestionplusparameters

John:
Try this:

Create PROCEDURE [dbo].[usp_CopyDatabase]
	
(
	@strDBNameFrom varchar(100),
	@strDBNameTo varchar(100),
	@strDBDir  varchar(255)
)
AS
BEGIN
	SET NOCOUNT ON;
declare @FileName varchar(255),
@MDFName varchar(255),
@NDFName varchar(255),
@strDBNameData varchar(255),
@strDBNameLog varchar(255)

Select @FileName = @strDBDir + @strDBNameFrom + '.bak'

BACKUP DATABASE @strDBNameFrom  TO DISK = @FileName Select @FileName =
@strDBDir + @strDBNameFrom + '.bak'
RESTORE FILELISTONLY  FROM DISK = @FileName

Select @FileName = @strDBDir + @strDBNameFrom + '.bak'
Select @MDFName = @strDBDir + @strDBNameTo + '.mdf'
Select @NDFName = @strDBDir + @strDBNameTo + '.ldf'
Select @strDBNameData = @strDBNameFrom + '_Data'
Select @strDBNameLog = @strDBNameFrom + '_Log'


RESTORE DATABASE @strDBNameTo 
   FROM DISK = @FileName
   WITH MOVE @strDBNameData  TO  @MDFName,
   MOVE @strDBNameLog TO @NDFName

END

It should do exactly what you need.

James Barash
 

-----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 9: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

_______________________________________________
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

_______________________________________________
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

_______________________________________________
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




More information about the dba-SQLServer mailing list