April 17, 2018 at 8:36 am
Hi All,
I have recently come across a new error with little info about why this is happening. I am currently copying and moving the mdf and log file from one server to another. I was able to do this with several smaller sized databases. I would zip the two files, and copy/paste the compressed file to the new server. It's a little time consuming, but it worked.
Now I am ready to move my 2 large databases, but I am getting an error message. The target drive has 1 TB of space and the compressed file is about 21 gb. When I try to copy/paste, I get the following error message:
Error copying file to folder
Unspecified error
Any thoughts on what I am doing wrong or is there a more efficient way of doing this?
Thank you for you time and help
Edited Note: I am moving a DB from SQL 2008 to SQL 2017
April 17, 2018 at 9:03 am
Hmm... are you using a special program to copy it? It might just be disliking the 21GB size.
April 17, 2018 at 9:18 am
I'm not sure how large your large databases are, but would you consider trying a native backup & restore instead? You could stripe the backups across multiple files and then robocopy them over, or something.
April 17, 2018 at 10:13 am
I believe it has to do with the size of the file. Does anyone know if a dropbox would work for this?
April 17, 2018 at 5:07 pm
skaggs.andrew - Tuesday, April 17, 2018 10:13 AMI believe it has to do with the size of the file. Does anyone know if a dropbox would work for this?
Using Dropbox is not safe for the database migration.
April 17, 2018 at 6:15 pm
Evgeny Garaev - Tuesday, April 17, 2018 5:07 PMskaggs.andrew - Tuesday, April 17, 2018 10:13 AMI believe it has to do with the size of the file. Does anyone know if a dropbox would work for this?Using Dropbox is not safe for the database migration.
Thank you for that. Any suggestions of what to use? FTP?
April 18, 2018 at 12:03 am
This was removed by the editor as SPAM
April 18, 2018 at 1:57 am
Evgeny Garaev - Tuesday, April 17, 2018 5:07 PM/quote]
Thank you for that. Any suggestions of what to use? FTP?
You could try RoboCopy.
April 18, 2018 at 12:26 pm
Perhaps there is an issue with zipping a file that big. I would try backing up (using the built-in compression) and then copying that file over to the other server and restoring. Also, try drag and drop to avoid putting something that big in the clipboard. Many people don't know you can right-click drag and drop to ensure you are moving rather than copying.
You'll have to script out the database and run the script on the target server before you do the restore, and you'll want to SET COMPATIBLE on the target server to take advantage of the 2017 features.
April 19, 2018 at 9:34 am
The Copy Database Wizard moves or copies databases and certain server objects easily from one instance of SQL Server to another instance, with no server downtime.
Formaciones para ingenieros industriales - VITC
April 23, 2018 at 4:56 am
skaggs.andrew - Tuesday, April 17, 2018 6:15 PMEvgeny Garaev - Tuesday, April 17, 2018 5:07 PM/quote]
Thank you for that. Any suggestions of what to use? FTP?
You could try RoboCopy.
This is exactly what I would use, its more resilient in the event of a failure which could be for numerous reasons like a temporary network issue. You can even generate the robocopy.exe calls from your SQL server like so, note the use of <> in the code to indicate what you can change. Obviously you could change your requirements in terms of locations etc.
use master
IF OBJECT_ID('fnGetFileName') IS NOT NULL
DROP FUNCTION fnGetFileName
GO
Create FUNCTION fnGetFileName
(
@fullpath nvarchar(260)
)
RETURNS nvarchar(260)
AS
BEGIN
IF(CHARINDEX('\', @fullpath) > 0)
SELECT @fullpath = LTRIM(RTRIM(RIGHT(@fullpath, CHARINDEX('\', REVERSE(@fullpath)) -1)))
RETURN @fullpath
END
GO
WITH CTE_metadata AS (
SELECT CASE WHEN CHARINDEX('\', [physical_name]) > 0 THEN substring(physical_name,1,charindex(reverse(substring(reverse(physical_name),1,charindex('\',reverse(physical_name)) ) ),physical_name) ) ELSE physical_name END AS SourcePath,
dbo.fnGetFileName(physical_name) AS FileName
FROM sys.master_files
WHERE DB_NAME(database_id) IN (<List Your DB's Here>)
)
SELECT 'START robocopy '
+ SourcePath
+ CASE WHEN [FileName] like '%LOG%' THEN ' \\<Destination server>\<Destination Drive>$\MSSQL\LOGS' ELSE ' \\<Destination server>\<Destination Drive>$\MSSQL\DATA' END
+' ' + LTRIM(RTRIM([FileName]))
+' /R:3 /log:?:\robocopy\log\'
+ [FileName] + '.log'
FROM CTE_metadata
MCITP SQL 2005, MCSA SQL 2012
April 24, 2018 at 10:35 pm
Del Lee - Wednesday, April 18, 2018 12:26 PMPerhaps there is an issue with zipping a file that big. I would try backing up (using the built-in compression) and then copying that file over to the other server and restoring. Also, try drag and drop to avoid putting something that big in the clipboard. Many people don't know you can right-click drag and drop to ensure you are moving rather than copying.You'll have to script out the database and run the script on the target server before you do the restore, and you'll want to SET COMPATIBLE on the target server to take advantage of the 2017 features.
+1There is a limit on how big a file can be zipped even when using 7zip, however this can be avoided by zipping into several files.
...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply