April 17, 2007 at 1:48 am
Hello everyone, In SQL Server 2000, i want to know the difference between backup database and Generate SQL script? As we generate the SQL script
for backup the database as run the script file it again makes Tables, SP and so on.. kindly tell me the clear difference between these.
I want to generate the scripts of my database on regular basis start of the each day, is that possible to make any thing that do this
without manually do that on each day if yes then how to make a plan for this as i have a little bit knowledge about Maintenance plan
how to make a plzn for taking backups, plz help me
Thanx in Advance as i also trying to learn and study BOL but still need ur suggestions and ideas also.
April 17, 2007 at 2:06 am
Generating script and running it does not copy any data.
It creates everything but all tables have no records.
April 17, 2007 at 9:09 am
Additionally, a backup save everything in the database: tables, views, stored procedures, functions, users, etc. Scripting tables only allows you to recreate the table schema.
To get started with backups, maybe you should look at creating a maintenance plan in Enterprise Manager. The GUI makes it pretty easy to set up.
Greg
Greg
April 18, 2007 at 6:46 am
isa,
If you need something that will script out your schema and data, there are such products. Nothing of the sort is built into MS SQL server, however. I've only tried FOSS tools and they did not work.
I know that in MySQL, you can do backups either way.
April 18, 2007 at 1:44 pm
I would also ask as to why you would want to generate a script on a daily basis?
I must admit I have not done any sql development for quite a while, but in the past, all I did was generate a script for all tables if there was a change to the schema. I'd also keep a copy of all stored procedures.
At that point, I didn't use source safe, but I use that now.
Seems a bit of overkill to actually script the database on a daily basis.
April 18, 2007 at 3:22 pm
I think the original poster is thinking of scripting as a way to backup his database. Hopefully, the responses in this thread have dissuaded him of that notion.
Greg
Greg
April 26, 2007 at 11:31 am
For daily maintenance, I backup databases and transaction logs. For monthly maintenance, I script out all my databases and jobs. A DB script recreates everything: devices, tables, user permissions, roles, logins, etc.
The scripts help me out when it comes to disaster recovery: running the script recreates the devices wherever they were supposed to go. The script also (potentially) gives me version control: I can load it into a version control system and then run new scripts against the old ones to see if any changes have been made or users added without my knowledge.
Unfortunatey I don't know of a way to automate scripting these puppies out, so it's a manual task that I do monthly.
Be sure to backup your MSDB database in addition to master as MSDB contains all of your jobs.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
May 3, 2007 at 8:35 am
Hi,
Can any one tell me, how can i generate the sqlscript for all the tables and the storedprocedure and triggers of my Database, i successfully generated the sqlscript of the database. but it does not contains the tables the stored procedures and the triggers within it.
I am using Sqlserver 2005.
- Manish
May 3, 2007 at 9:44 am
In SSMS, right-click on database name, then select Tasks and Generate Scripts.
Greg
Greg
May 4, 2007 at 8:18 am
thanks, thank you so much.
- Manish
May 4, 2007 at 9:34 am
Hi Wayne,
the following vb-script will script out all Jobs:
------------------------------ snip ---------------------------------
Option Explicit
Dim conServer
Dim fso
Dim iFile
Dim oJB
Dim strJob
Dim strFilename, strServer
Const ioModeAppend = 8
strServer = "SQL-Server1"
Set conServer = CreateObject("SQLDMO.SQLServer")
conServer.LoginSecure = True
conServer.Connect strServer
strFilename = "F:\Logs\JOBS_" & strServer & ".sql"
For Each oJB In conServer.JobServer.Jobs
strJob = strJob & "--------------------------------------------------" & vbCrLf
strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
strJob = strJob & "--------------------------------------------------" & vbCrLf
strJob = strJob & oJB.Script() & vbCrLf
Next
Set conServer = Nothing
Set fso = CreateObject("Scripting.FileSystemObject")
Set iFile = fso.CreateTextFile(strFilename, True)
iFile.Write (strJob)
iFile.Close
Set fso = Nothing
---------------------------------- snap -----------------------------
I added this as an step using ActiveX-Script to a scripting job...
regards
karl
Best regards
karl
May 4, 2007 at 10:45 am
Thanks, Karl. Does this work with 2000? We haven't yet upgraded to 2K5, that's on my list. Unfortunately I don't have VB installed at the moment. I tried implementing it in DTS as an ActiveX script but it didn't work, I've never done this type of DTS scripting before.
And congrats on 200 posts! I hope to be there in the not-too-distant future.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
May 7, 2007 at 4:01 am
Yes, it works with sql2k. I did nothing special regarding VB, simply dropped the script into an job step in EM, selected ActiveX-Script and marked VB as script language.
The script is not originally from me, i just filched it somwhere on the net...
Karl
(200 now, thank you 😉
Best regards
karl
May 7, 2007 at 8:24 pm
I had a requirement recently whereby I had to set up a job that generated scripts to recreate existing user views, functions and stored procedures. The reason for this in my case was replication - applying database snapshots requires that the tables being replicated as part of the snapshot are dropped and recreated. Applying the snapshot fails if the tables are referred to by views etc. I had to find a way of dropping the referring objects, and then recreate them exactly as they were afterwards, by applying these scripts as pre- and post- snapshot scripts.
But, because the objects on the reporting database could be modified by another developer, or new objects created etc, I had to find a way of automatically generating a single script to recreate the objects, and run it on a nightly basis to keep it current, so I couldn't just have a single unchanged script.
For me, the answer was to use sp_helptext within a cursor, and put it all into a T-SQL stored procedure, then called via a job.
The job uses sqlcmd to execute the proc and put the results into the single file that I wanted. Works a treat, and I can generate the pre- and post- snapshot scripts at will, execute them in SSMS perfectly (need to get the order of creation of the objects right, though...) and it works as intended with replication and snapshots. The procedure script is about fifty lines long and very simple. A few additional lines will script out the permissions for these objects. Runs in a matter of seconds. I have no doubt that it could be extended further to accommodate whatever you desire!
Runs under SQL2K5, but could be adapted for SQL2K easily enough.
Why SS can't recreate these objects itself is a question for another forum, I know, but I had a 'mare with trying to replicate views, sp's etc. SS could never get the objects created in the right order (dependencies).
Cheers
Simon
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply