2

I've got a linux server that already connects happily to a MS SQL Server and I want to know if there is a way to dump the whole thing into a format I can read. I don't have access to the desktop, but I can connect using PHP and I can issue whatever commands I want. I have admin access to the SQL Server, so no problem there.

My main goal is to understand how the people before me set this thing up. I already know how to get the stored procedures as text (SELECT * FROM sys.procedures), but I was wondering if there is a way to get the whole database. I'm not very familiar with SQL Server so I don't know what important bits I might be missing.

And I don't care if the solution is in PHP or not. That's just the thing I've got working right now. Any SQL-ish command that dumps the entire database would solve my world.

To summarize:

  1. I don't have access to the actual machine/desktop
  2. I have admin access to the DB using PHP's mssql libs
  3. I'm on linux
  4. I want a text file I can look at that tells me everything in the database
  5. My goal is not to answer a specific question - I'm looking to understand what the people before me did when they set up this database. Unknown unknowns, and all that.

Okay, hopefully I've made sense. I'm sorry if I've been a complete idiot. Be gentle. Thanks!

Shadow The GPT Wizard
  • 66,030
  • 26
  • 140
  • 208
pileofrogs
  • 154
  • 1
  • 9
  • 1
    You will have the easiest time examining the database via SQL Server management Studio. Can you access the db via a Windows machine? – D'Arcy Rittich Oct 17 '11 at 21:06
  • Do you have file system access to the database server? Or Remote desktop access? Getting access to this information from windows would save you a lot of time. However, its certainly possible from Linux. However, expect to spend a lot of time figuring out how to do things that are simpler on windows from SSMS. – Justin Dearing Oct 17 '11 at 21:26
  • FYI, there will soon be better Linux ODBC drivers for SQL server. http://blogs.msdn.com/b/sqlphp/archive/2011/10/14/announcing-microsoft-sql-server-odbc-driver-for-linux.aspx No word yet if that means the Microsoft written SQL server driver for PHP will be ported to linux. – Justin Dearing Oct 17 '11 at 21:28
  • http://stackoverflow.com/q/3103345/105929 – Remus Rusanu Oct 17 '11 at 23:37

3 Answers3

2

I would backup (http://msdn.microsoft.com/en-us/library/ms186865.aspx) the database to file and then download it, restore it on Windows and then use SQL Server tools like SQL Server Management Studio etc. to look at it.

There is plenty you can do with the metadata, but you could spend a lot of time writing queries instead of using existing off-the-shelf documentation tools.

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
1

You can use this script to create insert statements for any given table.

This stackoverflow question will tell you how to generate create table statements.

SELECT NAME FROM sys.tables will give you a list of table names.

Community
  • 1
  • 1
Justin Dearing
  • 14,270
  • 22
  • 88
  • 161
  • I'd actually go with BCP vs building insert statements myself but otherwise, seems like a fair approach – billinkc Oct 18 '11 at 02:33
  • Also, look at the [INFORMATION_SCHEMA](http://msdn.microsoft.com/en-us/library/ms186778.aspx) views and you should be able to get all the information you want about the database via queries and as a bonus, it's semi-portable since information schema is a standard. Zippy, feel free to roll any of this into your answer if you wish. – billinkc Oct 18 '11 at 02:36
1

You would probably save a LOT of time and pain by just using native SQL SErver Windows tools that work with it.

Kuberchaun
  • 29,160
  • 7
  • 51
  • 59