Backup DTS packages

We have a development environment where there are 4 development servers and a single production server.

some of our processes rely of some complex DTS packages.

what is the perferred method to create copies of these DTS packages so that they can be put onto all the development systems ?Check out this article:

http://www.codeproject.com/useritems/DTS_Export.asp|||thanks - thats what i was looking for.

it even has the added bonus of some code to reset all the connections to a different server.sql

Backup data during production time

I need to take a data backup for 10 GB avery active database during
production time. Is it affect the server performance? generate locks....
thanks
Backup doesn't lock data or wais for locks. But it will consume resources (reading all data,
possibly from disk if not in cache and writing the data to backup media).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"mecn" <mecn2002@.yahoo.com> wrote in message news:%231RMCKNtHHA.4600@.TK2MSFTNGP03.phx.gbl...
>I need to take a data backup for 10 GB avery active database during production time. Is it affect
>the server performance? generate locks....
> thanks
>

Backup data during production time

I need to take a data backup for 10 GB avery active database during
production time. Is it affect the server performance? generate locks....
thanksBackup doesn't lock data or wais for locks. But it will consume resources (reading all data,
possibly from disk if not in cache and writing the data to backup media).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"mecn" <mecn2002@.yahoo.com> wrote in message news:%231RMCKNtHHA.4600@.TK2MSFTNGP03.phx.gbl...
>I need to take a data backup for 10 GB avery active database during production time. Is it affect
>the server performance? generate locks....
> thanks
>

Backup data during production time

I need to take a data backup for 10 GB avery active database during
production time. Is it affect the server performance? generate locks....
thanksBackup doesn't lock data or wais for locks. But it will consume resources (r
eading all data,
possibly from disk if not in cache and writing the data to backup media).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"mecn" <mecn2002@.yahoo.com> wrote in message news:%231RMCKNtHHA.4600@.TK2MSFTNGP03.phx.gbl...

>I need to take a data backup for 10 GB avery active database during product
ion time. Is it affect
>the server performance? generate locks....
> thanks
>

Backup copy_only on 2000

Hi,
We are currently doing weekly full backups and hourly log backups, all to
tape.
However, sometimes we need to move production data to a test system, and
would like to do this using a full backup.
However if we do a full backup, this of course invalidates future
transaction log backups, as they will be based on the off-schedule full
backup.
Is there a way to instruct SQL Server 2000 to create the full backup without
affecting the currently used backup strategy, so future transaction log
backups will still be valid even if I get rid of the temporary full backup
made manually?
Ie:
Full A
Log A
Log B
Log C
Full B
Log D
Log E.
Is there any way to make the Full Backup B, so that a Point in time recovery
can make use of the sequeuce Full A, Log A, Log B, Log C, Log D, Log E?
From what I understand, SQL Server 2005 supports this using the WITH
COPY_ONLY, but this is not available on 2000.
Thanks in advance
Thomas Turn Jensen> However if we do a full backup, this of course invalidates future
> transaction log backups, as they will be based on the off-schedule full
> backup.
No, a full backup does not empty the log, i.e., does not break the log backu
p chain sequence. So, it
is perfectly possible to skip a full backup during restore, as long as you h
ave an unbroken chain of
log backups since the db backup that you *do* restore.

> From what I understand, SQL Server 2005 supports this using the WITH
> COPY_ONLY, but this is not available on 2000.
COPY_ONLY is not needed for above scenario. Below are the two scenarios wher
e COPY_ONLY is needed:
1. You do diff backups. Somebody want to have a copy of your db through a fu
ll backup. If that isn't
taken with COPY_ONLY, the following diff backups would be based on that db b
ackup.
2. You do log backups. Somebody want an up-to-date copy of the database, and
do this though a log
backup, without breaking the chain of log backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Thomas Turn Jensen" <ThomasTurnJensen@.discussions.microsoft.com> wrote in m
essage
news:F1BE0189-A225-44C3-8537-118214E131AF@.microsoft.com...
> Hi,
> We are currently doing weekly full backups and hourly log backups, all to
> tape.
> However, sometimes we need to move production data to a test system, and
> would like to do this using a full backup.
> However if we do a full backup, this of course invalidates future
> transaction log backups, as they will be based on the off-schedule full
> backup.
> Is there a way to instruct SQL Server 2000 to create the full backup witho
ut
> affecting the currently used backup strategy, so future transaction log
> backups will still be valid even if I get rid of the temporary full backup
> made manually?
> Ie:
> Full A
> Log A
> Log B
> Log C
> Full B
> Log D
> Log E.
> Is there any way to make the Full Backup B, so that a Point in time recove
ry
> can make use of the sequeuce Full A, Log A, Log B, Log C, Log D, Log E?
> From what I understand, SQL Server 2005 supports this using the WITH
> COPY_ONLY, but this is not available on 2000.
> Thanks in advance
> Thomas Turn Jensen|||Very interesting information, thanks.
The two local vendors we have talked to has told us not to do the
intermittent full backups because they would invalidate the transaction logs
.
Thank you very much.
"Tibor Karaszi" wrote:

> No, a full backup does not empty the log, i.e., does not break the log bac
kup chain sequence. So, it
> is perfectly possible to skip a full backup during restore, as long as you
have an unbroken chain of
> log backups since the db backup that you *do* restore.
>
> COPY_ONLY is not needed for above scenario. Below are the two scenarios wh
ere COPY_ONLY is needed:
> 1. You do diff backups. Somebody want to have a copy of your db through a
full backup. If that isn't
> taken with COPY_ONLY, the following diff backups would be based on that db
backup.
> 2. You do log backups. Somebody want an up-to-date copy of the database, a
nd do this though a log
> backup, without breaking the chain of log backups.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Thomas Turn Jensen" <ThomasTurnJensen@.discussions.microsoft.com> wrote in
message
> news:F1BE0189-A225-44C3-8537-118214E131AF@.microsoft.com...
>

Backup copy_only on 2000

Hi,
We are currently doing weekly full backups and hourly log backups, all to
tape.
However, sometimes we need to move production data to a test system, and
would like to do this using a full backup.
However if we do a full backup, this of course invalidates future
transaction log backups, as they will be based on the off-schedule full
backup.
Is there a way to instruct SQL Server 2000 to create the full backup without
affecting the currently used backup strategy, so future transaction log
backups will still be valid even if I get rid of the temporary full backup
made manually?
Ie:
Full A
Log A
Log B
Log C
Full B
Log D
Log E.
Is there any way to make the Full Backup B, so that a Point in time recovery
can make use of the sequeuce Full A, Log A, Log B, Log C, Log D, Log E?
From what I understand, SQL Server 2005 supports this using the WITH
COPY_ONLY, but this is not available on 2000.
Thanks in advance
Thomas Turn Jensen> However if we do a full backup, this of course invalidates future
> transaction log backups, as they will be based on the off-schedule full
> backup.
No, a full backup does not empty the log, i.e., does not break the log backup chain sequence. So, it
is perfectly possible to skip a full backup during restore, as long as you have an unbroken chain of
log backups since the db backup that you *do* restore.
> From what I understand, SQL Server 2005 supports this using the WITH
> COPY_ONLY, but this is not available on 2000.
COPY_ONLY is not needed for above scenario. Below are the two scenarios where COPY_ONLY is needed:
1. You do diff backups. Somebody want to have a copy of your db through a full backup. If that isn't
taken with COPY_ONLY, the following diff backups would be based on that db backup.
2. You do log backups. Somebody want an up-to-date copy of the database, and do this though a log
backup, without breaking the chain of log backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Thomas Turn Jensen" <ThomasTurnJensen@.discussions.microsoft.com> wrote in message
news:F1BE0189-A225-44C3-8537-118214E131AF@.microsoft.com...
> Hi,
> We are currently doing weekly full backups and hourly log backups, all to
> tape.
> However, sometimes we need to move production data to a test system, and
> would like to do this using a full backup.
> However if we do a full backup, this of course invalidates future
> transaction log backups, as they will be based on the off-schedule full
> backup.
> Is there a way to instruct SQL Server 2000 to create the full backup without
> affecting the currently used backup strategy, so future transaction log
> backups will still be valid even if I get rid of the temporary full backup
> made manually?
> Ie:
> Full A
> Log A
> Log B
> Log C
> Full B
> Log D
> Log E.
> Is there any way to make the Full Backup B, so that a Point in time recovery
> can make use of the sequeuce Full A, Log A, Log B, Log C, Log D, Log E?
> From what I understand, SQL Server 2005 supports this using the WITH
> COPY_ONLY, but this is not available on 2000.
> Thanks in advance
> Thomas Turn Jensen|||Very interesting information, thanks.
The two local vendors we have talked to has told us not to do the
intermittent full backups because they would invalidate the transaction logs.
Thank you very much.
"Tibor Karaszi" wrote:
> > However if we do a full backup, this of course invalidates future
> > transaction log backups, as they will be based on the off-schedule full
> > backup.
> No, a full backup does not empty the log, i.e., does not break the log backup chain sequence. So, it
> is perfectly possible to skip a full backup during restore, as long as you have an unbroken chain of
> log backups since the db backup that you *do* restore.
>
> > From what I understand, SQL Server 2005 supports this using the WITH
> > COPY_ONLY, but this is not available on 2000.
> COPY_ONLY is not needed for above scenario. Below are the two scenarios where COPY_ONLY is needed:
> 1. You do diff backups. Somebody want to have a copy of your db through a full backup. If that isn't
> taken with COPY_ONLY, the following diff backups would be based on that db backup.
> 2. You do log backups. Somebody want an up-to-date copy of the database, and do this though a log
> backup, without breaking the chain of log backups.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Thomas Turn Jensen" <ThomasTurnJensen@.discussions.microsoft.com> wrote in message
> news:F1BE0189-A225-44C3-8537-118214E131AF@.microsoft.com...
> > Hi,
> >
> > We are currently doing weekly full backups and hourly log backups, all to
> > tape.
> >
> > However, sometimes we need to move production data to a test system, and
> > would like to do this using a full backup.
> >
> > However if we do a full backup, this of course invalidates future
> > transaction log backups, as they will be based on the off-schedule full
> > backup.
> >
> > Is there a way to instruct SQL Server 2000 to create the full backup without
> > affecting the currently used backup strategy, so future transaction log
> > backups will still be valid even if I get rid of the temporary full backup
> > made manually?
> >
> > Ie:
> > Full A
> > Log A
> > Log B
> > Log C
> > Full B
> > Log D
> > Log E.
> >
> > Is there any way to make the Full Backup B, so that a Point in time recovery
> > can make use of the sequeuce Full A, Log A, Log B, Log C, Log D, Log E?
> >
> > From what I understand, SQL Server 2005 supports this using the WITH
> > COPY_ONLY, but this is not available on 2000.
> >
> > Thanks in advance
> > Thomas Turn Jensen
>

Backup and restore to another database

We are running SQL Server 2000 Standard Edition
Every night, I want to take a backup of our production database and
restore it to another database that is used for reporting/DR
purposes.
My thinking was as follows:
(1) Setup a job on the source (production) to create the backup to a
named file
(2) Setup a job on the destination (DR) sometime later to restore the
backup
Is this the best way to do this?
Perhaps I could create 1 job to do both?Hi
Do you want to do that on dayly,weekly period?
Take a look at WITH MOVE option in the BOL
"PromisedOyster" <PromisedOyster@.hotmail.com> wrote in message
news:1173960621.133170.194740@.l77g2000hsb.googlegroups.com...
> We are running SQL Server 2000 Standard Edition
> Every night, I want to take a backup of our production database and
> restore it to another database that is used for reporting/DR
> purposes.
> My thinking was as follows:
> (1) Setup a job on the source (production) to create the backup to a
> named file
> (2) Setup a job on the destination (DR) sometime later to restore the
> backup
> Is this the best way to do this?
> Perhaps I could create 1 job to do both?
>|||Hello,
If your database size is not huge you could do the way you mentioned. If the
database is really big take a look into the LOGSHIPPING option.
In SQL 2000 standard you may need to manually configure logshipping. take a
look into the below URL:-
http://www.sqlmag.com/Article/Artic...rver_23231.html
Thanks
Hari
"PromisedOyster" <PromisedOyster@.hotmail.com> wrote in message
news:1173960621.133170.194740@.l77g2000hsb.googlegroups.com...
> We are running SQL Server 2000 Standard Edition
> Every night, I want to take a backup of our production database and
> restore it to another database that is used for reporting/DR
> purposes.
> My thinking was as follows:
> (1) Setup a job on the source (production) to create the backup to a
> named file
> (2) Setup a job on the destination (DR) sometime later to restore the
> backup
> Is this the best way to do this?
> Perhaps I could create 1 job to do both?
>

Backup and restore to another database

We are running SQL Server 2000 Standard Edition
Every night, I want to take a backup of our production database and
restore it to another database that is used for reporting/DR
purposes.
My thinking was as follows:
(1) Setup a job on the source (production) to create the backup to a
named file
(2) Setup a job on the destination (DR) sometime later to restore the
backup
Is this the best way to do this?
Perhaps I could create 1 job to do both?Hi
Do you want to do that on dayly,weekly period?
Take a look at WITH MOVE option in the BOL
"PromisedOyster" <PromisedOyster@.hotmail.com> wrote in message
news:1173960621.133170.194740@.l77g2000hsb.googlegroups.com...
> We are running SQL Server 2000 Standard Edition
> Every night, I want to take a backup of our production database and
> restore it to another database that is used for reporting/DR
> purposes.
> My thinking was as follows:
> (1) Setup a job on the source (production) to create the backup to a
> named file
> (2) Setup a job on the destination (DR) sometime later to restore the
> backup
> Is this the best way to do this?
> Perhaps I could create 1 job to do both?
>|||Hello,
If your database size is not huge you could do the way you mentioned. If the
database is really big take a look into the LOGSHIPPING option.
In SQL 2000 standard you may need to manually configure logshipping. take a
look into the below URL:-
http://www.sqlmag.com/Article/ArticleID/23231/sql_server_23231.html
Thanks
Hari
"PromisedOyster" <PromisedOyster@.hotmail.com> wrote in message
news:1173960621.133170.194740@.l77g2000hsb.googlegroups.com...
> We are running SQL Server 2000 Standard Edition
> Every night, I want to take a backup of our production database and
> restore it to another database that is used for reporting/DR
> purposes.
> My thinking was as follows:
> (1) Setup a job on the source (production) to create the backup to a
> named file
> (2) Setup a job on the destination (DR) sometime later to restore the
> backup
> Is this the best way to do this?
> Perhaps I could create 1 job to do both?
>

Backup and restore to another database

We are running SQL Server 2000 Standard Edition
Every night, I want to take a backup of our production database and
restore it to another database that is used for reporting/DR
purposes.
My thinking was as follows:
(1) Setup a job on the source (production) to create the backup to a
named file
(2) Setup a job on the destination (DR) sometime later to restore the
backup
Is this the best way to do this?
Perhaps I could create 1 job to do both?
Hi
Do you want to do that on dayly,weekly period?
Take a look at WITH MOVE option in the BOL
"PromisedOyster" <PromisedOyster@.hotmail.com> wrote in message
news:1173960621.133170.194740@.l77g2000hsb.googlegr oups.com...
> We are running SQL Server 2000 Standard Edition
> Every night, I want to take a backup of our production database and
> restore it to another database that is used for reporting/DR
> purposes.
> My thinking was as follows:
> (1) Setup a job on the source (production) to create the backup to a
> named file
> (2) Setup a job on the destination (DR) sometime later to restore the
> backup
> Is this the best way to do this?
> Perhaps I could create 1 job to do both?
>
|||Hello,
If your database size is not huge you could do the way you mentioned. If the
database is really big take a look into the LOGSHIPPING option.
In SQL 2000 standard you may need to manually configure logshipping. take a
look into the below URL:-
http://www.sqlmag.com/Article/ArticleID/23231/sql_server_23231.html
Thanks
Hari
"PromisedOyster" <PromisedOyster@.hotmail.com> wrote in message
news:1173960621.133170.194740@.l77g2000hsb.googlegr oups.com...
> We are running SQL Server 2000 Standard Edition
> Every night, I want to take a backup of our production database and
> restore it to another database that is used for reporting/DR
> purposes.
> My thinking was as follows:
> (1) Setup a job on the source (production) to create the backup to a
> named file
> (2) Setup a job on the destination (DR) sometime later to restore the
> backup
> Is this the best way to do this?
> Perhaps I could create 1 job to do both?
>

Backup and Restore

Hi everyone
I have this situation with backup and restore.
I have two instances of sql, sql1 and sql2.
Production server is sql1 and test server is sql2.

I created database maintenance plan that makes full backup every night
on tape.

I would like to make some kind of job or to add steps to job that was
created with DMplan which will make restore of backuped database to sql2
server.

How can i do this? How can i read which is the last file backuped on
tape?

Thanks very much
Alex

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!"acko bogicevic" <aconi2002@.yahoo.com> wrote in message
news:40e3cace$0$16501$c397aba@.news.newsgroups.ws.. .
> Hi everyone
> I have this situation with backup and restore.
> I have two instances of sql, sql1 and sql2.
> Production server is sql1 and test server is sql2.
> I created database maintenance plan that makes full backup every night
> on tape.
> I would like to make some kind of job or to add steps to job that was
> created with DMplan which will make restore of backuped database to sql2
> server.
> How can i do this? How can i read which is the last file backuped on
> tape?

You can adapt the following. (note, in this case we backup and restore from
disk.)

create procedure restore_FOO as

declare @.backup_file as varchar(255)

select @.backup_file=physical_device_name from
nell.msdb.dbo.backupmediafamily where media_set_id in (select
max(media_set_id) from nell.msdb.dbo.backupset where database_name='FOO')

restore database FOO from disk=@.backup_file with
move 'FOO_Data' to 'f:\sql_data\FOO_data.mdf',
move 'FOO_Log' to 'e:\SQL_LOGs\FOO_log.ldf',
replace

GO

> Thanks very much
> Alex
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||thanks
alex

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

backup and restore

On a production server, I do a full backup monthly and then delete data
from the tables that is older than the current date. On a separate
non-production server I restored the initial full backup. My question
is how can I add/restore the subsequent backups to the non-production
server with out stepping on the data that was initially restored?
Thanks,
Daniel<danielsmith611@.gmail.com> wrote in message
news:1155141011.175566.161350@.m73g2000cwd.googlegroups.com...
> On a production server, I do a full backup monthly and then delete data
> from the tables that is older than the current date. On a separate
> non-production server I restored the initial full backup. My question
> is how can I add/restore the subsequent backups to the non-production
> server with out stepping on the data that was initially restored?
>
RESTORE WITH MOVE, followed perhaps by merging the data.
EG
BACKUP DATABASE AdventureWorks
TO AdventureWorksBackups ;
RESTORE DATABASE AdventureWorks_200608
FROM AdventureWorksBackups
WITH MOVE 'AdventureWorks_Data' TO
'C:\MySQLServer\AdventureWorks_200608.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\MySQLServer\AdventureWorks_200608.ldf';
David|||Do you have an example of the merge?
David Browne wrote:
> <danielsmith611@.gmail.com> wrote in message
> news:1155141011.175566.161350@.m73g2000cwd.googlegroups.com...
>
> RESTORE WITH MOVE, followed perhaps by merging the data.
>
> EG
> BACKUP DATABASE AdventureWorks
> TO AdventureWorksBackups ;
>
> RESTORE DATABASE AdventureWorks_200608
> FROM AdventureWorksBackups
> WITH MOVE 'AdventureWorks_Data' TO
> 'C:\MySQLServer\AdventureWorks_200608.mdf',
> MOVE 'AdventureWorks_Log' TO 'C:\MySQLServer\AdventureWorks_200608.ldf'
;
>
> David|||Or would log shipping work?
danielp wrote:[vbcol=seagreen]
> Do you have an example of the merge?
> David Browne wrote:|||"danielp" <danielsmith611@.gmail.com> wrote in message
news:1155158638.423828.3310@.h48g2000cwc.googlegroups.com...
> Or would log shipping work?
No. Your deletes would get shipped.
David|||"danielp" <danielsmith611@.gmail.com> wrote in message
news:1155149678.373420.184490@.i3g2000cwc.googlegroups.com...
> Do you have an example of the merge?
It's just SQL, or perhaps an SSIS package. What do the tables look like?
David

backup and restore

On a production server, I do a full backup monthly and then delete data
from the tables that is older than the current date. On a separate
non-production server I restored the initial full backup. My question
is how can I add/restore the subsequent backups to the non-production
server with out stepping on the data that was initially restored?
Thanks,
Daniel<danielsmith611@.gmail.com> wrote in message
news:1155141011.175566.161350@.m73g2000cwd.googlegroups.com...
> On a production server, I do a full backup monthly and then delete data
> from the tables that is older than the current date. On a separate
> non-production server I restored the initial full backup. My question
> is how can I add/restore the subsequent backups to the non-production
> server with out stepping on the data that was initially restored?
>
RESTORE WITH MOVE, followed perhaps by merging the data.
EG
BACKUP DATABASE AdventureWorks
TO AdventureWorksBackups ;
RESTORE DATABASE AdventureWorks_200608
FROM AdventureWorksBackups
WITH MOVE 'AdventureWorks_Data' TO
'C:\MySQLServer\AdventureWorks_200608.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\MySQLServer\AdventureWorks_200608.ldf';
David|||Do you have an example of the merge?
David Browne wrote:
> <danielsmith611@.gmail.com> wrote in message
> news:1155141011.175566.161350@.m73g2000cwd.googlegroups.com...
> > On a production server, I do a full backup monthly and then delete data
> > from the tables that is older than the current date. On a separate
> > non-production server I restored the initial full backup. My question
> > is how can I add/restore the subsequent backups to the non-production
> > server with out stepping on the data that was initially restored?
> >
>
> RESTORE WITH MOVE, followed perhaps by merging the data.
>
> EG
> BACKUP DATABASE AdventureWorks
> TO AdventureWorksBackups ;
>
> RESTORE DATABASE AdventureWorks_200608
> FROM AdventureWorksBackups
> WITH MOVE 'AdventureWorks_Data' TO
> 'C:\MySQLServer\AdventureWorks_200608.mdf',
> MOVE 'AdventureWorks_Log' TO 'C:\MySQLServer\AdventureWorks_200608.ldf';
>
> David|||Or would log shipping work?
danielp wrote:
> Do you have an example of the merge?
> David Browne wrote:
> > <danielsmith611@.gmail.com> wrote in message
> > news:1155141011.175566.161350@.m73g2000cwd.googlegroups.com...
> > > On a production server, I do a full backup monthly and then delete data
> > > from the tables that is older than the current date. On a separate
> > > non-production server I restored the initial full backup. My question
> > > is how can I add/restore the subsequent backups to the non-production
> > > server with out stepping on the data that was initially restored?
> > >
> >
> >
> > RESTORE WITH MOVE, followed perhaps by merging the data.
> >
> >
> > EG
> >
> > BACKUP DATABASE AdventureWorks
> > TO AdventureWorksBackups ;
> >
> >
> > RESTORE DATABASE AdventureWorks_200608
> > FROM AdventureWorksBackups
> > WITH MOVE 'AdventureWorks_Data' TO
> > 'C:\MySQLServer\AdventureWorks_200608.mdf',
> > MOVE 'AdventureWorks_Log' TO 'C:\MySQLServer\AdventureWorks_200608.ldf';
> >
> >
> > David|||"danielp" <danielsmith611@.gmail.com> wrote in message
news:1155158638.423828.3310@.h48g2000cwc.googlegroups.com...
> Or would log shipping work?
No. Your deletes would get shipped.
David|||"danielp" <danielsmith611@.gmail.com> wrote in message
news:1155149678.373420.184490@.i3g2000cwc.googlegroups.com...
> Do you have an example of the merge?
It's just SQL, or perhaps an SSIS package. What do the tables look like?
David

backup and resore encrypted data

I have two databases, one production and other a production copy.

One column of the production table is encrypted by the following syntax.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password!'

CREATE CERTIFICATE cert_demo

WITH SUBJECT = 'encryption demo'

CREATE SYMMETRIC KEY symkey_demo

WITH ALGORITHM = TRIPLE_DES

ENCRYPTION BY CERTIFICATE cert_demo

--

I have stored procedure to add records to a production table like this.

ALTER Procedure AddRecord

@.id int,

@.var1 varchar(100)

AS

BEGIN

DECLARE @.var2 varbinary(256)

OPEN SYMMETRIC KEY symkey_demo

DECRYPTION BY CERTIFICATE cert_demo

SET @.var2 = EncryptByKey(

key_guid( 'symkey_demo' ),

@.var1 )

insert into dbo.Test(id,Data,EData)

values(@.id,@.var1,@.var2)

CLOSE SYMMETRIC KEY symkey_demo

END

Now I need to decript the data in the production copy database. So I had export production data to the production copy database and also generates the same key and cerificate. When i tried to decrypt the data, no results has returned.

Create procedure ViewData

@.id int

AS

BEGIN

DECLARE @.var1 varbinary(256)

DECLARE @.var2 varchar(100)

OPEN SYMMETRIC KEY symkey_demo

DECRYPTION BY CERTIFICATE cert_demo

select @.var1=EData from Test where id=@.id

Set @.var2 = convert( varchar(100), DecryptByKey( @.var1))

print @.var2

END

It doesn't print the enctypted data. Please suggest what goes wrong.

The certificate protecting the key encrypting your data is protected by the DB master key (DBMK). In the server where the DBMK was created, the DBMK is protected by the password and by the service master kit (SMK). The SMK protection allows to use the DBMK automatically (i.e. without the need to open the DBMK explicitly).

When you move the DB to a different server, the protection by the SMK is no longer valid, and you need to manually open the DBMK and encrypt it with the current SMK. This is a one-time operation.

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<password>>'

Go

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Go

For more detailed information go to BOL:

http://msdn2.microsoft.com/en-us/library/ms174433.aspx

http://msdn2.microsoft.com/en-us/library/ms186937.aspx

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks Raul.

I had done two things.

1.The service master key doesn't working. So I have to manually open the DBMK and encrypt with the current SMK.

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password!'

Go

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

2. Again the symmetric key doesn't work.

Because keys cannot be individually backed up and restored, there is no direct way of moving a key from one database to another. However, by specifying the same values for the ALGORITHM, KEY_SOURCE, and IDENTITY_VALUE clauses of CREATE SYMMETRIC KEY, the same key can be generated on different databases.

so I have to change the symmetric key generation code to,

CREATE SYMMETRIC KEY symkey_demo

WITH ALGORITHM = TRIPLE_DES,

identity_value = 'Test Data encryption key',

key_source = 'test key'

ENCRYPTION BY CERTIFICATE cert_demo

Now I backed up the production database and restore it in a different server. I followed the stpe 1 and it works. Now I am able to decrypt the data in the production copy database.

Thanks

Amit

|||

I see, when you described a copy of the datbase I assumed (incorrectly) that you were referring to a copy based on a backup.

Thanks a lot for letting us know, and let us know if you have further questions or comments.

-Raul Garcia
SDE/T
SQL Server Engine

backup and resore encrypted data

I have two databases, one production and other a production copy.

One column of the production table is encrypted by the following syntax.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password!'

CREATE CERTIFICATE cert_demo

WITH SUBJECT = 'encryption demo'

CREATE SYMMETRIC KEY symkey_demo

WITH ALGORITHM = TRIPLE_DES

ENCRYPTION BY CERTIFICATE cert_demo

--

I have stored procedure to add records to a production table like this.

ALTER Procedure AddRecord

@.id int,

@.var1 varchar(100)

AS

BEGIN

DECLARE @.var2 varbinary(256)

OPEN SYMMETRIC KEY symkey_demo

DECRYPTION BY CERTIFICATE cert_demo

SET @.var2 = EncryptByKey(

key_guid( 'symkey_demo' ),

@.var1 )

insert into dbo.Test(id,Data,EData)

values(@.id,@.var1,@.var2)

CLOSE SYMMETRIC KEY symkey_demo

END

Now I need to decript the data in the production copy database. So I had export production data to the production copy database and also generates the same key and cerificate. When i tried to decrypt the data, no results has returned.

Create procedure ViewData

@.id int

AS

BEGIN

DECLARE @.var1 varbinary(256)

DECLARE @.var2 varchar(100)

OPEN SYMMETRIC KEY symkey_demo

DECRYPTION BY CERTIFICATE cert_demo

select @.var1=EData from Test where id=@.id

Set @.var2 = convert( varchar(100), DecryptByKey( @.var1))

print @.var2

END

It doesn't print the enctypted data. Please suggest what goes wrong.

The certificate protecting the key encrypting your data is protected by the DB master key (DBMK). In the server where the DBMK was created, the DBMK is protected by the password and by the service master kit (SMK). The SMK protection allows to use the DBMK automatically (i.e. without the need to open the DBMK explicitly).

When you move the DB to a different server, the protection by the SMK is no longer valid, and you need to manually open the DBMK and encrypt it with the current SMK. This is a one-time operation.

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<password>>'

Go

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Go

For more detailed information go to BOL:

http://msdn2.microsoft.com/en-us/library/ms174433.aspx

http://msdn2.microsoft.com/en-us/library/ms186937.aspx

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks Raul.

I had done two things.

1.The service master key doesn't working. So I have to manually open the DBMK and encrypt with the current SMK.

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password!'

Go

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

2. Again the symmetric key doesn't work.

Because keys cannot be individually backed up and restored, there is no direct way of moving a key from one database to another. However, by specifying the same values for the ALGORITHM, KEY_SOURCE, and IDENTITY_VALUE clauses of CREATE SYMMETRIC KEY, the same key can be generated on different databases.

so I have to change the symmetric key generation code to,

CREATE SYMMETRIC KEY symkey_demo

WITH ALGORITHM = TRIPLE_DES,

identity_value = 'Test Data encryption key',

key_source = 'test key'

ENCRYPTION BY CERTIFICATE cert_demo

Now I backed up the production database and restore it in a different server. I followed the stpe 1 and it works. Now I am able to decrypt the data in the production copy database.

Thanks

Amit

|||

I see, when you described a copy of the datbase I assumed (incorrectly) that you were referring to a copy based on a backup.

Thanks a lot for letting us know, and let us know if you have further questions or comments.

-Raul Garcia
SDE/T
SQL Server Engine

Backup 2GB database over WAN

Hi,
We have SQL server 2000 with 6 production dbs that we are putting into
a data center servered by a 2Mpbs internet link. Until now I have done
full backups at 02:00 every day then hourly log backups which are
taken offsite on tape each day.
I need to find a solution [cheap] which will allow me to continue
backing these dbs over a WAN link back to our HQ so I can restore
should the server die or other disaters strikes. The problem lies in
the fact one of the dbs is 1GB so wouldnt be suitable to take the
whole thing across as we are charged by the MB on data transfer. With
that in mind I looked at Red Gate software but it doesnt suggest
backing up over the WAN is a viable solution.
Can anyone suggest a better method to achieve this, sure I am not the
first person to have these requirements?
As you can probably guess I am no dba, just a sysadmin who is lumbered
with everything, to our director an IT person means you should be able
to do the lot!!
Cheers
Hi
Are you backing up to disc and then copying over the WAN? With SQL 2008
there will be compression options, with earlier versions you can use third
party tools such as Red Gate or roll your own solution although winzip has
file size limitations tools such as winrar has less of an issue. If you want
smaller files you can always back up to multiple files, this option may
actually speed up the backup especially if they are on separate discs with
their own controllers!
Another option would be to do differential backups and reduce the number of
full backups you do. This will not change the size of the full backups but
the differential backups should be smaller.
HTH
John
"gstar" <gary.brett@.gmail.com> wrote in message
news:baaf5b1f-f935-4d57-9850-e83c6f4fb5ef@.d21g2000prf.googlegroups.com...
> Hi,
> We have SQL server 2000 with 6 production dbs that we are putting into
> a data center servered by a 2Mpbs internet link. Until now I have done
> full backups at 02:00 every day then hourly log backups which are
> taken offsite on tape each day.
> I need to find a solution [cheap] which will allow me to continue
> backing these dbs over a WAN link back to our HQ so I can restore
> should the server die or other disaters strikes. The problem lies in
> the fact one of the dbs is 1GB so wouldnt be suitable to take the
> whole thing across as we are charged by the MB on data transfer. With
> that in mind I looked at Red Gate software but it doesnt suggest
> backing up over the WAN is a viable solution.
> Can anyone suggest a better method to achieve this, sure I am not the
> first person to have these requirements?
> As you can probably guess I am no dba, just a sysadmin who is lumbered
> with everything, to our director an IT person means you should be able
> to do the lot!!
> Cheers
|||Back up locally, then copy the resulting files after compressing.
If the database in teh data center goes south, you want the ability to
restore from disk without havnig to copy back to the server.
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"gstar" <gary.brett@.gmail.com> wrote in message
news:baaf5b1f-f935-4d57-9850-e83c6f4fb5ef@.d21g2000prf.googlegroups.com...
> Hi,
> We have SQL server 2000 with 6 production dbs that we are putting into
> a data center servered by a 2Mpbs internet link. Until now I have done
> full backups at 02:00 every day then hourly log backups which are
> taken offsite on tape each day.
> I need to find a solution [cheap] which will allow me to continue
> backing these dbs over a WAN link back to our HQ so I can restore
> should the server die or other disaters strikes. The problem lies in
> the fact one of the dbs is 1GB so wouldnt be suitable to take the
> whole thing across as we are charged by the MB on data transfer. With
> that in mind I looked at Red Gate software but it doesnt suggest
> backing up over the WAN is a viable solution.
> Can anyone suggest a better method to achieve this, sure I am not the
> first person to have these requirements?
> As you can probably guess I am no dba, just a sysadmin who is lumbered
> with everything, to our director an IT person means you should be able
> to do the lot!!
> Cheers
|||Agreed, but you might look into using a USB or other externally connected
drive. That way, if the server goes pear-shaped, you can still get at the
local backup files.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:eaCe3l7UIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Back up locally, then copy the resulting files after compressing.
> If the database in teh data center goes south, you want the ability to
> restore from disk without havnig to copy back to the server.
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "gstar" <gary.brett@.gmail.com> wrote in message
> news:baaf5b1f-f935-4d57-9850-e83c6f4fb5ef@.d21g2000prf.googlegroups.com...
>
|||Good point. I was thinking of the database level failure only, not the
whole server tanking...
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:Ok6ZM27UIHA.4696@.TK2MSFTNGP05.phx.gbl...
> Agreed, but you might look into using a USB or other externally connected
> drive. That way, if the server goes pear-shaped, you can still get at the
> local backup files.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:eaCe3l7UIHA.1212@.TK2MSFTNGP05.phx.gbl...
>
|||All vaild suggestions and much appreciated, however I really need to
limit the anount of traffic across the WAN as we get charged by the
MB. Unsure what type of compression a 1GB db would reduce down too but
copying dbs each night back to HQ over a month would wipe out over
30GB of out data allowance..
Currently all my backups are done over LAN, the server goes into data
centre next week so I have a week to play around and figure out the
best solution..
Situation now - All done using SQL backup jobs onto local disk, then
onto tape and offsite
MON - SAT - 02:00 - Full backups 1.3GB
MON - SAT - 08:30 >> 18:30 - Log backups on the hour
I would like to continue the schedule albeit without the tape option
meaning the backups will still run to the local disk [data center],
its how I get these files back to HQ without wiping out the alloted
data limit...
Cheers
|||Litespeed...backup file is 75-90% smaller than native SQL backups. Or
Red-Gate. Or (insert other vendors that I can't recall)...
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"gstar" <gary.brett@.gmail.com> wrote in message
news:9e9cfbdb-4e88-4cf4-a56a-50a3a98d3e3e@.i72g2000hsd.googlegroups.com...
> All vaild suggestions and much appreciated, however I really need to
> limit the anount of traffic across the WAN as we get charged by the
> MB. Unsure what type of compression a 1GB db would reduce down too but
> copying dbs each night back to HQ over a month would wipe out over
> 30GB of out data allowance..
> Currently all my backups are done over LAN, the server goes into data
> centre next week so I have a week to play around and figure out the
> best solution..
> Situation now - All done using SQL backup jobs onto local disk, then
> onto tape and offsite
> MON - SAT - 02:00 - Full backups 1.3GB
> MON - SAT - 08:30 >> 18:30 - Log backups on the hour
> I would like to continue the schedule albeit without the tape option
> meaning the backups will still run to the local disk [data center],
> its how I get these files back to HQ without wiping out the alloted
> data limit...
> Cheers
>
|||Idera makes one too.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:ewlS%23y8UIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Litespeed...backup file is 75-90% smaller than native SQL backups. Or
> Red-Gate. Or (insert other vendors that I can't recall)...
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "gstar" <gary.brett@.gmail.com> wrote in message
> news:9e9cfbdb-4e88-4cf4-a56a-50a3a98d3e3e@.i72g2000hsd.googlegroups.com...
>
|||Backup to disk and then zip the backup file, transfer it, unzip and restore.
This can be automated start to finish too using various mechanisms.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"gstar" <gary.brett@.gmail.com> wrote in message
news:baaf5b1f-f935-4d57-9850-e83c6f4fb5ef@.d21g2000prf.googlegroups.com...
> Hi,
> We have SQL server 2000 with 6 production dbs that we are putting into
> a data center servered by a 2Mpbs internet link. Until now I have done
> full backups at 02:00 every day then hourly log backups which are
> taken offsite on tape each day.
> I need to find a solution [cheap] which will allow me to continue
> backing these dbs over a WAN link back to our HQ so I can restore
> should the server die or other disaters strikes. The problem lies in
> the fact one of the dbs is 1GB so wouldnt be suitable to take the
> whole thing across as we are charged by the MB on data transfer. With
> that in mind I looked at Red Gate software but it doesnt suggest
> backing up over the WAN is a viable solution.
> Can anyone suggest a better method to achieve this, sure I am not the
> first person to have these requirements?
> As you can probably guess I am no dba, just a sysadmin who is lumbered
> with everything, to our director an IT person means you should be able
> to do the lot!!
> Cheers
|||"gstar" <gary.brett@.gmail.com> wrote in message
news:9e9cfbdb-4e88-4cf4-a56a-50a3a98d3e3e@.i72g2000hsd.googlegroups.com...
> All vaild suggestions and much appreciated, however I really need to
> limit the anount of traffic across the WAN as we get charged by the
> MB. Unsure what type of compression a 1GB db would reduce down too but
> copying dbs each night back to HQ over a month would wipe out over
> 30GB of out data allowance..
I've seen compression with Winzip 50-80%. So a 1GB backed up nightly would
be about 300MB. That would fit under 30GB.

> Currently all my backups are done over LAN, the server goes into data
> centre next week so I have a week to play around and figure out the
> best solution..
> Situation now - All done using SQL backup jobs onto local disk, then
> onto tape and offsite
> MON - SAT - 02:00 - Full backups 1.3GB
> MON - SAT - 08:30 >> 18:30 - Log backups on the hour
>
In theory you could do backups every other night also and continue to copy
over the logfiles and just extend your recovery period a bit.
So between compression and slightly reducing number of full backups, I think
you can keep under the 30GB.
(and of course the question to ask the finance guys... "how much would it
cost us to up from 30GB to 60GB vs. if we don't have adequate backups?"

> I would like to continue the schedule albeit without the tape option
> meaning the backups will still run to the local disk [data center],
> its how I get these files back to HQ without wiping out the alloted
> data limit...
> Cheers
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Backup 2GB database over WAN

Hi,
We have SQL server 2000 with 6 production dbs that we are putting into
a data center servered by a 2Mpbs internet link. Until now I have done
full backups at 02:00 every day then hourly log backups which are
taken offsite on tape each day.
I need to find a solution [cheap] which will allow me to continue
backing these dbs over a WAN link back to our HQ so I can restore
should the server die or other disaters strikes. The problem lies in
the fact one of the dbs is 1GB so wouldnt be suitable to take the
whole thing across as we are charged by the MB on data transfer. With
that in mind I looked at Red Gate software but it doesnt suggest
backing up over the WAN is a viable solution.
Can anyone suggest a better method to achieve this, sure I am not the
first person to have these requirements?
As you can probably guess I am no dba, just a sysadmin who is lumbered
with everything, to our director an IT person means you should be able
to do the lot!!
CheersHi
Are you backing up to disc and then copying over the WAN? With SQL 2008
there will be compression options, with earlier versions you can use third
party tools such as Red Gate or roll your own solution although winzip has
file size limitations tools such as winrar has less of an issue. If you want
smaller files you can always back up to multiple files, this option may
actually speed up the backup especially if they are on separate discs with
their own controllers!
Another option would be to do differential backups and reduce the number of
full backups you do. This will not change the size of the full backups but
the differential backups should be smaller.
HTH
John
"gstar" <gary.brett@.gmail.com> wrote in message
news:baaf5b1f-f935-4d57-9850-e83c6f4fb5ef@.d21g2000prf.googlegroups.com...
> Hi,
> We have SQL server 2000 with 6 production dbs that we are putting into
> a data center servered by a 2Mpbs internet link. Until now I have done
> full backups at 02:00 every day then hourly log backups which are
> taken offsite on tape each day.
> I need to find a solution [cheap] which will allow me to continue
> backing these dbs over a WAN link back to our HQ so I can restore
> should the server die or other disaters strikes. The problem lies in
> the fact one of the dbs is 1GB so wouldnt be suitable to take the
> whole thing across as we are charged by the MB on data transfer. With
> that in mind I looked at Red Gate software but it doesnt suggest
> backing up over the WAN is a viable solution.
> Can anyone suggest a better method to achieve this, sure I am not the
> first person to have these requirements?
> As you can probably guess I am no dba, just a sysadmin who is lumbered
> with everything, to our director an IT person means you should be able
> to do the lot!!
> Cheers|||Back up locally, then copy the resulting files after compressing.
If the database in teh data center goes south, you want the ability to
restore from disk without havnig to copy back to the server.
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"gstar" <gary.brett@.gmail.com> wrote in message
news:baaf5b1f-f935-4d57-9850-e83c6f4fb5ef@.d21g2000prf.googlegroups.com...
> Hi,
> We have SQL server 2000 with 6 production dbs that we are putting into
> a data center servered by a 2Mpbs internet link. Until now I have done
> full backups at 02:00 every day then hourly log backups which are
> taken offsite on tape each day.
> I need to find a solution [cheap] which will allow me to continue
> backing these dbs over a WAN link back to our HQ so I can restore
> should the server die or other disaters strikes. The problem lies in
> the fact one of the dbs is 1GB so wouldnt be suitable to take the
> whole thing across as we are charged by the MB on data transfer. With
> that in mind I looked at Red Gate software but it doesnt suggest
> backing up over the WAN is a viable solution.
> Can anyone suggest a better method to achieve this, sure I am not the
> first person to have these requirements?
> As you can probably guess I am no dba, just a sysadmin who is lumbered
> with everything, to our director an IT person means you should be able
> to do the lot!!
> Cheers|||Agreed, but you might look into using a USB or other externally connected
drive. That way, if the server goes pear-shaped, you can still get at the
local backup files.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:eaCe3l7UIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Back up locally, then copy the resulting files after compressing.
> If the database in teh data center goes south, you want the ability to
> restore from disk without havnig to copy back to the server.
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "gstar" <gary.brett@.gmail.com> wrote in message
> news:baaf5b1f-f935-4d57-9850-e83c6f4fb5ef@.d21g2000prf.googlegroups.com...
>> Hi,
>> We have SQL server 2000 with 6 production dbs that we are putting into
>> a data center servered by a 2Mpbs internet link. Until now I have done
>> full backups at 02:00 every day then hourly log backups which are
>> taken offsite on tape each day.
>> I need to find a solution [cheap] which will allow me to continue
>> backing these dbs over a WAN link back to our HQ so I can restore
>> should the server die or other disaters strikes. The problem lies in
>> the fact one of the dbs is 1GB so wouldnt be suitable to take the
>> whole thing across as we are charged by the MB on data transfer. With
>> that in mind I looked at Red Gate software but it doesnt suggest
>> backing up over the WAN is a viable solution.
>> Can anyone suggest a better method to achieve this, sure I am not the
>> first person to have these requirements?
>> As you can probably guess I am no dba, just a sysadmin who is lumbered
>> with everything, to our director an IT person means you should be able
>> to do the lot!!
>> Cheers
>|||Good point. I was thinking of the database level failure only, not the
whole server tanking...
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:Ok6ZM27UIHA.4696@.TK2MSFTNGP05.phx.gbl...
> Agreed, but you might look into using a USB or other externally connected
> drive. That way, if the server goes pear-shaped, you can still get at the
> local backup files.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:eaCe3l7UIHA.1212@.TK2MSFTNGP05.phx.gbl...
>> Back up locally, then copy the resulting files after compressing.
>> If the database in teh data center goes south, you want the ability to
>> restore from disk without havnig to copy back to the server.
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "gstar" <gary.brett@.gmail.com> wrote in message
>> news:baaf5b1f-f935-4d57-9850-e83c6f4fb5ef@.d21g2000prf.googlegroups.com...
>> Hi,
>> We have SQL server 2000 with 6 production dbs that we are putting into
>> a data center servered by a 2Mpbs internet link. Until now I have done
>> full backups at 02:00 every day then hourly log backups which are
>> taken offsite on tape each day.
>> I need to find a solution [cheap] which will allow me to continue
>> backing these dbs over a WAN link back to our HQ so I can restore
>> should the server die or other disaters strikes. The problem lies in
>> the fact one of the dbs is 1GB so wouldnt be suitable to take the
>> whole thing across as we are charged by the MB on data transfer. With
>> that in mind I looked at Red Gate software but it doesnt suggest
>> backing up over the WAN is a viable solution.
>> Can anyone suggest a better method to achieve this, sure I am not the
>> first person to have these requirements?
>> As you can probably guess I am no dba, just a sysadmin who is lumbered
>> with everything, to our director an IT person means you should be able
>> to do the lot!!
>> Cheers
>>
>|||All vaild suggestions and much appreciated, however I really need to
limit the anount of traffic across the WAN as we get charged by the
MB. Unsure what type of compression a 1GB db would reduce down too but
copying dbs each night back to HQ over a month would wipe out over
30GB of out data allowance..
Currently all my backups are done over LAN, the server goes into data
centre next week so I have a week to play around and figure out the
best solution..
Situation now - All done using SQL backup jobs onto local disk, then
onto tape and offsite
MON - SAT - 02:00 - Full backups 1.3GB
MON - SAT - 08:30 >> 18:30 - Log backups on the hour
I would like to continue the schedule albeit without the tape option
meaning the backups will still run to the local disk [data center],
its how I get these files back to HQ without wiping out the alloted
data limit...
Cheers|||Litespeed...backup file is 75-90% smaller than native SQL backups. Or
Red-Gate. Or (insert other vendors that I can't recall)...
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"gstar" <gary.brett@.gmail.com> wrote in message
news:9e9cfbdb-4e88-4cf4-a56a-50a3a98d3e3e@.i72g2000hsd.googlegroups.com...
> All vaild suggestions and much appreciated, however I really need to
> limit the anount of traffic across the WAN as we get charged by the
> MB. Unsure what type of compression a 1GB db would reduce down too but
> copying dbs each night back to HQ over a month would wipe out over
> 30GB of out data allowance..
> Currently all my backups are done over LAN, the server goes into data
> centre next week so I have a week to play around and figure out the
> best solution..
> Situation now - All done using SQL backup jobs onto local disk, then
> onto tape and offsite
> MON - SAT - 02:00 - Full backups 1.3GB
> MON - SAT - 08:30 >> 18:30 - Log backups on the hour
> I would like to continue the schedule albeit without the tape option
> meaning the backups will still run to the local disk [data center],
> its how I get these files back to HQ without wiping out the alloted
> data limit...
> Cheers
>|||Backup to disk and then zip the backup file, transfer it, unzip and restore.
This can be automated start to finish too using various mechanisms.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"gstar" <gary.brett@.gmail.com> wrote in message
news:baaf5b1f-f935-4d57-9850-e83c6f4fb5ef@.d21g2000prf.googlegroups.com...
> Hi,
> We have SQL server 2000 with 6 production dbs that we are putting into
> a data center servered by a 2Mpbs internet link. Until now I have done
> full backups at 02:00 every day then hourly log backups which are
> taken offsite on tape each day.
> I need to find a solution [cheap] which will allow me to continue
> backing these dbs over a WAN link back to our HQ so I can restore
> should the server die or other disaters strikes. The problem lies in
> the fact one of the dbs is 1GB so wouldnt be suitable to take the
> whole thing across as we are charged by the MB on data transfer. With
> that in mind I looked at Red Gate software but it doesnt suggest
> backing up over the WAN is a viable solution.
> Can anyone suggest a better method to achieve this, sure I am not the
> first person to have these requirements?
> As you can probably guess I am no dba, just a sysadmin who is lumbered
> with everything, to our director an IT person means you should be able
> to do the lot!!
> Cheers|||Idera makes one too.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:ewlS%23y8UIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Litespeed...backup file is 75-90% smaller than native SQL backups. Or
> Red-Gate. Or (insert other vendors that I can't recall)...
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "gstar" <gary.brett@.gmail.com> wrote in message
> news:9e9cfbdb-4e88-4cf4-a56a-50a3a98d3e3e@.i72g2000hsd.googlegroups.com...
>> All vaild suggestions and much appreciated, however I really need to
>> limit the anount of traffic across the WAN as we get charged by the
>> MB. Unsure what type of compression a 1GB db would reduce down too but
>> copying dbs each night back to HQ over a month would wipe out over
>> 30GB of out data allowance..
>> Currently all my backups are done over LAN, the server goes into data
>> centre next week so I have a week to play around and figure out the
>> best solution..
>> Situation now - All done using SQL backup jobs onto local disk, then
>> onto tape and offsite
>> MON - SAT - 02:00 - Full backups 1.3GB
>> MON - SAT - 08:30 >> 18:30 - Log backups on the hour
>> I would like to continue the schedule albeit without the tape option
>> meaning the backups will still run to the local disk [data center],
>> its how I get these files back to HQ without wiping out the alloted
>> data limit...
>> Cheers
>|||"gstar" <gary.brett@.gmail.com> wrote in message
news:9e9cfbdb-4e88-4cf4-a56a-50a3a98d3e3e@.i72g2000hsd.googlegroups.com...
> All vaild suggestions and much appreciated, however I really need to
> limit the anount of traffic across the WAN as we get charged by the
> MB. Unsure what type of compression a 1GB db would reduce down too but
> copying dbs each night back to HQ over a month would wipe out over
> 30GB of out data allowance..
I've seen compression with Winzip 50-80%. So a 1GB backed up nightly would
be about 300MB. That would fit under 30GB.
> Currently all my backups are done over LAN, the server goes into data
> centre next week so I have a week to play around and figure out the
> best solution..
> Situation now - All done using SQL backup jobs onto local disk, then
> onto tape and offsite
> MON - SAT - 02:00 - Full backups 1.3GB
> MON - SAT - 08:30 >> 18:30 - Log backups on the hour
>
In theory you could do backups every other night also and continue to copy
over the logfiles and just extend your recovery period a bit.
So between compression and slightly reducing number of full backups, I think
you can keep under the 30GB.
(and of course the question to ask the finance guys... "how much would it
cost us to up from 30GB to 60GB vs. if we don't have adequate backups?"
> I would like to continue the schedule albeit without the tape option
> meaning the backups will still run to the local disk [data center],
> its how I get these files back to HQ without wiping out the alloted
> data limit...
> Cheers
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Lovely stuff, thats given me something to crack on with, much
appreciated. This may sound stupid but would the following scenario be
viable and if so what sort of time would it take to fully restore
[approx]..
SUN - 02:00 - Full backups 1.3GB
> MON - SAT - 08:30 >> 18:30 - Log backups every 2 hours, meaning 6 logfiles per day approx 100mb per day.
This means I would only have 1 full backup per week, so should the
server die on Sunday morning I would need to restore last Sunbdays
full backup, then 30 log files...
Hmmm, looks a bit odd doesnt it?
Cheers|||"gstar" <gary.brett@.gmail.com> wrote in message
news:9f90951c-e505-4a5b-be4e-9101415dd3ad@.v29g2000hsf.googlegroups.com...
> Lovely stuff, thats given me something to crack on with, much
> appreciated. This may sound stupid but would the following scenario be
> viable and if so what sort of time would it take to fully restore
> [approx]..
> SUN - 02:00 - Full backups 1.3GB
>> MON - SAT - 08:30 >> 18:30 - Log backups every 2 hours, meaning 6
>> logfiles per day approx 100mb per day.
> This means I would only have 1 full backup per week, so should the
> server die on Sunday morning I would need to restore last Sunbdays
> full backup, then 30 log files...
> Hmmm, looks a bit odd doesnt it?
Yes and no.
You could schedule a differential each day if you wanted to minimize the
number of restores.
But, consider the case where you might do log backups every 10 minutes and a
full backup once a day. (That's what we did at our last job).
So 20 hours into the day you have 120 logfiles to restore.
Sounds like a pain but we routinely tested stuff like that.
If I were doing it manually I'd simply do
DIR *.log /b /od
Cut and paste that in query analyzer
Find/replace and prepend
restore log foo from disk='
and then append
' with norecevory
and then run it in a batch. Took about 5 minutes including cutting/pasting
and the find/replace.
So 30 logs, wouldn't bother me a bit.
> Cheers
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanx Greg
> If I were doing it manually I'd simply do DIR *.log /b /od, Cut and paste that in query analyzer
> Find/replace and prepend
> restore log foo from disk='
> and then append
> ' with norecevory
> and then run it in a batch.
What exactly would these commands be doing though, sorry to be thick!
Say I had db & logfile backups called sales.bak & sales.trn on D:
\Backups
salesdbfull.bak - 1gb
salesloghour.trn - 20mb
Into query analyzer -
DIR salesloghour.log /b /od
restore.bat - restore log foo from disk=D:\Backups
I'm not getting this am I!|||"gstar" <gary.brett@.gmail.com> wrote in message
news:f9bc834f-8e6f-4d23-af3f-979f483edc8a@.v29g2000hsf.googlegroups.com...
> Thanx Greg
>> If I were doing it manually I'd simply do DIR *.log /b /od, Cut and
>> paste that in query analyzer
>> Find/replace and prepend
>> restore log foo from disk='
>> and then append
>> ' with norecevory
>> and then run it in a batch.
> What exactly would these commands be doing though, sorry to be thick!
Hey, no problem. Better off asking questions now than after a disaster and
really be up a creak.
> Say I had db & logfile backups called sales.bak & sales.trn on D:
> \Backups
> salesdbfull.bak - 1gb
> salesloghour.trn - 20mb
>
Well I typically user maintenance plans or roll my own so my names would
more like:
sales_db_200801110200.bak - So, year, month, day, hour, minute.
Same for logs. So if I had four log files after that backup I'd have:
sales_log_200801110300.TRN
sales_log_200801110400.TRN
sales_log_200801110500.TRN
sales_log_200801110600.TRN
> Into query analyzer -
>
At the CMD prompt.I'd type
DIR sales_log_20080111*.TRN /b /od
That would return ONLY the names of the logs for the 11th with no
datetime/size information.
I'd cut and paste THAT list from DIR into query analyzer.
> DIR salesloghour.log /b /od
> restore.bat - restore log foo from disk=D:\Backups
>
In query analyzer I'd do a find on sales_log and replace it with restore log
sales from disk='sales_log
This would get me a series of commands:
restore log sales from disk='sales_log_200801110300.TRN
restore log sales from disk='sales_log_200801110400.TRN
restore log sales from disk='sales_log_200801110500.TRN
restore log sales from disk='sales_log_200801110600.TRN
And then I'd find .TRN and replace it with .TRN' with norecovery.
restore log sales from disk='sales_log_200801110300.TRN' with norecovery
restore log sales from disk='sales_log_200801110400.TRN' with norecovery
restore log sales from disk='sales_log_200801110500.TRN' with norecovery
restore log sales from disk='sales_log_200801110600.TRN' with norecovery
Then I'd execute that after restoring the DB backup itself with norecovery.
Once I'm satisified I haven't made any mistakes I'd
restore database sales with recovery
And we'd be good to go.
> I'm not getting this am I!
That's fine, ask away we'll do our best.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Backup ?

I do this to backup the database on our production server
BACKUP DATABASE [DP2] TO DISK = N'D:\mssql\data\MSSQL\BACKUP\DailyDbBack
up'
WITH INIT , NOUNLOAD , NAME = N'DP2 Backup Job', NOSKIP , STATS = 10,
NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='DP2'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
where database_name='DP2')
RESTORE VERIFYONLY FROM DISK = N'D:\mssql\data\MSSQL\BACKUP\DailyDbBack
up'
WITH FILE = @.i
It works fine. However if I change to a mapped drive it gives me the
following error. I had agent running under the system account with failures
so thought I would change to my account, it also fails. I have full control
of that directory from this machine in windows. What gives?
Executed as user: DP2SQL02\jcantley. Cannot open backup device
'V:\DailyDbBackup'. Device error or device off-line. See the SQL Server
error log for more details. [SQLSTATE 42000] (Error 3201) BACKUP DATABASE
is terminating abnormally. [SQLSTATE 42000] (Error 3013) Associated
statement is not prepared [SQLSTATE HY007] (Error 0) Cannot open backup
device 'V:\DailyDbBackup'. Device error or device off-line. See the SQL
Server error log for more details. [SQLSTATE 42000] (Error 3201) VERIFY
DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step
failed.See if this helps: http://vyaskn.tripod.com/administration_faq.htm#q17
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"JC" <striderguy56@.hotmail.com> wrote in message
news:OZ7MaIAIFHA.580@.TK2MSFTNGP15.phx.gbl...
I do this to backup the database on our production server
BACKUP DATABASE [DP2] TO DISK = N'D:\mssql\data\MSSQL\BACKUP\DailyDbBack
up'
WITH INIT , NOUNLOAD , NAME = N'DP2 Backup Job', NOSKIP , STATS = 10,
NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='DP2'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
where database_name='DP2')
RESTORE VERIFYONLY FROM DISK = N'D:\mssql\data\MSSQL\BACKUP\DailyDbBack
up'
WITH FILE = @.i
It works fine. However if I change to a mapped drive it gives me the
following error. I had agent running under the system account with failures
so thought I would change to my account, it also fails. I have full control
of that directory from this machine in windows. What gives?
Executed as user: DP2SQL02\jcantley. Cannot open backup device
'V:\DailyDbBackup'. Device error or device off-line. See the SQL Server
error log for more details. [SQLSTATE 42000] (Error 3201) BACKUP DATABASE
is terminating abnormally. [SQLSTATE 42000] (Error 3013) Associated
statement is not prepared [SQLSTATE HY007] (Error 0) Cannot open backup
device 'V:\DailyDbBackup'. Device error or device off-line. See the SQL
Server error log for more details. [SQLSTATE 42000] (Error 3201) VERIFY
DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step
failed.

Backup ?

I do this to backup the database on our production server
BACKUP DATABASE [DP2] TO DISK = N'D:\mssql\data\MSSQL\BACKUP\DailyDbBackup'
WITH INIT , NOUNLOAD , NAME = N'DP2 Backup Job', NOSKIP , STATS = 10,
NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='DP2'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
where database_name='DP2')
RESTORE VERIFYONLY FROM DISK = N'D:\mssql\data\MSSQL\BACKUP\DailyDbBackup'
WITH FILE = @.i
It works fine. However if I change to a mapped drive it gives me the
following error. I had agent running under the system account with failures
so thought I would change to my account, it also fails. I have full control
of that directory from this machine in windows. What gives?
Executed as user: DP2SQL02\jcantley. Cannot open backup device
'V:\DailyDbBackup'. Device error or device off-line. See the SQL Server error
log for more details. [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is
terminating abnormally. [SQLSTATE 42000] (Error 3013) Associated statement
is not prepared [SQLSTATE HY007] (Error 0) Cannot open backup device
'V:\DailyDbBackup'. Device error or device off-line. See the SQL Server error
log for more details. [SQLSTATE 42000] (Error 3201) VERIFY DATABASE is
terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Hi
Mapped drives are not supported. You have to use UNC paths.
Regards
Mike
"jcantley" wrote:

> I do this to backup the database on our production server
> BACKUP DATABASE [DP2] TO DISK = N'D:\mssql\data\MSSQL\BACKUP\DailyDbBackup'
> WITH INIT , NOUNLOAD , NAME = N'DP2 Backup Job', NOSKIP , STATS = 10,
> NOFORMAT DECLARE @.i INT
> select @.i = position from msdb..backupset where database_name='DP2'and
> type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
> where database_name='DP2')
> RESTORE VERIFYONLY FROM DISK = N'D:\mssql\data\MSSQL\BACKUP\DailyDbBackup'
> WITH FILE = @.i
> It works fine. However if I change to a mapped drive it gives me the
> following error. I had agent running under the system account with failures
> so thought I would change to my account, it also fails. I have full control
> of that directory from this machine in windows. What gives?
> Executed as user: DP2SQL02\jcantley. Cannot open backup device
> 'V:\DailyDbBackup'. Device error or device off-line. See the SQL Server error
> log for more details. [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is
> terminating abnormally. [SQLSTATE 42000] (Error 3013) Associated statement
> is not prepared [SQLSTATE HY007] (Error 0) Cannot open backup device
> 'V:\DailyDbBackup'. Device error or device off-line. See the SQL Server error
> log for more details. [SQLSTATE 42000] (Error 3201) VERIFY DATABASE is
> terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
|||Mike,
Here is what I get when i use UNC. We are running active dir could that be
an issue? I am running this under my account but would like to just run it
under system, but what ever works.
Executed as user: DP2SQL02\jcantley. Cannot open backup device
'\\Dp2sql01\BACKUP\DailyDbBackup'. Device error or device off-line. See the
SQL Server error log for more details. [SQLSTATE 42000] (Error 3201) BACKUP
DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013) Associated
statement is not prepared [SQLSTATE HY007] (Error 0) Cannot open backup
device '\\Dp2sql01\BACKUP\DailyDbBackup'. Device error or device off-line.
See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3201)
VERIFY DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).
The step failed.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Mapped drives are not supported. You have to use UNC paths.
> Regards
> Mike
> "jcantley" wrote:
|||Hi
Have you verified, that when you log on to the server as the account that
runs SQL server and Agent Service, then the folder is available and you have
write access to the folder?
What about the syntax for the TO DISK = ... is that correct? You specify TO
DISK = \\dp2sql01\BACKUP\DailyBackup - could it be that SQL reads this as a
folder rather than as a file? What if you try with ...TO DISK =
'\\dp2sql01\BACKUP\DailyBackup.bak' ?
Regards
Steen
StiderGuy wrote:[vbcol=seagreen]
> Mike,
> Here is what I get when i use UNC. We are running active dir could
> that be an issue? I am running this under my account but would like
> to just run it under system, but what ever works.
> Executed as user: DP2SQL02\jcantley. Cannot open backup device
> '\\Dp2sql01\BACKUP\DailyDbBackup'. Device error or device off-line.
> See the SQL Server error log for more details. [SQLSTATE 42000]
> (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE
> 42000] (Error 3013) Associated statement is not prepared [SQLSTATE
> HY007] (Error 0) Cannot open backup device
> '\\Dp2sql01\BACKUP\DailyDbBackup'. Device error or device off-line.
> See the SQL Server error log for more details. [SQLSTATE 42000]
> (Error 3201) VERIFY DATABASE is terminating abnormally. [SQLSTATE
> 42000] (Error 3013). The step failed.
> "Mike Epprecht (SQL MVP)" wrote:

Backup ?

I using the latest and greatest sp on sql2k. I had a failure the other day
on one of our production servers and the guy that used to maintain is gone.
Here is the strategy I am thinking of
Full Recovery Model,
3am - Backup the Db and transaction log
7am - first shift arrives
8am - first differential backup (runs every hour on the hour)
8:30am - first transaction log backup (runs everyhour on the half hour)
This will run all day until 2nd shift leaves at around midnight.
What I want to know is this, I look at the differential backup and see a
name for the last full backup preformed. When this differential backup runs
tomorrow morning will it be hitting the newly created 3am backup or the one
from yesterday when I created the differential backup.
Thanks,
jc
John,
I have some difficulty following you. Especially below part:

> What I want to know is this, I look at the differential backup and see a
> name for the last full backup preformed. When this differential backup runs
> tomorrow morning will it be hitting the newly created 3am backup or the one
> from yesterday when I created the differential backup.
I'm not sure where you "see" the name of the full backup. Also, what do you mean by "hitting the newly created
3am backup". Perhaps you can rephrase or explain a bit further. Or even post some BACKUP and/or RESTORE
commands?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John Cantley" <kayjohn59@.sbcglobal.net> wrote in message news:uSwuaWvJEHA.3592@.TK2MSFTNGP09.phx.gbl...
> I using the latest and greatest sp on sql2k. I had a failure the other day
> on one of our production servers and the guy that used to maintain is gone.
> Here is the strategy I am thinking of
> Full Recovery Model,
> 3am - Backup the Db and transaction log
> 7am - first shift arrives
> 8am - first differential backup (runs every hour on the hour)
> 8:30am - first transaction log backup (runs everyhour on the half hour)
> This will run all day until 2nd shift leaves at around midnight.
> What I want to know is this, I look at the differential backup and see a
> name for the last full backup preformed. When this differential backup runs
> tomorrow morning will it be hitting the newly created 3am backup or the one
> from yesterday when I created the differential backup.
> Thanks,
> jc
>

Backup ?

I do this to backup the database on our production server
BACKUP DATABASE [DP2] TO DISK = N'D:\mssql\data\MSSQL\BACKUP\DailyDbBackup'
WITH INIT , NOUNLOAD , NAME = N'DP2 Backup Job', NOSKIP , STATS = 10,
NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='DP2'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
where database_name='DP2')
RESTORE VERIFYONLY FROM DISK = N'D:\mssql\data\MSSQL\BACKUP\DailyDbBackup'
WITH FILE = @.i
It works fine. However if I change to a mapped drive it gives me the
following error. I had agent running under the system account with failures
so thought I would change to my account, it also fails. I have full control
of that directory from this machine in windows. What gives?
Executed as user: DP2SQL02\jcantley. Cannot open backup device
'V:\DailyDbBackup'. Device error or device off-line. See the SQL Server error
log for more details. [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is
terminating abnormally. [SQLSTATE 42000] (Error 3013) Associated statement
is not prepared [SQLSTATE HY007] (Error 0) Cannot open backup device
'V:\DailyDbBackup'. Device error or device off-line. See the SQL Server error
log for more details. [SQLSTATE 42000] (Error 3201) VERIFY DATABASE is
terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.Hi
Mapped drives are not supported. You have to use UNC paths.
Regards
Mike
"jcantley" wrote:
> I do this to backup the database on our production server
> BACKUP DATABASE [DP2] TO DISK = N'D:\mssql\data\MSSQL\BACKUP\DailyDbBackup'
> WITH INIT , NOUNLOAD , NAME = N'DP2 Backup Job', NOSKIP , STATS = 10,
> NOFORMAT DECLARE @.i INT
> select @.i = position from msdb..backupset where database_name='DP2'and
> type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
> where database_name='DP2')
> RESTORE VERIFYONLY FROM DISK = N'D:\mssql\data\MSSQL\BACKUP\DailyDbBackup'
> WITH FILE = @.i
> It works fine. However if I change to a mapped drive it gives me the
> following error. I had agent running under the system account with failures
> so thought I would change to my account, it also fails. I have full control
> of that directory from this machine in windows. What gives?
> Executed as user: DP2SQL02\jcantley. Cannot open backup device
> 'V:\DailyDbBackup'. Device error or device off-line. See the SQL Server error
> log for more details. [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is
> terminating abnormally. [SQLSTATE 42000] (Error 3013) Associated statement
> is not prepared [SQLSTATE HY007] (Error 0) Cannot open backup device
> 'V:\DailyDbBackup'. Device error or device off-line. See the SQL Server error
> log for more details. [SQLSTATE 42000] (Error 3201) VERIFY DATABASE is
> terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.|||Mike,
Here is what I get when i use UNC. We are running active dir could that be
an issue? I am running this under my account but would like to just run it
under system, but what ever works.
Executed as user: DP2SQL02\jcantley. Cannot open backup device
'\\Dp2sql01\BACKUP\DailyDbBackup'. Device error or device off-line. See the
SQL Server error log for more details. [SQLSTATE 42000] (Error 3201) BACKUP
DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013) Associated
statement is not prepared [SQLSTATE HY007] (Error 0) Cannot open backup
device '\\Dp2sql01\BACKUP\DailyDbBackup'. Device error or device off-line.
See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3201)
VERIFY DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).
The step failed.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Mapped drives are not supported. You have to use UNC paths.
> Regards
> Mike
> "jcantley" wrote:
> > I do this to backup the database on our production server
> >
> > BACKUP DATABASE [DP2] TO DISK = N'D:\mssql\data\MSSQL\BACKUP\DailyDbBackup'
> > WITH INIT , NOUNLOAD , NAME = N'DP2 Backup Job', NOSKIP , STATS = 10,
> > NOFORMAT DECLARE @.i INT
> > select @.i = position from msdb..backupset where database_name='DP2'and
> > type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
> > where database_name='DP2')
> > RESTORE VERIFYONLY FROM DISK = N'D:\mssql\data\MSSQL\BACKUP\DailyDbBackup'
> > WITH FILE = @.i
> >
> > It works fine. However if I change to a mapped drive it gives me the
> > following error. I had agent running under the system account with failures
> > so thought I would change to my account, it also fails. I have full control
> > of that directory from this machine in windows. What gives?
> >
> > Executed as user: DP2SQL02\jcantley. Cannot open backup device
> > 'V:\DailyDbBackup'. Device error or device off-line. See the SQL Server error
> > log for more details. [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is
> > terminating abnormally. [SQLSTATE 42000] (Error 3013) Associated statement
> > is not prepared [SQLSTATE HY007] (Error 0) Cannot open backup device
> > 'V:\DailyDbBackup'. Device error or device off-line. See the SQL Server error
> > log for more details. [SQLSTATE 42000] (Error 3201) VERIFY DATABASE is
> > terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.|||Hi
Have you verified, that when you log on to the server as the account that
runs SQL server and Agent Service, then the folder is available and you have
write access to the folder?
What about the syntax for the TO DISK = ... is that correct? You specify TO
DISK = \\dp2sql01\BACKUP\DailyBackup - could it be that SQL reads this as a
folder rather than as a file? What if you try with ...TO DISK ='\\dp2sql01\BACKUP\DailyBackup.bak' ?
Regards
Steen
StiderGuy wrote:
> Mike,
> Here is what I get when i use UNC. We are running active dir could
> that be an issue? I am running this under my account but would like
> to just run it under system, but what ever works.
> Executed as user: DP2SQL02\jcantley. Cannot open backup device
> '\\Dp2sql01\BACKUP\DailyDbBackup'. Device error or device off-line.
> See the SQL Server error log for more details. [SQLSTATE 42000]
> (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE
> 42000] (Error 3013) Associated statement is not prepared [SQLSTATE
> HY007] (Error 0) Cannot open backup device
> '\\Dp2sql01\BACKUP\DailyDbBackup'. Device error or device off-line.
> See the SQL Server error log for more details. [SQLSTATE 42000]
> (Error 3201) VERIFY DATABASE is terminating abnormally. [SQLSTATE
> 42000] (Error 3013). The step failed.
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Mapped drives are not supported. You have to use UNC paths.
>> Regards
>> Mike
>> "jcantley" wrote:
>> I do this to backup the database on our production server
>> BACKUP DATABASE [DP2] TO DISK =>> N'D:\mssql\data\MSSQL\BACKUP\DailyDbBackup' WITH INIT , NOUNLOAD
>> , NAME = N'DP2 Backup Job', NOSKIP , STATS = 10, NOFORMAT
>> DECLARE @.i INT
>> select @.i = position from msdb..backupset where
>> database_name='DP2'and type!='F' and backup_set_id=(select
>> max(backup_set_id) from msdb..backupset where database_name='DP2')
>> RESTORE VERIFYONLY FROM DISK =>> N'D:\mssql\data\MSSQL\BACKUP\DailyDbBackup' WITH FILE = @.i
>> It works fine. However if I change to a mapped drive it gives me the
>> following error. I had agent running under the system account with
>> failures so thought I would change to my account, it also fails. I
>> have full control of that directory from this machine in windows.
>> What gives?
>> Executed as user: DP2SQL02\jcantley. Cannot open backup device
>> 'V:\DailyDbBackup'. Device error or device off-line. See the SQL
>> Server error log for more details. [SQLSTATE 42000] (Error 3201)
>> BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error
>> 3013) Associated statement is not prepared [SQLSTATE HY007] (Error
>> 0) Cannot open backup device 'V:\DailyDbBackup'. Device error or
>> device off-line. See the SQL Server error log for more details.
>> [SQLSTATE 42000] (Error 3201) VERIFY DATABASE is terminating
>> abnormally. [SQLSTATE 42000] (Error 3013). The step failed.