Hi! I've been scouring web sites and local information and not having any luck finding out how to save and restore jobs.
The problem comes up that we have around 10 to 15 jobs that we use for daily business that we have to rebuild every time SQL Server crashes or we move to a new machine, etc., and I've been trying to find a way to back them up.
Can anyone help me out please?
VowelsJobs are stored in the MSDB database. So if you back that up (which should
be part of your daily routine) you should be able to restore it later on.
Another way is to script the job using EM or SQL-DMO. Here is a sample
script that you can use in a scheduled job as an Active-X job step.
Dim oSS
Set oSS = CreateObject("SQLDMO.SQLServer2")
Dim lcstring
Dim lcServer
Dim oJob
Set oJob = CreateObject("SQLDMO.Job")
Dim lcFile
lcServer = "YourDBNameHere"
oSS.LoginSecure = True
oSS.Connect lcServer
lcFile = "D:\Data\DB_Scripts\ScriptJobs" & "_" & Year(Now) & Right("0" &
Month(Now), 2) & Right("0" & Day(Now), 2) & ".sql"
For Each oJob In oSS.JobServer.Jobs
lcstring = lcstring & "-- ***** " & oJob.Name & " ****** " &
vbCrLf & vbCrLf
lcstring = lcstring & oJob.Script
Next
Dim fso, txtfile
Set fso = CreateObject("Scripting.FileSystemObject")
Set txtfile = fso.CreateTextFile(lcFile, True)
txtfile.Write (lcstring)
txtfile.Close
Set oSS = Nothing
Set fso = Nothing
End Sub
Andrew J. Kelly
SQL Server MVP
"Vowels" <anonymous@.discussions.microsoft.com> wrote in message
news:358B2D03-36EE-4D64-BEEA-57784865D7DE@.microsoft.com...
> Hi! I've been scouring web sites and local information and not having any
luck finding out how to save and restore jobs.
> The problem comes up that we have around 10 to 15 jobs that we use for
daily business that we have to rebuild every time SQL Server crashes or we
move to a new machine, etc., and I've been trying to find a way to back them
up.
> Can anyone help me out please?
> Vowels
Showing posts with label restoring. Show all posts
Showing posts with label restoring. Show all posts
Friday, March 23, 2012
Saving and restoring configuration
Hi all,
I have an SQL Server 7 machine which took me a while to get set up correctly. I have it fully documented in case I need to rebuild it but I'd like to have a (semi) automated rebuild procedure. Ideally this would involve me manually building the Windows and basic SQL Server installatoin then applying my SQL Server configuration on top. I can't use GHOST for various reasons.
I know that I can generate SQL scripts for various parts of the SQL Server configuration but does anyone know of a tool/utility which will help me do this?
TA,
H.May check tools from Redgate (http://www.red-gate.com) website.|||Thanks for the tip|||Hi,
the Red Gates stuff identifies differences (via the DTS Compare tool) but doesn't create a script of any sort.
Any other ideas anyone?
H.|||Check this article (http://www.informit.com/isapi/product_id~%7BFFF8022C-84BB-4509-9D52-68D4F1BC43AE%7D/content/index.asp) to create own unattended installation files for SQL Server configuration apart from generating SQL scripts.sql
I have an SQL Server 7 machine which took me a while to get set up correctly. I have it fully documented in case I need to rebuild it but I'd like to have a (semi) automated rebuild procedure. Ideally this would involve me manually building the Windows and basic SQL Server installatoin then applying my SQL Server configuration on top. I can't use GHOST for various reasons.
I know that I can generate SQL scripts for various parts of the SQL Server configuration but does anyone know of a tool/utility which will help me do this?
TA,
H.May check tools from Redgate (http://www.red-gate.com) website.|||Thanks for the tip|||Hi,
the Red Gates stuff identifies differences (via the DTS Compare tool) but doesn't create a script of any sort.
Any other ideas anyone?
H.|||Check this article (http://www.informit.com/isapi/product_id~%7BFFF8022C-84BB-4509-9D52-68D4F1BC43AE%7D/content/index.asp) to create own unattended installation files for SQL Server configuration apart from generating SQL scripts.sql
Saving and Restoring a SQL SERVER Table
Can anyone tell me how I can save or copy a SQL Server table and restore it
on another system?
I am running SQL Server 2000.
I see how I can export it to another database on my machine. But I need to
copy the table to another PC.
Any help is appreciated.
Ron
Hi
If you can export it into it's own database then a backup can be made and
restored on the other PC.
If the whole database is not big, then just take the whole backup. It is not
clear why you want the one table and not a whole consistent backup!
http://support.microsoft.com/kb/304692/EN-US/
John
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> Can anyone tell me how I can save or copy a SQL Server table and restore
> it
> on another system?
> I am running SQL Server 2000.
> I see how I can export it to another database on my machine. But I need
> to
> copy the table to another PC.
> Any help is appreciated.
> Ron
>
|||John
I only need one table to test with at another location.
I want to be able to copy one table to a floppy and then restore.
The backup option copies the whole database.
How can this be done?
"John Bell" wrote:
> Hi
> If you can export it into it's own database then a backup can be made and
> restored on the other PC.
> If the whole database is not big, then just take the whole backup. It is not
> clear why you want the one table and not a whole consistent backup!
> http://support.microsoft.com/kb/304692/EN-US/
> John
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
>
>
|||DTS can copy individual tables and their data to databases
on different servers.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> Can anyone tell me how I can save or copy a SQL Server table and restore
it
> on another system?
> I am running SQL Server 2000.
> I see how I can export it to another database on my machine. But I need
to
> copy the table to another PC.
> Any help is appreciated.
> Ron
>
|||I assume DTS stands for Data Transformation Service but that still doesn't
give me a clue how to save and restore an individual table.
"Armando Prato" wrote:
> DTS can copy individual tables and their data to databases
> on different servers.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> it
> to
>
>
|||Why do you need to save it and restore it? I think if worse came to worse,
you
could place it on it's on filegroup and go that route.
With DTS, you can copy the table directly and not jump through any hoops.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:893AFAE7-4648-4A1D-81AD-9F23559BDCC0@.microsoft.com...[vbcol=seagreen]
> I assume DTS stands for Data Transformation Service but that still doesn't
> give me a clue how to save and restore an individual table.
> "Armando Prato" wrote:
restore[vbcol=seagreen]
need[vbcol=seagreen]
|||Are you going to tell me how to do that with DTS or do you just want to keep
me in suspence?
I already explained why I need to save & restore?
"Armando Prato" wrote:
> Why do you need to save it and restore it? I think if worse came to worse,
> you
> could place it on it's on filegroup and go that route.
> With DTS, you can copy the table directly and not jump through any hoops.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:893AFAE7-4648-4A1D-81AD-9F23559BDCC0@.microsoft.com...
> restore
> need
>
>
|||Well, you could've done a wee bit of leg work and researched DTS.
DTS is a very simple tool that can be accessed right from
the Microsoft SQL Server folder on your server. The folder
entry is called "Import and Export Data". From there a wizard
launches that asks you your source and destination servers along
with a screen that lists all available tables on the source where you check
off the
one you want to export.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:DC936A64-DF31-45CB-B560-AADE917754B7@.microsoft.com...
> Are you going to tell me how to do that with DTS or do you just want to
keep[vbcol=seagreen]
> me in suspence?
> I already explained why I need to save & restore?
> "Armando Prato" wrote:
worse,[vbcol=seagreen]
hoops.[vbcol=seagreen]
doesn't[vbcol=seagreen]
I[vbcol=seagreen]
|||Hi
The point was that you can create a new database and copy the one table into
that. Then you backup contains the one table. You can then restore that
database and if necessary copy the table again. Alternatives would be to to
script the table and use DTS or BCP to export the data.
You may want to use a CD or USB flash device if this data/backup is bigger
than 1.44 MB.
John
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:CA95E845-DA13-4589-BAEC-D0B2852ED2CB@.microsoft.com...[vbcol=seagreen]
> John
> I only need one table to test with at another location.
> I want to be able to copy one table to a floppy and then restore.
> The backup option copies the whole database.
> How can this be done?
> "John Bell" wrote:
on another system?
I am running SQL Server 2000.
I see how I can export it to another database on my machine. But I need to
copy the table to another PC.
Any help is appreciated.
Ron
Hi
If you can export it into it's own database then a backup can be made and
restored on the other PC.
If the whole database is not big, then just take the whole backup. It is not
clear why you want the one table and not a whole consistent backup!
http://support.microsoft.com/kb/304692/EN-US/
John
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> Can anyone tell me how I can save or copy a SQL Server table and restore
> it
> on another system?
> I am running SQL Server 2000.
> I see how I can export it to another database on my machine. But I need
> to
> copy the table to another PC.
> Any help is appreciated.
> Ron
>
|||John
I only need one table to test with at another location.
I want to be able to copy one table to a floppy and then restore.
The backup option copies the whole database.
How can this be done?
"John Bell" wrote:
> Hi
> If you can export it into it's own database then a backup can be made and
> restored on the other PC.
> If the whole database is not big, then just take the whole backup. It is not
> clear why you want the one table and not a whole consistent backup!
> http://support.microsoft.com/kb/304692/EN-US/
> John
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
>
>
|||DTS can copy individual tables and their data to databases
on different servers.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> Can anyone tell me how I can save or copy a SQL Server table and restore
it
> on another system?
> I am running SQL Server 2000.
> I see how I can export it to another database on my machine. But I need
to
> copy the table to another PC.
> Any help is appreciated.
> Ron
>
|||I assume DTS stands for Data Transformation Service but that still doesn't
give me a clue how to save and restore an individual table.
"Armando Prato" wrote:
> DTS can copy individual tables and their data to databases
> on different servers.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> it
> to
>
>
|||Why do you need to save it and restore it? I think if worse came to worse,
you
could place it on it's on filegroup and go that route.
With DTS, you can copy the table directly and not jump through any hoops.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:893AFAE7-4648-4A1D-81AD-9F23559BDCC0@.microsoft.com...[vbcol=seagreen]
> I assume DTS stands for Data Transformation Service but that still doesn't
> give me a clue how to save and restore an individual table.
> "Armando Prato" wrote:
restore[vbcol=seagreen]
need[vbcol=seagreen]
|||Are you going to tell me how to do that with DTS or do you just want to keep
me in suspence?
I already explained why I need to save & restore?
"Armando Prato" wrote:
> Why do you need to save it and restore it? I think if worse came to worse,
> you
> could place it on it's on filegroup and go that route.
> With DTS, you can copy the table directly and not jump through any hoops.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:893AFAE7-4648-4A1D-81AD-9F23559BDCC0@.microsoft.com...
> restore
> need
>
>
|||Well, you could've done a wee bit of leg work and researched DTS.
DTS is a very simple tool that can be accessed right from
the Microsoft SQL Server folder on your server. The folder
entry is called "Import and Export Data". From there a wizard
launches that asks you your source and destination servers along
with a screen that lists all available tables on the source where you check
off the
one you want to export.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:DC936A64-DF31-45CB-B560-AADE917754B7@.microsoft.com...
> Are you going to tell me how to do that with DTS or do you just want to
keep[vbcol=seagreen]
> me in suspence?
> I already explained why I need to save & restore?
> "Armando Prato" wrote:
worse,[vbcol=seagreen]
hoops.[vbcol=seagreen]
doesn't[vbcol=seagreen]
I[vbcol=seagreen]
|||Hi
The point was that you can create a new database and copy the one table into
that. Then you backup contains the one table. You can then restore that
database and if necessary copy the table again. Alternatives would be to to
script the table and use DTS or BCP to export the data.
You may want to use a CD or USB flash device if this data/backup is bigger
than 1.44 MB.
John
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:CA95E845-DA13-4589-BAEC-D0B2852ED2CB@.microsoft.com...[vbcol=seagreen]
> John
> I only need one table to test with at another location.
> I want to be able to copy one table to a floppy and then restore.
> The backup option copies the whole database.
> How can this be done?
> "John Bell" wrote:
Saving and Restoring a SQL SERVER Table
Can anyone tell me how I can save or copy a SQL Server table and restore it
on another system?
I am running SQL Server 2000.
I see how I can export it to another database on my machine. But I need to
copy the table to another PC.
Any help is appreciated.
RonHi
If you can export it into it's own database then a backup can be made and
restored on the other PC.
If the whole database is not big, then just take the whole backup. It is not
clear why you want the one table and not a whole consistent backup!
http://support.microsoft.com/kb/304692/EN-US/
John
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> Can anyone tell me how I can save or copy a SQL Server table and restore
> it
> on another system?
> I am running SQL Server 2000.
> I see how I can export it to another database on my machine. But I need
> to
> copy the table to another PC.
> Any help is appreciated.
> Ron
>|||John
I only need one table to test with at another location.
I want to be able to copy one table to a floppy and then restore.
The backup option copies the whole database.
How can this be done?
"John Bell" wrote:
> Hi
> If you can export it into it's own database then a backup can be made and
> restored on the other PC.
> If the whole database is not big, then just take the whole backup. It is n
ot
> clear why you want the one table and not a whole consistent backup!
> http://support.microsoft.com/kb/304692/EN-US/
> John
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
>
>|||DTS can copy individual tables and their data to databases
on different servers.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> Can anyone tell me how I can save or copy a SQL Server table and restore
it
> on another system?
> I am running SQL Server 2000.
> I see how I can export it to another database on my machine. But I need
to
> copy the table to another PC.
> Any help is appreciated.
> Ron
>|||I assume DTS stands for Data Transformation Service but that still doesn't
give me a clue how to save and restore an individual table.
"Armando Prato" wrote:
> DTS can copy individual tables and their data to databases
> on different servers.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> it
> to
>
>|||Why do you need to save it and restore it? I think if worse came to worse,
you
could place it on it's on filegroup and go that route.
With DTS, you can copy the table directly and not jump through any hoops.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:893AFAE7-4648-4A1D-81AD-9F23559BDCC0@.microsoft.com...[vbcol=seagreen]
> I assume DTS stands for Data Transformation Service but that still doesn't
> give me a clue how to save and restore an individual table.
> "Armando Prato" wrote:
>
restore[vbcol=seagreen]
need[vbcol=seagreen]|||Are you going to tell me how to do that with DTS or do you just want to keep
me in suspence?
I already explained why I need to save & restore?
"Armando Prato" wrote:
> Why do you need to save it and restore it? I think if worse came to worse
,
> you
> could place it on it's on filegroup and go that route.
> With DTS, you can copy the table directly and not jump through any hoops.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:893AFAE7-4648-4A1D-81AD-9F23559BDCC0@.microsoft.com...
> restore
> need
>
>|||Well, you could've done a wee bit of leg work and researched DTS.
DTS is a very simple tool that can be accessed right from
the Microsoft SQL Server folder on your server. The folder
entry is called "Import and Export Data". From there a wizard
launches that asks you your source and destination servers along
with a screen that lists all available tables on the source where you check
off the
one you want to export.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:DC936A64-DF31-45CB-B560-AADE917754B7@.microsoft.com...
> Are you going to tell me how to do that with DTS or do you just want to
keep[vbcol=seagreen]
> me in suspence?
> I already explained why I need to save & restore?
> "Armando Prato" wrote:
>
worse,[vbcol=seagreen]
hoops.[vbcol=seagreen]
doesn't[vbcol=seagreen]
I[vbcol=seagreen]|||Hi
The point was that you can create a new database and copy the one table into
that. Then you backup contains the one table. You can then restore that
database and if necessary copy the table again. Alternatives would be to to
script the table and use DTS or BCP to export the data.
You may want to use a CD or USB flash device if this data/backup is bigger
than 1.44 MB.
John
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:CA95E845-DA13-4589-BAEC-D0B2852ED2CB@.microsoft.com...[vbcol=seagreen]
> John
> I only need one table to test with at another location.
> I want to be able to copy one table to a floppy and then restore.
> The backup option copies the whole database.
> How can this be done?
> "John Bell" wrote:
>
on another system?
I am running SQL Server 2000.
I see how I can export it to another database on my machine. But I need to
copy the table to another PC.
Any help is appreciated.
RonHi
If you can export it into it's own database then a backup can be made and
restored on the other PC.
If the whole database is not big, then just take the whole backup. It is not
clear why you want the one table and not a whole consistent backup!
http://support.microsoft.com/kb/304692/EN-US/
John
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> Can anyone tell me how I can save or copy a SQL Server table and restore
> it
> on another system?
> I am running SQL Server 2000.
> I see how I can export it to another database on my machine. But I need
> to
> copy the table to another PC.
> Any help is appreciated.
> Ron
>|||John
I only need one table to test with at another location.
I want to be able to copy one table to a floppy and then restore.
The backup option copies the whole database.
How can this be done?
"John Bell" wrote:
> Hi
> If you can export it into it's own database then a backup can be made and
> restored on the other PC.
> If the whole database is not big, then just take the whole backup. It is n
ot
> clear why you want the one table and not a whole consistent backup!
> http://support.microsoft.com/kb/304692/EN-US/
> John
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
>
>|||DTS can copy individual tables and their data to databases
on different servers.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> Can anyone tell me how I can save or copy a SQL Server table and restore
it
> on another system?
> I am running SQL Server 2000.
> I see how I can export it to another database on my machine. But I need
to
> copy the table to another PC.
> Any help is appreciated.
> Ron
>|||I assume DTS stands for Data Transformation Service but that still doesn't
give me a clue how to save and restore an individual table.
"Armando Prato" wrote:
> DTS can copy individual tables and their data to databases
> on different servers.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> it
> to
>
>|||Why do you need to save it and restore it? I think if worse came to worse,
you
could place it on it's on filegroup and go that route.
With DTS, you can copy the table directly and not jump through any hoops.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:893AFAE7-4648-4A1D-81AD-9F23559BDCC0@.microsoft.com...[vbcol=seagreen]
> I assume DTS stands for Data Transformation Service but that still doesn't
> give me a clue how to save and restore an individual table.
> "Armando Prato" wrote:
>
restore[vbcol=seagreen]
need[vbcol=seagreen]|||Are you going to tell me how to do that with DTS or do you just want to keep
me in suspence?
I already explained why I need to save & restore?
"Armando Prato" wrote:
> Why do you need to save it and restore it? I think if worse came to worse
,
> you
> could place it on it's on filegroup and go that route.
> With DTS, you can copy the table directly and not jump through any hoops.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:893AFAE7-4648-4A1D-81AD-9F23559BDCC0@.microsoft.com...
> restore
> need
>
>|||Well, you could've done a wee bit of leg work and researched DTS.
DTS is a very simple tool that can be accessed right from
the Microsoft SQL Server folder on your server. The folder
entry is called "Import and Export Data". From there a wizard
launches that asks you your source and destination servers along
with a screen that lists all available tables on the source where you check
off the
one you want to export.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:DC936A64-DF31-45CB-B560-AADE917754B7@.microsoft.com...
> Are you going to tell me how to do that with DTS or do you just want to
keep[vbcol=seagreen]
> me in suspence?
> I already explained why I need to save & restore?
> "Armando Prato" wrote:
>
worse,[vbcol=seagreen]
hoops.[vbcol=seagreen]
doesn't[vbcol=seagreen]
I[vbcol=seagreen]|||Hi
The point was that you can create a new database and copy the one table into
that. Then you backup contains the one table. You can then restore that
database and if necessary copy the table again. Alternatives would be to to
script the table and use DTS or BCP to export the data.
You may want to use a CD or USB flash device if this data/backup is bigger
than 1.44 MB.
John
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:CA95E845-DA13-4589-BAEC-D0B2852ED2CB@.microsoft.com...[vbcol=seagreen]
> John
> I only need one table to test with at another location.
> I want to be able to copy one table to a floppy and then restore.
> The backup option copies the whole database.
> How can this be done?
> "John Bell" wrote:
>
Saving and Restoring a SQL SERVER Table
Can anyone tell me how I can save or copy a SQL Server table and restore it
on another system?
I am running SQL Server 2000.
I see how I can export it to another database on my machine. But I need to
copy the table to another PC.
Any help is appreciated.
RonHi
If you can export it into it's own database then a backup can be made and
restored on the other PC.
If the whole database is not big, then just take the whole backup. It is not
clear why you want the one table and not a whole consistent backup!
http://support.microsoft.com/kb/304692/EN-US/
John
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> Can anyone tell me how I can save or copy a SQL Server table and restore
> it
> on another system?
> I am running SQL Server 2000.
> I see how I can export it to another database on my machine. But I need
> to
> copy the table to another PC.
> Any help is appreciated.
> Ron
>|||John
I only need one table to test with at another location.
I want to be able to copy one table to a floppy and then restore.
The backup option copies the whole database.
How can this be done?
"John Bell" wrote:
> Hi
> If you can export it into it's own database then a backup can be made and
> restored on the other PC.
> If the whole database is not big, then just take the whole backup. It is not
> clear why you want the one table and not a whole consistent backup!
> http://support.microsoft.com/kb/304692/EN-US/
> John
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> > Can anyone tell me how I can save or copy a SQL Server table and restore
> > it
> > on another system?
> > I am running SQL Server 2000.
> > I see how I can export it to another database on my machine. But I need
> > to
> > copy the table to another PC.
> > Any help is appreciated.
> > Ron
> >
>
>|||DTS can copy individual tables and their data to databases
on different servers.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> Can anyone tell me how I can save or copy a SQL Server table and restore
it
> on another system?
> I am running SQL Server 2000.
> I see how I can export it to another database on my machine. But I need
to
> copy the table to another PC.
> Any help is appreciated.
> Ron
>|||I assume DTS stands for Data Transformation Service but that still doesn't
give me a clue how to save and restore an individual table.
"Armando Prato" wrote:
> DTS can copy individual tables and their data to databases
> on different servers.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> > Can anyone tell me how I can save or copy a SQL Server table and restore
> it
> > on another system?
> > I am running SQL Server 2000.
> > I see how I can export it to another database on my machine. But I need
> to
> > copy the table to another PC.
> > Any help is appreciated.
> > Ron
> >
>
>|||Why do you need to save it and restore it? I think if worse came to worse,
you
could place it on it's on filegroup and go that route.
With DTS, you can copy the table directly and not jump through any hoops.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:893AFAE7-4648-4A1D-81AD-9F23559BDCC0@.microsoft.com...
> I assume DTS stands for Data Transformation Service but that still doesn't
> give me a clue how to save and restore an individual table.
> "Armando Prato" wrote:
> >
> > DTS can copy individual tables and their data to databases
> > on different servers.
> >
> > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> > > Can anyone tell me how I can save or copy a SQL Server table and
restore
> > it
> > > on another system?
> > > I am running SQL Server 2000.
> > > I see how I can export it to another database on my machine. But I
need
> > to
> > > copy the table to another PC.
> > > Any help is appreciated.
> > > Ron
> > >
> >
> >
> >|||Are you going to tell me how to do that with DTS or do you just want to keep
me in suspence?
I already explained why I need to save & restore?
"Armando Prato" wrote:
> Why do you need to save it and restore it? I think if worse came to worse,
> you
> could place it on it's on filegroup and go that route.
> With DTS, you can copy the table directly and not jump through any hoops.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:893AFAE7-4648-4A1D-81AD-9F23559BDCC0@.microsoft.com...
> > I assume DTS stands for Data Transformation Service but that still doesn't
> > give me a clue how to save and restore an individual table.
> >
> > "Armando Prato" wrote:
> >
> > >
> > > DTS can copy individual tables and their data to databases
> > > on different servers.
> > >
> > > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > > news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> > > > Can anyone tell me how I can save or copy a SQL Server table and
> restore
> > > it
> > > > on another system?
> > > > I am running SQL Server 2000.
> > > > I see how I can export it to another database on my machine. But I
> need
> > > to
> > > > copy the table to another PC.
> > > > Any help is appreciated.
> > > > Ron
> > > >
> > >
> > >
> > >
>
>|||Well, you could've done a wee bit of leg work and researched DTS.
DTS is a very simple tool that can be accessed right from
the Microsoft SQL Server folder on your server. The folder
entry is called "Import and Export Data". From there a wizard
launches that asks you your source and destination servers along
with a screen that lists all available tables on the source where you check
off the
one you want to export.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:DC936A64-DF31-45CB-B560-AADE917754B7@.microsoft.com...
> Are you going to tell me how to do that with DTS or do you just want to
keep
> me in suspence?
> I already explained why I need to save & restore?
> "Armando Prato" wrote:
> > Why do you need to save it and restore it? I think if worse came to
worse,
> > you
> > could place it on it's on filegroup and go that route.
> >
> > With DTS, you can copy the table directly and not jump through any
hoops.
> >
> > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > news:893AFAE7-4648-4A1D-81AD-9F23559BDCC0@.microsoft.com...
> > > I assume DTS stands for Data Transformation Service but that still
doesn't
> > > give me a clue how to save and restore an individual table.
> > >
> > > "Armando Prato" wrote:
> > >
> > > >
> > > > DTS can copy individual tables and their data to databases
> > > > on different servers.
> > > >
> > > > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > > > news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> > > > > Can anyone tell me how I can save or copy a SQL Server table and
> > restore
> > > > it
> > > > > on another system?
> > > > > I am running SQL Server 2000.
> > > > > I see how I can export it to another database on my machine. But
I
> > need
> > > > to
> > > > > copy the table to another PC.
> > > > > Any help is appreciated.
> > > > > Ron
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Hi
The point was that you can create a new database and copy the one table into
that. Then you backup contains the one table. You can then restore that
database and if necessary copy the table again. Alternatives would be to to
script the table and use DTS or BCP to export the data.
You may want to use a CD or USB flash device if this data/backup is bigger
than 1.44 MB.
John
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:CA95E845-DA13-4589-BAEC-D0B2852ED2CB@.microsoft.com...
> John
> I only need one table to test with at another location.
> I want to be able to copy one table to a floppy and then restore.
> The backup option copies the whole database.
> How can this be done?
> "John Bell" wrote:
>> Hi
>> If you can export it into it's own database then a backup can be made
>> and
>> restored on the other PC.
>> If the whole database is not big, then just take the whole backup. It is
>> not
>> clear why you want the one table and not a whole consistent backup!
>> http://support.microsoft.com/kb/304692/EN-US/
>> John
>> "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
>> > Can anyone tell me how I can save or copy a SQL Server table and
>> > restore
>> > it
>> > on another system?
>> > I am running SQL Server 2000.
>> > I see how I can export it to another database on my machine. But I
>> > need
>> > to
>> > copy the table to another PC.
>> > Any help is appreciated.
>> > Ron
>> >
>>
on another system?
I am running SQL Server 2000.
I see how I can export it to another database on my machine. But I need to
copy the table to another PC.
Any help is appreciated.
RonHi
If you can export it into it's own database then a backup can be made and
restored on the other PC.
If the whole database is not big, then just take the whole backup. It is not
clear why you want the one table and not a whole consistent backup!
http://support.microsoft.com/kb/304692/EN-US/
John
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> Can anyone tell me how I can save or copy a SQL Server table and restore
> it
> on another system?
> I am running SQL Server 2000.
> I see how I can export it to another database on my machine. But I need
> to
> copy the table to another PC.
> Any help is appreciated.
> Ron
>|||John
I only need one table to test with at another location.
I want to be able to copy one table to a floppy and then restore.
The backup option copies the whole database.
How can this be done?
"John Bell" wrote:
> Hi
> If you can export it into it's own database then a backup can be made and
> restored on the other PC.
> If the whole database is not big, then just take the whole backup. It is not
> clear why you want the one table and not a whole consistent backup!
> http://support.microsoft.com/kb/304692/EN-US/
> John
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> > Can anyone tell me how I can save or copy a SQL Server table and restore
> > it
> > on another system?
> > I am running SQL Server 2000.
> > I see how I can export it to another database on my machine. But I need
> > to
> > copy the table to another PC.
> > Any help is appreciated.
> > Ron
> >
>
>|||DTS can copy individual tables and their data to databases
on different servers.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> Can anyone tell me how I can save or copy a SQL Server table and restore
it
> on another system?
> I am running SQL Server 2000.
> I see how I can export it to another database on my machine. But I need
to
> copy the table to another PC.
> Any help is appreciated.
> Ron
>|||I assume DTS stands for Data Transformation Service but that still doesn't
give me a clue how to save and restore an individual table.
"Armando Prato" wrote:
> DTS can copy individual tables and their data to databases
> on different servers.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> > Can anyone tell me how I can save or copy a SQL Server table and restore
> it
> > on another system?
> > I am running SQL Server 2000.
> > I see how I can export it to another database on my machine. But I need
> to
> > copy the table to another PC.
> > Any help is appreciated.
> > Ron
> >
>
>|||Why do you need to save it and restore it? I think if worse came to worse,
you
could place it on it's on filegroup and go that route.
With DTS, you can copy the table directly and not jump through any hoops.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:893AFAE7-4648-4A1D-81AD-9F23559BDCC0@.microsoft.com...
> I assume DTS stands for Data Transformation Service but that still doesn't
> give me a clue how to save and restore an individual table.
> "Armando Prato" wrote:
> >
> > DTS can copy individual tables and their data to databases
> > on different servers.
> >
> > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> > > Can anyone tell me how I can save or copy a SQL Server table and
restore
> > it
> > > on another system?
> > > I am running SQL Server 2000.
> > > I see how I can export it to another database on my machine. But I
need
> > to
> > > copy the table to another PC.
> > > Any help is appreciated.
> > > Ron
> > >
> >
> >
> >|||Are you going to tell me how to do that with DTS or do you just want to keep
me in suspence?
I already explained why I need to save & restore?
"Armando Prato" wrote:
> Why do you need to save it and restore it? I think if worse came to worse,
> you
> could place it on it's on filegroup and go that route.
> With DTS, you can copy the table directly and not jump through any hoops.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:893AFAE7-4648-4A1D-81AD-9F23559BDCC0@.microsoft.com...
> > I assume DTS stands for Data Transformation Service but that still doesn't
> > give me a clue how to save and restore an individual table.
> >
> > "Armando Prato" wrote:
> >
> > >
> > > DTS can copy individual tables and their data to databases
> > > on different servers.
> > >
> > > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > > news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> > > > Can anyone tell me how I can save or copy a SQL Server table and
> restore
> > > it
> > > > on another system?
> > > > I am running SQL Server 2000.
> > > > I see how I can export it to another database on my machine. But I
> need
> > > to
> > > > copy the table to another PC.
> > > > Any help is appreciated.
> > > > Ron
> > > >
> > >
> > >
> > >
>
>|||Well, you could've done a wee bit of leg work and researched DTS.
DTS is a very simple tool that can be accessed right from
the Microsoft SQL Server folder on your server. The folder
entry is called "Import and Export Data". From there a wizard
launches that asks you your source and destination servers along
with a screen that lists all available tables on the source where you check
off the
one you want to export.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:DC936A64-DF31-45CB-B560-AADE917754B7@.microsoft.com...
> Are you going to tell me how to do that with DTS or do you just want to
keep
> me in suspence?
> I already explained why I need to save & restore?
> "Armando Prato" wrote:
> > Why do you need to save it and restore it? I think if worse came to
worse,
> > you
> > could place it on it's on filegroup and go that route.
> >
> > With DTS, you can copy the table directly and not jump through any
hoops.
> >
> > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > news:893AFAE7-4648-4A1D-81AD-9F23559BDCC0@.microsoft.com...
> > > I assume DTS stands for Data Transformation Service but that still
doesn't
> > > give me a clue how to save and restore an individual table.
> > >
> > > "Armando Prato" wrote:
> > >
> > > >
> > > > DTS can copy individual tables and their data to databases
> > > > on different servers.
> > > >
> > > > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > > > news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
> > > > > Can anyone tell me how I can save or copy a SQL Server table and
> > restore
> > > > it
> > > > > on another system?
> > > > > I am running SQL Server 2000.
> > > > > I see how I can export it to another database on my machine. But
I
> > need
> > > > to
> > > > > copy the table to another PC.
> > > > > Any help is appreciated.
> > > > > Ron
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Hi
The point was that you can create a new database and copy the one table into
that. Then you backup contains the one table. You can then restore that
database and if necessary copy the table again. Alternatives would be to to
script the table and use DTS or BCP to export the data.
You may want to use a CD or USB flash device if this data/backup is bigger
than 1.44 MB.
John
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:CA95E845-DA13-4589-BAEC-D0B2852ED2CB@.microsoft.com...
> John
> I only need one table to test with at another location.
> I want to be able to copy one table to a floppy and then restore.
> The backup option copies the whole database.
> How can this be done?
> "John Bell" wrote:
>> Hi
>> If you can export it into it's own database then a backup can be made
>> and
>> restored on the other PC.
>> If the whole database is not big, then just take the whole backup. It is
>> not
>> clear why you want the one table and not a whole consistent backup!
>> http://support.microsoft.com/kb/304692/EN-US/
>> John
>> "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> news:3AD5917E-4CFF-4A6C-8DC3-54E98F92E6C6@.microsoft.com...
>> > Can anyone tell me how I can save or copy a SQL Server table and
>> > restore
>> > it
>> > on another system?
>> > I am running SQL Server 2000.
>> > I see how I can export it to another database on my machine. But I
>> > need
>> > to
>> > copy the table to another PC.
>> > Any help is appreciated.
>> > Ron
>> >
>>
Tuesday, March 20, 2012
Save some Stored proc in file and create SP from file
Every day we are restoring prod DB in Development env. I need to save before restore users stored proc,
restore DB and after create SP from file.
Thanks.
use
sqlcmd or osql
|||The command-line tools may not be adequate because the defaults do not suffice if your definitions are larger than say 8000 characters. And it will be a pain to automate the scripting of multiple objects and so on. Best is to use SQL-DMO/SMO or DTS/SSIS tasks for this.
Subscribe to:
Posts (Atom)