[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Restore Database From Script for SQL

Posted on 2006-05-24
12
Medium Priority
?
495 Views
Last Modified: 2012-06-21
I would like to create a new local database from a customers backup. I would like to  specify the following information via TSQL.

- name of new database
- set the File Growth of the the data files and transaction log to be by megabytes
- set the path of the .bak file
- select force restore over existing
- set the path of the data and log file -- "Move to physical file name"
0
Comment
Question by:tricks801
  • 7
  • 2
  • 2
  • +1
12 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 800 total points
ID: 16755328
RESTORE DATABASE <new_name>
  FROM DISK='<path of .bak file>'
WITH REPLACE
, MOVE '<logical_name_1>' TO '<physical_path_1'>
, MOVE '<logical_name_2>' TO '<physical_path_2'>
etc for additional files

- set the File Growth of the the data files and transaction log to be by megabytes
you can only alter that AFTER the restore.


0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16755360
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_4e5h.asp

ALTER DATABASE <new_name>
MODIFY FILE (NAME= <logical_name> , FILEGROWTH = xMB)
0
 
LVL 20

Assisted Solution

by:Sirees
Sirees earned 600 total points
ID: 16755442
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16757449
read this article from BOL

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsqlref.chm::/ts_ra-rz_25rm.htm
0
 

Author Comment

by:tricks801
ID: 16763152
do I need to create the database before I restore to it? or does it suffice to RESTORE DATABASE and it will create it for me.

0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 600 total points
ID: 16763178
>or does it suffice to RESTORE DATABASE and it will create it for me
Yes, the new name has to be mentioned in the query

RESTORE DATABASE <new_name>  -- put the new database name here
  FROM DISK='<path of .bak file>'
WITH REPLACE
0
 

Author Comment

by:tricks801
ID: 16763228
its giving me errors regarding the data and log files....in the manual process, I have to first create a new database and then restore to it.
0
 

Author Comment

by:tricks801
ID: 16763234
and i cant make the path of .bak work if i use the UNC....it has to be the drive letter of the drive on the SQL server machine
0
 

Author Comment

by:tricks801
ID: 16763273
Ex.
RESTORE DATABASE TEST_20061105
FROM DISK='F:\Imports\TEST_20060517.bak'
WITH REPLACE,  
MOVE 'TEST_20061105_Data' TO 'F:\pmts\TEST_20061105_Data.MDF',
MOVE 'TEST_20061105_Log' TO 'F:\pmts\TEST_20061105_Log.LDF'

it gives me the error
Logical file 'TEST_20061105_Data' is not part of database 'TEST_20061105'. Use RESTORE FILELISTONLY to list the logical file names.
0
 

Author Comment

by:tricks801
ID: 16763539
I think my problem is, is that I do not know the logical file names for the log and the data...how can i find this out?
0
 

Author Comment

by:tricks801
ID: 16763734
i can use ESTORE FILELISTONLY FROM DISK = .... but how can i use this to generate the RESTORE DATABASE TSQL?
0
 

Author Comment

by:tricks801
ID: 16764075
got it...i can use a recordset. thanks
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question