none
Clone SQL database RRS feed

  • Question

  • Hi to all and sorry for my english,
    here my code to clone SQL db. Starting from .sql base file, i change db name and run script. Accepting any suggestion about this code, i explain my issue.

    I write code in my develop machine with VS2010 and cassini web server. This pc is joined in mydomain and i login (and work) with domain user "admin". Also SQL server mysqlserver is joined in domain. User admin have dbcreator, public and sysadmin rule on this SQL server.

    Now, if i run webpage in my develop machine, all work fine and db is created as desidered.

    Issue born when i publish page in W2008 web server (IIS 7). This server is joined in domain and is in LAN with develop machine and mysqlserver.

    Web page stop with

    Object reference not set to an instance of an object


    After some test i understand the issue is caused by this row

    server.ConnectionContext.ExecuteNonQuery(script);


    My test before write this post:
    - run this row inside try/catch. Nothing change and no exception is generated.
    - run with another (domain admin) elevated user. Nothing change
    - run with a sample SQL query (select * from testdb). Nothing change

    How i can approach this (for me) very strange issue?

    I hope to be clear in my explanation.

    Thanks in advanced for any help.

    Regards.

    Massimo

    using System; 
    using System.Collections.Generic; 
    using System.Linq; 
    using System.Web; 
    using System.Web.UI; 
    using System.Web.UI.WebControls; 
    using System.IO; 
    using System.Data.SqlClient; 
    using Microsoft.SqlServer.Management.Common; 
    using Microsoft.SqlServer.Management.Smo; 
    using System.Security.Principal; 
    using System.Runtime.InteropServices; 
     
        private void ImpAuthEdEseguiFunc() 
        { 
                  WindowsIdentity winId = null; 
     
            IntPtr token = IntPtr.Zero; 
     
            string EnabledPwd = "adminpwd"; 
     
            bool LogonElevate = LogonUser("admin", "mydomain", EnabledPwd, LogonSessionType.Interactive, LogonProvider.Default, out token); 
     
            if (LogonElevate) 
            { 
                winId = new WindowsIdentity(token); 
     
                WindowsImpersonationContext ctx = null; 
     
                try 
                { 
                    ctx = winId.Impersonate(); 
     
                    /**/ 
     
                    string sqlConnectionString = @"Data Source=mysqlserver;Integrated Security=True;Trusted_Connection=Yes"; 
     
                    FileInfo file = new FileInfo(Server.MapPath("~\\mysql.sql")); 
     
                    if (file.Exists) 
                    { 
                        string script = file.OpenText().ReadToEnd(); 
     
                        if ((Request.QueryString["databasename"] != null) && (Request.QueryString["databasename"].Length > 3)) 
                        { 
                            script = script.Replace("basedb", Request.QueryString["databasename"]); 
                            SqlConnection conn = new SqlConnection(sqlConnectionString); 
                            Server server = new Server(new ServerConnection(conn)); 
                            server.ConnectionContext.ExecuteNonQuery(script); 
                            file.OpenText().Close(); 
                        } 
                        else 
                        { 
                            Response.Write("missed parameter"); 
                        } 
                    } 
                    else 
                    { 
                        Response.Write("file sql not found"); 
                    } 
     
                    /**/ 
                } 
                catch (Exception E) 
                { 
                    Response.Write(E.InnerException.Message); 
                } 
                finally 
                { 
                    if (ctx != null) 
                        ctx.Undo(); 
                } 
     
            } 
            else 
            { 
                Response.Write("Logon failed"); 
            } 
    }

    Friday, October 19, 2012 10:10 AM

Answers

  • Is it possible to use a System.Data.SqlClient.SqlCommand to execute your script?

    if ((Request.QueryString["databasename"] != null) && (Request.QueryString["databasename"].Length > 3))
    {
        string sqlConnectionString = @"Data Source=mysqlserver;Integrated Security=True;Trusted_Connection=Yes";
    
        FileInfo file = new FileInfo(Server.MapPath("~\\mysql.sql"));
    
        if (file.Exists)
        {
            string script;
    
            using (StreamReader reader = file.OpenText())
                script = reader.ReadToEnd();
    
            script = script.Replace("basedb", Request.QueryString["databasename"]);
    
            using (SqlConnection conn = new SqlConnection(sqlConnectionString))
            {
                conn.Open();
    
                using (SqlCommand command = new SqlCommand(script, conn))
                    command.ExecuteNonQuery();
            }
        }
        else
        {
            Response.Write("file sql not found");
        }
    }
    else
    {
        Response.Write("missed parameter");
    }
    • Marked as answer by Alexander Sun Friday, November 9, 2012 9:01 AM
    Saturday, October 20, 2012 2:25 AM