Hi folks
Just need a little advice. If we backup our new SQL 2005 database using
the SQL Server backup system might we have any issued restoring the
database to a 2000 server? We're not planning to have any complex DTS
or Replication jobs to deal with, so that shouldn't be an issue, but I
wasn't sure if there might be other issues to look out for.
much appreciated...
LaurenceHi
You can not restore a SQL Server 2005 backup into SQL Server 2000. The DB
structures have changed slightly.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"LaurenceT" <laurencetruman@.gmail.com> wrote in message
news:1133782233.403165.130820@.g43g2000cwa.googlegroups.com...
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
>|||Sorry to be the bearer of bad news but you can't restore a SQL 2005 database
backup to SQL 2000. As you probably already know, you can restore from 2000
to 2005, though.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"LaurenceT" <laurencetruman@.gmail.com> wrote in message
news:1133782233.403165.130820@.g43g2000cwa.googlegroups.com...
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
>|||LaurenceT wrote:
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
You can't do that. What exactly do you want to achieve by backing up
and restoring between versions? Is this about availability or
deployment or something else? There ought to be some other solution.
--
David Portas
SQL Server MVP
--
Showing posts with label folks. Show all posts
Showing posts with label folks. Show all posts
Sunday, March 11, 2012
Sunday, February 19, 2012
Backup/Restore all databases
Hi Folks,
Is there a syntax for backing up ALL databases for the
backup and restore database commands. The only examples I
see are for one database at a time.
Thanks
LarryNope. You have to loop through the master..sysdatabases table with a cursor
or pseudo-cursor to backup/restore all the databases.
Jacco Schalkwijk
SQL Server MVP
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx
.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry|||Hi,
I suggest you to perform a FULL database backup of all databases (User
databases, System databases such as Master (Includes logins),msdb).
COmpile the below stored procedure in Master database and define the folder
in which backup needs to be taken.
schedule this procedure using SQL Agent -- Jobs. This procedure will backup
all the databases with a unique name place it in the
folder your are passing.
Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_DUMP.BAK
Script to Backup all databases
CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
AS
begin
DECLARE @.NAME VARCHAR(100),
@.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM sysdatabases where name not
in('model','pubs','tempdb','northwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
@.NAME=ltrim(rtrim(@.folder))+@.@.SERVERNAME
+'_'+@.DBNAME+'_'+ltrim(rtrim(convert
(char,getdate(),105)))+'Dump.bak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD , NAME =
@.DBNAME, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
end
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job.
Give a name to the Job and in Job step menthon this procedure with
foldername as parameter and
scdule the job to be executed based on requirement
Thanks
Hari
MCDBA
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx
.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry
Is there a syntax for backing up ALL databases for the
backup and restore database commands. The only examples I
see are for one database at a time.
Thanks
LarryNope. You have to loop through the master..sysdatabases table with a cursor
or pseudo-cursor to backup/restore all the databases.
Jacco Schalkwijk
SQL Server MVP
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx
.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry|||Hi,
I suggest you to perform a FULL database backup of all databases (User
databases, System databases such as Master (Includes logins),msdb).
COmpile the below stored procedure in Master database and define the folder
in which backup needs to be taken.
schedule this procedure using SQL Agent -- Jobs. This procedure will backup
all the databases with a unique name place it in the
folder your are passing.
Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_DUMP.BAK
Script to Backup all databases
CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
AS
begin
DECLARE @.NAME VARCHAR(100),
@.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM sysdatabases where name not
in('model','pubs','tempdb','northwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
@.NAME=ltrim(rtrim(@.folder))+@.@.SERVERNAME
+'_'+@.DBNAME+'_'+ltrim(rtrim(convert
(char,getdate(),105)))+'Dump.bak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD , NAME =
@.DBNAME, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
end
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job.
Give a name to the Job and in Job step menthon this procedure with
foldername as parameter and
scdule the job to be executed based on requirement
Thanks
Hari
MCDBA
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx
.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry
Backup/Restore all databases
Hi Folks,
Is there a syntax for backing up ALL databases for the
backup and restore database commands. The only examples I
see are for one database at a time.
Thanks
LarryNope. You have to loop through the master..sysdatabases table with a cursor
or pseudo-cursor to backup/restore all the databases.
--
Jacco Schalkwijk
SQL Server MVP
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry|||Hi,
I suggest you to perform a FULL database backup of all databases (User
databases, System databases such as Master (Includes logins),msdb).
COmpile the below stored procedure in Master database and define the folder
in which backup needs to be taken.
schedule this procedure using SQL Agent -- Jobs. This procedure will backup
all the databases with a unique name place it in the
folder your are passing.
Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_DUMP.BAK
Script to Backup all databases
CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
AS
begin
DECLARE @.NAME VARCHAR(100),
@.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM sysdatabases where name not
in('model','pubs','tempdb','northwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
@.NAME=ltrim(rtrim(@.folder))+@.@.SERVERNAME+'_'+@.DBNAME+'_'+ltrim(rtrim(convert
(char,getdate(),105)))+'Dump.bak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD , NAME =@.DBNAME, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
end
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job.
Give a name to the Job and in Job step menthon this procedure with
foldername as parameter and
scdule the job to be executed based on requirement
Thanks
Hari
MCDBA
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry
Is there a syntax for backing up ALL databases for the
backup and restore database commands. The only examples I
see are for one database at a time.
Thanks
LarryNope. You have to loop through the master..sysdatabases table with a cursor
or pseudo-cursor to backup/restore all the databases.
--
Jacco Schalkwijk
SQL Server MVP
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry|||Hi,
I suggest you to perform a FULL database backup of all databases (User
databases, System databases such as Master (Includes logins),msdb).
COmpile the below stored procedure in Master database and define the folder
in which backup needs to be taken.
schedule this procedure using SQL Agent -- Jobs. This procedure will backup
all the databases with a unique name place it in the
folder your are passing.
Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_DUMP.BAK
Script to Backup all databases
CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
AS
begin
DECLARE @.NAME VARCHAR(100),
@.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM sysdatabases where name not
in('model','pubs','tempdb','northwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
@.NAME=ltrim(rtrim(@.folder))+@.@.SERVERNAME+'_'+@.DBNAME+'_'+ltrim(rtrim(convert
(char,getdate(),105)))+'Dump.bak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD , NAME =@.DBNAME, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
end
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job.
Give a name to the Job and in Job step menthon this procedure with
foldername as parameter and
scdule the job to be executed based on requirement
Thanks
Hari
MCDBA
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry
Backup/Restore all databases
Hi Folks,
Is there a syntax for backing up ALL databases for the
backup and restore database commands. The only examples I
see are for one database at a time.
Thanks
Larry
Nope. You have to loop through the master..sysdatabases table with a cursor
or pseudo-cursor to backup/restore all the databases.
Jacco Schalkwijk
SQL Server MVP
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry
|||Hi,
I suggest you to perform a FULL database backup of all databases (User
databases, System databases such as Master (Includes logins),msdb).
COmpile the below stored procedure in Master database and define the folder
in which backup needs to be taken.
schedule this procedure using SQL Agent -- Jobs. This procedure will backup
all the databases with a unique name place it in the
folder your are passing.
Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_DUMP.BAK
Script to Backup all databases
CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
AS
begin
DECLARE @.NAME VARCHAR(100),
@.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM sysdatabases where name not
in('model','pubs','tempdb','northwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
@.NAME=ltrim(rtrim(@.folder))+@.@.SERVERNAME+'_'+@.DBNA ME+'_'+ltrim(rtrim(convert
(char,getdate(),105)))+'Dump.bak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD , NAME =
@.DBNAME, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
end
How to schedule
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job.
Give a name to the Job and in Job step menthon this procedure with
foldername as parameter and
scdule the job to be executed based on requirement
Thanks
Hari
MCDBA
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry
Is there a syntax for backing up ALL databases for the
backup and restore database commands. The only examples I
see are for one database at a time.
Thanks
Larry
Nope. You have to loop through the master..sysdatabases table with a cursor
or pseudo-cursor to backup/restore all the databases.
Jacco Schalkwijk
SQL Server MVP
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry
|||Hi,
I suggest you to perform a FULL database backup of all databases (User
databases, System databases such as Master (Includes logins),msdb).
COmpile the below stored procedure in Master database and define the folder
in which backup needs to be taken.
schedule this procedure using SQL Agent -- Jobs. This procedure will backup
all the databases with a unique name place it in the
folder your are passing.
Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_DUMP.BAK
Script to Backup all databases
CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
AS
begin
DECLARE @.NAME VARCHAR(100),
@.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM sysdatabases where name not
in('model','pubs','tempdb','northwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
@.NAME=ltrim(rtrim(@.folder))+@.@.SERVERNAME+'_'+@.DBNA ME+'_'+ltrim(rtrim(convert
(char,getdate(),105)))+'Dump.bak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD , NAME =
@.DBNAME, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
end
How to schedule
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job.
Give a name to the Job and in Job step menthon this procedure with
foldername as parameter and
scdule the job to be executed based on requirement
Thanks
Hari
MCDBA
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry
Friday, February 10, 2012
Backup to Network
We just recently experiences a catastrophic hard drive failure. It is off
right now to the data recovery folks and I hope we get something back. In
the mean time I have been asked to come up with some backup plan. Because
the backups were on the same drive as the database, this is not a good
solution. We are implementing a RAID system, but I have some questions
about the following:
1. How well does log shipping work. The data is not real-time and we could
afford to loose the little data we would produce in the time it would take
to switch to the new server?
2. Can I back up the files to a network. I would like to do a Complete
backup once a sunday, full backups once a sunday, then do incremental
backups every 15-30 minutes. I would like to make the backups to the local
drive and two network locations. How can I back up across the network?
When I set up the backups I only get my local drives (c:,d
.
We are using SQL Server 2000 (will migrate to 2005 in a year) with the
current service pack running on Win 2k3
John
Having your database on the same drive on your backup not only leaves you
vulnerable to the crash you experience but also leads to IO contention
between your backup and your database files.
1) Log shipping works well, but it is not really scalable for very large
databases or large numbers of databases. The main reason people don't use
log shipping is that their exposure to data loss is significantly greater
than with other HA technologies.
2) Yes you can but it is not recommended. You should backup locally and then
copy the backup off the local drive to a network location.
Here is one way you can do backup across the network :
BACKUP DATABASE DatabaseName TO DISK =
'\\ServerName\ShareName\DatabaseBackupName.bak' WITH INIT
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Wright" <riley_wrightx@.hotmail.com> wrote in message
news:uQuhXzseHHA.1868@.TK2MSFTNGP04.phx.gbl...
> We just recently experiences a catastrophic hard drive failure. It is off
> right now to the data recovery folks and I hope we get something back. In
> the mean time I have been asked to come up with some backup plan. Because
> the backups were on the same drive as the database, this is not a good
> solution. We are implementing a RAID system, but I have some questions
> about the following:
> 1. How well does log shipping work. The data is not real-time and we
> could afford to loose the little data we would produce in the time it
> would take to switch to the new server?
> 2. Can I back up the files to a network. I would like to do a Complete
> backup once a sunday, full backups once a sunday, then do incremental
> backups every 15-30 minutes. I would like to make the backups to the
> local drive and two network locations. How can I back up across the
> network? When I set up the backups I only get my local drives (c:,d.
> We are using SQL Server 2000 (will migrate to 2005 in a year) with the
> current service pack running on Win 2k3
>
> John
>
|||If having the backup on the same drive as the backup leaves us this
vulnerable, then how we backup to another location. We have a c: and d:
drive (one drive two partitions) and they are the only devices that show.
It seems odd to me that SQL Server would not have the ability to backup
across the network.
When I ran the BACKUP DATABASE command I get the following error:
Operating system error 1385(Logon failure: the user has not been granted the
requested logon type at this computer.).
I am the administrator and have full rights to this location. What user is
this trying to back up to?
John
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uV1cZ3seHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Having your database on the same drive on your backup not only leaves you
> vulnerable to the crash you experience but also leads to IO contention
> between your backup and your database files.
> 1) Log shipping works well, but it is not really scalable for very large
> databases or large numbers of databases. The main reason people don't use
> log shipping is that their exposure to data loss is significantly greater
> than with other HA technologies.
> 2) Yes you can but it is not recommended. You should backup locally and
> then copy the backup off the local drive to a network location.
> Here is one way you can do backup across the network :
> BACKUP DATABASE DatabaseName TO DISK =
> '\\ServerName\ShareName\DatabaseBackupName.bak' WITH INIT
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "John Wright" <riley_wrightx@.hotmail.com> wrote in message
> news:uQuhXzseHHA.1868@.TK2MSFTNGP04.phx.gbl...
>
|||If you have your backup on the same drive as your datafile and this drive
fails you have lost your backup and your datafiles.
If you have your backup on a different drive and your backup fails you still
have your data files which is some level of protection.
If you have your backup on a different drive and your database gets corrupt
you still have your backup files which you can restore from.
If you backup to a local drive and immediately copy it off the network your
exposure is only in the time interval it takes to copy the backup off your
local drive.
If you backup to the network it takes longer so your vulnerability is more
than if you were to backup locally and then copy.
to backup to the network drive do this:
BACKUP DATABASE DatabaseName TO DISK =
'\\ServerName\ShareName\DatabaseBackupName.bak' WITH INIT
The error message tells me that the account that you are using to run the
backup does not have rights to write to the network share or the files and
folders beneath it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Wright" <riley_wrightx@.hotmail.com> wrote in message
news:%23IWAQ%23seHHA.4872@.TK2MSFTNGP03.phx.gbl...
> If having the backup on the same drive as the backup leaves us this
> vulnerable, then how we backup to another location. We have a c: and d:
> drive (one drive two partitions) and they are the only devices that show.
> It seems odd to me that SQL Server would not have the ability to backup
> across the network.
> When I ran the BACKUP DATABASE command I get the following error:
> Operating system error 1385(Logon failure: the user has not been granted
> the requested logon type at this computer.).
>
> I am the administrator and have full rights to this location. What user
> is this trying to back up to?
> John
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uV1cZ3seHHA.4772@.TK2MSFTNGP05.phx.gbl...
>
right now to the data recovery folks and I hope we get something back. In
the mean time I have been asked to come up with some backup plan. Because
the backups were on the same drive as the database, this is not a good
solution. We are implementing a RAID system, but I have some questions
about the following:
1. How well does log shipping work. The data is not real-time and we could
afford to loose the little data we would produce in the time it would take
to switch to the new server?
2. Can I back up the files to a network. I would like to do a Complete
backup once a sunday, full backups once a sunday, then do incremental
backups every 15-30 minutes. I would like to make the backups to the local
drive and two network locations. How can I back up across the network?
When I set up the backups I only get my local drives (c:,d
.We are using SQL Server 2000 (will migrate to 2005 in a year) with the
current service pack running on Win 2k3
John
Having your database on the same drive on your backup not only leaves you
vulnerable to the crash you experience but also leads to IO contention
between your backup and your database files.
1) Log shipping works well, but it is not really scalable for very large
databases or large numbers of databases. The main reason people don't use
log shipping is that their exposure to data loss is significantly greater
than with other HA technologies.
2) Yes you can but it is not recommended. You should backup locally and then
copy the backup off the local drive to a network location.
Here is one way you can do backup across the network :
BACKUP DATABASE DatabaseName TO DISK =
'\\ServerName\ShareName\DatabaseBackupName.bak' WITH INIT
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Wright" <riley_wrightx@.hotmail.com> wrote in message
news:uQuhXzseHHA.1868@.TK2MSFTNGP04.phx.gbl...
> We just recently experiences a catastrophic hard drive failure. It is off
> right now to the data recovery folks and I hope we get something back. In
> the mean time I have been asked to come up with some backup plan. Because
> the backups were on the same drive as the database, this is not a good
> solution. We are implementing a RAID system, but I have some questions
> about the following:
> 1. How well does log shipping work. The data is not real-time and we
> could afford to loose the little data we would produce in the time it
> would take to switch to the new server?
> 2. Can I back up the files to a network. I would like to do a Complete
> backup once a sunday, full backups once a sunday, then do incremental
> backups every 15-30 minutes. I would like to make the backups to the
> local drive and two network locations. How can I back up across the
> network? When I set up the backups I only get my local drives (c:,d
.> We are using SQL Server 2000 (will migrate to 2005 in a year) with the
> current service pack running on Win 2k3
>
> John
>
|||If having the backup on the same drive as the backup leaves us this
vulnerable, then how we backup to another location. We have a c: and d:
drive (one drive two partitions) and they are the only devices that show.
It seems odd to me that SQL Server would not have the ability to backup
across the network.
When I ran the BACKUP DATABASE command I get the following error:
Operating system error 1385(Logon failure: the user has not been granted the
requested logon type at this computer.).
I am the administrator and have full rights to this location. What user is
this trying to back up to?
John
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uV1cZ3seHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Having your database on the same drive on your backup not only leaves you
> vulnerable to the crash you experience but also leads to IO contention
> between your backup and your database files.
> 1) Log shipping works well, but it is not really scalable for very large
> databases or large numbers of databases. The main reason people don't use
> log shipping is that their exposure to data loss is significantly greater
> than with other HA technologies.
> 2) Yes you can but it is not recommended. You should backup locally and
> then copy the backup off the local drive to a network location.
> Here is one way you can do backup across the network :
> BACKUP DATABASE DatabaseName TO DISK =
> '\\ServerName\ShareName\DatabaseBackupName.bak' WITH INIT
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "John Wright" <riley_wrightx@.hotmail.com> wrote in message
> news:uQuhXzseHHA.1868@.TK2MSFTNGP04.phx.gbl...
>
|||If you have your backup on the same drive as your datafile and this drive
fails you have lost your backup and your datafiles.
If you have your backup on a different drive and your backup fails you still
have your data files which is some level of protection.
If you have your backup on a different drive and your database gets corrupt
you still have your backup files which you can restore from.
If you backup to a local drive and immediately copy it off the network your
exposure is only in the time interval it takes to copy the backup off your
local drive.
If you backup to the network it takes longer so your vulnerability is more
than if you were to backup locally and then copy.
to backup to the network drive do this:
BACKUP DATABASE DatabaseName TO DISK =
'\\ServerName\ShareName\DatabaseBackupName.bak' WITH INIT
The error message tells me that the account that you are using to run the
backup does not have rights to write to the network share or the files and
folders beneath it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Wright" <riley_wrightx@.hotmail.com> wrote in message
news:%23IWAQ%23seHHA.4872@.TK2MSFTNGP03.phx.gbl...
> If having the backup on the same drive as the backup leaves us this
> vulnerable, then how we backup to another location. We have a c: and d:
> drive (one drive two partitions) and they are the only devices that show.
> It seems odd to me that SQL Server would not have the ability to backup
> across the network.
> When I ran the BACKUP DATABASE command I get the following error:
> Operating system error 1385(Logon failure: the user has not been granted
> the requested logon type at this computer.).
>
> I am the administrator and have full rights to this location. What user
> is this trying to back up to?
> John
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uV1cZ3seHHA.4772@.TK2MSFTNGP05.phx.gbl...
>
Subscribe to:
Posts (Atom)