If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Restore Database from one server to another

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Location: South Africa
Posts: 71
Restore Database from one server to another

Hello All!

I would like you to help me with syntax to restore the database
Here is my situation I haver ServerA and ServerB. ServerA has DB called TEST and is being backed up everyday on D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Test.Bak, now I need to restore this backup on ServerB's D-drive. Help me with the T-SQL syntax.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Mar 2004
Location: UK
Posts: 26
Why TSQL?

Why do you need to do it in T-SQL?
Can't this be done with Enterprise manager, possibly setting up database maintenance plans using SQL Server Agent?
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Location: South Africa
Posts: 71
It is fine with me, give me the steps to do that because i didn't see on Enterprise Manager where to backup from another server
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Location: India
Posts: 523
The best way to do this is to copy the backup from the server A to server B
and use 'Restore database' with move option.
for more details on restore database check BOL.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Mar 2004
Location: UK
Posts: 26
Automate it

Really you should be able to automate the whole process.
1) Set up maintenance plan to do backup
2) Schedule the copy the .bak file to the other server, either with a sql server agent job or a with .bat file in windows (I've never actually tried this)
3) Schedule a task to run the 'Restore database' as mentioned before. You may need to set up a backup device that points to the copied .bak file.

Obviously you want to make sure you schedule these things so you give them time to finish.

Alternatively you might want to just do the restore bit manually.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Oct 2003
Posts: 47
Suppose that we have only the backup (done by EM). How to restore this last in another server (and change the dbname)
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Location: Poland
Posts: 96
I do foloowing

1. Create db in new server winth the same name
2. Retsore
3. go to DATA folder and coby db files with new name
4. exec sp_attach_db
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Location: South Africa
Posts: 71
How will I copy that .bak from one server to the other
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: Kansas City, MO
Posts: 734
RESTORE db_new_name
FROM DISK = '\\server\share\backup_name.bak'
WITH
MOVE logical_data_file TO 'physical_data_file.mdf'
MOVE logical_log_file TO 'physical_log_file.ldf'

To get the logical file names, you can use RESTORE FILELISTONLY.

All these commands are described with cut-and-paste examples in Books Online.

The account that runs SQL Server on the server you're wanting to restore these files to will need to have access to the network share you're wanting to restore from.
__________________
MeanOldDBA
derrickleggett@hotmail.com
When life gives you a lemon, fire the DBA.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Location: South Africa
Posts: 71
I get an error
'db_new_name' is not a recognized RESTORE option.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: Kansas City, MO
Posts: 734
RESTORE DATABASE db_new_name
__________________
MeanOldDBA
derrickleggett@hotmail.com
When life gives you a lemon, fire the DBA.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On