Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
 Posted - 2003-01-02 : 07:42:31
|
Hi all, and here's wishing for PEACE and PROSPERITY for all on 2003.On to the problem. I need to create a end of year database for the accounting types in my company. I have a full backup (38gb) created for this purpose, and am trying to restore across the network, as the target server has 65 GB free - not enough for backup and database.Setup: Source server = Win 2K ASE SP 3. SQL 7 SP2.Target server = Win 2K ASE SP 3. SQL 7 SP2.The error message I get is : quote:
Server: Msg 3158, Level 16, State1, Line 1Could not create one or more files. Consider using the WITH MOVE option to identify valid locations.
This after running for approx. 3 minutes.The script I'm running is: restore --filelistonly --verifyonly database XXXfrom disk = '\\at002\incoming$\plce_eoy.bak'with replace,stats=1,move 'datafile' to 'e:\Data\XXXdata.mdf',move 'logfile' to 'e:\Log\XXXlog.ldf',move 'documentdata' to 'e:\data\XXXDocumentData.ndf'go As you can infer from the above, I've run filelistonly, and verifyonly - both succeed without problems, as would be expected.Now the restore script is standard - it's used on serveral server's all over the show, and I don't think I've made any typo's etc. SQL run's from a domain account, and has authority to read the source (hence the working of the verify etc.), and authority to create files. I have tried different directory names, file names, database name ... no joy. What am I missing here, what's the next step ?TIA*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2003-01-02 : 11:45:24
|
| The only time I've ever gotten this messae is when my destiniation directories don't exist. My only suggestion is to verify that the E:\data\ and E:\log\ directories have already been created. HTHJeff BanschbachConsultant, MCDBA |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-01-02 : 12:53:07
|
| I'm not sure that you can restore across the network AND use the with move option. You only need to use the with move option when you need to move the files because you want them in another location or because you don't have those drive letters and/or directories. How about you run the script without the with move option? Of course, you will have to have an E drive plus the corresponding directories. The only other option would be to free up some disk space on your target server or add some disk space so that you can run the restore locally. |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-01-02 : 13:02:37
|
| On SQL2000 (don't have 7 anywhere at the moment) it works fine. I even tested it using a different number of files in the backup than the database to be replaced and it still worked no problems. Your syntax seems fine. Have you tried copying the backup across to the server and restoring it locally ? HTHJasper Smith |
|
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2003-01-02 : 13:17:01
|
| I think, not sure, that you should be able to use the WITH MOVE option, regardless of whether or not it is across the network. Try a mapped drive and see if that helps, although I doubt it.***************************************Death must absolutely come to enemies of the code! |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-01-03 : 01:01:36
|
| With move does work across network, we've used it many times before. The only notable change has been the move from NT4 SP5 to Win 2K SP3, about 3 weeks ago.Jeff, the directories do exist, and I've tried different directory names. The data and log directories as specified are the same setup, drive and directory wise, as the source (i.e e:\data, e:\log).Jasper, unfortunately there is only 65GB available on the server, and the backup is 38 GB - the db around 45 GB or so ... I just don't have enough space ( I would probably need around90 to 100, for safety's sake).If I can't find anything, I'm going to restore the backup to the original db over the weekend - I have a vague suspicion that there's something wrong with the backup, and this weekend is the last time I can re-create the backup if I need to. I haven't got any other ideas, or any other servers to try against, due to space constraints.*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here!Edited by - Wanderer on 01/03/2003 01:04:11Edited by - Wanderer on 01/03/2003 01:06:15 |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-01-03 : 06:23:56
|
| Well, I've been able to restore the backup on the server where it was created, to a different name. Kinda frustrating, since there's no reason it shouldn't have been able to do the x-network restore. Now I'll have to try and find a server with enough space for backup and db, since it can't stay where it is now.ciao*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-01-03 : 17:53:17
|
You could create a compressed folder (using the buit in windows 2000 compression) on the destination server and copy the backup to that - you *might* just have enough room  There is no problem using compressed folders for backup files - just not for the live database files. It seems odd that it won't restore as your command looks perfectly valid.HTHJasper Smith |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-01-06 : 05:26:45
|
| Well, it wasn't solved, but I got around it.I eventually managed to get enough space on the TARGET server to copy the backup across, and have space to restore. I got the same D$MN error. Having been able to get space on the SOURCE server, and time on the weekend when I could take the db "offline" (fortunately we're 24x6, not 24x7), I restored the back on the SOURCE server - no problem. Than I detached, manually copied the files across to the target server, sp_attach'd, and bob's your uncle...Has anyone got an idea as to why a backup could become "server-specific" ? While it looks like this crisis is averted, the behaviour of SQL here concerns me !!!Ciao*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
|
|
|
|
|
Restore fails ... msg 3158