Sript out a remote sql server database

sqlcmd is the command line tool you can use to access the sql server
http://www.microsoft.com/download/en/details.aspx?id=15748
http://msdn.microsoft.com/en-us/library/ms162773%28v=SQL.90%29.aspx

can I use sqlcmd to script out a db and it's data to a local file?  I have a username and password.

As I said in my post I am not running SSMS on the web server.  I need another tool to use.

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.

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

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!

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.

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