Restore Database From Script for SQL

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"
tricks801Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Aneesh RetnakaranDatabase AdministratorCommented:
read this article from BOL

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsqlref.chm::/ts_ra-rz_25rm.htm
0
 
tricks801Author Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
>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
 
tricks801Author Commented:
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
 
tricks801Author Commented:
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
 
tricks801Author Commented:
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
 
tricks801Author Commented:
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
 
tricks801Author Commented:
i can use ESTORE FILELISTONLY FROM DISK = .... but how can i use this to generate the RESTORE DATABASE TSQL?
0
 
tricks801Author Commented:
got it...i can use a recordset. thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.