Copy Database Between Servers

Hi All,
I have a need to copy a database from the production server to a new test
server. I tried using the Copy Database Wizard, but it fails because there
are active connections to the production database. Unfortunately, I cannot
close those connections because it is in use 24/7, and the next maintenance
cycle is the end of April.
I also tried backing up the database to a disk device, copying the backup to
the new test server, and restoring from the disk device to a new database
but it fails, saying "device activation error" and something to do with "Use
WITH MOVE to identify a valid location", so I tried running the restore
command from the query analyser, specifiying the WITH MOVE for the 2 files
but it gives the same error.
Any ideas on what I'm missing?
Regards...Andrew
> I also tried backing up the database to a disk device, copying the backup
to
> the new test server, and restoring from the disk device to a new database
> but it fails, saying "device activation error" and something to do with
"Use
> WITH MOVE to identify a valid location", so I tried running the restore
> command from the query analyser, specifiying the WITH MOVE for the 2 files
> but it gives the same error.
Backup / Restore should work. Check again whether your Restore syntax is
correct and whether SQL Server can realy access the folder(s) specified.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||> I also tried backing up the database to a disk device, copying the backup
to
> the new test server, and restoring from the disk device to a new database
> but it fails, saying "device activation error" and something to do with
"Use
> WITH MOVE to identify a valid location", so I tried running the restore
> command from the query analyser, specifiying the WITH MOVE for the 2 files
> but it gives the same error.
Backup / Restore should work. Check again whether your Restore syntax is
correct and whether SQL Server can realy access the folder(s) specified.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||SQL command is as follows:
RESTORE FILELISTONLY
FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\PayrollASP_Syngentatest.bak'
RESTORE DATABASE PayrollASP_Syngentatest
FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\PayrollASP_Syngentatest.bak'
WITH RECOVERY,
MOVE 'PayrollASP_Data' TO 'C:\DataFiles\Microsoft SQL
Server\MSSQL\data\PayrollASP_Syngentatest.mdf',
MOVE 'PayrollASP_Log' TO 'C:\DataFiles\Microsoft SQL
Server\MSSQL\data\PayrollASP_Syngentatest_log.ldf'
Results are as follows:
LogicalName
PhysicalName
Type FileGroupName
Size MaxSize
---- --
----
-- -- ---
-- -- --
PayrollASP_Data
D:\DataFiles\Microsoft SQL Server\MSSQL\data\PayrollASP_Syngentatest.mdf
D PRIMARY
201326592 35184372080640
PayrollASP_Log
D:\DataFiles\Microsoft SQL Server\MSSQL\data\PayrollASP_Syngentatest_log.ldf
L NULL
121372672 35184372080640
(2 row(s) affected)
Server: Msg 5105, Level 16, State 2, Line 5
Device activation error. The physical file name 'C:\DataFiles\Microsoft SQL
Server\MSSQL\data\PayrollASP_Syngentatest.mdf' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 5
File 'PayrollASP_Data' cannot be restored to 'C:\DataFiles\Microsoft SQL
Server\MSSQL\data\PayrollASP_Syngentatest.mdf'. Use WITH MOVE to identify a
valid location for the file.
Server: Msg 5105, Level 16, State 1, Line 5
Device activation error. The physical file name 'C:\DataFiles\Microsoft SQL
Server\MSSQL\data\PayrollASP_Syngentatest_log.ldf' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 5
File 'PayrollASP_Log' cannot be restored to 'C:\DataFiles\Microsoft SQL
Server\MSSQL\data\PayrollASP_Syngentatest_log.ldf' . Use WITH MOVE to
identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.
I even tried creating an empty database of the correct name first so that
the mdf and ldf files existed, then included the REPLACE keyword. No go I'm
afraid.
The main thing is that the DB's on the production server are on drive D:,
but the test server doesn't have a D: drive, hence the need to use the MOVE
keywords.
Thanks in advance for any help you may give.
Regards...Andrew
|||Hi Andrew
Have you verified that the folder where you try to place the new data and
log files do exist? SQL can't create the folder on it's own, so it have to
exist before the RESTORE command can put the files there.
Regards
Steen
Andrew Hayes wrote:
> SQL command is as follows:
> RESTORE FILELISTONLY
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\PayrollASP_Syngentatest.bak'
> RESTORE DATABASE PayrollASP_Syngentatest
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\PayrollASP_Syngentatest.bak'
> WITH RECOVERY,
> MOVE 'PayrollASP_Data' TO 'C:\DataFiles\Microsoft SQL
> Server\MSSQL\data\PayrollASP_Syngentatest.mdf',
> MOVE 'PayrollASP_Log' TO 'C:\DataFiles\Microsoft SQL
> Server\MSSQL\data\PayrollASP_Syngentatest_log.ldf'
> Results are as follows:
> LogicalName
> PhysicalName
> Type FileGroupName
> Size MaxSize
> ----
--
> ----
> --
> ----
--
> ----
--
> ----
--
> -- --
> ----
--
> -- -- --
> PayrollASP_Data
> D:\DataFiles\Microsoft SQL
> Server\MSSQL\data\PayrollASP_Syngentatest.mdf
> D PRIMARY
> 201326592 35184372080640
> PayrollASP_Log
> D:\DataFiles\Microsoft SQL
> Server\MSSQL\data\PayrollASP_Syngentatest_log.ldf L
> NULL 121372672 35184372080640
> (2 row(s) affected)
> Server: Msg 5105, Level 16, State 2, Line 5
> Device activation error. The physical file name
> 'C:\DataFiles\Microsoft SQL
> Server\MSSQL\data\PayrollASP_Syngentatest.mdf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 5
> File 'PayrollASP_Data' cannot be restored to 'C:\DataFiles\Microsoft
> SQL Server\MSSQL\data\PayrollASP_Syngentatest.mdf'. Use WITH MOVE to
> identify a valid location for the file.
> Server: Msg 5105, Level 16, State 1, Line 5
> Device activation error. The physical file name
> 'C:\DataFiles\Microsoft SQL
> Server\MSSQL\data\PayrollASP_Syngentatest_log.ldf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 5
> File 'PayrollASP_Log' cannot be restored to 'C:\DataFiles\Microsoft
> SQL Server\MSSQL\data\PayrollASP_Syngentatest_log.ldf' . Use WITH MOVE
> to identify a valid location for the file.
> Server: Msg 3013, Level 16, State 1, Line 5
> RESTORE DATABASE is terminating abnormally.
> I even tried creating an empty database of the correct name first so
> that the mdf and ldf files existed, then included the REPLACE
> keyword. No go I'm afraid.
> The main thing is that the DB's on the production server are on drive
> D:, but the test server doesn't have a D: drive, hence the need to
> use the MOVE keywords.
> Thanks in advance for any help you may give.
> Regards...Andrew
|||Yes. The folder exists, but since I didn't have time to mess about trying to
figure out why SQL Server wouldn't restore from a backup to a different
server with a different folder structure, I just generated the creation
scripts, ran those, and exported the data.
Thanks for the help though Steen. Not your fault SQL Server is finiky.
It would be nice if SQL Server, when it reports "Device activation error.
The physical file name '...' may be incorrect." was a little more
forthcoming with why it may be incorrect.
Maybe, "the folder doesn't exist and I can't create it", or "a file of the
same name already exists (and either couldn't be overwritten because it was
read-only, or the [whoever] account used by this operation does not have the
appropriate access rights."
Maybe verbose error reporting is a feature of SQL Server 2005? One can but
hope.
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:ewQ2EHrNFHA.3220@.TK2MSFTNGP14.phx.gbl...
> Hi Andrew
> Have you verified that the folder where you try to place the new data and
> log files do exist? SQL can't create the folder on it's own, so it have to
> exist before the RESTORE command can put the files there.
> Regards
> Steen
> Andrew Hayes wrote:
> ----
> --
> ----
> --
> ----
> --
> ----
> --
> ----
> --
>
|||Hi Andrew
I agree that the SQL server error messages isn't always very clear - but
isn't that a problem in every software program...;-).
/Steen
Andrew Hayes wrote:[vbcol=seagreen]
> Yes. The folder exists, but since I didn't have time to mess about
> trying to figure out why SQL Server wouldn't restore from a backup to
> a different server with a different folder structure, I just
> generated the creation scripts, ran those, and exported the data.
> Thanks for the help though Steen. Not your fault SQL Server is finiky.
> It would be nice if SQL Server, when it reports "Device activation
> error. The physical file name '...' may be incorrect." was a little
> more forthcoming with why it may be incorrect.
> Maybe, "the folder doesn't exist and I can't create it", or "a file
> of the same name already exists (and either couldn't be overwritten
> because it was read-only, or the [whoever] account used by this
> operation does not have the appropriate access rights."
> Maybe verbose error reporting is a feature of SQL Server 2005? One
> can but hope.
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ewQ2EHrNFHA.3220@.TK2MSFTNGP14.phx.gbl...
-[vbcol=seagreen]
-[vbcol=seagreen]
-[vbcol=seagreen]
-[vbcol=seagreen]
-[vbcol=seagreen]