Click here to Skip to main content
6,822,613 members and growing! (20,154 online)
Email Password   helpLost your password?
Database » Database » SQL Server     Intermediate

Restore SQL Server Backup Files into any database

By Phorozan

When you create a backup, you must be able to restore this backup into the same database and same location. But this code also helps you to restore a backup into a new database or existing database.
C#, SQL.NET1.1, Win2K, WinXP, Win2003, Visual-Studio, SQL2000, DBA, Dev
Posted:23 Oct 2005
Views:75,935
Bookmarked:54 times
printPrint   add Share
      Discuss Discuss   Broken Article?Report  
22 votes for this article.
Popularity: 4.87 Rating: 3.63 out of 5
5 votes, 22.7%
1

2
1 vote, 4.5%
3
5 votes, 22.7%
4
11 votes, 50.0%
5

Sample Image - Title.jpg

Introduction

When a backup is created from a Microsoft SQL Server database, by default it must restore the same database at the same location. But if you want to restore this backup at another location in another server you must use customized T-SQL scripts. This operation consumes a lot of time.

Customize T-SQL Backup Statements

Now you can use customized T-SQL statements to restore a database in any location. For example:

RESTORE DATABASE NewNorthwind
   FROM DISK = 'C:\Northwind.BAK'
   WITH 
      MOVE 'Northwind_Data' TO 'C:\NewNorthwind_Data.mdf' ,
      MOVE 'Northwind_Log'  TO 'C:\NewNorthwind_log.ldf', REPLACE

This script must be generated for each database.

Load T-SQL Statements from Assembly

We can store T-SQL statements in an Exe or a DLL file by adding a new file to the project and setting Build Action property to Embedded Resource. For example, add a new file with the name Restore.sql to a project and set the Build Action property. Now for loading it from the assembly, use the function:

private string LoadSQLFromAssembly (string Name)
{
  System.IO.Stream stream = 
    this.GetType().Assembly.GetManifestResourceStream(this.GetType(), 
                                                      "SQL." + Name);

  if(stream == null)
  {
    MessageBox.Show("Internal Error occured! Close Application" + 
      " & try again.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    return null;
  }

  System.IO.StreamReader reader= new System.IO.StreamReader(stream);

  if (reader == null)
  {
    MessageBox.Show("Internal Error occured! Close Application" + 
      " & try again.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    return null;
  }

  string s = reader.ReadToEnd();
  reader.Close();
  return s;
}

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

Phorozan


Member

Occupation: Web Developer
Location: United States United States

Other popular Database articles:

Article Top
You must Sign In to use this message board.
FAQ FAQ 
 
Noise Tolerance  Layout  Per page   
 Msgs 1 to 16 of 16 (Total in Forum: 16) (Refresh)FirstPrevNext
Generalvery Coooooooooooool ! PinmemberNamdar200221:11 16 Aug '09  
GeneralTry EZmanage SQL Pro Pinmemberitayl4:13 27 May '09  
GeneralRestore problem Pinmemberayman tawfik21:56 24 May '09  
GeneralWorks great! PinmemberEd Brown 6510:28 18 Mar '09  
GeneralThank You ;) Pinmembersaravanan.rex@gmail.com21:11 13 Nov '08  
Generalthank you Pinmemberhsy113:25 9 Sep '08  
GeneralThank you PinmemberTaheri618220:43 12 May '08  
GeneralBackup option PinmemberRakesh16116:33 6 Nov '07  
GeneralThis is just what the doctor ordered! PinmemberEric Murray13:48 19 Mar '07  
GeneralSuper usefull!!! Thank's mate.;) PinmemberOrmusDog3:52 16 Dec '06  
GeneralYou're a Tool Pinmemberjberkhei10:28 17 Nov '06  
GeneralThank you PinmemberGianluca Simionato4:45 9 Oct '06  
QuestionSql Backup Pinmember| Muhammad Waqas Butt |0:49 9 Nov '05  
GeneralNice article, but this is possible without T-SQL PinmemberRandy Friend7:11 3 Nov '05  
GeneralRe: Nice article, but this is possible without T-SQL PinmemberDeeJRoss5:59 7 Apr '06  
GeneralRe: Nice article, but this is possible without T-SQL PinmemberPolymorpher6:46 1 Jun '06  

General General    News News    Question Question    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads.

PermaLink | Privacy | Terms of Use
Last Updated: 23 Oct 2005
Editor: Smitha Vijayan
Copyright 2005 by Phorozan
Everything else Copyright © CodeProject, 1999-2010
Web21 | Advertise on the Code Project