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 |
|
cblatchford
Starting Member
40 Posts |
 Posted - 2007-06-05 : 10:36:31
|
| Hi all,I'm running the below script on a current 2000 server trying to backup and restore all databases to a 2005 server:DECLARE @DB varchar(100), @Backup_location varchar(100), @cmd varchar(100)DECLARE ICursor CURSOR FOR SELECT name from sysdatabases where name not in ('master','msdb','model','tempdb')OPEN ICursorFetch Next From ICursor Into @DBWHILE(@@fetch_status = 0) BEGIN SET @Backup_Location = '\\LON-SQAPPS01\backup\'+@DB+'.BAK' SET @cmd = 'del '+@Backup_Location Backup Database @DB to disk = @Backup_Location with init Exec('[LON-SQAPPS01].master.dbo.sp_executesql N''restore database ' + @DB + ' from disk =''''' + @Backup_Location + ''''' WITH move ''''' + @DB + '_data' + ''''' to ''''G:\Data\' + @DB + '_data.mdf'''', move ''''' + @DB + '_log' + ''''' to ''''F:\Data\' + @DB + '_Log.ldf''''''') /*exec xp_cmdshell @cmd*/ Fetch Next From ICursor Into @DB ENDdeallocate ICursorThe backup part of the script works fine, but the restore keeps giving me the following error:Server: Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally.Server: Msg 3119, Level 16, State 1, Line 1Problems were identified while planning for the RESTORE statement. Previous messages provide details.Server: Msg 3156, Level 16, State 1, Line 1File 'AML_Log' cannot be restored to 'F:\Data\AML_Log.ldf'. Use WITH MOVE to identify a valid location for the file.Server: Msg 5184, Level 16, State 1, Line 1Cannot use file 'F:\Data\AML_Log.ldf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used.I'm specifying the "WITH MOVE" syntax in the script, but it still keeps throwing this up, any ideas?Thanks |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-05 : 10:39:51
|
| seehttp://www.nigelrivett.net/SQLAdmin/s_RestoreDatabase.htmlIt does a move for the data and logs getting the names from the backup file.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-05 : 11:35:34
|
| If it was me I wouldn't be using nested EXEC and sp_ExecuteSQL.Plus I would put the SQL into a variable first so that I could use a PRINT statement, or similar, so see what was happening.Perhaps a database name has a reserved character in it?Maybe the Logical Names of the database being backed up / restore do NOT end in "_data" / "_log" ?Not sure what this error means (but it may only be a cascade error)"Cannot use file 'F:\Data\AML_Log.ldf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used"Kristen |
|
|
|
cornall
Posting Yak Master
148 Posts |
Posted - 2007-06-05 : 11:38:36
|
| I know this may sound stupid but does the folder F:\Data exist? |
|
|
|
cblatchford
Starting Member
40 Posts |
Posted - 2007-06-05 : 12:25:10
|
| Thanks, yes the folders exist and i'm only testing it on a few databases, all of which end in _data (or where applicable); i'll try the script nr suggested...thanks for your responses thus far! |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-05 : 23:09:21
|
| Did you add f:\ drive as sql resource's dependency resource? |
|
|
|
rcavill
Starting Member
1 Post |
Posted - 2007-06-10 : 23:15:52
|
| Have a look at the following KB article.How to create databases or change disk file locations on a shared cluster drive on which SQL Server was not originally installedhttp://support.microsoft.com/default.aspx/kb/295732If might be that you need to add the F: drive to the dependency list of drives that SQL 2005 is able to use.------Robert* smile - its infectious * |
|
|
|
|
|
|
|
|
Restore WITH MOVE