Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sript out a remote sql server database

Posted on 2011-09-02
11
Medium Priority
?
260 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 79

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 40

Assisted Solution

by:lcohan
lcohan earned 1000 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

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 40

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
 
LVL 40

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 40

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 79

Assisted Solution

by:arnold
arnold earned 1000 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 40

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

722 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