Backup and restore to a different DB

Hello,
I am trying to get all the data from one DB and put it in another DB. I
want to do this via TSQL so that I can script it to run daily.
I have tried the following:
****************************************************************************
USE master
EXEC sp_addumpdevice 'disk', 'SMSBackup',
'F:\MSSQL\backup\SMSBackup.dat'
Backup Database SMS_S01 to SMSBackup
Restore filelistonly from SMSBackup
Restore Database Inventory from SMSBackup with Move 'SMSBackup' TO
'F:\MSSQL\Data\Inventory.mdf'
****************************************************************************
I get the following results:
****************************************************************************
(1 row(s) affected)
'Disk' device added.
Processed 24344 pages for database 'SMS_S01', file 'SMS_S01_Data' on
file 4.
Processed 1 pages for database 'SMS_S01', file 'SMS_S01_Log' on file 4.
BACKUP DATABASE successfully processed 24345 pages in 13.192 seconds
(15.117 MB/sec).
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 7
Logical file 'SMSBackup' is not part of database 'Inventory'. Use
RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.
****************************************************************************
What am I doing wrong here? Should I be trying to use a file rather
than disk device?
Cheers-- Backup Database
BACKUP DATABASE DatabaseName TO DISK = 'D:\DatabaseName.bak'
GO
-- Show File info
RESTORE FILELISTONLY FROM DISK = 'D:\DatabaseName.bak'
GO
-- Restore the files for DatabaseName2 new db
RESTORE DATABASE DatabaseName2
FROM DISK = 'D:\DatabaseName.bak' -- backuped file
WITH RECOVERY,
MOVE 'DatabaseName_Data' TO 'D:\SQL Server
Data\MSSQL\Data\DatabaseName2_data.mdf', -- new file
MOVE 'DatabaseName_Log' TO 'D:\SQL Server
Data\MSSQL\Data\DatabaseName2_log.ldf' -- new file
GO|||You need to provide the logical filename that you got from the output of
"RESTORE FILELISTONLY" +>
RESTORE FILELISTONLY
FROM DISK = 'F:\backupfilename.bak'
as input of
RESTORE DATABASE DB_NAME_TO_BE_RESTORED
FROM DISK = 'F:\backupfilename.bak'
WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
STATS = 1, REPLACE
GO
Thanks,
Sree
"dishan@.gmail.com" wrote:
> -- Backup Database
> BACKUP DATABASE DatabaseName TO DISK = 'D:\DatabaseName.bak'
> GO
> -- Show File info
> RESTORE FILELISTONLY FROM DISK = 'D:\DatabaseName.bak'
> GO
> -- Restore the files for DatabaseName2 new db
> RESTORE DATABASE DatabaseName2
> FROM DISK = 'D:\DatabaseName.bak' -- backuped file
> WITH RECOVERY,
> MOVE 'DatabaseName_Data' TO 'D:\SQL Server
> Data\MSSQL\Data\DatabaseName2_data.mdf', -- new file
> MOVE 'DatabaseName_Log' TO 'D:\SQL Server
> Data\MSSQL\Data\DatabaseName2_log.ldf' -- new file
> GO
>|||Sreejith G wrote:
> You need to provide the logical filename that you got from the output of
> "RESTORE FILELISTONLY" +>
> RESTORE FILELISTONLY
> FROM DISK = 'F:\backupfilename.bak'
>
> as input of
>
> RESTORE DATABASE DB_NAME_TO_BE_RESTORED
> FROM DISK = 'F:\backupfilename.bak'
> WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
> MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
> STATS = 1, REPLACE
> GO
>
> Thanks,
> Sree
Thanks. Does that mean that it cannot be done as an automated script,
or is there a way to pass the output of the RESTORE FILELISTONLY to the
RESTORE DATABASE command (of does your command do that, but I just
can't tell)?|||There should be no proble with diskdevice.
Do,
restore database inventory
from smsbackup
with move
<logical name for data file> to <physical path>,
move
<logical name for log file> to <physical path>
Regards
Amish Shah|||> Thanks. Does that mean that it cannot be done as an automated script,
> or is there a way to pass the output of the RESTORE FILELISTONLY to the
> RESTORE DATABASE command
Not sure exactly what you are looking for, but I have some code at
http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp that might be useful, with some
changes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"cqmman" <cqmman@.yahoo.co.uk> wrote in message
news:1139917015.033030.137950@.g44g2000cwa.googlegroups.com...
> Sreejith G wrote:
>> You need to provide the logical filename that you got from the output of
>> "RESTORE FILELISTONLY" +>
>> RESTORE FILELISTONLY
>> FROM DISK = 'F:\backupfilename.bak'
>>
>> as input of
>>
>> RESTORE DATABASE DB_NAME_TO_BE_RESTORED
>> FROM DISK = 'F:\backupfilename.bak'
>> WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
>> MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
>> STATS = 1, REPLACE
>> GO
>>
>> Thanks,
>> Sree
> Thanks. Does that mean that it cannot be done as an automated script,
> or is there a way to pass the output of the RESTORE FILELISTONLY to the
> RESTORE DATABASE command (of does your command do that, but I just
> can't tell)?
>|||Tibor Karaszi wrote:
> Not sure exactly what you are looking for, but I have some code at
> http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp that might be useful, with some
> changes.
>
Thanks I will take a look.
Bascially, I have a database, and I want to perform queries on it from
remote workstations. I don't want to be querying the original DB, so
want a DB which is effectively a copy. I want to make this copy (or
update it) automatically (daily), and thought that the best way to do
this would be a scheduled TSQL script.. So I am trying to get a TSQL
script that will take a copy of the DB, and put that data into a
different DB.
Cheers