|
Home > Archive > MS SQL Server > July 2005 > full server backup
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
full server backup
|
|
| Oren101 2005-07-28, 7:23 am |
| is that possible to make a full server backup which include all databases in
the current server , DTS packages and everything else in one single backup
file?
| |
| Narayana Vyas Kondreddi 2005-07-28, 7:23 am |
| No. The backup restore mechanism works at database level, not server level.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/
"Oren101" <Oren101@discussions.microsoft.com> wrote in message
news:AAA53155-0EED-47FA-8CCD- 08BF0DF3A3E9@microso
ft.com...
is that possible to make a full server backup which include all databases in
the current server , DTS packages and everything else in one single backup
file?
| |
| Uri Dimant 2005-07-28, 9:23 am |
| Hi,Vyas
He can use undocumented stored procedure to do that
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @sql varchar(1000);
set @sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
''''D:\MSSQL2000\MSS
QL\BACKUP'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@sql)
END'
BTW. What is about .NET examples on your site that you have promised us some
time ago :-)
"Narayana Vyas Kondreddi" <answer_me@hotmail.com> wrote in message
news:ubAFNs2kFHA.2472@TK2MSFTNGP15.phx.gbl...
> No. The backup restore mechanism works at database level, not server
> level.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/
>
>
> "Oren101" <Oren101@discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD- 08BF0DF3A3E9@microso
ft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?
>
>
| |
| Narayana Vyas Kondreddi 2005-07-28, 9:23 am |
| Uri, your script is still creating multiple backup files, which is not what
the OP wanted. Anyway, you could still write multiple database backups to a
single backup file though.
Anyway, I just wanted to point out that you cannot run a single command, and
create a single backup with everything that is there on the server.
Regarding the .Net code samples, hopefully before end of this year :-)
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/
"Uri Dimant" <urid@iscar.co.il> wrote in message
news:uqh7DY3kFHA.3256@TK2MSFTNGP12.phx.gbl...
Hi,Vyas
He can use undocumented stored procedure to do that
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @sql varchar(1000);
set @sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
''''D:\MSSQL2000\MSS
QL\BACKUP'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@sql)
END'
BTW. What is about .NET examples on your site that you have promised us some
time ago :-)
"Narayana Vyas Kondreddi" <answer_me@hotmail.com> wrote in message
news:ubAFNs2kFHA.2472@TK2MSFTNGP15.phx.gbl...
> No. The backup restore mechanism works at database level, not server
> level.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/
>
>
> "Oren101" <Oren101@discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD- 08BF0DF3A3E9@microso
ft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?
>
>
| |
| Oren101 2005-07-28, 9:23 am |
| is that code will backup the DTS packeges too?
Regards.
OreN101
"Uri Dimant" wrote:
> Hi,Vyas
> He can use undocumented stored procedure to do that
>
>
> EXEC sp_MSforeachdb
> 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> ''pubs'')
> BEGIN
> DECLARE @sql varchar(1000);
> set @sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
> ''''D:\MSSQL2000\MSS
QL\BACKUP'' + (''?'') + ''.bak'''' WITH INIT''
> EXEC (@sql)
> END'
>
> BTW. What is about .NET examples on your site that you have promised us some
> time ago :-)
>
>
>
> "Narayana Vyas Kondreddi" <answer_me@hotmail.com> wrote in message
> news:ubAFNs2kFHA.2472@TK2MSFTNGP15.phx.gbl...
>
>
>
| |
| Narayana Vyas Kondreddi 2005-07-28, 9:23 am |
| Yes, if your DTS packages are stored in SQL Server.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/
"Oren101" <Oren101@discussions.microsoft.com> wrote in message
news:0937D676-861F-4FFB-9174- 156131BFC4E8@microso
ft.com...
is that code will backup the DTS packeges too?
Regards.
OreN101
"Uri Dimant" wrote:
> Hi,Vyas
> He can use undocumented stored procedure to do that
>
>
> EXEC sp_MSforeachdb
> 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> ''pubs'')
> BEGIN
> DECLARE @sql varchar(1000);
> set @sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
> ''''D:\MSSQL2000\MSS
QL\BACKUP'' + (''?'') + ''.bak'''' WITH INIT''
> EXEC (@sql)
> END'
>
> BTW. What is about .NET examples on your site that you have promised us
some
> time ago :-)
>
>
>
> "Narayana Vyas Kondreddi" <answer_me@hotmail.com> wrote in message
> news:ubAFNs2kFHA.2472@TK2MSFTNGP15.phx.gbl...
databases[color=dark
red]
backup[color=darkred
]
>
>
>
| |
| Oren101 2005-07-28, 9:23 am |
| how come? the script above make backups to the databases in the sql server
instance ,not to any other objects(like DTS)?
"Oren101" wrote:
[color=darkred]
> is that code will backup the DTS packeges too?
>
> Regards.
> OreN101
>
> "Uri Dimant" wrote:
>
| |
| unc27932@yahoo.com 2005-07-28, 9:23 am |
| I agree - how does this grab the DTS packages? The DTS packages are
saved to the server - not to a database. The backup command only
references a specific database.
| |
| Narayana Vyas Kondreddi 2005-07-28, 9:23 am |
| If you save DTS packages to SQL Server, then they get stored in the msdb
database.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/
<unc27932@yahoo.com> wrote in message
news:1122560371.634115.202710@z14g2000cwz.googlegroups.com...
I agree - how does this grab the DTS packages? The DTS packages are
saved to the server - not to a database. The backup command only
references a specific database.
| |
| unc27932@yahoo.com 2005-07-28, 9:23 am |
| Well, now there's something I didn't realize. These newsgroups are
helpful aren't they. :)
| |
| Oren101 2005-07-28, 9:23 am |
| where exactly can i c it un the msdb?
"Narayana Vyas Kondreddi" wrote:
> If you save DTS packages to SQL Server, then they get stored in the msdb
> database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/
>
>
> <unc27932@yahoo.com> wrote in message
> news:1122560371.634115.202710@z14g2000cwz.googlegroups.com...
> I agree - how does this grab the DTS packages? The DTS packages are
> saved to the server - not to a database. The backup command only
> references a specific database.
>
>
>
| |
|
|
| Narayana Vyas Kondreddi 2005-07-28, 9:23 am |
| sysdtspackages
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/
"Oren101" <Oren101@discussions.microsoft.com> wrote in message
news:7C78600B-9414-48A8-9B9B- 2E8312D9A223@microso
ft.com...
where exactly can i c it un the msdb?
"Narayana Vyas Kondreddi" wrote:
> If you save DTS packages to SQL Server, then they get stored in the msdb
> database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/
>
>
> <unc27932@yahoo.com> wrote in message
> news:1122560371.634115.202710@z14g2000cwz.googlegroups.com...
> I agree - how does this grab the DTS packages? The DTS packages are
> saved to the server - not to a database. The backup command only
> references a specific database.
>
>
>
| |
| Wojtek Garwol 2005-07-28, 1:25 pm |
| The closest thing to that would be to stop the server and to zip all the
database files (mdf and ldf) to 1 archive.
--
Cheers,
Wojtek
"Oren101" <Oren101@discussions.microsoft.com> wrote in message
news:AAA53155-0EED-47FA-8CCD- 08BF0DF3A3E9@microso
ft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?
| |
| Tibor Karaszi 2005-07-28, 8:23 pm |
| It would be much easier and supported to just do regular database backups of each database, all
going to the same backup file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/
"Wojtek Garwol" <garwol@usunto.poczta.fm> wrote in message news:dcb893$r69$1@ne
mesis.news.tpi.pl...
> The closest thing to that would be to stop the server and to zip all the database files (mdf and
> ldf) to 1 archive.
>
> --
> Cheers,
> Wojtek
>
>
> "Oren101" <Oren101@discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD- 08BF0DF3A3E9@microso
ft.com...
>
>
|
|
|
|
|