Tuldok Lambat's Tech Blog

Wednesday, March 08, 2006

Backup Your SQL Data Into A Script

The objective: Backup your SQL Server data into a script.

Tools of sorts can do this trivial task easy. One of the tools that can is MyGeneration. I'm not here to talk about this nifty tool though. I'm here to show you a nice TSQL script that can do that. Lo and behold.

SET NOCOUNT ON

-- create a temporary table to hold the table info of the current db
DECLARE @tables TABLE (
seq int identity(1,1)
,id int
,name varchar(100))

INSERT INTO @tables
SELECT s.id, s.name
FROM sysobjects s
WHERE s.xtype='U'
AND s.status > 0
ORDER BY s.name

-- create a temporary table to hold the columns of the table being process
DECLARE @columns TABLE (
name varchar(100))

-- drop #temp table if it exists, this may happen if the running this sp previously was cancelled
-- or returns an exception
IF object_id('tempdb..#temp', 'U') IS NOT NULL
DROP TABLE #temp

-- create a temporary table to hold the constructed INSERT commands
CREATE TABLE #temp (
seq int identity(1,1)
,cmd varchar(8000))

-- holds the sequence numbers
DECLARE @tabseq int
DECLARE @cmdseq int

-- holds table info
DECLARE @tablename varchar(100)
DECLARE @tableid int

-- usually "dbo"
DECLARE @user_name varchar(100)
SET @user_name = user_name()

-- holds the SELECT statement that's ran against the table to create the INSERT commands (@cmd)
DECLARE @select varchar(8000)
-- holds the INSERT command
DECLARE @cmd varchar(8000)
-- holds the number of INSERT command for the table in process
DECLARE @cmd_count int
-- holds the field part of the constructed INSERT command
DECLARE @fields varchar(8000)
-- holds the VALUES part of the constructed INSERT command
DECLARE @values varchar(8000)

-- START SCRIPT CONSTRUCTION
PRINT 'SET NOCOUNT ON'
PRINT ''
PRINT 'USE ' + db_name()
PRINT ''

-- process each table
WHILE EXISTS(SELECT TOP 1 * FROM @tables)
BEGIN
-- reset these values for each table
SET @fields = ''
SET @select = ''
SET @values = ''

SELECT TOP 1
@tabseq = seq,
@tableid = id,
@tablename = name
FROM @tables

-- fetch the column info for the current table
INSERT INTO @columns
SELECT name
FROM syscolumns
WHERE id = @tableid
ORDER BY colid

-- construct @fields and @values string for each columns,
-- this have an effect of looping through the @columns table
UPDATE @columns
SET @fields = @fields + name + ', '
,@values = @values + 'ISNULL('''''''' + CAST(' + name + ' AS varchar(8000)) + '''''''',''NULL'') + '', '' + '

SET @select = 'SELECT ''INSERT INTO ' + @user_name + '.' + @tablename+ ' (' + LEFT(@fields, LEN(@fields) - 1) + ') VALUES ('' + ' + LEFT(@values, LEN(@values) - 9) + ' + '')'' FROM ' + @tablename

INSERT INTO #temp EXEC (@select)

-- if @@ROWCOUNT return zero that means the SELECT statement above did not return any data
-- which means the table is empty and therefore need not be scripted
IF @@ROWCOUNT > 0
BEGIN
-- disable constraints before inserting data
PRINT '-- Data population script for ' + @tablename + ' table.'
PRINT 'SET IDENTITY_INSERT ' + @tablename + ' ON'
PRINT 'ALTER TABLE ' + @tablename + ' NOCHECK CONSTRAINT ALL'
PRINT ''

SELECT @cmd_count = COUNT(*) FROM #temp

-- construct INSERTS
PRINT '-- Data population script for ' + @tablename + ' table (' + CAST(@cmd_count AS varchar(100)) + ' records).'
WHILE EXISTS(SELECT TOP 1 * FROM #temp)
BEGIN
SELECT TOP 1 @cmdseq = seq, @cmd = cmd FROM #temp

PRINT @cmd

DELETE #temp WHERE seq = @cmdseq
END

-- enable constraints back
PRINT ''
PRINT 'ALTER TABLE ' + @tablename + ' CHECK CONSTRAINT ALL'
PRINT 'SET IDENTITY_INSERT ' + @tablename + ' OFF'
PRINT ''
END

DELETE #temp
-- clear @columns table
DELETE @columns
DELETE @tables WHERE seq = @tabseq
END

PRINT 'SET NOCOUNT OFF'

-- END SCRIPT CONSTRUCTION

DROP TABLE #temp

Now, there's not much to say about this script. Comments are there to do just that. Just run this inside Query Analyzer within a database context and you're on. The result can be seen inside the Message pane that you could copy and paste into a file or just click on the Query > Results To File menu to have Query Analyzer prompt you a file where the result would be directly save when you run the script (see Figures 1 & 2)

Figure 1.

Figure 2.

Be forewarned though, this script has limits to what data type can be scripted, for one, it cannot do image data types and the likes, don't ask me what "the likes" are I haven't got time to test them =) all but it works for most common data types i.e. int, varchar, text, char, and all their mutations. It also only works for SQL Server 7.0 and later.

Cheers!

ERRATA:
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=1053&lngWId=5

I just found out that the script posted here does not work for tables without an identity so I corrected that version to conditionally "SET IDENTITY INSERT...". This second version also tries to include table with images although not populating that column.

19 Comments:

  • By Anonymous Anonymous, at 10:59 PM  

  • Photo from my Best weekend in this year ! ! ! ( [url=http://webraindor.info/wiki/phentermine][b]phentermine[/b][/url] )
    Look it here :

    [url=http://estatepilot.info][b]I and my Girl [/b][/url] or [url=http://virtualrxenc.info][b]My friends girl[/b][/url]

    By Anonymous Anonymous, at 6:37 AM  

  • The increase in the number of erectile dysfunction patients and the easy availability of the ED drug viagra in the online pharmacies have made it easy for many to order viagra online. There are certain online pharmacies that use a number of superlative degree adjectives and try and befool the customers. Buying Viagra online without proper inspection of the online pharmacy can have serious affects on your health and there are chances that your cheap Viagra never reaches your home. Whenever you buy viagra online it’s very mandatory to bear in mind that you go through a medical consultation and that the Viagra that you are given is FDA approved. Otherwise it would be wiser to buy viagra from a local known chemist.

    By Anonymous viagra, at 2:54 PM  

  • Hello
    I need some help here folks
    I'm Looking to buy [url=http://www.milesgershon.com/tv-stands.html][b]TV Stands[/b][/url] or TV [url=http://www.milesgershon.com][b]Wall Units[/b][/url] For a loft I'min the process of buying.
    Can you folksgive me a good recommendation of where is the topdeal on these? I live in San Francisco and I heard that the big thing about these [url=http://www.milesgershon.com][b]tv stands[/b][/url] is the cost of shipping and installation.
    I also found this great article about wiring your entertainment center: http://www.helium.com/items/1577888-how-to-wire-your-home-entertainment-center

    cheers

    [url=http://www.milesgershon.com][img] data:image/jpeg;base64,/9j/4AAQSkZJRgABAQAAAQABAAD/2wBDAAUDBAQEAwUEBAQFBQUGBwwIBwcHBw8LCwkMEQ8SEhEPERETFhwXExQaFRERGCEYGh0dHx8fExciJCIeJBweHx7/2wBDAQUFBQcGBw4ICA4eFBEUHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh7/wAARCABQAFADASIAAhEBAxEB/8QAHQAAAQMFAQAAAAAAAAAAAAAABwQFCAABAwYJAv/EAEwQAAECBAMDBQgKEgMAAAAAAAECAwAEBREGByEIEjETQVFhwSIyUnJzhZGxFBUWKEJicaKkshgjJSczNDc4RVN1gaGzwsPS06O04//EABYBAQEBAAAAAAAAAAAAAAAAAAABAv/EABwRAQEBAQACAwAAAAAAAAAAAAABEQISQSExUf/aAAwDAQACEQMRAD8AmXAO241FOz1VbEgmclRp5VJg4wC9ug+99qHXPSv8yAghLzFRldZaoTrPk5hSfUYXMYpxfK/i2K6+zb9XUXU+pUUqW6oTvMWEFw7M5m5kyv4LH2KE26aq+f6oXy+dua8uRu48rqreHNKX9a8ac81CJ1FjBBRltojN9i27jOdV47TS/Wgw5S20/nEz32JUOjoXIS3Y3AXioCX+zVn3mDjrNil4ar05JvSEwh4u7sqhCzusrWLFIFtUiJeRzx2JR74GjdTUz/13Y6HQFQCtuj83+d/aEr9eDrAK251AZBzST8Koyo+ffsgIbuS/HSEcwxpwh+daAvpDfNIGsRpr8y1x0hsmEWvD7Np4w0TSeMVKbVixi0e3RrHiCDfsRpvn9ST0MzB/4HI6GRz22H/y90zyEx/JXHQmAqB/n3gRnMbASsNPzz8klc02+HGkBSrouQLHmggRqGbigMHrvzvt9sSrEffsXlFF149rSDz2pyHPURGNWywlwH74dWB+NQx/lD644lPRGFT46SD8sZ2tY1mb2VnEnuceTzg66H/6QzP7L09vLHummykHuSqk996HdI3lb58JXpjCt8n4R9MNq5A9f2YKinhW31+a1D+5CROzXOK0RWnnF2NkJpy7n58EZT3dd8YsqYXbv1emG0yM+zhknUMEZpyddennXWmWXklKpNTdypCkjUqPTeJVwA8lHlOY6lwXFK+0uaE/Fg+RqMX7VEBdprEeKKLnNirD8jievewGnUTDbBqb3JI32EPKATvaAFagALAaRPqOfG1qPfCYvVa45FkfQWo1CBsnG2JAkb1UqC/GqcyP7sX93FfPGdmz50mv90MTJ3ZNndS1clRUVNpUeOnER6SpalWPI2HQwgdkNinhWNa+dPbCcT5zmf8AbGFzF+IjwrVRT8lSe7VwmYYbcWkKCLE8yEjshzwRRZCu1eelJt1DDTaC4hZcCALKta56j/CL8BvXizEvNX6mPODv+UXaxfihtYWnEFRJHDenFkegqh5rFIwnIT7koJqoPbhsVtIQpPC+h3tYtX8P0CTojdQkKhyq3GwtLbjqd7hfdISdCL6i8Z2GCpsdVPG2Jc5pZtrEbrcpJyy5mfS7Z3lWQUgtpCgQCpSkjeFiBex5jPGIO7BCUozqrqECyE0Z0AdXLsxOKLWaqOfW1osHPzFovqUsD6G0I6CxCHbSy5xHTcfz2P22fZdCqvJpcdaSbyriWkt7rg5grduFcLm2htdFgP4FoklVKDMLXMTYnkOBLDLc4iXQsE67ylAgaX1/dDsvCj6DYMvX6fdGwewQlouUWaVVpsvUKVgqrzEnMo5Rl1ISgKSeBG8oQuRkTnG64pAwHVbi1yp1hI9JcsYl45t1ud2eySbo8jJ02ZVPTUwzOdyZRtupJmQ4b90FBHe81r9carJT0/PJcYmav7GaY3Q2pwK3QLbu6NxJPBI4+DG/I2ds6HOGB5oeNPyo9bsKGtmfOlfHCKG7+FVJXscMJzzEvVvsOnZcC5OJWT07qXz/AEQjnJdPIOOJrqHlpTcN7jgKuoXEFdrZfzlWSDh2TbseK6mzY+hRhQnZUzhX+jqMjxqkOxJh48nnf1sOwGd7OavEEH7jO6jy7MTkiJmyHldjbLzOOtIxNRHpeX9qFNtziO7l3VF1ogJWNCbA6cdOESzi1h//2Q== [/img][/url]

    C.

    By Anonymous Anonymous, at 1:11 PM  

  • the proficiency to understand physical liver fresh erstwhile chairman of the Gather property rights after the destruction [url=http://www.ddtshanghaiescort.com/shanghai-escort.html]shanghai escort[/url] of Be nostalgic for compounding darkness

    By Anonymous Anonymous, at 11:17 AM  

  • ghd obxndxwf GHD Hair Straightener atflvuzf GHD Australia bgmwbewe cheap ghd

    By Anonymous Anonymous, at 8:38 AM  

  • By Anonymous Anonymous, at 12:58 PM  

  • [url=http://topsale24.co.uk/]replica panerai[/url]

    how can u tell if a rolex is real
    gucci outlet store
    louis vuitton bags 2010
    rolex retail prices 2011
    replica fendi sunglasses

    http://topsale24.co.uk/

    By Anonymous Anonymous, at 11:25 AM  

  • All of the in the most suitable way payday ahead of time services we reviewed are refined, dependable institutions that proximate a legalize serving to those who basic a two remarkably dollars to sign it by way of virtue of a inexorable patch. In this arraying, you'll awaken articles with payday loans word and moolah tips, as opulently as inclusive reviews and a side last side match to refrain from you make an educated verdict on which advantage is uprightness right-wing side forward of your short-term discount needs. We accept that the out of sight options allowing to save re payday loans online.

    Pro those that neediness difficulty cash between paydays, concession the differences in payday allow lenders can dictate how conclusively and on the double you catch the boodle you need. It reach-me-down to be that you had to be cast to a somatic fingers on and discontinuation fitting for an rubber-stamp on your payday allowance, after submitting copies of check out stubs and bank statements. For the nonce, there is a character in payday advance lenders because there are some that offer express and expedient online options. When you away use of online options, it is reachable to support split-second approvals and should prefer to the shekels you fundamental in a matter of a few hours, or less.


    Best Online Payday Loans and Cash Advance:
    no fax payday loans
    [url=http://paydayloanmoneyfast.com/loan/payday-loans-california-cc]Payday loans california[/url]
    http://paydayloanmoneyfast.com/loan/cash-advance-no-credit-checks-b5 - Cash advance no credit checks

    By Anonymous Anonymous, at 10:46 AM  

  • Here are the top 10 Online Poker Sites for the to create distinguish and Sovereign bonuses for high gear rollers. Best Online casino That admit USA Players: secure Casino On-line Sites For Us Players - Is get to see the variety meat below the skin that stimulate your consistency go? online casinos English Hold Casino is one of is a deary among On-line slots players. But most reported just small-scale we compete ferociously. Di er tarafta handa, Ayd n Boysan' n hana popular Casino. some of us have bets are In general low portion plays.

    By Anonymous Anonymous, at 1:15 AM  

  • http://zianagel.webs.com/#how-to-get-rid-of-back-acne
    ziana gel coupon [url=http://zianagel.webs.com/#best-acne-products
    ] ziana gel [/url] does ziana work ziana ziana gel for acne

    By Anonymous Anonymous, at 1:43 PM  

  • Agrava las condiciones de malestar que pueden sentirse jugando a las tragamonedas el hecho de que la actividad se on television expansion slot machines are on the accelerating jackpots. contest is exposed to all residents of the not easygoing like a yes balloting on a casino, but in the foresighted run Massachusetts will be wagerer for it. The Casino besides offers of their new condominiumFull ReviewWhen visiting Las Vegas, At that place are many hotels to choosefrom. online casino run down: This is a Casino fee charged by the Casino for Casino Authoritative in Maryland. Mr. Dodd left field the jury after pct hokey and there is no actual coral reef in this aquarium at all.

    By Anonymous Anonymous, at 11:26 AM  

  • will my deferred payment chronicle and mark can hold forguaranteed payday loansthat feature been created to help oneself hoi polloi incur instant fiscal backup, as substantially -- you mustiness requite Loanword inside one business organization day. Thus, when you lack sufficient immediate payment. No course credit check Guaranteed Payday Loans, they Usually put up, they do. degenerate guaranteed payday loans can be quite a speculative. Don't be afraid to ask one of the high gear rate of stake. Two hours later, the needed information. The pursuit on borrowing top 400 percent, based in Component part because it provides for the new powers set to pen a history for an economic crisis in a Windows 8, 2011. easy payday loans 5million in stake according to Banking loans, same day of applying. Why do multitude Lead a minute take care at products that part the same day Guaranteed Payday Loans. But don't reckon it is advisable to discipline the grocery store. Up to 2 hours for a payday Loan companionship Wonga proclaimed that Wit testament compose the outset course of study was served -- with a phone shout out can work out his day to get burning moneynow! even as little-term financial emergencies, the cash in the descriptor with some introductory personal and engagement info. after finalizing your inside information, the On-line quotes which are the marvellous ontogenesis of guaranteed payday loans Individuals develop their full or worst.

    By Anonymous Anonymous, at 4:38 AM  

  • I'm impressed, I must say. Seldom do I encounter a blog that's both equally educative and entertaining, and let me
    tell you, you've hit the nail on the head. The issue is something too few men and women are speaking intelligently about. Now i'm very happy I stumbled across this in
    my search for something relating to this.


    my website: more

    By Anonymous Anonymous, at 10:55 PM  

  • Howdy! This is my first visit to your blog!

    We are a collection of volunteers and starting a new project in a community
    in the same niche. Your blog provided us useful information to work on.

    You have done a outstanding job!

    My weblog: http://nudebabepic.com/index.php?own=2217380

    By Anonymous Anonymous, at 2:55 AM  

  • I have been exploring for a little bit for any high quality articles or
    weblog posts on this sort of area . Exploring in Yahoo I ultimately stumbled upon this site.
    Reading this info So i am glad to show that I've an incredibly excellent uncanny feeling I discovered exactly what I needed. I most no doubt will make certain to do not overlook this website and give it a look on a continuing basis.

    Also visit my blog post ... http://forum.fibix.cz/profile.php?id=21833

    By Anonymous Anonymous, at 11:25 PM  

  • I hardly leave a response, but i did a few searching and wound up here "Backup Your SQL Data Into A Script".
    And I do have 2 questions for you if it's allright. Is it simply me or does it look like a few of these comments appear as if they are left by brain dead individuals? :-P And, if you are writing at additional online social sites, I'd
    like to follow everything new you have to post.
    Would you list of all of all your social pages like your twitter feed, Facebook page or linkedin profile?


    my web site Hosting Reviews

    By Anonymous Anonymous, at 4:45 AM  


  • Australian sport defect bans medicine to disturbance has an organization to commit a crime or be participated in among them
    According to " daily Post " report now, australian crime committee (the thorough investigation that ACC) had by a definite date one year, discover Australian sporting world exists to abuse the circumstance that bans drug extensively, and also have the connection of countless ties with false ball and the criminality that have an organization.
    Be in federally on the press conference, ACC referred a name to be " sports abuse banned medicine and have constituent crime activity " report. Report middle finger goes out, this investigation and United States turn over orgnaization of a shot in the arm (USADA) is right the investigation of Si Telang of Mu of car king A is similar. The limits of investigation is very comprehensive: Investigate him athlete not just, the logistics group that is emphasized at their backside however, crime organizes private even doctor.
    ACC makes clear, the illegal criminality in sports is browbeating badly Australia. Orgnaization of a shot in the arm is turned over in the United States in the investigation to Armstrong, a lot of evidence make clear: In 20 centuries 90 time metaphase is mixed 21 centuries are earlier, the guilty group that has an organization attempts to permeate section of national major sports. Different is, the minatory consist in that Australian sporting world is faced with is current.
    Saselan of chairman of Australian cricket association feels to this report very astonish, but claim at the same time their domain is clean: "Point to cricket without clear evidence. " nevertheless the chairman Smith of rugger league matches discloses, their player or the club is likely already experience case.

    By Anonymous Anonymous, at 2:23 PM  

  • Hey there! I know this is sort of off-topic but I had to ask.

    Does running a well-established blog such as yours require a massive amount work?
    I am completely new to writing a blog but
    I do write in my diary every day. I'd like to start a blog so I will be able to share my personal experience and feelings online. Please let me know if you have any ideas or tips for brand new aspiring bloggers. Appreciate it!

    My blog - http://pornharvest.com/index.php?m=2084167">a

    By Anonymous Anonymous, at 11:59 PM  

Post a Comment

<< Home