Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

Sript out a remote sql server database

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
mrwarejr
Asked:
mrwarejr
  • 5
  • 4
  • 2
3 Solutions
 
arnoldCommented:
0
 
mrwarejrAuthor Commented:
can I use sqlcmd to script out a db and it's data to a local file?  I have a username and password.
0
 
lcohanDatabase AnalystCommented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
mrwarejrAuthor Commented:
As I said in my post I am not running SSMS on the web server.  I need another tool to use.
0
 
lcohanDatabase AnalystCommented:
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
 
lcohanDatabase AnalystCommented:
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
 
lcohanDatabase AnalystCommented:
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
 
mrwarejrAuthor Commented:
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
 
mrwarejrAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now