SQL_DUMP Utility for Microsoft SQL Server
This Utility Script was coded and contributed to Wisdombay by Mr. NAZEEM.N.J
This tool emulates the pg_dump utility available in PostgreSQL, in a way that it offers options for backing up the schema, data or both of a particular database in the form of dump files.
This stored procedure once installed in to the master database of SQL Server, will offer powerful functionality for Database Administrators as it equips them with the control of taking dumps of any database they choose from command line. This script offers versatility in the form of allowing admins to take data only, schema only or schema along with data backups on a database according to the need on hand.
So where is this Script?
This script is offered as two different components and you as a DBA will have to install both these stored procedures on your SQL Server master database for this code to work.
1. A stored Procedure which is used by the sql_dump utility for the purpose of File Writing capabilities. You can download it HERE.
2. The SQL_DUMP Stored Procedure Source code, which does the actual magic. You can download it HERE.
Tell Me how to Install it
Step 1 : Download both the source files ('FileWrite.txt' and 'sqldump.txt') from the links given above.
Step 2 : using the 'SQL Server Management Studio' connect to the 'master' database.
Step 3 : While connected to the master database, execute the code given inside the first file, i.e. 'FileWrite.txt', by copying the entire file content into the 'Query Window' and pressing the 'Execute' button. This installs a stored procedure used by the sql_dump script for file writing purposes.
Step 4 : Now copy the whole content from the second file, i.e. 'sqldump.txt', into the 'Query Window' and execute it. This will install the sql_dump stored procedure on your server.
How to use sql_dump stored procedure
You make use of the sql_dump stored procedure using the following syntax. Type it in your Query Window after connecting to the 'master' database.
exec sql_dump '-d (databasename) [-t (tablename)] [-f (outputfilename)] [-a] [-s]'
-d (databasename) : The database whose backup is to be taken
-t (tablename) : This option is to be used if you need to take the structure or data belonging to a particular table only. [Optional - If omitted then all the tables are involved in the dump process]
-f (outputfilenamewithpath) : This option is to be used if you need to specify the path and name of file to which dump is to be written. [Optional - If omitted then the dump is stored at the Backup folder found in the installation directory of MS SQL Server, with database name as the filename.]
-s : Dumps the database Schema also.
-a : Dumps the data found inside the database also.
Example 1 :
exec sql_dump '-d ritz_laundery_2003 -f c:\backup\onMonday23Oct.txt -a -s
The above given sample when executed will create a dump file named 'c:\backup\onMonday23Oct.txt' with both the schema and all the data contained in the database 'ritz_laundery_2003'
Example 2 :
exec sql_dump '-d ritz_laundery_2003'
The above given sample when executed will create a dump file named 'ritz_laundery_2003.txt' at the Backup folder found inside the installation directory of SQL Server. The dump will contain both the schema and all the data contained in the database 'ritz_laundery_2003'. [if -a and -s are not given then by default they are applied.]
Example 2 :
exec sql_dump '-d ritz_laundery_2003 -t employee -s'
The above given sample when executed will create a dump file named 'ritz_laundery_2003.txt' at the Backup folder found inside the installation directory of SQL Server. The dump will contain only the schema of the table 'employee'
This script was tested on both MS SQL Server 2000 and MS SQL Server 2005.
This script will not work correctly in replication enabled servers.