Skip to main content

Taking Backup of database in one server and restoring in other via sql commands


Database YourDB has full backup YourBaackUpFile.bak. It can be restored using following two steps.

Step 1: Retrive the Logical file name of the database from backup.

 RESTORE FILELISTONLY FROM DISK = 'D:BackUpYourBaackUpFile.bak'
GO
Step 2: Use the values in the LogicalName Column in following Step.

Step 2.1:
----Checking if the drive exist(for ldf n mdf files) in this particular server
EXEC master.dbo.xp_fileexist 'YourLDFLogicalName' 
EXEC master.dbo.xp_fileexist ' YourMDFLogicalName
It will give 3 columns, for file, directory and root directory.
Check if it exist or not for both LDF and MDF files.

Step 2.2
---If not then change the file path using these lines
--For data file
SELECT TOP 1 ms.physical_name FROM master.sys.master_files ms WHERE ms.file_id=2 ORDER BY ms.database_id DESC
--For log file
SELECT TOP 1 ms.physical_name FROM master.sys.master_files ms WHERE ms.file_id=1 ORDER BY ms.database_id DESC

Step 2.3
--Change the file name to the name of the DataNEWYourMDFFile.mdf and DataNEWYourLDFFile.ldf'

Step 2.4
--Checking if the db exist, if yes making it to single user
IF EXISTS (SELECT name FROM master.sys.databases WHERE name = '')

BEGIN
ALTER DATABASE userDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
END

Step 2.5
----Restore Database
RESTORE DATABASE YourDB FROM DISK = '\\ServerIP\\D:\BackUpYourBaackUpFile.bak' 
WITH MOVE 'YourMDFLogicalName' TO 'DataNEWYourMDFFile.mdf', 
MOVE 'YourLDFLogicalName' TO 'DataNEWYourLDFFile.ldf'

/*If there is no error in statement before database will be in multiuser
mode.
If error occurs please execute following command it will convert
database in multi user.*/
ALTER DATABASE YourDB SET MULTI_USER
GO



Labels:

Comments

  1. AmitMonday, 12 August, 2013

    Its quite confusing for new-bees... well quite a good post, but a more descriptive post will be appreciated.

    ReplyDelete

Post a Comment

Important - Make sure to click the Notify Me check-box below the comment to be notified of follow up comments and replies.

Popular posts from this blog

Convert your datatable into generic poco object in c# using linq, ado and reflections.

Follow @harshit_parshii The most common problem that we face these days is to create a common class and method that can be used across all the projects and codes. So today I will be sharing my code where you can see how to make and create a generic function without using entity framework for ado. net. The scenario is like you have an old software that uses stored procedure to return set of entities as a data-table, you do not want to re-write the back-end code as you are creating a web API in c# which needs to be delivered asap. You need to map these data tables to models as you might be using MV* pattern. So here we will be doing one to one mapping of model to data- table, and in similar fashion insert or update can also be done. So basically we are converting a data-table to list of strongly typed object model to do CRUD operations. So we have following things before hand. A helper class is referenced as the database(dbFactory) which executes ado. ne...
Post a Comment
Read more

Run CSS specific to Internet Explorer - Browser Hack

Run CSS specific to Internet Explorer - Browser Hack Referencing to the following blog post, we are going to make CSS targeting exclusively to IE browser, to make it work first we should know what are media queries. A media query consists of a media type and at least one expression that limits the style sheets' scope by using media features, such as width, height, and color. Media queries, added in CSS3, let the presentation of content be tailored to a specific range of output devices without having to change the content itself. For Ex: < style > @media (max-width : 600px) { .facet_sidebar { display : none ; } } So below we will wrap the IE specific CSS rules in @media blocks and trick IE into rendering @media blocks that use media queries. Targeting only IE browsers Style rules defined in the following blocks will only be applied in IE, other browsers will ignore them. IE 6 and 7  @media screen\9 {     body { background: red; ...
Post a Comment
Read more

With new experiences comes great responsibility

Hello Everyone, I am an ordinary person with some highly looked after skills, not boasting about myself. But after coding in this IT industry for almost 5 years for the first time I am thinking:- Is delivering the code my only responsibility or is there anything else I need to look into as well? From last few month's I was highly involved in some entirely new domain which is financial, and when you play with the number you need to be highly focused, so unable to update my blog as well. When I was working on the urgent deadlines and tight estimates and being from a healthy UI background like css3, html5, responsive design, knockout js, less etc. I was put on the SQL server and asp.net with grid views and some freaked out Telerik controls. I know it's a learning for me and I can see why UX matters to every person in this entire world but not UI and you can write it on stamp paper. User Experience, with logical and functionally correct code, is the need of the hour to...
Post a Comment
Read more