We use Omniback for a backup solution. However most of the time, we could
not restore a database from the backups. We changed our strategy last week
to have the SQL Agent to backup all of the SQL Severs to a central location
and have Omniback backup the files.
Omniback took about an hour to backup a 1gig+ database. Here is the log file.
Database backed up: Database: pdi_warehouse_001_001, creation date(time):
2004/05/17(18:29:34), pages dumped: 18105899, first LSN: 5111210:44:1, last
LSN: 5111210:327:1, number of dump devices: 1, device information: (FILE=1,
TYPE=VIRTUAL_DEVICE: {'Data
Protector_(DEFAULT)_pdi_warehouse_001_001_17_36_36 '}).
Now, the same database took approximately 10 hours to back up last night.
Here is the log file.
Database backed up: Database: pdi_warehouse_001_001, creation date(time):
2004/05/17(18:29:34), pages dumped: 18546755, first LSN: 5143133:52:1, last
LSN: 5148376:689:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'\\co-ap-08\SqlBkups\FOCALPOINT_pdi_warehouse_001_001_20070 4111705.BAK'}).
Why so log when using the SQL Agent? There is a 1 gig connection from the
sql server to the backup media. Any suggestion will be appreciated.
brymer28303 wrote:
> We use Omniback for a backup solution. However most of the time, we could
> not restore a database from the backups. We changed our strategy last week
> to have the SQL Agent to backup all of the SQL Severs to a central location
> and have Omniback backup the files.
> Omniback took about an hour to backup a 1gig+ database. Here is the log file.
> Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> 2004/05/17(18:29:34), pages dumped: 18105899, first LSN: 5111210:44:1, last
> LSN: 5111210:327:1, number of dump devices: 1, device information: (FILE=1,
> TYPE=VIRTUAL_DEVICE: {'Data
> Protector_(DEFAULT)_pdi_warehouse_001_001_17_36_36 '}).
> Now, the same database took approximately 10 hours to back up last night.
> Here is the log file.
> Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> 2004/05/17(18:29:34), pages dumped: 18546755, first LSN: 5143133:52:1, last
> LSN: 5148376:689:1, number of dump devices: 1, device information: (FILE=1,
> TYPE=DISK:
> {'\\co-ap-08\SqlBkups\FOCALPOINT_pdi_warehouse_001_001_20070 4111705.BAK'}).
> Why so log when using the SQL Agent? There is a 1 gig connection from the
> sql server to the backup media. Any suggestion will be appreciated.
>
Hi,
Have you tried running the backup to a local disk in order to rule out
network issues? 10 hours for doing a 1 GB database backup is definitely
far too long, so something must have gone wrong. The backup destination
for the 2 backup scenarios seems to be different, so I think you should
start looking here.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
|||Thanks Steen. Since this post, we have started received network error 64.
This gives us a place to look.
""Steen Schlüter Persson (DK)"" wrote:
> brymer28303 wrote:
> Hi,
> Have you tried running the backup to a local disk in order to rule out
> network issues? 10 hours for doing a 1 GB database backup is definitely
> far too long, so something must have gone wrong. The backup destination
> for the 2 backup scenarios seems to be different, so I think you should
> start looking here.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>
Showing posts with label solution. Show all posts
Showing posts with label solution. Show all posts
Monday, March 26, 2012
Long Backups with SQL Agent
We use Omniback for a backup solution. However most of the time, we could
not restore a database from the backups. We changed our strategy last week
to have the SQL Agent to backup all of the SQL Severs to a central location
and have Omniback backup the files.
Omniback took about an hour to backup a 1gig+ database. Here is the log file.
Database backed up: Database: pdi_warehouse_001_001, creation date(time):
2004/05/17(18:29:34), pages dumped: 18105899, first LSN: 5111210:44:1, last
LSN: 5111210:327:1, number of dump devices: 1, device information: (FILE=1,
TYPE=VIRTUAL_DEVICE: {'Data
Protector_(DEFAULT)_pdi_warehouse_001_001_17_36_36'}).
Now, the same database took approximately 10 hours to back up last night.
Here is the log file.
Database backed up: Database: pdi_warehouse_001_001, creation date(time):
2004/05/17(18:29:34), pages dumped: 18546755, first LSN: 5143133:52:1, last
LSN: 5148376:689:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'\\co-ap-08\SqlBkups\FOCALPOINT_pdi_warehouse_001_001_200704111705.BAK'}).
Why so log when using the SQL Agent? There is a 1 gig connection from the
sql server to the backup media. Any suggestion will be appreciated.brymer28303 wrote:
> We use Omniback for a backup solution. However most of the time, we could
> not restore a database from the backups. We changed our strategy last week
> to have the SQL Agent to backup all of the SQL Severs to a central location
> and have Omniback backup the files.
> Omniback took about an hour to backup a 1gig+ database. Here is the log file.
> Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> 2004/05/17(18:29:34), pages dumped: 18105899, first LSN: 5111210:44:1, last
> LSN: 5111210:327:1, number of dump devices: 1, device information: (FILE=1,
> TYPE=VIRTUAL_DEVICE: {'Data
> Protector_(DEFAULT)_pdi_warehouse_001_001_17_36_36'}).
> Now, the same database took approximately 10 hours to back up last night.
> Here is the log file.
> Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> 2004/05/17(18:29:34), pages dumped: 18546755, first LSN: 5143133:52:1, last
> LSN: 5148376:689:1, number of dump devices: 1, device information: (FILE=1,
> TYPE=DISK:
> {'\\co-ap-08\SqlBkups\FOCALPOINT_pdi_warehouse_001_001_200704111705.BAK'}).
> Why so log when using the SQL Agent? There is a 1 gig connection from the
> sql server to the backup media. Any suggestion will be appreciated.
>
Hi,
Have you tried running the backup to a local disk in order to rule out
network issues? 10 hours for doing a 1 GB database backup is definitely
far too long, so something must have gone wrong. The backup destination
for the 2 backup scenarios seems to be different, so I think you should
start looking here.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Thanks Steen. Since this post, we have started received network error 64.
This gives us a place to look.
""Steen Schlüter Persson (DK)"" wrote:
> brymer28303 wrote:
> > We use Omniback for a backup solution. However most of the time, we could
> > not restore a database from the backups. We changed our strategy last week
> > to have the SQL Agent to backup all of the SQL Severs to a central location
> > and have Omniback backup the files.
> >
> > Omniback took about an hour to backup a 1gig+ database. Here is the log file.
> >
> > Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> > 2004/05/17(18:29:34), pages dumped: 18105899, first LSN: 5111210:44:1, last
> > LSN: 5111210:327:1, number of dump devices: 1, device information: (FILE=1,
> > TYPE=VIRTUAL_DEVICE: {'Data
> > Protector_(DEFAULT)_pdi_warehouse_001_001_17_36_36'}).
> >
> > Now, the same database took approximately 10 hours to back up last night.
> > Here is the log file.
> >
> > Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> > 2004/05/17(18:29:34), pages dumped: 18546755, first LSN: 5143133:52:1, last
> > LSN: 5148376:689:1, number of dump devices: 1, device information: (FILE=1,
> > TYPE=DISK:
> > {'\\co-ap-08\SqlBkups\FOCALPOINT_pdi_warehouse_001_001_200704111705.BAK'}).
> >
> > Why so log when using the SQL Agent? There is a 1 gig connection from the
> > sql server to the backup media. Any suggestion will be appreciated.
> >
> Hi,
> Have you tried running the backup to a local disk in order to rule out
> network issues? 10 hours for doing a 1 GB database backup is definitely
> far too long, so something must have gone wrong. The backup destination
> for the 2 backup scenarios seems to be different, so I think you should
> start looking here.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>
not restore a database from the backups. We changed our strategy last week
to have the SQL Agent to backup all of the SQL Severs to a central location
and have Omniback backup the files.
Omniback took about an hour to backup a 1gig+ database. Here is the log file.
Database backed up: Database: pdi_warehouse_001_001, creation date(time):
2004/05/17(18:29:34), pages dumped: 18105899, first LSN: 5111210:44:1, last
LSN: 5111210:327:1, number of dump devices: 1, device information: (FILE=1,
TYPE=VIRTUAL_DEVICE: {'Data
Protector_(DEFAULT)_pdi_warehouse_001_001_17_36_36'}).
Now, the same database took approximately 10 hours to back up last night.
Here is the log file.
Database backed up: Database: pdi_warehouse_001_001, creation date(time):
2004/05/17(18:29:34), pages dumped: 18546755, first LSN: 5143133:52:1, last
LSN: 5148376:689:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'\\co-ap-08\SqlBkups\FOCALPOINT_pdi_warehouse_001_001_200704111705.BAK'}).
Why so log when using the SQL Agent? There is a 1 gig connection from the
sql server to the backup media. Any suggestion will be appreciated.brymer28303 wrote:
> We use Omniback for a backup solution. However most of the time, we could
> not restore a database from the backups. We changed our strategy last week
> to have the SQL Agent to backup all of the SQL Severs to a central location
> and have Omniback backup the files.
> Omniback took about an hour to backup a 1gig+ database. Here is the log file.
> Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> 2004/05/17(18:29:34), pages dumped: 18105899, first LSN: 5111210:44:1, last
> LSN: 5111210:327:1, number of dump devices: 1, device information: (FILE=1,
> TYPE=VIRTUAL_DEVICE: {'Data
> Protector_(DEFAULT)_pdi_warehouse_001_001_17_36_36'}).
> Now, the same database took approximately 10 hours to back up last night.
> Here is the log file.
> Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> 2004/05/17(18:29:34), pages dumped: 18546755, first LSN: 5143133:52:1, last
> LSN: 5148376:689:1, number of dump devices: 1, device information: (FILE=1,
> TYPE=DISK:
> {'\\co-ap-08\SqlBkups\FOCALPOINT_pdi_warehouse_001_001_200704111705.BAK'}).
> Why so log when using the SQL Agent? There is a 1 gig connection from the
> sql server to the backup media. Any suggestion will be appreciated.
>
Hi,
Have you tried running the backup to a local disk in order to rule out
network issues? 10 hours for doing a 1 GB database backup is definitely
far too long, so something must have gone wrong. The backup destination
for the 2 backup scenarios seems to be different, so I think you should
start looking here.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Thanks Steen. Since this post, we have started received network error 64.
This gives us a place to look.
""Steen Schlüter Persson (DK)"" wrote:
> brymer28303 wrote:
> > We use Omniback for a backup solution. However most of the time, we could
> > not restore a database from the backups. We changed our strategy last week
> > to have the SQL Agent to backup all of the SQL Severs to a central location
> > and have Omniback backup the files.
> >
> > Omniback took about an hour to backup a 1gig+ database. Here is the log file.
> >
> > Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> > 2004/05/17(18:29:34), pages dumped: 18105899, first LSN: 5111210:44:1, last
> > LSN: 5111210:327:1, number of dump devices: 1, device information: (FILE=1,
> > TYPE=VIRTUAL_DEVICE: {'Data
> > Protector_(DEFAULT)_pdi_warehouse_001_001_17_36_36'}).
> >
> > Now, the same database took approximately 10 hours to back up last night.
> > Here is the log file.
> >
> > Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> > 2004/05/17(18:29:34), pages dumped: 18546755, first LSN: 5143133:52:1, last
> > LSN: 5148376:689:1, number of dump devices: 1, device information: (FILE=1,
> > TYPE=DISK:
> > {'\\co-ap-08\SqlBkups\FOCALPOINT_pdi_warehouse_001_001_200704111705.BAK'}).
> >
> > Why so log when using the SQL Agent? There is a 1 gig connection from the
> > sql server to the backup media. Any suggestion will be appreciated.
> >
> Hi,
> Have you tried running the backup to a local disk in order to rule out
> network issues? 10 hours for doing a 1 GB database backup is definitely
> far too long, so something must have gone wrong. The backup destination
> for the 2 backup scenarios seems to be different, so I think you should
> start looking here.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>
Long Backups with SQL Agent
We use Omniback for a backup solution. However most of the time, we could
not restore a database from the backups. We changed our strategy last week
to have the SQL Agent to backup all of the SQL Severs to a central location
and have Omniback backup the files.
Omniback took about an hour to backup a 1gig+ database. Here is the log fil
e.
Database backed up: Database: pdi_warehouse_001_001, creation date(time):
2004/05/17(18:29:34), pages dumped: 18105899, first LSN: 5111210:44:1, last
LSN: 5111210:327:1, number of dump devices: 1, device information: (FILE=1,
TYPE=VIRTUAL_DEVICE: {'Data
Protector_(DEFAULT)_pdi_warehouse_001_00
1_17_36_36'}).
Now, the same database took approximately 10 hours to back up last night.
Here is the log file.
Database backed up: Database: pdi_warehouse_001_001, creation date(time):
2004/05/17(18:29:34), pages dumped: 18546755, first LSN: 5143133:52:1, last
LSN: 5148376:689:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'\\co-ap- 08\SqlBkups\FOCALPOINT_pdi_warehouse_001
_001_200704111705.BAK
'}).
Why so log when using the SQL Agent? There is a 1 gig connection from the
sql server to the backup media. Any suggestion will be appreciated.brymer28303 wrote:
> We use Omniback for a backup solution. However most of the time, we could
> not restore a database from the backups. We changed our strategy last wee
k
> to have the SQL Agent to backup all of the SQL Severs to a central locatio
n
> and have Omniback backup the files.
> Omniback took about an hour to backup a 1gig+ database. Here is the log f
ile.
> Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> 2004/05/17(18:29:34), pages dumped: 18105899, first LSN: 5111210:44:1, las
t
> LSN: 5111210:327:1, number of dump devices: 1, device information: (FILE=1
,
> TYPE=VIRTUAL_DEVICE: {'Data
> Protector_(DEFAULT)_pdi_warehouse_001_00
1_17_36_36'}).
> Now, the same database took approximately 10 hours to back up last night.
> Here is the log file.
> Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> 2004/05/17(18:29:34), pages dumped: 18546755, first LSN: 5143133:52:1, las
t
> LSN: 5148376:689:1, number of dump devices: 1, device information: (FILE=1
,
> TYPE=DISK:
> {'\\co-ap- 08\SqlBkups\FOCALPOINT_pdi_warehouse_001
_001_200704111705.B
AK'}).
> Why so log when using the SQL Agent? There is a 1 gig connection from the
> sql server to the backup media. Any suggestion will be appreciated.
>
Hi,
Have you tried running the backup to a local disk in order to rule out
network issues? 10 hours for doing a 1 GB database backup is definitely
far too long, so something must have gone wrong. The backup destination
for the 2 backup scenarios seems to be different, so I think you should
start looking here.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Thanks Steen. Since this post, we have started received network error 64.
This gives us a place to look.
""Steen Schlüter Persson (DK)"" wrote:
> brymer28303 wrote:
> Hi,
> Have you tried running the backup to a local disk in order to rule out
> network issues? 10 hours for doing a 1 GB database backup is definitely
> far too long, so something must have gone wrong. The backup destination
> for the 2 backup scenarios seems to be different, so I think you should
> start looking here.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>sql
not restore a database from the backups. We changed our strategy last week
to have the SQL Agent to backup all of the SQL Severs to a central location
and have Omniback backup the files.
Omniback took about an hour to backup a 1gig+ database. Here is the log fil
e.
Database backed up: Database: pdi_warehouse_001_001, creation date(time):
2004/05/17(18:29:34), pages dumped: 18105899, first LSN: 5111210:44:1, last
LSN: 5111210:327:1, number of dump devices: 1, device information: (FILE=1,
TYPE=VIRTUAL_DEVICE: {'Data
Protector_(DEFAULT)_pdi_warehouse_001_00
1_17_36_36'}).
Now, the same database took approximately 10 hours to back up last night.
Here is the log file.
Database backed up: Database: pdi_warehouse_001_001, creation date(time):
2004/05/17(18:29:34), pages dumped: 18546755, first LSN: 5143133:52:1, last
LSN: 5148376:689:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'\\co-ap- 08\SqlBkups\FOCALPOINT_pdi_warehouse_001
_001_200704111705.BAK
'}).
Why so log when using the SQL Agent? There is a 1 gig connection from the
sql server to the backup media. Any suggestion will be appreciated.brymer28303 wrote:
> We use Omniback for a backup solution. However most of the time, we could
> not restore a database from the backups. We changed our strategy last wee
k
> to have the SQL Agent to backup all of the SQL Severs to a central locatio
n
> and have Omniback backup the files.
> Omniback took about an hour to backup a 1gig+ database. Here is the log f
ile.
> Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> 2004/05/17(18:29:34), pages dumped: 18105899, first LSN: 5111210:44:1, las
t
> LSN: 5111210:327:1, number of dump devices: 1, device information: (FILE=1
,
> TYPE=VIRTUAL_DEVICE: {'Data
> Protector_(DEFAULT)_pdi_warehouse_001_00
1_17_36_36'}).
> Now, the same database took approximately 10 hours to back up last night.
> Here is the log file.
> Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> 2004/05/17(18:29:34), pages dumped: 18546755, first LSN: 5143133:52:1, las
t
> LSN: 5148376:689:1, number of dump devices: 1, device information: (FILE=1
,
> TYPE=DISK:
> {'\\co-ap- 08\SqlBkups\FOCALPOINT_pdi_warehouse_001
_001_200704111705.B
AK'}).
> Why so log when using the SQL Agent? There is a 1 gig connection from the
> sql server to the backup media. Any suggestion will be appreciated.
>
Hi,
Have you tried running the backup to a local disk in order to rule out
network issues? 10 hours for doing a 1 GB database backup is definitely
far too long, so something must have gone wrong. The backup destination
for the 2 backup scenarios seems to be different, so I think you should
start looking here.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Thanks Steen. Since this post, we have started received network error 64.
This gives us a place to look.
""Steen Schlüter Persson (DK)"" wrote:
> brymer28303 wrote:
> Hi,
> Have you tried running the backup to a local disk in order to rule out
> network issues? 10 hours for doing a 1 GB database backup is definitely
> far too long, so something must have gone wrong. The backup destination
> for the 2 backup scenarios seems to be different, so I think you should
> start looking here.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>sql
Friday, March 23, 2012
logshipping
Yea maan, i think its very impressive,
bu i am away this technical issues for 3 months,
sorry maan.
I hope you will find more usefull solution technics..
Cihan.
"Melih" wrote:
> Hi,
> I'll take FULL and transaction LOG backups via a custom written stored
> procedure.
> But its working only for single database file. Does not dynamically pick u
p
> database files/filegroups from the backup files themselves.
> Is it possible to take backup of database contain files/filegroups?
> I used this code in stored procedure:
> BACKUP DATABASE @.db_name TO @.vbackupdevice
> WITH INIT, NAME=@.db_name, NOSKIP, STATS=10, DESCRIPTION=@.v_filename, NOFO
RMAT
>
> Any help will be appreciated.
> Kind Regards,
> Melih.
>Melih,
Your backup is probably working... Even when the database contains multiple
files, from one or more filegroups the backup file (given your command) will
contain all of this in a single backup file..
Do a small test , backup, then restore a multi-file database as a different
name. This should show you that things are working fine.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Melih" wrote:
> Hi,
> I'll take FULL and transaction LOG backups via a custom written stored
> procedure.
> But its working only for single database file. Does not dynamically pick u
p
> database files/filegroups from the backup files themselves.
> Is it possible to take backup of database contain files/filegroups?
> I used this code in stored procedure:
> BACKUP DATABASE @.db_name TO @.vbackupdevice
> WITH INIT, NAME=@.db_name, NOSKIP, STATS=10, DESCRIPTION=@.v_filename, NOFO
RMAT
>
> Any help will be appreciated.
> Kind Regards,
> Melih.
>|||Hi,
I'll take FULL and transaction LOG backups via a custom written stored
procedure.
But its working only for single database file. Does not dynamically pick up
database files/filegroups from the backup files themselves.
Is it possible to take backup of database contain files/filegroups?
I used this code in stored procedure:
BACKUP DATABASE @.db_name TO @.vbackupdevice
WITH INIT, NAME=@.db_name, NOSKIP, STATS=10, DESCRIPTION=@.v_filename, NOFORMA
T
Any help will be appreciated.
Kind Regards,
Melih.|||Yea maan, i think its very impressive,
bu i am away this technical issues for 3 months,
sorry maan.
I hope you will find more usefull solution technics..
Cihan.
"Melih" wrote:
> Hi,
> I'll take FULL and transaction LOG backups via a custom written stored
> procedure.
> But its working only for single database file. Does not dynamically pick u
p
> database files/filegroups from the backup files themselves.
> Is it possible to take backup of database contain files/filegroups?
> I used this code in stored procedure:
> BACKUP DATABASE @.db_name TO @.vbackupdevice
> WITH INIT, NAME=@.db_name, NOSKIP, STATS=10, DESCRIPTION=@.v_filename, NOFO
RMAT
>
> Any help will be appreciated.
> Kind Regards,
> Melih.
>|||Melih,
Your backup is probably working... Even when the database contains multiple
files, from one or more filegroups the backup file (given your command) will
contain all of this in a single backup file..
Do a small test , backup, then restore a multi-file database as a different
name. This should show you that things are working fine.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Melih" wrote:
> Hi,
> I'll take FULL and transaction LOG backups via a custom written stored
> procedure.
> But its working only for single database file. Does not dynamically pick u
p
> database files/filegroups from the backup files themselves.
> Is it possible to take backup of database contain files/filegroups?
> I used this code in stored procedure:
> BACKUP DATABASE @.db_name TO @.vbackupdevice
> WITH INIT, NAME=@.db_name, NOSKIP, STATS=10, DESCRIPTION=@.v_filename, NOFO
RMAT
>
> Any help will be appreciated.
> Kind Regards,
> Melih.
>|||I tested and get result:
Database 'Exchange' does not exist. Check sysdatabases.
ALTER DATABASE statement failed.
Device activation error. The physical file name 'C:\DATABASES
SQL2K\Exchange_Data2_Data.NDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'Exchange_Data2' cannot be restored to 'C:\DATABASES
SQL2K\Exchange_Data2_Data.NDF'. Use WITH MOVE to identify a valid location
for the file.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'C:\DATABASES
SQL2K\Exchange_Data3_Data.NDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'Exchange_Data3' cannot be restored to 'C:\DATABASES
SQL2K\Exchange_Data3_Data.NDF'. Use WITH MOVE to identify a valid location
for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I used this code in stored procedure:
set @.found_full = 0
if @.@.fetch_status = 0 and @.found_full = 0
begin
set @.sqlstring = 'alter database ' + @.dbname + ' set RESTRICTED_USER with
rollback immediate'
exec (@.sqlstring)
set @.sqlstring = 'alter database ' + @.dbname + ' set MULTI_USER with
rollback immediate'
exec (@.sqlstring)
-- check file isnt zipped, if so decompress & restore
if charindex('.gz', @.restore_filename) > 0 or charindex('.zip',
@.restore_filename) > 0
begin
SELECT @.sqlstring = @.zippath + '\gzip.exe -d ' + @.backuppath +
@.restore_filename
EXEC @.error = master..xp_cmdshell @.sqlstring, NO_OUTPUT
set @.restore_filename = replace(@.restore_filename, '.gz', '')
set @.restore_filename = replace(@.restore_filename, '.zip', '')
end
--set @.sqlstring = 'alter database ' + @.dbname + ' set RESTRICTED_USER
with rollback immediate'
--exec (@.sqlstring)
set @.sqlstring = 'RESTORE DATABASE ' + @.dbname + ' ' +
'FROM DISK= ''' + @.backuppath + @.restore_filename + ''' ' +
'WITH ' + @.fullbackupmovecommand + ' , STANDBY = ''' + @.standbyfile + ''''
exec (@.sqlstring)
etc......|||I tested and get result:
Database 'Exchange' does not exist. Check sysdatabases.
ALTER DATABASE statement failed.
Device activation error. The physical file name 'C:\DATABASES
SQL2K\Exchange_Data2_Data.NDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'Exchange_Data2' cannot be restored to 'C:\DATABASES
SQL2K\Exchange_Data2_Data.NDF'. Use WITH MOVE to identify a valid location
for the file.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'C:\DATABASES
SQL2K\Exchange_Data3_Data.NDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'Exchange_Data3' cannot be restored to 'C:\DATABASES
SQL2K\Exchange_Data3_Data.NDF'. Use WITH MOVE to identify a valid location
for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I used this code in stored procedure:
set @.found_full = 0
if @.@.fetch_status = 0 and @.found_full = 0
begin
set @.sqlstring = 'alter database ' + @.dbname + ' set RESTRICTED_USER with
rollback immediate'
exec (@.sqlstring)
set @.sqlstring = 'alter database ' + @.dbname + ' set MULTI_USER with
rollback immediate'
exec (@.sqlstring)
-- check file isnt zipped, if so decompress & restore
if charindex('.gz', @.restore_filename) > 0 or charindex('.zip',
@.restore_filename) > 0
begin
SELECT @.sqlstring = @.zippath + '\gzip.exe -d ' + @.backuppath +
@.restore_filename
EXEC @.error = master..xp_cmdshell @.sqlstring, NO_OUTPUT
set @.restore_filename = replace(@.restore_filename, '.gz', '')
set @.restore_filename = replace(@.restore_filename, '.zip', '')
end
--set @.sqlstring = 'alter database ' + @.dbname + ' set RESTRICTED_USER
with rollback immediate'
--exec (@.sqlstring)
set @.sqlstring = 'RESTORE DATABASE ' + @.dbname + ' ' +
'FROM DISK= ''' + @.backuppath + @.restore_filename + ''' ' +
'WITH ' + @.fullbackupmovecommand + ' , STANDBY = ''' + @.standbyfile + ''''
exec (@.sqlstring)
etc......
bu i am away this technical issues for 3 months,
sorry maan.
I hope you will find more usefull solution technics..
Cihan.
"Melih" wrote:
> Hi,
> I'll take FULL and transaction LOG backups via a custom written stored
> procedure.
> But its working only for single database file. Does not dynamically pick u
p
> database files/filegroups from the backup files themselves.
> Is it possible to take backup of database contain files/filegroups?
> I used this code in stored procedure:
> BACKUP DATABASE @.db_name TO @.vbackupdevice
> WITH INIT, NAME=@.db_name, NOSKIP, STATS=10, DESCRIPTION=@.v_filename, NOFO
RMAT
>
> Any help will be appreciated.
> Kind Regards,
> Melih.
>Melih,
Your backup is probably working... Even when the database contains multiple
files, from one or more filegroups the backup file (given your command) will
contain all of this in a single backup file..
Do a small test , backup, then restore a multi-file database as a different
name. This should show you that things are working fine.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Melih" wrote:
> Hi,
> I'll take FULL and transaction LOG backups via a custom written stored
> procedure.
> But its working only for single database file. Does not dynamically pick u
p
> database files/filegroups from the backup files themselves.
> Is it possible to take backup of database contain files/filegroups?
> I used this code in stored procedure:
> BACKUP DATABASE @.db_name TO @.vbackupdevice
> WITH INIT, NAME=@.db_name, NOSKIP, STATS=10, DESCRIPTION=@.v_filename, NOFO
RMAT
>
> Any help will be appreciated.
> Kind Regards,
> Melih.
>|||Hi,
I'll take FULL and transaction LOG backups via a custom written stored
procedure.
But its working only for single database file. Does not dynamically pick up
database files/filegroups from the backup files themselves.
Is it possible to take backup of database contain files/filegroups?
I used this code in stored procedure:
BACKUP DATABASE @.db_name TO @.vbackupdevice
WITH INIT, NAME=@.db_name, NOSKIP, STATS=10, DESCRIPTION=@.v_filename, NOFORMA
T
Any help will be appreciated.
Kind Regards,
Melih.|||Yea maan, i think its very impressive,
bu i am away this technical issues for 3 months,
sorry maan.
I hope you will find more usefull solution technics..
Cihan.
"Melih" wrote:
> Hi,
> I'll take FULL and transaction LOG backups via a custom written stored
> procedure.
> But its working only for single database file. Does not dynamically pick u
p
> database files/filegroups from the backup files themselves.
> Is it possible to take backup of database contain files/filegroups?
> I used this code in stored procedure:
> BACKUP DATABASE @.db_name TO @.vbackupdevice
> WITH INIT, NAME=@.db_name, NOSKIP, STATS=10, DESCRIPTION=@.v_filename, NOFO
RMAT
>
> Any help will be appreciated.
> Kind Regards,
> Melih.
>|||Melih,
Your backup is probably working... Even when the database contains multiple
files, from one or more filegroups the backup file (given your command) will
contain all of this in a single backup file..
Do a small test , backup, then restore a multi-file database as a different
name. This should show you that things are working fine.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Melih" wrote:
> Hi,
> I'll take FULL and transaction LOG backups via a custom written stored
> procedure.
> But its working only for single database file. Does not dynamically pick u
p
> database files/filegroups from the backup files themselves.
> Is it possible to take backup of database contain files/filegroups?
> I used this code in stored procedure:
> BACKUP DATABASE @.db_name TO @.vbackupdevice
> WITH INIT, NAME=@.db_name, NOSKIP, STATS=10, DESCRIPTION=@.v_filename, NOFO
RMAT
>
> Any help will be appreciated.
> Kind Regards,
> Melih.
>|||I tested and get result:
Database 'Exchange' does not exist. Check sysdatabases.
ALTER DATABASE statement failed.
Device activation error. The physical file name 'C:\DATABASES
SQL2K\Exchange_Data2_Data.NDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'Exchange_Data2' cannot be restored to 'C:\DATABASES
SQL2K\Exchange_Data2_Data.NDF'. Use WITH MOVE to identify a valid location
for the file.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'C:\DATABASES
SQL2K\Exchange_Data3_Data.NDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'Exchange_Data3' cannot be restored to 'C:\DATABASES
SQL2K\Exchange_Data3_Data.NDF'. Use WITH MOVE to identify a valid location
for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I used this code in stored procedure:
set @.found_full = 0
if @.@.fetch_status = 0 and @.found_full = 0
begin
set @.sqlstring = 'alter database ' + @.dbname + ' set RESTRICTED_USER with
rollback immediate'
exec (@.sqlstring)
set @.sqlstring = 'alter database ' + @.dbname + ' set MULTI_USER with
rollback immediate'
exec (@.sqlstring)
-- check file isnt zipped, if so decompress & restore
if charindex('.gz', @.restore_filename) > 0 or charindex('.zip',
@.restore_filename) > 0
begin
SELECT @.sqlstring = @.zippath + '\gzip.exe -d ' + @.backuppath +
@.restore_filename
EXEC @.error = master..xp_cmdshell @.sqlstring, NO_OUTPUT
set @.restore_filename = replace(@.restore_filename, '.gz', '')
set @.restore_filename = replace(@.restore_filename, '.zip', '')
end
--set @.sqlstring = 'alter database ' + @.dbname + ' set RESTRICTED_USER
with rollback immediate'
--exec (@.sqlstring)
set @.sqlstring = 'RESTORE DATABASE ' + @.dbname + ' ' +
'FROM DISK= ''' + @.backuppath + @.restore_filename + ''' ' +
'WITH ' + @.fullbackupmovecommand + ' , STANDBY = ''' + @.standbyfile + ''''
exec (@.sqlstring)
etc......|||I tested and get result:
Database 'Exchange' does not exist. Check sysdatabases.
ALTER DATABASE statement failed.
Device activation error. The physical file name 'C:\DATABASES
SQL2K\Exchange_Data2_Data.NDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'Exchange_Data2' cannot be restored to 'C:\DATABASES
SQL2K\Exchange_Data2_Data.NDF'. Use WITH MOVE to identify a valid location
for the file.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'C:\DATABASES
SQL2K\Exchange_Data3_Data.NDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'Exchange_Data3' cannot be restored to 'C:\DATABASES
SQL2K\Exchange_Data3_Data.NDF'. Use WITH MOVE to identify a valid location
for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I used this code in stored procedure:
set @.found_full = 0
if @.@.fetch_status = 0 and @.found_full = 0
begin
set @.sqlstring = 'alter database ' + @.dbname + ' set RESTRICTED_USER with
rollback immediate'
exec (@.sqlstring)
set @.sqlstring = 'alter database ' + @.dbname + ' set MULTI_USER with
rollback immediate'
exec (@.sqlstring)
-- check file isnt zipped, if so decompress & restore
if charindex('.gz', @.restore_filename) > 0 or charindex('.zip',
@.restore_filename) > 0
begin
SELECT @.sqlstring = @.zippath + '\gzip.exe -d ' + @.backuppath +
@.restore_filename
EXEC @.error = master..xp_cmdshell @.sqlstring, NO_OUTPUT
set @.restore_filename = replace(@.restore_filename, '.gz', '')
set @.restore_filename = replace(@.restore_filename, '.zip', '')
end
--set @.sqlstring = 'alter database ' + @.dbname + ' set RESTRICTED_USER
with rollback immediate'
--exec (@.sqlstring)
set @.sqlstring = 'RESTORE DATABASE ' + @.dbname + ' ' +
'FROM DISK= ''' + @.backuppath + @.restore_filename + ''' ' +
'WITH ' + @.fullbackupmovecommand + ' , STANDBY = ''' + @.standbyfile + ''''
exec (@.sqlstring)
etc......
Wednesday, March 21, 2012
Logon window - Another one
Hi all,
I'm experiencing problems that so many people already had...except for
the fact, that no given solution worked for me.
Some of my users are getting windows-logon-popups, when they click on a
report. Admins have no troubles, just *some* users.
I tried Adding them in the virtual server security dialog, didn't work.
I tried the IE settings, didn't solve the problem.
Does anybody have other ideas?
Regards,
Bj=F6rnAdd the report server to the INTRANET zone (you want it to pass the users
authentication credentials). The INTRANET zone by default does this.
When I say add it to the INTRANET zone, be sure to add the fully qualified
name, not just the server name.
i.e. http://reportserver.mydomain
=-Chris
"Bjorn" <bjorn.vaessen@.gmail.com> wrote in message
news:1161766822.164928.118120@.h48g2000cwc.googlegroups.com...
Hi all,
I'm experiencing problems that so many people already had...except for
the fact, that no given solution worked for me.
Some of my users are getting windows-logon-popups, when they click on a
report. Admins have no troubles, just *some* users.
I tried Adding them in the virtual server security dialog, didn't work.
I tried the IE settings, didn't solve the problem.
Does anybody have other ideas?
Regards,
Björn
I'm experiencing problems that so many people already had...except for
the fact, that no given solution worked for me.
Some of my users are getting windows-logon-popups, when they click on a
report. Admins have no troubles, just *some* users.
I tried Adding them in the virtual server security dialog, didn't work.
I tried the IE settings, didn't solve the problem.
Does anybody have other ideas?
Regards,
Bj=F6rnAdd the report server to the INTRANET zone (you want it to pass the users
authentication credentials). The INTRANET zone by default does this.
When I say add it to the INTRANET zone, be sure to add the fully qualified
name, not just the server name.
i.e. http://reportserver.mydomain
=-Chris
"Bjorn" <bjorn.vaessen@.gmail.com> wrote in message
news:1161766822.164928.118120@.h48g2000cwc.googlegroups.com...
Hi all,
I'm experiencing problems that so many people already had...except for
the fact, that no given solution worked for me.
Some of my users are getting windows-logon-popups, when they click on a
report. Admins have no troubles, just *some* users.
I tried Adding them in the virtual server security dialog, didn't work.
I tried the IE settings, didn't solve the problem.
Does anybody have other ideas?
Regards,
Björn
Monday, March 12, 2012
logins lock
hi ,
how can we lock and login on a sql server as we can do it
in sybase by sp_locklogin?
do we an alternate solution as we dont have sp_locklogin
sp in mssql 2k .Hi,
Only Windows based users can be locked. SQL server by itself do not have any
user or password polycies.
So for SQL server based logins we can not set the lockout option.
Thanks
Hari
MCDBA
"sid" <sid_m15@.coolgoose.com> wrote in message
news:571801c4811d$8d8e6290$a501280a@.phx.gbl...
> hi ,
> how can we lock and login on a sql server as we can do it
> in sybase by sp_locklogin?
> do we an alternate solution as we dont have sp_locklogin
> sp in mssql 2k .
>|||This will be possible for SQL logins in SQL2005 however.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OtKeCpTgEHA.3548@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Only Windows based users can be locked. SQL server by itself do not have
> any
> user or password polycies.
> So for SQL server based logins we can not set the lockout option.
>
>
> Thanks
> Hari
> MCDBA
> "sid" <sid_m15@.coolgoose.com> wrote in message
> news:571801c4811d$8d8e6290$a501280a@.phx.gbl...
>|||thanks a lot
sid
>--Original Message--
>This will be possible for SQL logins in SQL2005 however.
>--
>HTH
>Jasper Smith (SQL Server MVP)
>http://www.sqldbatips.com
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in
message
>news:OtKeCpTgEHA.3548@.TK2MSFTNGP09.phx.gbl...
itself do not have[vbcol=seagreen]
lockout option.[vbcol=seagreen]
it[vbcol=seagreen]
sp_locklogin[vbcol=seagreen]
>
>.
>|||thanks a lot
sid
>--Original Message--
>Hi,
>Only Windows based users can be locked. SQL server by
itself do not have any
>user or password polycies.
>So for SQL server based logins we can not set the lockout
option.
>
>
>Thanks
>Hari
>MCDBA
>"sid" <sid_m15@.coolgoose.com> wrote in message
>news:571801c4811d$8d8e6290$a501280a@.phx.gbl...
it[vbcol=seagreen]
>
>.
>
how can we lock and login on a sql server as we can do it
in sybase by sp_locklogin?
do we an alternate solution as we dont have sp_locklogin
sp in mssql 2k .Hi,
Only Windows based users can be locked. SQL server by itself do not have any
user or password polycies.
So for SQL server based logins we can not set the lockout option.
Thanks
Hari
MCDBA
"sid" <sid_m15@.coolgoose.com> wrote in message
news:571801c4811d$8d8e6290$a501280a@.phx.gbl...
> hi ,
> how can we lock and login on a sql server as we can do it
> in sybase by sp_locklogin?
> do we an alternate solution as we dont have sp_locklogin
> sp in mssql 2k .
>|||This will be possible for SQL logins in SQL2005 however.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OtKeCpTgEHA.3548@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Only Windows based users can be locked. SQL server by itself do not have
> any
> user or password polycies.
> So for SQL server based logins we can not set the lockout option.
>
>
> Thanks
> Hari
> MCDBA
> "sid" <sid_m15@.coolgoose.com> wrote in message
> news:571801c4811d$8d8e6290$a501280a@.phx.gbl...
>|||thanks a lot
sid
>--Original Message--
>This will be possible for SQL logins in SQL2005 however.
>--
>HTH
>Jasper Smith (SQL Server MVP)
>http://www.sqldbatips.com
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in
message
>news:OtKeCpTgEHA.3548@.TK2MSFTNGP09.phx.gbl...
itself do not have[vbcol=seagreen]
lockout option.[vbcol=seagreen]
it[vbcol=seagreen]
sp_locklogin[vbcol=seagreen]
>
>.
>|||thanks a lot
sid
>--Original Message--
>Hi,
>Only Windows based users can be locked. SQL server by
itself do not have any
>user or password polycies.
>So for SQL server based logins we can not set the lockout
option.
>
>
>Thanks
>Hari
>MCDBA
>"sid" <sid_m15@.coolgoose.com> wrote in message
>news:571801c4811d$8d8e6290$a501280a@.phx.gbl...
it[vbcol=seagreen]
>
>.
>
Friday, February 24, 2012
Login prompt when deploying solution/report to report server
Hi there,
I've seen some similar posts to this one, however none with an answer and I'm wondering if anyone has actually figured out what causes this issue. I'm running SQL Server 2005 Reporting Services.
I'm attempting to implement a folder structure for the report server, therefore am creating individual solutions in Visual Studio for each folder. At the top level i.e http://servername/reportserver/ I am able to deploy reports no problem.
However creating a folder at the next level in the tree and then deploying to that level such as http://servername/reportserver/ManagementReports causes a Reporting Services Login prompt to appear. No matter what user credentials I enter they are not accepted. I have full administrator rights the server, the site and the folders in question.
Has anyone else experienced this? Does anyone have any suggestions?
Thanks
Matt
You need to split your deployment path. Remove the folder name from TargetServerURL and add it to TargetReportFolder.
If you want to have a new report projoect that gets deployed to a folder under your ManagementReports folder, you change TargetReportFolder to be ManagementReports/Name of new folder.
Kaisa
|||Thanks very much Kaisa, that has worked. I should have spotted that
Subscribe to:
Posts (Atom)