Click here to Skip to main content
11,710,943 members (88,367 online)
Click here to Skip to main content

Restore SQL Server Backup Files into any database

, 23 Oct 2005 129.3K 4K 71
Rate this:
Please Sign up or sign in to vote.
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.

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

Share

About the Author

Phorozan
Web Developer
United States United States
No Biography provided

You may also be interested in...

Comments and Discussions

 
QuestionNetwork restorin not working Pin
veena nair19-Mar-13 21:47
memberveena nair19-Mar-13 21:47 
GeneralMy vote of 5 Pin
Reza Alipour Fard3-Jan-13 2:04
memberReza Alipour Fard3-Jan-13 2:04 
GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 6:10
mvpKanasz Robert24-Sep-12 6:10 
GeneralMy vote of 5 Pin
Ms komika battranah16-Jul-12 2:37
memberMs komika battranah16-Jul-12 2:37 
May GOD Bless You
Evry Time & Evry Work
GeneralMy vote of 5 Pin
m-mousavi1-Jun-12 1:44
memberm-mousavi1-Jun-12 1:44 
GeneralGR8 Pin
Vishweshwar Ballary22-Mar-11 0:02
memberVishweshwar Ballary22-Mar-11 0:02 
GeneralMy vote of 5 Pin
Vishweshwar Ballary22-Mar-11 0:02
memberVishweshwar Ballary22-Mar-11 0:02 
GeneralMy vote of 5 Pin
Shahin Khorshidnia31-Dec-10 16:22
memberShahin Khorshidnia31-Dec-10 16:22 
Generalvery Coooooooooooool ! Pin
Namdar200216-Aug-09 20:11
memberNamdar200216-Aug-09 20:11 
GeneralTry EZmanage SQL Pro Pin
itayl27-May-09 3:13
memberitayl27-May-09 3:13 
GeneralRestore problem Pin
ayman tawfik24-May-09 20:56
memberayman tawfik24-May-09 20:56 
GeneralWorks great! Pin
Ed Brown 6518-Mar-09 9:28
memberEd Brown 6518-Mar-09 9:28 
GeneralThank You ;) Pin
saravanan.rex@gmail.com13-Nov-08 20:11
membersaravanan.rex@gmail.com13-Nov-08 20:11 
Generalthank you Pin
hsy119-Sep-08 2:25
memberhsy119-Sep-08 2:25 
GeneralThank you Pin
Taheri618212-May-08 19:43
memberTaheri618212-May-08 19:43 
GeneralBackup option Pin
Rakesh16116-Nov-07 5:33
memberRakesh16116-Nov-07 5:33 
GeneralThis is just what the doctor ordered! Pin
Eric Murray19-Mar-07 12:48
memberEric Murray19-Mar-07 12:48 
GeneralSuper usefull!!! Thank's mate.;) Pin
OrmusDog16-Dec-06 2:52
memberOrmusDog16-Dec-06 2:52 
GeneralYou're a Tool Pin
jberkhei17-Nov-06 9:28
memberjberkhei17-Nov-06 9:28 
GeneralThank you Pin
Gianluca Simionato9-Oct-06 3:45
memberGianluca Simionato9-Oct-06 3:45 
QuestionSql Backup Pin
| Muhammad Waqas Butt |8-Nov-05 23:49
member| Muhammad Waqas Butt |8-Nov-05 23:49 
GeneralNice article, but this is possible without T-SQL Pin
Randy Friend3-Nov-05 6:11
memberRandy Friend3-Nov-05 6:11 
GeneralRe: Nice article, but this is possible without T-SQL Pin
DeeJRoss7-Apr-06 4:59
memberDeeJRoss7-Apr-06 4:59 
GeneralRe: Nice article, but this is possible without T-SQL Pin
Polymorpher1-Jun-06 5:46
memberPolymorpher1-Jun-06 5:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

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

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150819.1 | Last Updated 24 Oct 2005
Article Copyright 2005 by Phorozan
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid