| Author |
Topic |
|
hle
Starting Member
7 Posts |
 Posted - 2009-02-17 : 04:18:33
|
| Hello,I need to copy a database from SQL 2000 server to SQL 2005 server. I don't have access to the SQL 2000 server, so doing a backup isn't an option. According to the "SQL Server Configuration Manager" every service is running, including SQL Server (Yes!) and SQL Server Agent.I am the owner of the database to copy on the source server, and I'm logging in to the source server using SQL Server Authentication, and to the destination server (my own workstation running the full version of SQL Server 2005) using Windows Authentication.When the wizard comes to the last step (Execute SQL Server Agent Job), it stops with the error "The job failed. Check the event log on the destination server for details. (Copy Database Wizard)". What am I doing wrong??? |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-02-17 : 04:32:22
|
| If you have access to the source server then you should have access to any backup files the 2000 db has created (assuming that backups are already running on a schedule). Search the C:\ drive for *.bak files. The easiest method would be to copy the most recent *.bak file to the 2005 server and restore from that backup file. If a restore from the most recent full backup is not current enough then you will need to script the backup to use *.bak and *.trn files and ensure the SQL service account has permissions to the folder on 2000 server.Can you confirm if you have found any *.bak files on the 2000 server? |
 |
|
|
hle
Starting Member
7 Posts |
Posted - 2009-02-17 : 06:44:32
|
| I can find the server (with the source database) doing a search, but all folders at that computer have private looking names and I'm not able to open any of them. I don't see any backup, system or data folder. So, no, I havn't found any *.bak file. |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2009-02-17 : 09:52:46
|
| What do you mean you dont have access to that server, even from SSMS you can't connect or you don't have the right to connect |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-02-17 : 10:53:22
|
| Sorry can you clarify your permissions?Can you connect to the SQL Server 2000 through your SQL Server 2005 and run a query e.g. "select top 10 * from YourTable" ?Can you view if any backups are running on a schedule on the 2000 database? - look at: Management \ Maintenance Plans or \Legacy. Also look under SQL Server Agent\JobsWhat are your windows permissions on the 2000 server. Can you connect to any of the windows folders on the server? Can you setup a mapped drive to the server's C:\ drive, eg \\IP_address\C$ |
|
|
|
hle
Starting Member
7 Posts |
Posted - 2009-02-18 : 05:19:08
|
| Looks like I'm new to servers too! Using SSMS I can access MY database, to which I can create tables and logins, select, update and delete data.But as far as I understand (I might be wrong, and I hope I am!:) a backup of a databse ends up somewhere on the server. And the only way for me to get that backup (*.bak-file), copy it to my computer, is to open that folder with the Windows Explorer. And the problem is that I don't seem to have any privileges on that machine, I can see the folders, but not open them. I do see a "node" in SSMS named "Backup Devices", but my database isn't listed (and if I try to add it, it's still tied to a Windows filesystem path that I cant access, ending with ...\MSSQL\BACKUP\mydb.bak). |
|
|
|
hle
Starting Member
7 Posts |
Posted - 2009-02-18 : 05:22:20
|
| What I mean by "...ends up somewhere on the server" is that I can't choose to save the backup on a remote client computer. |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-02-18 : 05:46:34
|
| First on new machine try to map to the remote server's C:\ drive. In Windows Explorer choose: Tools \map network drive, in folder textbox enter: \\xxx.xxx.xxx.xxx\C$where xxx is the IP address of your remote server. If you have rights on server you can now access the remote server's C:\ drive. So just copy the backup file to the new machine. (If no backups exist right-click on database choose - Tasks\Backup to create a new one)If you can not map a new drive you can script a new backup to new machine:BACKUP DATABASE [MyDatabase] TO DISK = N'\\xxx.xxx.xxx.xxx\C$\temp\test.bak' WITH NOFORMAT, NOINIT, NAME = N'MyDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10To do this you will need to make sure the logon which SQL Server agent is running under has access to new machine's C:\drive (easiest is to make it a local administrator or run the service as a network administrator) |
|
|
|
hle
Starting Member
7 Posts |
Posted - 2009-02-18 : 09:17:50
|
| I've tried to map the root of C, I'm asked to enter username/password different from logging in to the SQL server. So, I'll go ahead and try the backup script.But what happened to my oroginal question? Why do the copy job fail? |
|
|
|
|
Copy Database Wizard