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

SUCCESS!

Thanks Asger, and thanks James.  I now have a SP that copies my template
database to a name of my choice. 

Woohoooo.


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

John,

You wrote:

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


You are close indeed... But your syntax should be:

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

- according to BOL ... and experience ;-)

Asger

-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af jwcolby
Sendt: 27. marts 2008 22:14
Til: 'Discussion concerning MS SQL Server'
Emne: 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-)