| Author |
 Topic  |
|
|
Chad Saar
Starting Member
3 Posts |
 Posted - 09/07/2006 : 16:41:14
|
I work with hundreds of sql database backups and need to restore them constantly. They come from all over the states from different servers that are configured differently.
I am using
RESTORE DATABASE FooBar
FROM DATA = 'c:\program files\microsoft s....\BACKUP\FooBar.bak'
The problem is the data files attached to the backup are constantly set to something different (exmaple: s:\sqldata\data\)
So I need to use WITH MOVE
My question is about WITH MOVE and the arguments required.
MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' ]
[ ,...n ]
I have no way of knowing what the 'logical_file_name_in_backup' is going to be but I know that SQL can figure it out on its own. (because it does not ask you, when you use Enterprise Manager to restore a database through the GUI, it just asks where you want the data files to go and what to call them)
This is what I am trying to do with the MOVE statement. I am trying to tell sql "Do not bother with the data information in the backup, put the data files HERE and name them THIS"
I hope that makes sense. Any help would be appreciated! |
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 09/07/2006 : 16:57:03
|
RESTORE FILELISTONLY will tell you the logical file names from a given backup.
-ec |
 |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 09/07/2006 : 16:58:23
|
You can use RESTORE FILELISTONLY to get the information that you need. This is what Enterprise Manager uses as well.
Tara Kizer |
Edited by - tkizer on 09/07/2006 16:58:51 |
|
|
|
Chad Saar
Starting Member
3 Posts |
Posted - 09/07/2006 : 17:06:48
|
| FILELISTONLY does not support FROM DISK or WITH MOVE though :( I need to tell it where to find the backup file and where to put the data files. |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 09/07/2006 : 17:11:25
|
It supports FROM DISK!
RESTORE FILELISTONLY SomeDatabase
FROM DISK = '...'
Using the result set from the above query, you then can choose what to put for the WITH MOVE option of the RESTORE DATABASE command.
Tara Kizer |
Edited by - tkizer on 09/07/2006 17:12:00 |
|
|
|
Chad Saar
Starting Member
3 Posts |
Posted - 09/07/2006 : 17:50:17
|
| I guess I am confused. I wanted to restore a database, when I run this it says The command(s) completed successfully. But the database is not there and it completes way to quick for a restore. I need to restore a database from a backup for the first time in a single query because I will not be there to do it, it will be automated. |
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 09/07/2006 : 17:55:23
|
quote:
Originally posted by Chad Saar
I guess I am confused. I wanted to restore a database, when I run this it says The command(s) completed successfully. But the database is not there and it completes way to quick for a restore. I need to restore a database from a backup for the first time in a single query because I will not be there to do it, it will be automated.
there is not a single command that will do this. You could run the RESTORE FILELISTONLY command to get the logical filenames, then you could run the RESTORE WITH MOVE command using the logical names from the first step.
This could effectively be a single command if you made it into a stored procedure.
-ec |
Edited by - eyechart on 09/07/2006 17:55:38 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 09/07/2006 : 17:57:19
|
quote:
Originally posted by Chad Saar
I guess I am confused. I wanted to restore a database, when I run this it says The command(s) completed successfully. But the database is not there and it completes way to quick for a restore. I need to restore a database from a backup for the first time in a single query because I will not be there to do it, it will be automated.
The command does not restore the database. You use the information that it returns to decide what to pass to the RESTORE DATABASE command. I'll post some sample code in a few.
Tara Kizer |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 09/07/2006 : 18:13:50
|
Here's an example:
CREATE TABLE ##Restore (LogicalName nvarchar(128), PhysicalName nvarchar(260), Type char(1), FileGroupName nvarchar(128), SizeB numeric(20, 0), MaxSizeB numeric(20, 0))
INSERT INTO ##Restore
EXEC('RESTORE FILELISTONLY FROM DISK = ''C:\MSSQL\BACKUP\Toolbox\Toolbox_20060713141727.BAK''')
DECLARE @LogicalNameData nvarchar(128), @LogicalNameLog nvarchar(128)
SELECT @LogicalNameData = LogicalName
FROM ##Restore
WHERE Type = 'D'
SELECT @LogicalNameLog = LogicalName
FROM ##Restore
WHERE Type = 'L'
DECLARE @SQL nvarchar(4000)
SET @SQL = 'RESTORE DATABASE SomeDatabaseName FROM DISK = ''C:\MSSQL\BACKUP\Toolbox\Toolbox_20060713141727.BAK'' WITH MOVE '''
SET @SQL = @SQL + @LogicalNameData + ''' TO ''C:\MSSQL\DATA\SomeDatabaseName_Data.MDF'', MOVE ''' + @LogicalNameLog + ''' TO '
SET @SQL = @SQL + '''C:\MSSQL\DATA\SomeDatabaseName_Log.LDF'''
--PRINT @SQL
EXEC(@SQL)
DROP TABLE ##Restore
The above code runs the RESTORE FILELISTONLY command against my backup file, puts the data into a temp table (have to use dynamic SQL for this), then retrieves the values that we want and uses those for the RESTORE DATABASE command (have to use dynamic SQL again). Run the PRINT statement to see the command that I built. Get that part right prior to running the EXEC (so comment out the EXEC until you have what you want).
The code can be automated so that no one has to be there when it runs.
Tara Kizer |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
|
| |
Topic |
|
Restoring a database with Transact-SQL
Forum Locked
