[web] SQL Server BACKUP RESTORE
Author
I've run into a situation where I need to transfer a database (SQL Server 2000) from one system to another. However I've run into a problem - first I have nothing more then command line access to the target machine, meaning everything is done via TSQL. The source system isn't much better - it's running MSDN2000, which basically leaves two methods of interacting with the database - command line or connecting with Access XP.
The big issue is that because the systems are running different versions of SQL Server, the database file pathnames are different. Even using WITH REPLACE this is preventing the RESTORE command from taking a backup made on the first machine and restoring it to a blank database on the second (Device activation error. The physical file name 'X' may be incorrect)
So my question is, can I either improve my BACKUP procedure to remove the normal filename safe guards (creating a backup that can be restored to any generic server without issue), or can I tweak the restore command to automatically handle the conversion. I've tried using the MOVE argument to manually specify filenames, but either due to a limited understanding of MOVE or another issue, it failed (Logical file 'X' is not part of database 'X')
You might try installing SQL Server Enterprise Manager (I think it comes free with the MSDE). If you can successfully connect to both databases through it (I think you should be able to, even with differing versions), you can do a direct copy from one server to the other. Unfortunately, I'm not sure what to expect when copying from differing versions of SQL server - but if you aren't using any extravigant features of 2000, I'd think that you should be able to copy over without a problem.
Another suggestion for changing the filepaths, again with the Enterprise Manager, if you right click on your database and go to properties, there's a tab for DATA Files. You can't change the path once the db has been created, but I'm thinking you could make a temporary copy of the database using the same filepath as the second server path on the first server, and then attempt to do your backup and restore to the second machine.
Author
(I started this reply about 30 minutes ago, but someone came in and we had a mini-meeting)
Can't install Manager. All I can do is upload files and execute SQL commands on the server. Also, the source system isn't even networked (I used a USB keychain to backup and copy the database to an networked dumb terminal from where I'm executing the commands).
Anyway, I had almost exhausted my options before I made the OP, so it's not surprising that I would find the solution right after that. The MOVE idea was the right thing to do, the only problem is that I hadn't realized that the internal names where a bit off from what I'd expect - instead of X and X_log, I had X_dat and X_log. Basically I succeed with:
executed this command
BACKUP DATABASE x TO DISK = 'c:\x.bak'
(x.bak is then transfered to server)
executed this query
RESTORE FILELISTONLY FROM DISK = 'c:\x.bak'
using the two filenames (x_dat/x_log) given by this query I executed this restore command
RESTORE DATABASE x from disk = 'c:\x.bak' with move 'x_dat' TO 'c:\database\x.mdf', MOVE 'x_log' to 'c:\database\x.ldf'
Can't install Manager. All I can do is upload files and execute SQL commands on the server. Also, the source system isn't even networked (I used a USB keychain to backup and copy the database to an networked dumb terminal from where I'm executing the commands).
Anyway, I had almost exhausted my options before I made the OP, so it's not surprising that I would find the solution right after that. The MOVE idea was the right thing to do, the only problem is that I hadn't realized that the internal names where a bit off from what I'd expect - instead of X and X_log, I had X_dat and X_log. Basically I succeed with:
executed this command
BACKUP DATABASE x TO DISK = 'c:\x.bak'
(x.bak is then transfered to server)
executed this query
RESTORE FILELISTONLY FROM DISK = 'c:\x.bak'
using the two filenames (x_dat/x_log) given by this query I executed this restore command
RESTORE DATABASE x from disk = 'c:\x.bak' with move 'x_dat' TO 'c:\database\x.mdf', MOVE 'x_log' to 'c:\database\x.ldf'
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
