Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
 New Feature: Community Sites:
Create your own .NET community website and start earning from Google AdSense !
It's Free !
|
How to Restore Database by moving database to a specified locations
Posted Date: 05 Sep 2008 Resource Type: Articles Category: Databases
|
Posted By: Nagendra kumar Battiprolu Member Level: Silver
Rating:  Points: 10
|
How to Restore Database by moving database to a specified locations:-
Applicable to SQL Server 2000
Dear Friends,
This article represents how to restore a backup copy of database to a specified targeted database or as a new database. This is very helpful to have control on specifying the location of the database files to be restored by moving them from the current location. Even we can place the MDF and LDF files separately in different locations.
In brief we require the following information as inputs for restoring the database.
1. The database name you want to restore. Assuming you are running the code snippet (Stored Procedure) on the same server where the actual database which exists to be restored.
2. The Backup File name. For example pubs.bak. Assuming you had already taken a backup copy.
3. The Backup File Path. For example \\mybackups or C:\mybackups. It can be a network path, if provided security and permissions given for that location.
4. Source Database Logical filename (MDF). Logical file name of the data file. (You can look for the Logical name in enterprise manager or using command restore filelistonly from )
5. Source Database Logical filename (LDF). Logical file name of the Transaction log file (You can look for the Logical name in enterprise manager or using command restore filelistonly from )
6. The path and File Name details of the database’s MDF file to be restored. For example :c:\sqldatabases\mydatabase_Data.mdf
7. The path and File Name details of the database’s LDF file to be restored. For example :d:\sqldatabases\mydatabase_log.Ldf
The above information helps to restore the existing database or create a new database with a given backup file.
The code snippet (Stored procedure) which I am providing will perform the following Operations:-
1. Creates a device as for restoring the database a device is required with specified
backup location and filename. By default the database name will be the device name. It
even searches whether any device already exists with the specified path. If exists it
deletes that and continues creating the device.
2. Restores the specified database with the given backup file on a specified location. If
database not exists, new database will be created. If database files (MDF & LDF)
already exists, then they will be restored. If the Path of those files varies then those
database files will be moved to the specified location.
3. Updates the Logical file names of the database with given Logical MDF and LDF file
names.
4. Drops the device
---Code Snippet
CREATE PROCEDURE [Dbo].[RestoreDB]
@RestoreDBName varchar(100), ---Restore as
@BackupFileName varchar(100), -- Backup file name
@BackupFilePath varchar(100), -- path of the backup file where it exists
@SourceDB_Logical_MDF_Name varchar(500), --- Logical file name of the data file(MDF)
@SourceDB_Logical_LDF_Name varchar(500), --- Logical file name of the data file(LDF)
@RestoreDB_MDF_Path_AND_Name varchar(500), -- Restore location and File name(MDF)
@RestoreDB_LDF_Path_AND_Name varchar(500) -- Restore location and File name(LDF)
AS
SET IMPLICIT_TRANSACTIONS OFF
---setting this Sql server wont commit the transactions implicitly
declare @DeviceCnt numeric
declare @pathANDName varchar(100)
------------------
----here we are concatenating path and name in a variable
select @pathANDName=@backupFilePath +'\'+ @BackupFileName
--finding whether already any device exists with same physical location and file name
select @DeviceCnt=count(phyname) from master.dbo.sysdevices where phyname= @backupFilePath +'\'+ @BackupFileName
-------
--@devicecnt contains total devices already exists
------------------
if (@DeviceCnt<=0) -- if no such device exists then create the device
exec master.dbo.sp_addumpdevice 'disk', @RestoreDBName ,@pathANDName
else
-- if device exists search for all such devices , delete them and create newly. Here we are using cursor for that activity
begin
declare @deviceNames varchar(100)
---------
declare searchdevices cursor local forward_only for select name from master.dbo.sysdevices
where upper(phyname) =upper(@pathANDName)
------
open searchdevices
---
fetch next from searchdevices into @devicenames
----
while (@@fetch_Status!=-1)
begin
----
if (@@fetch_Status<>-2)
begin
-- if exists drop the device
exec master.dbo.sp_dropdevice @devicenames
end
-----
fetch next from searchdevices into @devicenames
-----
end
--------
close searchdevices
deallocate searchdevices
---create the device
exec master.dbo.sp_addumpdevice 'disk', @RestoreDBName ,@pathANDName
------
end
-------------
----
-- Restoring the database with Backup File
-----
restore database @RestoreDBName from @RestoreDBName
with replace, move @SourceDB_Logical_MDF_Name TO @RestoreDB_MDF_Path_AND_Name,
move @SourceDB_Logical_LDF_Name TO @RestoreDB_LDF_Path_AND_Name
--------------
-- Restore of database with ' Backup File Completed. Here we restored database at a specified location
--Here we are altering the Logical names of MDF and LDF files of the database
exec ('alter database '+ @RestoreDBName + ' MODIFY FILE (NAME = ' +
@SourceDB_Logical_MDF_Name + ', NEWNAME = ' + @RestoreDBName +')')
----
exec ('alter database ' + @RestoreDBName +
' MODIFY FILE (NAME =' + @SourceDB_Logical_LDF_Name + ', NEWNAME =' + @RestoreDBName + '_Log)')
-------------
--Dropping Device -----
exec master.dbo.sp_dropdevice @RestoreDBName
--------
-------------
--Dropped the Device
GO
|
Responses
|
No responses found. Be the first to respond and make money from revenue sharing program.
|
|