Solved

Sript out a remote sql server database

Posted on 2011-09-02
11
247 Views
Last Modified: 2012-05-12
I have a webserver and a separate mssql server 2005.  I am not running SQL Management Studio on the webserver, but here is what I need to accomplish.  I need to know if there is a free tool that I can use on the web server to connect to the SQL server and script out a DB to a file.  I will need the schema and data of the database.  Then I can use the script to recreate on my other mssql server at a separate location.  The 2 mssql servers can not talk to each other which is why I need to script it out.
0
Comment
Question by:mrwarejr
  • 5
  • 4
  • 2
11 Comments
 
LVL 76

Expert Comment

by:arnold
ID: 36474608
0
 

Author Comment

by:mrwarejr
ID: 36474636
can I use sqlcmd to script out a db and it's data to a local file?  I have a username and password.
0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 36474646
You can use SSMS to connect to your server and database then just right click the DB name if you have sufficient rights and under Tasks menu option select Generate Scripts and use the wizzard that will lead you through all the objects in that DB
0
 

Author Comment

by:mrwarejr
ID: 36474656
As I said in my post I am not running SSMS on the web server.  I need another tool to use.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 36474673
Another much easier way in my opinion would be to do a full backup of the DB then copy the backup file to the other server somehow and restore it there. you may want to strip out some sensitive info from your DB but then you can do whatever you need on the other server in terms of scripting.


And for my above comment I missed to say that you can install just SSMS on a local client computer that can reach into the SQL server and not necessarily on the web server.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 39

Expert Comment

by:lcohan
ID: 36474720
BTW...all SQL code objects are in sys.syscomments text column and you could get them from there if they are not encrypted by running a single query like below:

select so.name, so.[type], sc.[text] from sys.syscomments  sc inner join sys.all_objects so on sc.id = so.object_id
0
 
LVL 39

Expert Comment

by:lcohan
ID: 36474746
And if you realy don't want to use SQL SSMS which is the native tool not even from a client computer (???) then you could use something like sql-dmo and generate all the scripts you need from tsql:

http://www.nigelrivett.net/DMO/DMOScriptAllDatabases.html

To script all your data....better do a backup and restore even if you don't have much in your DB.

Good luck!
0
 
LVL 76

Assisted Solution

by:arnold
arnold earned 250 total points
ID: 36474788
0
 

Accepted Solution

by:
mrwarejr earned 0 total points
ID: 36498395
Okay I got SSMS installed on the machine.  Now how do I script out just the schema then just the data.  The DB is too big to do it all in one.
0
 

Author Closing Comment

by:mrwarejr
ID: 36521425
I actually was able to get SSMS installed on the web server and able to backup to the remote drive.  Now I can just do a restore to my SQL server.  I appreciate all your help and awarded points to both of you since you both assisted.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 36504706
To script the Schema, just Right click the database name in SSMS, the select Tasks->Generate Scripts and a Wizzard pops up then just follow the super easy steps to do it.

For data is way different story...below is a script (AS IS and you should test it before use) that can generate INSERT commands including data from a table however...keep in mind that if you have FKEYs then you need more than just this.

Still I can't understand why you can't do a backup/restore which is 1 g-zillion times easyer in my opinion.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

create procedure dbo.GenerateInsertSscript
                 @tablename_mask varchar(30) = NULL
as
begin

  declare @tablename       varchar (128)
  declare @tablename_max   varchar (128)
  declare @tableid         int
  declare @columncount     numeric (7,0)
  declare @columncount_max numeric (7,0)
  declare @columnname      varchar (30)
  declare @columntype      int
  declare @string          varchar (30)
  declare @leftpart        varchar (8000)    /* 8000 is the longest string SQL Srv can EXECUTE */
  declare @rightpart       varchar (8000)    /* without having to resort to concatenation      */
  declare @hasident        int

  set nocount on

  -- take ALL tables when no mask is given (!)
  if (@tablename_mask is NULL)
  begin
    select @tablename_mask = '%'
  end

  -- create table columninfo now, because it will be used several times

  create table #columninfo
  (num      numeric (7,0) identity,
   name     varchar(30),
   usertype smallint)


  select name,
         id
    into #tablenames
    from sysobjects
   where type in ('U' ,'S')
     and name like @tablename_mask

  -- loop through the table #tablenames

  select @tablename_max  = MAX (name),
         @tablename      = MIN (name)
    from #tablenames

  while @tablename <= @tablename_max
  begin
    select @tableid   = id
      from #tablenames
     where name = @tablename

    if (@@rowcount <> 0)
    begin
      -- Find out whether the table contains an identity column
      select @hasident = max( status & 0x80 )
        from syscolumns
       where id = @tableid

      truncate table #columninfo

      insert into #columninfo (name,usertype)
      select name, type
        from syscolumns C
       where id =
--(select id from sysobjects where name ='_dsc')
@tableid
         and xtype not in (37,99,35)            -- do not include varbinary and text/ntext

      -- Fill @leftpart with the first part of the desired insert-statement, with the fieldnames

      select @leftpart = 'select ''insert into '+@tablename
      select @leftpart = @leftpart + '('

      select @columncount     = MIN (num),
             @columncount_max = MAX (num)
        from #columninfo
      while @columncount <= @columncount_max
      begin
        select @columnname = name,
               @columntype = usertype
          from #columninfo
         where num = @columncount
        if (@@rowcount <> 0)
        begin
          if (@columncount < @columncount_max)
          begin
            select @leftpart = @leftpart + @columnname + ','
          end
          else
          begin
            select @leftpart = @leftpart + @columnname + ')'
          end
        end

        select @columncount = @columncount + 1
      end

      select @leftpart = @leftpart + ' values('''

      -- Now fill @rightpart with the statement to retrieve the values of the fields, correctly formatted

      select @columncount     = MIN (num),
             @columncount_max = MAX (num)
        from #columninfo

      select @rightpart = ''

      while @columncount <= @columncount_max
      begin
        select @columnname = name,
               @columntype = usertype
          from #columninfo
         where num = @columncount

        if (@@rowcount <> 0)
        begin

          if @columntype in (39,47) /* char fields need quotes (except when entering NULL);
                                    *  use char(39) == ', easier readable than escaping
                                    */
          begin
            select @rightpart = @rightpart + '+'
            select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+replace(' + @columnname + ',' + replicate( char(39), 4 ) + ',' + replicate( char(39), 6) + ')+' + replicate( char(39), 4 ) + ',''NULL'')'
          end

          else if @columntype = 37 /* uniqueidentifier need quotes and converted to VC 38  */

          begin
            select @rightpart = @rightpart + '+'
            select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+convert(varchar(99),' + @columnname + ')+'+ replicate( char(39), 4 ) + ',''NULL'')'
          end

          else if @columntype in (58,61,111) /* datetime fields */
          begin
            select @rightpart = @rightpart + '+'
            select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+convert(varchar(20),' + @columnname + ')+'+ replicate( char(39), 4 ) + ',''NULL'')'
          end

          else   /* numeric types */
          begin
            select @rightpart = @rightpart + '+'
            select @rightpart = @rightpart + 'ISNULL(convert(varchar(99),' + @columnname + '),''NULL'')'
          end


          if ( @columncount < @columncount_max)
          begin
            select @rightpart = @rightpart + '+'','''
          end

        end
        select @columncount = @columncount + 1
      end

    end

    select @rightpart = @rightpart + '+'')''' + ' from ' + @tablename

    -- Order the select-statements by the first column so you have the same order for
    -- different database (easy for comparisons between databases with different creation orders)
    select @rightpart = @rightpart + ' order by 1'

    -- For tables which contain an identity column we turn identity_insert on
    -- so we get exactly the same content

    if @hasident > 0
       select 'SET IDENTITY_INSERT ' + @tablename + ' ON'

    exec ( @leftpart + @rightpart )

    if @hasident > 0
       select 'SET IDENTITY_INSERT ' + @tablename + ' OFF'

    select @tablename      = MIN (name)
      from #tablenames
     where name            > @tablename
  end

end





GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Select2 jquery help 9 46
null value 15 70
SQL Server creating a temp table 7 39
SQL JOIN + SUBQUERY? 3 14
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now