Restoring database in T-SQL

Question

Restoring database in T-SQL

Dinesh Asanka

SSChampion

Points: 11058
More actions
February 9, 2005 at 10:00 pm

#152344

i used following script to restore the database.
CREATE PROCEDURE sp_CreateProduct
@varProductName nvarchar(20),
@bitStatusbit bit OUTPUT
AS
--
DECLARE @install_path NVARCHAR(260)
EXEC master.dbo.sp_MSgettools_path @install_path OUTPUT
SET @bitStatusbit =0
declare @varDatafile nvarchar(50), @varLogFile nvarchar(50),@varmdfFielName nvarchar(150),@varldfFielName nvarchar(150)
--Set @varDatafile = @varProductName --+ '_Data'
Set @varmdfFielName = left(@install_path,2) + '\Program Files\Microsoft SQL Server\MSSQL\Data\' + @varProductName + '.mdf'
Set @varldfFielName = left(@install_path,2) + '\Program Files\Microsoft SQL Server\MSSQL\Data\' + @varProductName + '.ldf'
--Set @varLogFile = @varProductName + '_Log'
--Set @varmdfFielName =
RESTORE DATABASE @varProductName
FROM DISK = 'C:\dbprodtemp.BAK'
WITH MOVE 'org_data' TO @varmdfFielName,
MOVE 'org_Log' TO @varldfFielName,
REPLACE
SET @bitStatusbit =1
GO
I need to restore databases from one backs to several databases but issu I have is I can't give different logical file name????

My Blog: http://dineshasanka.spaces.live.com/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

hoo-t SSChampion Points: 12713 More actions · Answer 1

If I understand your question, not being able to change the logical filename in the restore won't be a problem. Give the database a new name, and new names for the physical files, and restore the database. Once the database is restored, you can change the logical filenames with an ALTER DATABASE statement. It doesn't hurt anything to have more than one database on a server that has the same logical filenames, but it would be less confusing for us humans if they are different!

Steve

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 2

Thank you

But can we chnage the primary file name from ALter Datbabase ???

My Blog: http://dineshasanka.spaces.live.com/

hoo-t SSChampion Points: 12713 More actions · Answer 3

You can change the LOGICAL filename of your primary file using ALTER DATABASE-

ALTER DATABASE pubs

MODIFY FILE (NAME = 'pubs', NEWNAME = 'pubs_data')

You can only change the physical filename using ALTER DATABASE if its the tempdb database . From Books Online -

FILENAME

Specifies an operating system file name. When used with MODIFY FILE, FILENAME can be specified only for files in the tempdb database. The new tempdb file name takes effect only after SQL Server is stopped and restarted.

'os_file_name'

Is the path and file name used by the operating system for the file. The file must reside in the server in which SQL Server is installed. Data and log files should not be placed on compressed file systems.

If the file is on a raw partition, os_file_name must specify only the drive letter of an existing raw partition. Only one file can be placed on each raw partition. Files on raw partitions do not autogrow; therefore, the MAXSIZE and FILEGROWTH parameters are not needed when os_file_name specifies a raw partition.

To change the physical filename of a user database, you detach the database, change the name of the physical file from the operating system (open Windows Explorer, browse to the file, right click, select rename), and then attaching the database. Here are the SQL commands -

EXEC sp_detach_db pubs

-- Go to the operating system and modify the physical file now.

EXEC sp_attach_db @dbname = N'pubs',

@filename1 = N'D:\SQL_Data\pubs_data.mdf',

@filename2 = N'D:\SQL_logs\pubs_log.ldf'

Steve

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 4

thankx for the infoamrtion

My Blog: http://dineshasanka.spaces.live.com/