Hello,
I have one SQL Server on a server. and 3 other (?MSDE?)SQL Databases on
pc's in the plant. I make daily backups on the server of the databases.
The SQL databases on the pc's(I'll call clients) don't run sql agent. Can I
back up these databases from my central server. Each of the clients
are(should be) setup alike. So, with my job I would need to maybe zip these
files into a file so I know which machine it came from. For example after I
backup the master, msdb,and user DB, zip into a file called, pc1, pc2, pc3.
Any suggestions?
Thanks,
Brian
That would require some programming. I'd look into the DMO programming API (SMO if you are on 2005).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brian Shafer" <BrianShafer@.discussions.microsoft.com> wrote in message
news:72F32CDD-C790-4B2C-AE45-7AF2D5083BB7@.microsoft.com...
> Hello,
> I have one SQL Server on a server. and 3 other (?MSDE?)SQL Databases on
> pc's in the plant. I make daily backups on the server of the databases.
> The SQL databases on the pc's(I'll call clients) don't run sql agent. Can I
> back up these databases from my central server. Each of the clients
> are(should be) setup alike. So, with my job I would need to maybe zip these
> files into a file so I know which machine it came from. For example after I
> backup the master, msdb,and user DB, zip into a file called, pc1, pc2, pc3.
> Any suggestions?
> Thanks,
> Brian
|||Brian Shafer wrote:
> Hello,
> I have one SQL Server on a server. and 3 other (?MSDE?)SQL Databases on
> pc's in the plant. I make daily backups on the server of the databases.
> The SQL databases on the pc's(I'll call clients) don't run sql agent. Can I
> back up these databases from my central server. Each of the clients
> are(should be) setup alike. So, with my job I would need to maybe zip these
> files into a file so I know which machine it came from. For example after I
> backup the master, msdb,and user DB, zip into a file called, pc1, pc2, pc3.
> Any suggestions?
> Thanks,
> Brian
You could try something like this:
Create a stored procedure on each remote instance to backup the databases using T-SQL commands.
Link the remote instances to your central server.
Create DTS packages on your central server to run the stored procedures on the remote instances and then FTP their
resulting backup files to some other server.
Showing posts with label backuping. Show all posts
Showing posts with label backuping. Show all posts
Friday, February 24, 2012
Backuping up Remote Databases
Hello,
I have one SQL Server on a server. and 3 other (?MSDE?)SQL Databases on
pc's in the plant. I make daily backups on the server of the databases.
The SQL databases on the pc's(I'll call clients) don't run sql agent. Can I
back up these databases from my central server. Each of the clients
are(should be) setup alike. So, with my job I would need to maybe zip these
files into a file so I know which machine it came from. For example after I
backup the master, msdb,and user DB, zip into a file called, pc1, pc2, pc3.
Any suggestions?
Thanks,
BrianThat would require some programming. I'd look into the DMO programming API (SMO if you are on 2005).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brian Shafer" <BrianShafer@.discussions.microsoft.com> wrote in message
news:72F32CDD-C790-4B2C-AE45-7AF2D5083BB7@.microsoft.com...
> Hello,
> I have one SQL Server on a server. and 3 other (?MSDE?)SQL Databases on
> pc's in the plant. I make daily backups on the server of the databases.
> The SQL databases on the pc's(I'll call clients) don't run sql agent. Can I
> back up these databases from my central server. Each of the clients
> are(should be) setup alike. So, with my job I would need to maybe zip these
> files into a file so I know which machine it came from. For example after I
> backup the master, msdb,and user DB, zip into a file called, pc1, pc2, pc3.
> Any suggestions?
> Thanks,
> Brian|||Brian Shafer wrote:
> Hello,
> I have one SQL Server on a server. and 3 other (?MSDE?)SQL Databases on
> pc's in the plant. I make daily backups on the server of the databases.
> The SQL databases on the pc's(I'll call clients) don't run sql agent. Can I
> back up these databases from my central server. Each of the clients
> are(should be) setup alike. So, with my job I would need to maybe zip these
> files into a file so I know which machine it came from. For example after I
> backup the master, msdb,and user DB, zip into a file called, pc1, pc2, pc3.
> Any suggestions?
> Thanks,
> Brian
You could try something like this:
Create a stored procedure on each remote instance to backup the databases using T-SQL commands.
Link the remote instances to your central server.
Create DTS packages on your central server to run the stored procedures on the remote instances and then FTP their
resulting backup files to some other server.
I have one SQL Server on a server. and 3 other (?MSDE?)SQL Databases on
pc's in the plant. I make daily backups on the server of the databases.
The SQL databases on the pc's(I'll call clients) don't run sql agent. Can I
back up these databases from my central server. Each of the clients
are(should be) setup alike. So, with my job I would need to maybe zip these
files into a file so I know which machine it came from. For example after I
backup the master, msdb,and user DB, zip into a file called, pc1, pc2, pc3.
Any suggestions?
Thanks,
BrianThat would require some programming. I'd look into the DMO programming API (SMO if you are on 2005).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brian Shafer" <BrianShafer@.discussions.microsoft.com> wrote in message
news:72F32CDD-C790-4B2C-AE45-7AF2D5083BB7@.microsoft.com...
> Hello,
> I have one SQL Server on a server. and 3 other (?MSDE?)SQL Databases on
> pc's in the plant. I make daily backups on the server of the databases.
> The SQL databases on the pc's(I'll call clients) don't run sql agent. Can I
> back up these databases from my central server. Each of the clients
> are(should be) setup alike. So, with my job I would need to maybe zip these
> files into a file so I know which machine it came from. For example after I
> backup the master, msdb,and user DB, zip into a file called, pc1, pc2, pc3.
> Any suggestions?
> Thanks,
> Brian|||Brian Shafer wrote:
> Hello,
> I have one SQL Server on a server. and 3 other (?MSDE?)SQL Databases on
> pc's in the plant. I make daily backups on the server of the databases.
> The SQL databases on the pc's(I'll call clients) don't run sql agent. Can I
> back up these databases from my central server. Each of the clients
> are(should be) setup alike. So, with my job I would need to maybe zip these
> files into a file so I know which machine it came from. For example after I
> backup the master, msdb,and user DB, zip into a file called, pc1, pc2, pc3.
> Any suggestions?
> Thanks,
> Brian
You could try something like this:
Create a stored procedure on each remote instance to backup the databases using T-SQL commands.
Link the remote instances to your central server.
Create DTS packages on your central server to run the stored procedures on the remote instances and then FTP their
resulting backup files to some other server.
Backuping up Remote Databases
Hello,
I have one SQL Server on a server. and 3 other (?MSDE?)SQL Databases on
pc's in the plant. I make daily backups on the server of the databases.
The SQL databases on the pc's(I'll call clients) don't run sql agent. Can
I
back up these databases from my central server. Each of the clients
are(should be) setup alike. So, with my job I would need to maybe zip these
files into a file so I know which machine it came from. For example after I
backup the master, msdb,and user DB, zip into a file called, pc1, pc2, pc3.
Any suggestions?
Thanks,
BrianThat would require some programming. I'd look into the DMO programming API (
SMO if you are on 2005).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brian Shafer" <BrianShafer@.discussions.microsoft.com> wrote in message
news:72F32CDD-C790-4B2C-AE45-7AF2D5083BB7@.microsoft.com...
> Hello,
> I have one SQL Server on a server. and 3 other (?MSDE?)SQL Databases on
> pc's in the plant. I make daily backups on the server of the databases.
> The SQL databases on the pc's(I'll call clients) don't run sql agent. Ca
n I
> back up these databases from my central server. Each of the clients
> are(should be) setup alike. So, with my job I would need to maybe zip the
se
> files into a file so I know which machine it came from. For example after
I
> backup the master, msdb,and user DB, zip into a file called, pc1, pc2, pc3
.
> Any suggestions?
> Thanks,
> Brian|||Brian Shafer wrote:
> Hello,
> I have one SQL Server on a server. and 3 other (?MSDE?)SQL Databases on
> pc's in the plant. I make daily backups on the server of the databases.
> The SQL databases on the pc's(I'll call clients) don't run sql agent. Ca
n I
> back up these databases from my central server. Each of the clients
> are(should be) setup alike. So, with my job I would need to maybe zip the
se
> files into a file so I know which machine it came from. For example after
I
> backup the master, msdb,and user DB, zip into a file called, pc1, pc2, pc3
.
> Any suggestions?
> Thanks,
> Brian
You could try something like this:
Create a stored procedure on each remote instance to backup the databases us
ing T-SQL commands.
Link the remote instances to your central server.
Create DTS packages on your central server to run the stored procedures on t
he remote instances and then FTP their
resulting backup files to some other server.
I have one SQL Server on a server. and 3 other (?MSDE?)SQL Databases on
pc's in the plant. I make daily backups on the server of the databases.
The SQL databases on the pc's(I'll call clients) don't run sql agent. Can
I
back up these databases from my central server. Each of the clients
are(should be) setup alike. So, with my job I would need to maybe zip these
files into a file so I know which machine it came from. For example after I
backup the master, msdb,and user DB, zip into a file called, pc1, pc2, pc3.
Any suggestions?
Thanks,
BrianThat would require some programming. I'd look into the DMO programming API (
SMO if you are on 2005).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brian Shafer" <BrianShafer@.discussions.microsoft.com> wrote in message
news:72F32CDD-C790-4B2C-AE45-7AF2D5083BB7@.microsoft.com...
> Hello,
> I have one SQL Server on a server. and 3 other (?MSDE?)SQL Databases on
> pc's in the plant. I make daily backups on the server of the databases.
> The SQL databases on the pc's(I'll call clients) don't run sql agent. Ca
n I
> back up these databases from my central server. Each of the clients
> are(should be) setup alike. So, with my job I would need to maybe zip the
se
> files into a file so I know which machine it came from. For example after
I
> backup the master, msdb,and user DB, zip into a file called, pc1, pc2, pc3
.
> Any suggestions?
> Thanks,
> Brian|||Brian Shafer wrote:
> Hello,
> I have one SQL Server on a server. and 3 other (?MSDE?)SQL Databases on
> pc's in the plant. I make daily backups on the server of the databases.
> The SQL databases on the pc's(I'll call clients) don't run sql agent. Ca
n I
> back up these databases from my central server. Each of the clients
> are(should be) setup alike. So, with my job I would need to maybe zip the
se
> files into a file so I know which machine it came from. For example after
I
> backup the master, msdb,and user DB, zip into a file called, pc1, pc2, pc3
.
> Any suggestions?
> Thanks,
> Brian
You could try something like this:
Create a stored procedure on each remote instance to backup the databases us
ing T-SQL commands.
Link the remote instances to your central server.
Create DTS packages on your central server to run the stored procedures on t
he remote instances and then FTP their
resulting backup files to some other server.
Backuping up multiple databases at once
I'm going through the docs and I can't find a way to backup multiple databases at once, rather than them individually. I see you can create a backup device, but you have to select every individual database. I know in 2000 you could do it ....
Are you referring to using a maintanence plan or t-sql? I assume a maintanence plan?
Regardless, you can't backup multiple db's in a single backup operation...what you can do is create multiple backup statements, one for each database (which is what would happen in SQL 2000 if you chose multiple db's from the GUI), then each database will be backed up serially (i.e. one after the other), and would look like this in t-sql:
backup db1 to disk....
backup db2 to disk....
backup db3 to disk....
If you'd like to backup multiple db's in parallel, you could take each of your t-sql backup commands and run them across seperate connections, or create seperate scheduled jobs for each...
I don't have the 2005 GUI on this computer, so I can't validate/verify the selection of multiple db's from it, but I would be surprised if you can't do it from the Maintanence Plan...seems odd...I'll check later and get back to you if nobody else responds before I get to it...
HTH,
|||Thanks Chad
That works out just fine.. i can do it individually . I thought I was missing an obvious setting or something . I'm not that experienced with 2005 and I wasn't sure.
I appreciate the response.
|||Backup task UI of Maintenace plan provides selection of multiple databases.
Gops Dwarak
backuping to tape
Hello there
To the server with sql server there is also backup tape.
is there a way to backup the database directly to dape?
use
BACKUP DATABASE TO TAPE
read BACKUP DATABASE from BOL for more info
vt
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OvVVz%23EGHHA.4580@.TK2MSFTNGP05.phx.gbl...
> Hello there
> To the server with sql server there is also backup tape.
> is there a way to backup the database directly to dape?
>
|||Roy
While tape backups are a good thing. You can store them off site for
disaster recovery purposes. It takes longer to both backup and restore from
tape.
Unless disk space is really an issue, it's best to do both.
Backup to disk first, it's quicker so less impact on your application. Then
copy the disk backup to tape and store off site. Very often if you do need to
restore a database, it will be the last backup, so you can restore quicker
from disk.
We usually keep the last two full backups on disk, and two days worth of
transaction logs. This gives us good flexibility and although we have a lot
of SQL Server based systems, we virtually never need the tape based backups.
Our backup strategy was designed to make that the case.
If required, restores from disk are much quicker and quite often time is an
issue if you need to perform a restore.
Hope this helps
John
"vt" wrote:
> use
> BACKUP DATABASE TO TAPE
> read BACKUP DATABASE from BOL for more info
> vt
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:OvVVz%23EGHHA.4580@.TK2MSFTNGP05.phx.gbl...
>
>
|||Whell John:
my question came from argue with the system administrators that said: we
need sql agent third party program to backup to tape. and it cost at least
750$
as you explain, it is not true and that is what i need to know.
About backup, there is a problem of storage: my HD size is 130GB, and it
holds all sql and backups.
I need to backup 5 databases every day which their size approx 3.5 gb of
data and log. so after two weeks of backuping there is no more free space to
use.
thanks
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:5F9AC2AA-4049-48F4-848B-C526F4FF5B38@.microsoft.com...[vbcol=seagreen]
> Roy
> While tape backups are a good thing. You can store them off site for
> disaster recovery purposes. It takes longer to both backup and restore
> from
> tape.
> Unless disk space is really an issue, it's best to do both.
> Backup to disk first, it's quicker so less impact on your application.
> Then
> copy the disk backup to tape and store off site. Very often if you do need
> to
> restore a database, it will be the last backup, so you can restore quicker
> from disk.
> We usually keep the last two full backups on disk, and two days worth of
> transaction logs. This gives us good flexibility and although we have a
> lot
> of SQL Server based systems, we virtually never need the tape based
> backups.
> Our backup strategy was designed to make that the case.
> If required, restores from disk are much quicker and quite often time is
> an
> issue if you need to perform a restore.
> Hope this helps
> John
> "vt" wrote:
|||I suggest you keep only the most recent SQL Server backups on disk,
depending on your disk space constraints. You can archive those backup
files to tape for offsite and long term storage.
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OSYRMQGGHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Whell John:
> my question came from argue with the system administrators that said: we
> need sql agent third party program to backup to tape. and it cost at least
> 750$
> as you explain, it is not true and that is what i need to know.
> About backup, there is a problem of storage: my HD size is 130GB, and it
> holds all sql and backups.
> I need to backup 5 databases every day which their size approx 3.5 gb of
> data and log. so after two weeks of backuping there is no more free space
> to use.
> thanks
> "John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
> message news:5F9AC2AA-4049-48F4-848B-C526F4FF5B38@.microsoft.com...
>
To the server with sql server there is also backup tape.
is there a way to backup the database directly to dape?
use
BACKUP DATABASE TO TAPE
read BACKUP DATABASE from BOL for more info
vt
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OvVVz%23EGHHA.4580@.TK2MSFTNGP05.phx.gbl...
> Hello there
> To the server with sql server there is also backup tape.
> is there a way to backup the database directly to dape?
>
|||Roy
While tape backups are a good thing. You can store them off site for
disaster recovery purposes. It takes longer to both backup and restore from
tape.
Unless disk space is really an issue, it's best to do both.
Backup to disk first, it's quicker so less impact on your application. Then
copy the disk backup to tape and store off site. Very often if you do need to
restore a database, it will be the last backup, so you can restore quicker
from disk.
We usually keep the last two full backups on disk, and two days worth of
transaction logs. This gives us good flexibility and although we have a lot
of SQL Server based systems, we virtually never need the tape based backups.
Our backup strategy was designed to make that the case.
If required, restores from disk are much quicker and quite often time is an
issue if you need to perform a restore.
Hope this helps
John
"vt" wrote:
> use
> BACKUP DATABASE TO TAPE
> read BACKUP DATABASE from BOL for more info
> vt
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:OvVVz%23EGHHA.4580@.TK2MSFTNGP05.phx.gbl...
>
>
|||Whell John:
my question came from argue with the system administrators that said: we
need sql agent third party program to backup to tape. and it cost at least
750$
as you explain, it is not true and that is what i need to know.
About backup, there is a problem of storage: my HD size is 130GB, and it
holds all sql and backups.
I need to backup 5 databases every day which their size approx 3.5 gb of
data and log. so after two weeks of backuping there is no more free space to
use.
thanks
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:5F9AC2AA-4049-48F4-848B-C526F4FF5B38@.microsoft.com...[vbcol=seagreen]
> Roy
> While tape backups are a good thing. You can store them off site for
> disaster recovery purposes. It takes longer to both backup and restore
> from
> tape.
> Unless disk space is really an issue, it's best to do both.
> Backup to disk first, it's quicker so less impact on your application.
> Then
> copy the disk backup to tape and store off site. Very often if you do need
> to
> restore a database, it will be the last backup, so you can restore quicker
> from disk.
> We usually keep the last two full backups on disk, and two days worth of
> transaction logs. This gives us good flexibility and although we have a
> lot
> of SQL Server based systems, we virtually never need the tape based
> backups.
> Our backup strategy was designed to make that the case.
> If required, restores from disk are much quicker and quite often time is
> an
> issue if you need to perform a restore.
> Hope this helps
> John
> "vt" wrote:
|||I suggest you keep only the most recent SQL Server backups on disk,
depending on your disk space constraints. You can archive those backup
files to tape for offsite and long term storage.
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OSYRMQGGHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Whell John:
> my question came from argue with the system administrators that said: we
> need sql agent third party program to backup to tape. and it cost at least
> 750$
> as you explain, it is not true and that is what i need to know.
> About backup, there is a problem of storage: my HD size is 130GB, and it
> holds all sql and backups.
> I need to backup 5 databases every day which their size approx 3.5 gb of
> data and log. so after two weeks of backuping there is no more free space
> to use.
> thanks
> "John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
> message news:5F9AC2AA-4049-48F4-848B-C526F4FF5B38@.microsoft.com...
>
backuping to tape
Hello there
To the server with sql server there is also backup tape.
is there a way to backup the database directly to dape?use
BACKUP DATABASE TO TAPE
read BACKUP DATABASE from BOL for more info
vt
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OvVVz%23EGHHA.4580@.TK2MSFTNGP05.phx.gbl...
> Hello there
> To the server with sql server there is also backup tape.
> is there a way to backup the database directly to dape?
>|||Roy
While tape backups are a good thing. You can store them off site for
disaster recovery purposes. It takes longer to both backup and restore from
tape.
Unless disk space is really an issue, it's best to do both.
Backup to disk first, it's quicker so less impact on your application. Then
copy the disk backup to tape and store off site. Very often if you do need t
o
restore a database, it will be the last backup, so you can restore quicker
from disk.
We usually keep the last two full backups on disk, and two days worth of
transaction logs. This gives us good flexibility and although we have a lot
of SQL Server based systems, we virtually never need the tape based backups.
Our backup strategy was designed to make that the case.
If required, restores from disk are much quicker and quite often time is an
issue if you need to perform a restore.
Hope this helps
John
"vt" wrote:
> use
> BACKUP DATABASE TO TAPE
> read BACKUP DATABASE from BOL for more info
> vt
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:OvVVz%23EGHHA.4580@.TK2MSFTNGP05.phx.gbl...
>
>|||Whell John:
my question came from argue with the system administrators that said: we
need sql agent third party program to backup to tape. and it cost at least
750$
as you explain, it is not true and that is what i need to know.
About backup, there is a problem of storage: my HD size is 130GB, and it
holds all sql and backups.
I need to backup 5 databases every day which their size approx 3.5 gb of
data and log. so after two weeks of backuping there is no more free space to
use.
thanks
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:5F9AC2AA-4049-48F4-848B-C526F4FF5B38@.microsoft.com...[vbcol=seagreen]
> Roy
> While tape backups are a good thing. You can store them off site for
> disaster recovery purposes. It takes longer to both backup and restore
> from
> tape.
> Unless disk space is really an issue, it's best to do both.
> Backup to disk first, it's quicker so less impact on your application.
> Then
> copy the disk backup to tape and store off site. Very often if you do need
> to
> restore a database, it will be the last backup, so you can restore quicker
> from disk.
> We usually keep the last two full backups on disk, and two days worth of
> transaction logs. This gives us good flexibility and although we have a
> lot
> of SQL Server based systems, we virtually never need the tape based
> backups.
> Our backup strategy was designed to make that the case.
> If required, restores from disk are much quicker and quite often time is
> an
> issue if you need to perform a restore.
> Hope this helps
> John
> "vt" wrote:
>|||I suggest you keep only the most recent SQL Server backups on disk,
depending on your disk space constraints. You can archive those backup
files to tape for offsite and long term storage.
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OSYRMQGGHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Whell John:
> my question came from argue with the system administrators that said: we
> need sql agent third party program to backup to tape. and it cost at least
> 750$
> as you explain, it is not true and that is what i need to know.
> About backup, there is a problem of storage: my HD size is 130GB, and it
> holds all sql and backups.
> I need to backup 5 databases every day which their size approx 3.5 gb of
> data and log. so after two weeks of backuping there is no more free space
> to use.
> thanks
> "John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
> message news:5F9AC2AA-4049-48F4-848B-C526F4FF5B38@.microsoft.com...
>
To the server with sql server there is also backup tape.
is there a way to backup the database directly to dape?use
BACKUP DATABASE TO TAPE
read BACKUP DATABASE from BOL for more info
vt
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OvVVz%23EGHHA.4580@.TK2MSFTNGP05.phx.gbl...
> Hello there
> To the server with sql server there is also backup tape.
> is there a way to backup the database directly to dape?
>|||Roy
While tape backups are a good thing. You can store them off site for
disaster recovery purposes. It takes longer to both backup and restore from
tape.
Unless disk space is really an issue, it's best to do both.
Backup to disk first, it's quicker so less impact on your application. Then
copy the disk backup to tape and store off site. Very often if you do need t
o
restore a database, it will be the last backup, so you can restore quicker
from disk.
We usually keep the last two full backups on disk, and two days worth of
transaction logs. This gives us good flexibility and although we have a lot
of SQL Server based systems, we virtually never need the tape based backups.
Our backup strategy was designed to make that the case.
If required, restores from disk are much quicker and quite often time is an
issue if you need to perform a restore.
Hope this helps
John
"vt" wrote:
> use
> BACKUP DATABASE TO TAPE
> read BACKUP DATABASE from BOL for more info
> vt
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:OvVVz%23EGHHA.4580@.TK2MSFTNGP05.phx.gbl...
>
>|||Whell John:
my question came from argue with the system administrators that said: we
need sql agent third party program to backup to tape. and it cost at least
750$
as you explain, it is not true and that is what i need to know.
About backup, there is a problem of storage: my HD size is 130GB, and it
holds all sql and backups.
I need to backup 5 databases every day which their size approx 3.5 gb of
data and log. so after two weeks of backuping there is no more free space to
use.
thanks
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:5F9AC2AA-4049-48F4-848B-C526F4FF5B38@.microsoft.com...[vbcol=seagreen]
> Roy
> While tape backups are a good thing. You can store them off site for
> disaster recovery purposes. It takes longer to both backup and restore
> from
> tape.
> Unless disk space is really an issue, it's best to do both.
> Backup to disk first, it's quicker so less impact on your application.
> Then
> copy the disk backup to tape and store off site. Very often if you do need
> to
> restore a database, it will be the last backup, so you can restore quicker
> from disk.
> We usually keep the last two full backups on disk, and two days worth of
> transaction logs. This gives us good flexibility and although we have a
> lot
> of SQL Server based systems, we virtually never need the tape based
> backups.
> Our backup strategy was designed to make that the case.
> If required, restores from disk are much quicker and quite often time is
> an
> issue if you need to perform a restore.
> Hope this helps
> John
> "vt" wrote:
>|||I suggest you keep only the most recent SQL Server backups on disk,
depending on your disk space constraints. You can archive those backup
files to tape for offsite and long term storage.
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OSYRMQGGHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Whell John:
> my question came from argue with the system administrators that said: we
> need sql agent third party program to backup to tape. and it cost at least
> 750$
> as you explain, it is not true and that is what i need to know.
> About backup, there is a problem of storage: my HD size is 130GB, and it
> holds all sql and backups.
> I need to backup 5 databases every day which their size approx 3.5 gb of
> data and log. so after two weeks of backuping there is no more free space
> to use.
> thanks
> "John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
> message news:5F9AC2AA-4049-48F4-848B-C526F4FF5B38@.microsoft.com...
>
backuping to tape
Hello there
To the server with sql server there is also backup tape.
is there a way to backup the database directly to dape?use
BACKUP DATABASE TO TAPE
read BACKUP DATABASE from BOL for more info
vt
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OvVVz%23EGHHA.4580@.TK2MSFTNGP05.phx.gbl...
> Hello there
> To the server with sql server there is also backup tape.
> is there a way to backup the database directly to dape?
>|||Roy
While tape backups are a good thing. You can store them off site for
disaster recovery purposes. It takes longer to both backup and restore from
tape.
Unless disk space is really an issue, it's best to do both.
Backup to disk first, it's quicker so less impact on your application. Then
copy the disk backup to tape and store off site. Very often if you do need to
restore a database, it will be the last backup, so you can restore quicker
from disk.
We usually keep the last two full backups on disk, and two days worth of
transaction logs. This gives us good flexibility and although we have a lot
of SQL Server based systems, we virtually never need the tape based backups.
Our backup strategy was designed to make that the case.
If required, restores from disk are much quicker and quite often time is an
issue if you need to perform a restore.
Hope this helps
John
"vt" wrote:
> use
> BACKUP DATABASE TO TAPE
> read BACKUP DATABASE from BOL for more info
> vt
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:OvVVz%23EGHHA.4580@.TK2MSFTNGP05.phx.gbl...
> > Hello there
> >
> > To the server with sql server there is also backup tape.
> >
> > is there a way to backup the database directly to dape?
> >
>
>|||Whell John:
my question came from argue with the system administrators that said: we
need sql agent third party program to backup to tape. and it cost at least
750$
as you explain, it is not true and that is what i need to know.
About backup, there is a problem of storage: my HD size is 130GB, and it
holds all sql and backups.
I need to backup 5 databases every day which their size approx 3.5 gb of
data and log. so after two weeks of backuping there is no more free space to
use.
thanks
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:5F9AC2AA-4049-48F4-848B-C526F4FF5B38@.microsoft.com...
> Roy
> While tape backups are a good thing. You can store them off site for
> disaster recovery purposes. It takes longer to both backup and restore
> from
> tape.
> Unless disk space is really an issue, it's best to do both.
> Backup to disk first, it's quicker so less impact on your application.
> Then
> copy the disk backup to tape and store off site. Very often if you do need
> to
> restore a database, it will be the last backup, so you can restore quicker
> from disk.
> We usually keep the last two full backups on disk, and two days worth of
> transaction logs. This gives us good flexibility and although we have a
> lot
> of SQL Server based systems, we virtually never need the tape based
> backups.
> Our backup strategy was designed to make that the case.
> If required, restores from disk are much quicker and quite often time is
> an
> issue if you need to perform a restore.
> Hope this helps
> John
> "vt" wrote:
>> use
>> BACKUP DATABASE TO TAPE
>> read BACKUP DATABASE from BOL for more info
>> vt
>>
>> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
>> news:OvVVz%23EGHHA.4580@.TK2MSFTNGP05.phx.gbl...
>> > Hello there
>> >
>> > To the server with sql server there is also backup tape.
>> >
>> > is there a way to backup the database directly to dape?
>> >
>>|||I suggest you keep only the most recent SQL Server backups on disk,
depending on your disk space constraints. You can archive those backup
files to tape for offsite and long term storage.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OSYRMQGGHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Whell John:
> my question came from argue with the system administrators that said: we
> need sql agent third party program to backup to tape. and it cost at least
> 750$
> as you explain, it is not true and that is what i need to know.
> About backup, there is a problem of storage: my HD size is 130GB, and it
> holds all sql and backups.
> I need to backup 5 databases every day which their size approx 3.5 gb of
> data and log. so after two weeks of backuping there is no more free space
> to use.
> thanks
> "John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
> message news:5F9AC2AA-4049-48F4-848B-C526F4FF5B38@.microsoft.com...
>> Roy
>> While tape backups are a good thing. You can store them off site for
>> disaster recovery purposes. It takes longer to both backup and restore
>> from
>> tape.
>> Unless disk space is really an issue, it's best to do both.
>> Backup to disk first, it's quicker so less impact on your application.
>> Then
>> copy the disk backup to tape and store off site. Very often if you do
>> need to
>> restore a database, it will be the last backup, so you can restore
>> quicker
>> from disk.
>> We usually keep the last two full backups on disk, and two days worth of
>> transaction logs. This gives us good flexibility and although we have a
>> lot
>> of SQL Server based systems, we virtually never need the tape based
>> backups.
>> Our backup strategy was designed to make that the case.
>> If required, restores from disk are much quicker and quite often time is
>> an
>> issue if you need to perform a restore.
>> Hope this helps
>> John
>> "vt" wrote:
>> use
>> BACKUP DATABASE TO TAPE
>> read BACKUP DATABASE from BOL for more info
>> vt
>>
>> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
>> news:OvVVz%23EGHHA.4580@.TK2MSFTNGP05.phx.gbl...
>> > Hello there
>> >
>> > To the server with sql server there is also backup tape.
>> >
>> > is there a way to backup the database directly to dape?
>> >
>>
>
To the server with sql server there is also backup tape.
is there a way to backup the database directly to dape?use
BACKUP DATABASE TO TAPE
read BACKUP DATABASE from BOL for more info
vt
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OvVVz%23EGHHA.4580@.TK2MSFTNGP05.phx.gbl...
> Hello there
> To the server with sql server there is also backup tape.
> is there a way to backup the database directly to dape?
>|||Roy
While tape backups are a good thing. You can store them off site for
disaster recovery purposes. It takes longer to both backup and restore from
tape.
Unless disk space is really an issue, it's best to do both.
Backup to disk first, it's quicker so less impact on your application. Then
copy the disk backup to tape and store off site. Very often if you do need to
restore a database, it will be the last backup, so you can restore quicker
from disk.
We usually keep the last two full backups on disk, and two days worth of
transaction logs. This gives us good flexibility and although we have a lot
of SQL Server based systems, we virtually never need the tape based backups.
Our backup strategy was designed to make that the case.
If required, restores from disk are much quicker and quite often time is an
issue if you need to perform a restore.
Hope this helps
John
"vt" wrote:
> use
> BACKUP DATABASE TO TAPE
> read BACKUP DATABASE from BOL for more info
> vt
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:OvVVz%23EGHHA.4580@.TK2MSFTNGP05.phx.gbl...
> > Hello there
> >
> > To the server with sql server there is also backup tape.
> >
> > is there a way to backup the database directly to dape?
> >
>
>|||Whell John:
my question came from argue with the system administrators that said: we
need sql agent third party program to backup to tape. and it cost at least
750$
as you explain, it is not true and that is what i need to know.
About backup, there is a problem of storage: my HD size is 130GB, and it
holds all sql and backups.
I need to backup 5 databases every day which their size approx 3.5 gb of
data and log. so after two weeks of backuping there is no more free space to
use.
thanks
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:5F9AC2AA-4049-48F4-848B-C526F4FF5B38@.microsoft.com...
> Roy
> While tape backups are a good thing. You can store them off site for
> disaster recovery purposes. It takes longer to both backup and restore
> from
> tape.
> Unless disk space is really an issue, it's best to do both.
> Backup to disk first, it's quicker so less impact on your application.
> Then
> copy the disk backup to tape and store off site. Very often if you do need
> to
> restore a database, it will be the last backup, so you can restore quicker
> from disk.
> We usually keep the last two full backups on disk, and two days worth of
> transaction logs. This gives us good flexibility and although we have a
> lot
> of SQL Server based systems, we virtually never need the tape based
> backups.
> Our backup strategy was designed to make that the case.
> If required, restores from disk are much quicker and quite often time is
> an
> issue if you need to perform a restore.
> Hope this helps
> John
> "vt" wrote:
>> use
>> BACKUP DATABASE TO TAPE
>> read BACKUP DATABASE from BOL for more info
>> vt
>>
>> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
>> news:OvVVz%23EGHHA.4580@.TK2MSFTNGP05.phx.gbl...
>> > Hello there
>> >
>> > To the server with sql server there is also backup tape.
>> >
>> > is there a way to backup the database directly to dape?
>> >
>>|||I suggest you keep only the most recent SQL Server backups on disk,
depending on your disk space constraints. You can archive those backup
files to tape for offsite and long term storage.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OSYRMQGGHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Whell John:
> my question came from argue with the system administrators that said: we
> need sql agent third party program to backup to tape. and it cost at least
> 750$
> as you explain, it is not true and that is what i need to know.
> About backup, there is a problem of storage: my HD size is 130GB, and it
> holds all sql and backups.
> I need to backup 5 databases every day which their size approx 3.5 gb of
> data and log. so after two weeks of backuping there is no more free space
> to use.
> thanks
> "John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
> message news:5F9AC2AA-4049-48F4-848B-C526F4FF5B38@.microsoft.com...
>> Roy
>> While tape backups are a good thing. You can store them off site for
>> disaster recovery purposes. It takes longer to both backup and restore
>> from
>> tape.
>> Unless disk space is really an issue, it's best to do both.
>> Backup to disk first, it's quicker so less impact on your application.
>> Then
>> copy the disk backup to tape and store off site. Very often if you do
>> need to
>> restore a database, it will be the last backup, so you can restore
>> quicker
>> from disk.
>> We usually keep the last two full backups on disk, and two days worth of
>> transaction logs. This gives us good flexibility and although we have a
>> lot
>> of SQL Server based systems, we virtually never need the tape based
>> backups.
>> Our backup strategy was designed to make that the case.
>> If required, restores from disk are much quicker and quite often time is
>> an
>> issue if you need to perform a restore.
>> Hope this helps
>> John
>> "vt" wrote:
>> use
>> BACKUP DATABASE TO TAPE
>> read BACKUP DATABASE from BOL for more info
>> vt
>>
>> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
>> news:OvVVz%23EGHHA.4580@.TK2MSFTNGP05.phx.gbl...
>> > Hello there
>> >
>> > To the server with sql server there is also backup tape.
>> >
>> > is there a way to backup the database directly to dape?
>> >
>>
>
backuping logins + passwords.
I can backup my logins but without passwords....using SQL 2005 , generate sql scripts...
any idea i can generate a SQL script with the passwords in cleartext? i like to recreate them in my test database later.
As MSSQL 2005 doesn't store passwords (it stores only password hashes), there is no way to get them in clear text. If you want to back up your logins, you should backup master database; if you want to thransfer them to another server, you should use the DTS "Transfer Login Task" (just don't know analogue in MSSQL 2005 SSIS, it is possible that task didn't change the name).
Anyway, maybe it would be useful : http://search.microsoft.com/results.aspx?q=Transfer+Logins+and+Passwords+Between+Instances+of+SQL+Server&l=1&mkt=en-US&FORM=QBME1
Subscribe to:
Posts (Atom)