Printable View
I'm setting upp new machine with Win Srvr. 2003 and SQL server.
My old Win2K server is running SQL 7 with SP4, I want to move one DB and associated SP from SQL 7 to SQL 2005 but can't succeed :(
While on SQL 2005 Srv Managemt, I tried to connect to the SQL 7 and Copy Database (and assiciated SP). I did not take DB offline, but this shoudl not matter...
I get error on last Action (Execute SQL Server Agent Job), "The job failed".
PS. Will the SP from SQL 7 work on SQL 2005 ?
Did you use Copy database wizard?
It sounds like permission issue on SQL Agent service account, do you use same account on both servers?
Another way will be to backup database in 7.0 and restore it in SQL2K5.
can't you just take backup and restore it in 2005?
make sure you set the compatibility to 70.. or else any pre-ANSI 92 standard syntax in stored procs and views will not work.
Well, I took full backup of my DB in SQL 7, tried to restore on 2005 but I get error:
Restore failed for Server 'WEBSERVER3'. (Microsoft.SqlServer.Smo)
System.Data.SqlClient.SqlError: Directory lookup for the file "D:\SQL Data\MSSQL\Data\minishop.mdf" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo)
I tried to do use SQL 2005 Wizard, connect to my SQL 7, make backup, all steps was OK but the last one :( "Execute SQL Agent Server Job Error"
That is because the directory structure for data files is different in two servers, you can use WITH MOVE option in RESTORE DATABASE command to point to new location.
OK, hmm sorry I'm so advanced user. When copying with the wizzard I can't choose any parameters, when restoring I can go the script, but what's the syntax ?
It looks like this right now:
RESTORE DATABASE [minishop] FROM DISK = N'E:\minishop_db_200808280532.BAK' WITH FILE = 1, NOUNLOAD, STATS = 10
GO
I need to get moved also all SP for thsi DB. IS it included in the backup? Thx.
RESTORE DATABASE [minishop] FROM DISK = N'E:\minishop_db_200808280532.BAK' WITH
move 'data_file' to 'new data file path',
move 'log_file' to 'new log file path' ...
Books online has sample code.
I saw the syntax... in my case the SQL 7 DB is on another server, I mapped this as X: but still no luck.
RESTORE DATABASE [minishop] FROM DISK = N'E:\minishop_db_200808280532.BAK'
WITH FILE = 2, NOUNLOAD, STATS = 10;
WITH MOVE "X:\minishop.mdf" to "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\minishop.mdf";
WITH MOVE "X:\minishop_log.ldf" to "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\minishop.ldf";
GO
ERROR:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'X:\minishop.mdf'.
1. you need only one 'with' for all options.
2. data_file and log_file should be db's logical file name, you can find that with 'restore filelistonly'.
If RESTORE syntax is not working, then you can do this from Management Studio as well. You need to go to Options and specify new locations.
Options for what? The SQL 7 DB is running on different server in my network, and it's online. The SQL 2005 DB is on the new server. I could specify all locations in the wizzard, but still no luck.
Did you fix your statement like following?
RESTORE DATABASE [minishop] FROM DISK = N'E:\minishop_db_200808280532.BAK'
WITH FILE = 2, NOUNLOAD, STATS = 10, MOVE 'minishop' to 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\minishop.mdf',
MOVE 'minishop_log' to 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\minishop.ldf'
GO
This results in:
Msg 3234, Level 16, State 2, Line 1
Logical file 'minishop' is not part of database 'minishop'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
As I said above, you have to find out logical file names with following command and replace them:
restore filelistonly from DISK = N'E:\minishop_db_200808280532.BAK'