We are moving backups from being written on a local box, to writing tham on a
NAS and making a daily copy to a report server. So, to start, I made a manual
copy of the backup files to the NAS and then wen't to restore tham.
What I get is an error message that includes the path name from the machine
where the backup was made as well as the path name for the log file.
Duh!
I can see that I need to add the 'WITH MOVE' parameters to the restore
command, but I can't get them to work. This is probably because I flat-out
don't understand what is happening. Once I say where the full backup is, why
does it care about the path is was backed up to? Why does it care about
restoring a log file I didn't ask for?
Once I change the backup path on the production machine to the NAS (so that
the paths are the same, will this issue just go away?> I can see that I need to add the 'WITH MOVE' parameters to the restore
> command, but I can't get them to work. This is probably because I flat-out
> don't understand what is happening. Once I say where the full backup is,
> why
> does it care about the path is was backed up to?
Because when it restores the database, it needs to create an MDF and LDF
file of the same size for the database to be restored correctly and usable.
> Why does it care about
> restoring a log file I didn't ask for?
When you restore a database, you get the data file(s) and the log file(s).
This is not negotiable.
> Once I change the backup path on the production machine to the NAS (so
> that
> the paths are the same, will this issue just go away?
That depends, if you mean that you intend to restore to the exact same
location as an existing data/log file location, then no, that won't work.
Showing posts with label report. Show all posts
Showing posts with label report. Show all posts
Monday, March 12, 2012
Restore backup on different machine
We are moving backups from being written on a local box, to writing tham on a
NAS and making a daily copy to a report server. So, to start, I made a manual
copy of the backup files to the NAS and then wen't to restore tham.
What I get is an error message that includes the path name from the machine
where the backup was made as well as the path name for the log file.
Duh!
I can see that I need to add the 'WITH MOVE' parameters to the restore
command, but I can't get them to work. This is probably because I flat-out
don't understand what is happening. Once I say where the full backup is, why
does it care about the path is was backed up to? Why does it care about
restoring a log file I didn't ask for?
Once I change the backup path on the production machine to the NAS (so that
the paths are the same, will this issue just go away?
> I can see that I need to add the 'WITH MOVE' parameters to the restore
> command, but I can't get them to work. This is probably because I flat-out
> don't understand what is happening. Once I say where the full backup is,
> why
> does it care about the path is was backed up to?
Because when it restores the database, it needs to create an MDF and LDF
file of the same size for the database to be restored correctly and usable.
> Why does it care about
> restoring a log file I didn't ask for?
When you restore a database, you get the data file(s) and the log file(s).
This is not negotiable.
> Once I change the backup path on the production machine to the NAS (so
> that
> the paths are the same, will this issue just go away?
That depends, if you mean that you intend to restore to the exact same
location as an existing data/log file location, then no, that won't work.
NAS and making a daily copy to a report server. So, to start, I made a manual
copy of the backup files to the NAS and then wen't to restore tham.
What I get is an error message that includes the path name from the machine
where the backup was made as well as the path name for the log file.
Duh!
I can see that I need to add the 'WITH MOVE' parameters to the restore
command, but I can't get them to work. This is probably because I flat-out
don't understand what is happening. Once I say where the full backup is, why
does it care about the path is was backed up to? Why does it care about
restoring a log file I didn't ask for?
Once I change the backup path on the production machine to the NAS (so that
the paths are the same, will this issue just go away?
> I can see that I need to add the 'WITH MOVE' parameters to the restore
> command, but I can't get them to work. This is probably because I flat-out
> don't understand what is happening. Once I say where the full backup is,
> why
> does it care about the path is was backed up to?
Because when it restores the database, it needs to create an MDF and LDF
file of the same size for the database to be restored correctly and usable.
> Why does it care about
> restoring a log file I didn't ask for?
When you restore a database, you get the data file(s) and the log file(s).
This is not negotiable.
> Once I change the backup path on the production machine to the NAS (so
> that
> the paths are the same, will this issue just go away?
That depends, if you mean that you intend to restore to the exact same
location as an existing data/log file location, then no, that won't work.
Restore backup on different machine
We are moving backups from being written on a local box, to writing tham on
a
NAS and making a daily copy to a report server. So, to start, I made a manua
l
copy of the backup files to the NAS and then wen't to restore tham.
What I get is an error message that includes the path name from the machine
where the backup was made as well as the path name for the log file.
Duh!
I can see that I need to add the 'WITH MOVE' parameters to the restore
command, but I can't get them to work. This is probably because I flat-out
don't understand what is happening. Once I say where the full backup is, why
does it care about the path is was backed up to? Why does it care about
restoring a log file I didn't ask for?
Once I change the backup path on the production machine to the NAS (so that
the paths are the same, will this issue just go away?> I can see that I need to add the 'WITH MOVE' parameters to the restore
> command, but I can't get them to work. This is probably because I flat-out
> don't understand what is happening. Once I say where the full backup is,
> why
> does it care about the path is was backed up to?
Because when it restores the database, it needs to create an MDF and LDF
file of the same size for the database to be restored correctly and usable.
> Why does it care about
> restoring a log file I didn't ask for?
When you restore a database, you get the data file(s) and the log file(s).
This is not negotiable.
> Once I change the backup path on the production machine to the NAS (so
> that
> the paths are the same, will this issue just go away?
That depends, if you mean that you intend to restore to the exact same
location as an existing data/log file location, then no, that won't work.
a
NAS and making a daily copy to a report server. So, to start, I made a manua
l
copy of the backup files to the NAS and then wen't to restore tham.
What I get is an error message that includes the path name from the machine
where the backup was made as well as the path name for the log file.
Duh!
I can see that I need to add the 'WITH MOVE' parameters to the restore
command, but I can't get them to work. This is probably because I flat-out
don't understand what is happening. Once I say where the full backup is, why
does it care about the path is was backed up to? Why does it care about
restoring a log file I didn't ask for?
Once I change the backup path on the production machine to the NAS (so that
the paths are the same, will this issue just go away?> I can see that I need to add the 'WITH MOVE' parameters to the restore
> command, but I can't get them to work. This is probably because I flat-out
> don't understand what is happening. Once I say where the full backup is,
> why
> does it care about the path is was backed up to?
Because when it restores the database, it needs to create an MDF and LDF
file of the same size for the database to be restored correctly and usable.
> Why does it care about
> restoring a log file I didn't ask for?
When you restore a database, you get the data file(s) and the log file(s).
This is not negotiable.
> Once I change the backup path on the production machine to the NAS (so
> that
> the paths are the same, will this issue just go away?
That depends, if you mean that you intend to restore to the exact same
location as an existing data/log file location, then no, that won't work.
Friday, March 9, 2012
restore backup from one database to another database
Here's the situation : I have a production database - PRODDB
I have a report and a test database - REPTDB and TESTDB respectively
After the backup maintenance plan for PRODDB runs - I want to take the backup and apply it to the test and report databases. I use the following script.
***start of script:***
declare @.openfile as char(84)
-- pre-initialize production backup file
set @.openfile = 'd:\program files\microsoft sql server\mssql\backup\proddb\proddb_db_yyyymmddhhmm. bak'
-- OK here it is in a nutshell
-- the object is to replace the yyyymmddhhmm string in the openfile variable with
-- the correct info so that restore will use the correct backup file for the run date.
--
-- The select will return the correct message field which contains the file path for
-- the last run production database backup - it's contends are :
-- 'Backup Destination: [d:\program files\microsoft sql server\mssql\backup\proddb\proddb_db_200208150202. bk]'
--
-- Once you have above you have to replace the yyymmddhhmm with the 200208150202 (this changes daily)
--
-- Then you can do the restores once you build the openfile variable
--
use msdb
set @.openfile = replace(@.openfile, 'yyyymmddhhmm',right((select message
from sysdbmaintplan_history
where convert(char(11),start_time) = convert(char(11), getdate())
and cast(message as char(18)) = 'Backup Destination') ,17))
-- restore test database with production backup
restore database testdb
from disk=@.openfile
with norecovery
-- Restore report database with production backup
restore database reptdb
from disk=@.openfile
with norecovery
*** End script***
****the messages/logs I get is this:****
Processed 236888 pages for database 'testdb', file 'Base' on file 1.
Processed 2 pages for database 'testdb', file 'Base_log' on file 1.
RESTORE DATABASE successfully processed 236890 pages in 270.579 seconds (7.172 MB/sec).
Processed 236888 pages for database 'reptdb', file 'Base' on file 1.
Processed 2 pages for database 'reptdb', file 'Base_log' on file 1.
RESTORE DATABASE successfully processed 236890 pages in 276.522 seconds (7.017 MB/sec).
**** end message****
The problem I have is that the REPTDB and TESTDB are left in a (loading...) state when you look in Enterprise Manager. It say's that they are in a restore process.
Can anyone tell me what's going on and how to correct this?restore database reptdb
from disk=@.openfile
with norecovery
BOL:
When specifying the NORECOVERY option, the database is not usable in this intermediate, nonrecovered state.
It has to be this way:
restore database reptdb
from disk=@.openfile
with recovery|||Originally posted by snail
restore database reptdb
from disk=@.openfile
with norecovery
BOL:
When specifying the NORECOVERY option, the database is not usable in this intermediate, nonrecovered state.
It has to be this way:
restore database reptdb
from disk=@.openfile
with recovery
Snail it worked thanks again.
I have a report and a test database - REPTDB and TESTDB respectively
After the backup maintenance plan for PRODDB runs - I want to take the backup and apply it to the test and report databases. I use the following script.
***start of script:***
declare @.openfile as char(84)
-- pre-initialize production backup file
set @.openfile = 'd:\program files\microsoft sql server\mssql\backup\proddb\proddb_db_yyyymmddhhmm. bak'
-- OK here it is in a nutshell
-- the object is to replace the yyyymmddhhmm string in the openfile variable with
-- the correct info so that restore will use the correct backup file for the run date.
--
-- The select will return the correct message field which contains the file path for
-- the last run production database backup - it's contends are :
-- 'Backup Destination: [d:\program files\microsoft sql server\mssql\backup\proddb\proddb_db_200208150202. bk]'
--
-- Once you have above you have to replace the yyymmddhhmm with the 200208150202 (this changes daily)
--
-- Then you can do the restores once you build the openfile variable
--
use msdb
set @.openfile = replace(@.openfile, 'yyyymmddhhmm',right((select message
from sysdbmaintplan_history
where convert(char(11),start_time) = convert(char(11), getdate())
and cast(message as char(18)) = 'Backup Destination') ,17))
-- restore test database with production backup
restore database testdb
from disk=@.openfile
with norecovery
-- Restore report database with production backup
restore database reptdb
from disk=@.openfile
with norecovery
*** End script***
****the messages/logs I get is this:****
Processed 236888 pages for database 'testdb', file 'Base' on file 1.
Processed 2 pages for database 'testdb', file 'Base_log' on file 1.
RESTORE DATABASE successfully processed 236890 pages in 270.579 seconds (7.172 MB/sec).
Processed 236888 pages for database 'reptdb', file 'Base' on file 1.
Processed 2 pages for database 'reptdb', file 'Base_log' on file 1.
RESTORE DATABASE successfully processed 236890 pages in 276.522 seconds (7.017 MB/sec).
**** end message****
The problem I have is that the REPTDB and TESTDB are left in a (loading...) state when you look in Enterprise Manager. It say's that they are in a restore process.
Can anyone tell me what's going on and how to correct this?restore database reptdb
from disk=@.openfile
with norecovery
BOL:
When specifying the NORECOVERY option, the database is not usable in this intermediate, nonrecovered state.
It has to be this way:
restore database reptdb
from disk=@.openfile
with recovery|||Originally posted by snail
restore database reptdb
from disk=@.openfile
with norecovery
BOL:
When specifying the NORECOVERY option, the database is not usable in this intermediate, nonrecovered state.
It has to be this way:
restore database reptdb
from disk=@.openfile
with recovery
Snail it worked thanks again.
Wednesday, March 7, 2012
Restore A Report Model From Reports Database
Is there a way to restore / export a model from the report database?
You can click the Edit link in the model properties page in Report Manager. This will download the model as a file.
Another option is to call the GetModelDefinition on the report server web service. This is what Report Manager does behind the scenes.
We do not support or recomment direct access to the report server database.
Hope that helps!
|||Thanks that worked great!!!Restore A Report Model From Reports Database
Is there a way to restore / export a model from the report database?
You can click the Edit link in the model properties page in Report Manager. This will download the model as a file.
Another option is to call the GetModelDefinition on the report server web service. This is what Report Manager does behind the scenes.
We do not support or recomment direct access to the report server database.
Hope that helps!
|||Thanks that worked great!!!Restore a Group/Role?
I have a client with an eager IT manager who used Report Manager, saw the
BUILTIN\Administrators group in the roles list and decided to remove it from
the Report Server groups, or I think that is how he did it, he says he is
not sure. Sadly that was the only RS group with Content Manager and
Publisher rights, so no more report publishing/role assignment there.
I looked at the Books Online to see how I can restore/create groups/roles in
RS, but all cases say "use Report Manager and navigate ..." but obviously
that does not apply any more, because Report Manager now does not show the
Site Settings, New Role Assignment, etc.
Must they re-install?
Thanks.There is no need to reinstall. Use account that is windows admin on the box
where Report Server is installed. This account should have sufficient rights
to browse and change security. Admin on the box has these special rights on
Report Server regardless of the Report Server policies.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"Chris Botha" <chris_s_botha@.AT_h.o.t.m.a.i.l.com> wrote in message
news:OQnl$4aaEHA.3988@.tk2msftngp13.phx.gbl...
> I have a client with an eager IT manager who used Report Manager, saw the
> BUILTIN\Administrators group in the roles list and decided to remove it
from
> the Report Server groups, or I think that is how he did it, he says he is
> not sure. Sadly that was the only RS group with Content Manager and
> Publisher rights, so no more report publishing/role assignment there.
> I looked at the Books Online to see how I can restore/create groups/roles
in
> RS, but all cases say "use Report Manager and navigate ..." but obviously
> that does not apply any more, because Report Manager now does not show the
> Site Settings, New Role Assignment, etc.
> Must they re-install?
> Thanks.
>|||Thanks, it sounds good. I will give it a shot tomorrow, they went home
already, and will let you know if it worked.
"Dmitry Vasilevsky [MSFT]" <dmvasi@.microsoft.com> wrote in message
news:e#BflgeaEHA.1356@.TK2MSFTNGP09.phx.gbl...
> There is no need to reinstall. Use account that is windows admin on the
box
> where Report Server is installed. This account should have sufficient
rights
> to browse and change security. Admin on the box has these special rights
on
> Report Server regardless of the Report Server policies.
> --
> Dmitry Vasilevsky, SQL Server Reporting Services Developer
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> --
> ---
> "Chris Botha" <chris_s_botha@.AT_h.o.t.m.a.i.l.com> wrote in message
> news:OQnl$4aaEHA.3988@.tk2msftngp13.phx.gbl...
> > I have a client with an eager IT manager who used Report Manager, saw
the
> > BUILTIN\Administrators group in the roles list and decided to remove it
> from
> > the Report Server groups, or I think that is how he did it, he says he
is
> > not sure. Sadly that was the only RS group with Content Manager and
> > Publisher rights, so no more report publishing/role assignment there.
> > I looked at the Books Online to see how I can restore/create
groups/roles
> in
> > RS, but all cases say "use Report Manager and navigate ..." but
obviously
> > that does not apply any more, because Report Manager now does not show
the
> > Site Settings, New Role Assignment, etc.
> > Must they re-install?
> >
> > Thanks.
> >
> >
>|||Hi Dmitry, in the end it was a re-install and everything is back to normal
now. Thanks in any case.
"Dmitry Vasilevsky [MSFT]" <dmvasi@.microsoft.com> wrote in message
news:e#BflgeaEHA.1356@.TK2MSFTNGP09.phx.gbl...
> There is no need to reinstall. Use account that is windows admin on the
box
> where Report Server is installed. This account should have sufficient
rights
> to browse and change security. Admin on the box has these special rights
on
> Report Server regardless of the Report Server policies.
> --
> Dmitry Vasilevsky, SQL Server Reporting Services Developer
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> --
> ---
> "Chris Botha" <chris_s_botha@.AT_h.o.t.m.a.i.l.com> wrote in message
> news:OQnl$4aaEHA.3988@.tk2msftngp13.phx.gbl...
> > I have a client with an eager IT manager who used Report Manager, saw
the
> > BUILTIN\Administrators group in the roles list and decided to remove it
> from
> > the Report Server groups, or I think that is how he did it, he says he
is
> > not sure. Sadly that was the only RS group with Content Manager and
> > Publisher rights, so no more report publishing/role assignment there.
> > I looked at the Books Online to see how I can restore/create
groups/roles
> in
> > RS, but all cases say "use Report Manager and navigate ..." but
obviously
> > that does not apply any more, because Report Manager now does not show
the
> > Site Settings, New Role Assignment, etc.
> > Must they re-install?
> >
> > Thanks.
> >
> >
>
BUILTIN\Administrators group in the roles list and decided to remove it from
the Report Server groups, or I think that is how he did it, he says he is
not sure. Sadly that was the only RS group with Content Manager and
Publisher rights, so no more report publishing/role assignment there.
I looked at the Books Online to see how I can restore/create groups/roles in
RS, but all cases say "use Report Manager and navigate ..." but obviously
that does not apply any more, because Report Manager now does not show the
Site Settings, New Role Assignment, etc.
Must they re-install?
Thanks.There is no need to reinstall. Use account that is windows admin on the box
where Report Server is installed. This account should have sufficient rights
to browse and change security. Admin on the box has these special rights on
Report Server regardless of the Report Server policies.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"Chris Botha" <chris_s_botha@.AT_h.o.t.m.a.i.l.com> wrote in message
news:OQnl$4aaEHA.3988@.tk2msftngp13.phx.gbl...
> I have a client with an eager IT manager who used Report Manager, saw the
> BUILTIN\Administrators group in the roles list and decided to remove it
from
> the Report Server groups, or I think that is how he did it, he says he is
> not sure. Sadly that was the only RS group with Content Manager and
> Publisher rights, so no more report publishing/role assignment there.
> I looked at the Books Online to see how I can restore/create groups/roles
in
> RS, but all cases say "use Report Manager and navigate ..." but obviously
> that does not apply any more, because Report Manager now does not show the
> Site Settings, New Role Assignment, etc.
> Must they re-install?
> Thanks.
>|||Thanks, it sounds good. I will give it a shot tomorrow, they went home
already, and will let you know if it worked.
"Dmitry Vasilevsky [MSFT]" <dmvasi@.microsoft.com> wrote in message
news:e#BflgeaEHA.1356@.TK2MSFTNGP09.phx.gbl...
> There is no need to reinstall. Use account that is windows admin on the
box
> where Report Server is installed. This account should have sufficient
rights
> to browse and change security. Admin on the box has these special rights
on
> Report Server regardless of the Report Server policies.
> --
> Dmitry Vasilevsky, SQL Server Reporting Services Developer
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> --
> ---
> "Chris Botha" <chris_s_botha@.AT_h.o.t.m.a.i.l.com> wrote in message
> news:OQnl$4aaEHA.3988@.tk2msftngp13.phx.gbl...
> > I have a client with an eager IT manager who used Report Manager, saw
the
> > BUILTIN\Administrators group in the roles list and decided to remove it
> from
> > the Report Server groups, or I think that is how he did it, he says he
is
> > not sure. Sadly that was the only RS group with Content Manager and
> > Publisher rights, so no more report publishing/role assignment there.
> > I looked at the Books Online to see how I can restore/create
groups/roles
> in
> > RS, but all cases say "use Report Manager and navigate ..." but
obviously
> > that does not apply any more, because Report Manager now does not show
the
> > Site Settings, New Role Assignment, etc.
> > Must they re-install?
> >
> > Thanks.
> >
> >
>|||Hi Dmitry, in the end it was a re-install and everything is back to normal
now. Thanks in any case.
"Dmitry Vasilevsky [MSFT]" <dmvasi@.microsoft.com> wrote in message
news:e#BflgeaEHA.1356@.TK2MSFTNGP09.phx.gbl...
> There is no need to reinstall. Use account that is windows admin on the
box
> where Report Server is installed. This account should have sufficient
rights
> to browse and change security. Admin on the box has these special rights
on
> Report Server regardless of the Report Server policies.
> --
> Dmitry Vasilevsky, SQL Server Reporting Services Developer
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> --
> ---
> "Chris Botha" <chris_s_botha@.AT_h.o.t.m.a.i.l.com> wrote in message
> news:OQnl$4aaEHA.3988@.tk2msftngp13.phx.gbl...
> > I have a client with an eager IT manager who used Report Manager, saw
the
> > BUILTIN\Administrators group in the roles list and decided to remove it
> from
> > the Report Server groups, or I think that is how he did it, he says he
is
> > not sure. Sadly that was the only RS group with Content Manager and
> > Publisher rights, so no more report publishing/role assignment there.
> > I looked at the Books Online to see how I can restore/create
groups/roles
> in
> > RS, but all cases say "use Report Manager and navigate ..." but
obviously
> > that does not apply any more, because Report Manager now does not show
the
> > Site Settings, New Role Assignment, etc.
> > Must they re-install?
> >
> > Thanks.
> >
> >
>
Subscribe to:
Posts (Atom)