SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Restoring a database with Transact-SQL
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Chad Saar
Starting Member

3 Posts

Posted - 09/07/2006 :  16:41:14  Show Profile  Visit Chad Saar's Homepage
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  Show Profile
RESTORE FILELISTONLY will tell you the logical file names from a given backup.



-ec
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36834 Posts

Posted - 09/07/2006 :  16:58:23  Show Profile  Visit tkizer's Homepage
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
Go to Top of Page

Chad Saar
Starting Member

3 Posts

Posted - 09/07/2006 :  17:06:48  Show Profile  Visit Chad Saar's Homepage
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36834 Posts

Posted - 09/07/2006 :  17:11:25  Show Profile  Visit tkizer's Homepage
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
Go to Top of Page

Chad Saar
Starting Member

3 Posts

Posted - 09/07/2006 :  17:50:17  Show Profile  Visit Chad Saar's Homepage
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.
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 09/07/2006 :  17:55:23  Show Profile
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36834 Posts

Posted - 09/07/2006 :  17:57:19  Show Profile  Visit tkizer's Homepage
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36834 Posts

Posted - 09/07/2006 :  18:13:50  Show Profile  Visit tkizer's Homepage
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/07/2006 :  18:20:52  Show Profile
You can also look at the way the script in this topic does it, and adapt it to your needs.

Create Restore Command from DB Backup File:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71665

CODO ERGO SUM
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000