DBCC CHECKDB: backup

Showing posts with label backup. Show all posts

Sunday, March 25, 2012

db_backupoperator question

I have assigned an user id to a role of db_backupoperator. But after I
created a scheduled job to do backup weekly, it won't run at all. No error
message in the event application. So I go to do the backup manually. I get
an error message when I click the "..." button to specify the backup file
location as following:
Microsoft SQL-DMO (ODBC SQLState: 42000)
"Error 229: Execute permission denied on object 'xp_availablemedia',
database 'master', owner 'dbo'."
Please help. Thanks.Hi,
Its seems your login account does not have the permissions required to
create a backup
device in hard disk. Please consult your system administrator or database
administrator
to obtain the required permissions to write in to the hard disk (Write
permission on the folder you craete the backup file).
Thanks
Hari
MCDBA
"Eric Clapton" <no_spam@.bk.com> wrote in message
news:#bQGXdZMEHA.2628@.TK2MSFTNGP12.phx.gbl...
> I have assigned an user id to a role of db_backupoperator. But after I
> created a scheduled job to do backup weekly, it won't run at all. No error
> message in the event application. So I go to do the backup manually. I
get
> an error message when I click the "..." button to specify the backup file
> location as following:
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> "Error 229: Execute permission denied on object 'xp_availablemedia',
> database 'master', owner 'dbo'."
> Please help. Thanks.
>|||Thanks, Hari. I have created the user id previously and didn't assign a
local win 2k user id to it. May be this is the problem. Now I just created
a local win 2k user id with the access right. But I don't want to delete the
sql user id and re-create it again? How can I map the current sql 2k user id
with the local win 2k user? Please let me know. Thanks.
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:uWGed8ZMEHA.808@.tk2msftngp13.phx.gbl...
> Hi,
> Its seems your login account does not have the permissions required to
> create a backup
> device in hard disk. Please consult your system administrator or database
> administrator
> to obtain the required permissions to write in to the hard disk (Write
> permission on the folder you craete the backup file).
> Thanks
> Hari
> MCDBA
> "Eric Clapton" <no_spam@.bk.com> wrote in message
> news:#bQGXdZMEHA.2628@.TK2MSFTNGP12.phx.gbl...
> > I have assigned an user id to a role of db_backupoperator. But after I
> > created a scheduled job to do backup weekly, it won't run at all. No
error
> > message in the event application. So I go to do the backup manually. I
> get
> > an error message when I click the "..." button to specify the backup
file
> > location as following:
> >
> > Microsoft SQL-DMO (ODBC SQLState: 42000)
> >
> > "Error 229: Execute permission denied on object 'xp_availablemedia',
> > database 'master', owner 'dbo'."
> >
> > Please help. Thanks.
> >
> >
>|||Hi,
The user in which you start the SQL Server and SQL Agent service should have
the permission.
Thanks
Hari
MCDBA
"Eric Clapton" <no_spam@.bk.com> wrote in message
news:uTQM2WaMEHA.2824@.TK2MSFTNGP10.phx.gbl...
> Thanks, Hari. I have created the user id previously and didn't assign a
> local win 2k user id to it. May be this is the problem. Now I just
created
> a local win 2k user id with the access right. But I don't want to delete
the
> sql user id and re-create it again? How can I map the current sql 2k user
id
> with the local win 2k user? Please let me know. Thanks.
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:uWGed8ZMEHA.808@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > Its seems your login account does not have the permissions required to
> > create a backup
> > device in hard disk. Please consult your system administrator or
database
> > administrator
> > to obtain the required permissions to write in to the hard disk (Write
> > permission on the folder you craete the backup file).
> >
> > Thanks
> > Hari
> > MCDBA
> >
> > "Eric Clapton" <no_spam@.bk.com> wrote in message
> > news:#bQGXdZMEHA.2628@.TK2MSFTNGP12.phx.gbl...
> > > I have assigned an user id to a role of db_backupoperator. But after I
> > > created a scheduled job to do backup weekly, it won't run at all. No
> error
> > > message in the event application. So I go to do the backup manually.
I
> > get
> > > an error message when I click the "..." button to specify the backup
> file
> > > location as following:
> > >
> > > Microsoft SQL-DMO (ODBC SQLState: 42000)
> > >
> > > "Error 229: Execute permission denied on object 'xp_availablemedia',
> > > database 'master', owner 'dbo'."
> > >
> > > Please help. Thanks.
> > >
> > >
> >
> >
>|||Hari,
I found out that both services are started with local system
accounts. Does that mean whoever shut down and re-start the service is the
local system accounts? Or it mean something else? Do I need to re-start both
services with the user id I used to do backup?
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23601RbaMEHA.3348@.TK2MSFTNGP09.phx.gbl...
> Hi,
> The user in which you start the SQL Server and SQL Agent service should
have
> the permission.
> Thanks
> Hari
> MCDBA
>
> "Eric Clapton" <no_spam@.bk.com> wrote in message
> news:uTQM2WaMEHA.2824@.TK2MSFTNGP10.phx.gbl...
> > Thanks, Hari. I have created the user id previously and didn't assign a
> > local win 2k user id to it. May be this is the problem. Now I just
> created
> > a local win 2k user id with the access right. But I don't want to delete
> the
> > sql user id and re-create it again? How can I map the current sql 2k
user
> id
> > with the local win 2k user? Please let me know. Thanks.
> >
> >
> > "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> > news:uWGed8ZMEHA.808@.tk2msftngp13.phx.gbl...
> > > Hi,
> > >
> > > Its seems your login account does not have the permissions required to
> > > create a backup
> > > device in hard disk. Please consult your system administrator or
> database
> > > administrator
> > > to obtain the required permissions to write in to the hard disk (Write
> > > permission on the folder you craete the backup file).
> > >
> > > Thanks
> > > Hari
> > > MCDBA
> > >
> > > "Eric Clapton" <no_spam@.bk.com> wrote in message
> > > news:#bQGXdZMEHA.2628@.TK2MSFTNGP12.phx.gbl...
> > > > I have assigned an user id to a role of db_backupoperator. But after
I
> > > > created a scheduled job to do backup weekly, it won't run at all. No
> > error
> > > > message in the event application. So I go to do the backup
manually.
> I
> > > get
> > > > an error message when I click the "..." button to specify the backup
> > file
> > > > location as following:
> > > >
> > > > Microsoft SQL-DMO (ODBC SQLState: 42000)
> > > >
> > > > "Error 229: Execute permission denied on object 'xp_availablemedia',
> > > > database 'master', owner 'dbo'."
> > > >
> > > > Please help. Thanks.
> > > >
> > > >
> > >
> > >
> >
> >
>

db_backupoperator question

Thursday, March 22, 2012

db stuck on loaded status

I restored a full backup, then each of the logs, after the final log I performed a restore with standby option to make Read-Only, yet accessible.
Problem, database stuck in loaded status, all my logs were restored properly. How do I change status to Read-Only from Loaded?
Thanks.
Hi,
Ir seems the last transaction log was restored with NORECOVERY option. Can
you issue the below command to open the database in standby mode.
restore database <dbname> with STANDBY = 'c:\undo.ldf'
After that try to access the database.
Thanks
Hari
MCDBA
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:B17DAE52-973F-4C8E-8118-261AE8131D71@.microsoft.com...
> I restored a full backup, then each of the logs, after the final log I
performed a restore with standby option to make Read-Only, yet accessible.
> Problem, database stuck in loaded status, all my logs were restored
properly. How do I change status to Read-Only from Loaded?
> Thanks.
|||I had a feeling that's what happened. So I created another log backup form the source db and double/triple checked my syntax... problem solved.
Thanks.
"Hari Prasad" wrote:

> Hi,
> Ir seems the last transaction log was restored with NORECOVERY option. Can
> you issue the below command to open the database in standby mode.
> restore database <dbname> with STANDBY = 'c:\undo.ldf'
> After that try to access the database.
> Thanks
> Hari
> MCDBA
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:B17DAE52-973F-4C8E-8118-261AE8131D71@.microsoft.com...
> performed a restore with standby option to make Read-Only, yet accessible.
> properly. How do I change status to Read-Only from Loaded?
>
>

db stuck on loaded status

I restored a full backup, then each of the logs, after the final log I perfo
rmed a restore with standby option to make Read-Only, yet accessible.
Problem, database stuck in loaded status, all my logs were restored properly
. How do I change status to Read-Only from Loaded?
Thanks.Hi,
Ir seems the last transaction log was restored with NORECOVERY option. Can
you issue the below command to open the database in standby mode.
restore database <dbname> with STANDBY = 'c:\undo.ldf'
After that try to access the database.
Thanks
Hari
MCDBA
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:B17DAE52-973F-4C8E-8118-261AE8131D71@.microsoft.com...
> I restored a full backup, then each of the logs, after the final log I
performed a restore with standby option to make Read-Only, yet accessible.
> Problem, database stuck in loaded status, all my logs were restored
properly. How do I change status to Read-Only from Loaded?
> Thanks.|||I had a feeling that's what happened. So I created another log backup form t
he source db and double/triple checked my syntax... problem solved.
Thanks.
"Hari Prasad" wrote:

> Hi,
> Ir seems the last transaction log was restored with NORECOVERY option. Can
> you issue the below command to open the database in standby mode.
> restore database <dbname> with STANDBY = 'c:\undo.ldf'
> After that try to access the database.
> Thanks
> Hari
> MCDBA
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:B17DAE52-973F-4C8E-8118-261AE8131D71@.microsoft.com...
> performed a restore with standby option to make Read-Only, yet accessible.
> properly. How do I change status to Read-Only from Loaded?
>
>

Wednesday, March 21, 2012

DB Size

I backup the db first, and i then shrink the db. I just
find it hard to believe that the db is still 38M.
Bill,
You may need to reorganize the data in order to move it from the end of the
space.
DBCC DBREINDEX may help.
Russell Fields
"Bill" <anonymous@.discussions.microsoft.com> wrote in message
news:2ed801c4289d$b0099110$a501280a@.phx.gbl...
> I backup the db first, and i then shrink the db. I just
> find it hard to believe that the db is still 38M.

db rights

How do you set up an user which can do backup and restore DB but do not have
read or write right to that DB? How do you set up a DBA user other than sa?
Hi,
How do you set up a DBA user other than sa?
Assign the "SYSADMIN" server fixed role to that user using the below command
sp_addsrvrolemember <Logsin Name>,sysadmin
How do you set up an user which can do backup and restore DB but do not have
read or write right to that DB?
Assign "db_backupoperator" to user. This will allow users to backup not
restore. Restore database permission can not be granted.
sp_addrolemember 'db_backupoperator',<User Name>
If you assign the server fixed "dbcreator" role. The user will be able to
Backup, restore , create database, drop database and alter database.
Thanks
Hari
MCDBA
"Eric Clapton" <no_spam@.bk.com> wrote in message
news:unI8kgNKEHA.1144@.TK2MSFTNGP12.phx.gbl...
> How do you set up an user which can do backup and restore DB but do not
have
> read or write right to that DB? How do you set up a DBA user other than
sa?
>

db rights

How do you set up an user which can do backup and restore DB but do not have
read or write right to that DB? How do you set up a DBA user other than sa?Hi,
How do you set up a DBA user other than sa?
Assign the "SYSADMIN" server fixed role to that user using the below command
sp_addsrvrolemember <Logsin Name>,sysadmin
How do you set up an user which can do backup and restore DB but do not have
read or write right to that DB?
Assign "db_backupoperator" to user. This will allow users to backup not
restore. Restore database permission can not be granted.
sp_addrolemember 'db_backupoperator',<User Name>
If you assign the server fixed "dbcreator" role. The user will be able to
Backup, restore , create database, drop database and alter database.
Thanks
Hari
MCDBA
"Eric Clapton" <no_spam@.bk.com> wrote in message
news:unI8kgNKEHA.1144@.TK2MSFTNGP12.phx.gbl...
> How do you set up an user which can do backup and restore DB but do not
have
> read or write right to that DB? How do you set up a DBA user other than
sa?
>

Monday, March 19, 2012

db rights

How do you set up an user which can do backup and restore DB but do not have
read or write right to that DB? How do you set up a DBA user other than sa?Hi,
How do you set up a DBA user other than sa?
Assign the "SYSADMIN" server fixed role to that user using the below command
sp_addsrvrolemember <Logsin Name>,sysadmin
How do you set up an user which can do backup and restore DB but do not have
read or write right to that DB?
Assign "db_backupoperator" to user. This will allow users to backup not
restore. Restore database permission can not be granted.
sp_addrolemember 'db_backupoperator',<User Name>
If you assign the server fixed "dbcreator" role. The user will be able to
Backup, restore , create database, drop database and alter database.
Thanks
Hari
MCDBA
"Eric Clapton" <no_spam@.bk.com> wrote in message
news:unI8kgNKEHA.1144@.TK2MSFTNGP12.phx.gbl...
> How do you set up an user which can do backup and restore DB but do not
have
> read or write right to that DB? How do you set up a DBA user other than
sa?
>

DB Restored to New Server, ODBC Says Database is Not Valid

I'm setting up a replacement server for SQL 2000 and have restored a DB from old server to new server using backup/restore. All seems to work, DB listed in Enterprise Manager, I can see data. When I try to setup an ODBC connection on same server, however, the DB is not listed in drop-down list and if I enter the name, it responds with "the database entered is not valid". What is left to do?
Detaching the database and then attaching it solved this problem.

DB Restore Time

I have a database backup on one server and I am restoring
this to another server. It is about 75 GB. Is there any
way to find out how much of it has been restored through
SQL Server or any other tool '
Thanks.If you are using STATS, you can see the progress...
Thanks
GYK
"Ron" wrote:
> I have a database backup on one server and I am restoring
> this to another server. It is about 75 GB. Is there any
> way to find out how much of it has been restored through
> SQL Server or any other tool '
> Thanks.
>|||I am running this through DTS package (then, as a
scheduled job).
>--Original Message--
>If you are using STATS, you can see the progress...
>Thanks
>GYK
>"Ron" wrote:
>> I have a database backup on one server and I am
restoring
>> this to another server. It is about 75 GB. Is there any
>> way to find out how much of it has been restored
through
>> SQL Server or any other tool '
>> Thanks.
>.
>|||Ron,
I think what CYK is saying is to run something like this:
RESTORE DATABASE MY_DB FROM DISK = 'C:\PATH\MY_DB.BAK' WITH STATS=10
The value for STATS can be any integer between 1 and 100. See Books Online
subject 'RESTORE' under 'Transact-SQL Reference' for more details.
HTH
"Ron" wrote:
> I am running this through DTS package (then, as a
> scheduled job).
>
> >--Original Message--
> >If you are using STATS, you can see the progress...
> >
> >Thanks
> >GYK
> >
> >"Ron" wrote:
> >
> >> I have a database backup on one server and I am
> restoring
> >> this to another server. It is about 75 GB. Is there any
> >> way to find out how much of it has been restored
> through
> >> SQL Server or any other tool '
> >>
> >> Thanks.
> >>
> >.
> >
>|||I understand that but how am I going to view this ?
The script is in the DTS package. I can add that statement
into the DTS package. DTS package is scheduled to run as a
job. May be on the "status" column when executing DTS
package '
My question is either executing the DTS package or just
running the job, how am I going to see this visually ?
I have test it in the Query analyzer and works. I can see
the progress in "Messages" window.
Thanks.
>--Original Message--
>Ron,
>I think what CYK is saying is to run something like this:
>RESTORE DATABASE MY_DB FROM DISK = 'C:\PATH\MY_DB.BAK'
WITH STATS=10
>The value for STATS can be any integer between 1 and
100. See Books Online
>subject 'RESTORE' under 'Transact-SQL Reference' for more
details.
>HTH
>"Ron" wrote:
>> I am running this through DTS package (then, as a
>> scheduled job).
>>
>> >--Original Message--
>> >If you are using STATS, you can see the progress...
>> >
>> >Thanks
>> >GYK
>> >
>> >"Ron" wrote:
>> >
>> >> I have a database backup on one server and I am
>> restoring
>> >> this to another server. It is about 75 GB. Is there
any
>> >> way to find out how much of it has been restored
>> through
>> >> SQL Server or any other tool '
>> >>
>> >> Thanks.
>> >>
>> >.
>> >
>.
>|||Run the RESTORE DATABASE command from Query Analyzer instead of a DTS
package. DTS won't offer an 'interactive' response to the query like QA will.
"Ron" wrote:
> I understand that but how am I going to view this ?
> The script is in the DTS package. I can add that statement
> into the DTS package. DTS package is scheduled to run as a
> job. May be on the "status" column when executing DTS
> package '
> My question is either executing the DTS package or just
> running the job, how am I going to see this visually ?
> I have test it in the Query analyzer and works. I can see
> the progress in "Messages" window.
> Thanks.
>
> >--Original Message--
> >Ron,
> >
> >I think what CYK is saying is to run something like this:
> >
> >RESTORE DATABASE MY_DB FROM DISK = 'C:\PATH\MY_DB.BAK'
> WITH STATS=10
> >
> >The value for STATS can be any integer between 1 and
> 100. See Books Online
> >subject 'RESTORE' under 'Transact-SQL Reference' for more
> details.
> >
> >HTH
> >
> >"Ron" wrote:
> >
> >> I am running this through DTS package (then, as a
> >> scheduled job).
> >>
> >>
> >>
> >> >--Original Message--
> >> >If you are using STATS, you can see the progress...
> >> >
> >> >Thanks
> >> >GYK
> >> >
> >> >"Ron" wrote:
> >> >
> >> >> I have a database backup on one server and I am
> >> restoring
> >> >> this to another server. It is about 75 GB. Is there
> any
> >> >> way to find out how much of it has been restored
> >> through
> >> >> SQL Server or any other tool '
> >> >>
> >> >> Thanks.
> >> >>
> >> >.
> >> >
> >>
> >.
> >
>|||"Mike" <mstuart_spamtastesgood@.gates.com> wrote in message
news:CBDF6AC8-3854-40BD-B7A0-33A36685D6D7@.microsoft.com...
> Run the RESTORE DATABASE command from Query Analyzer instead of a DTS
> package. DTS won't offer an 'interactive' response to the query like QA
will.
>
Right, but it's tough to automate that.
I believe Ron's asking for automatic jobs.
I do this as a routine thing (backup from box 1 and restore to box 2.)

db restore question

Hi all, is it possible to restore a database to another server with only the
..mdf & the .ldf files? Unfortunately I dont have a backup file, only the
data files itself. Also another thing is the data files are from sql2k and
the new server is sql2k sp3a. Am i SOL?
TIA!
If you are lucky, or you explicitly detached then first, you can use sp_attach_db to attach the
database files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RP" <rp@.nospam.com> wrote in message news:ue9YVIXqEHA.708@.tk2msftngp13.phx.gbl...
> Hi all, is it possible to restore a database to another server with only the
> .mdf & the .ldf files? Unfortunately I dont have a backup file, only the
> data files itself. Also another thing is the data files are from sql2k and
> the new server is sql2k sp3a. Am i SOL?
> TIA!
>
|||You can try to use sp_attach_db to attach the files but this might not work
if the database wasn't cleanly detached using sp_detach_db. See the Books
Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"RP" <rp@.nospam.com> wrote in message
news:ue9YVIXqEHA.708@.tk2msftngp13.phx.gbl...
> Hi all, is it possible to restore a database to another server with only
> the
> .mdf & the .ldf files? Unfortunately I dont have a backup file, only the
> data files itself. Also another thing is the data files are from sql2k and
> the new server is sql2k sp3a. Am i SOL?
> TIA!
>

db restore question

Hi all, is it possible to restore a database to another server with only the
.mdf & the .ldf files? Unfortunately I dont have a backup file, only the
data files itself. Also another thing is the data files are from sql2k and
the new server is sql2k sp3a. Am i SOL?
TIA!If you are lucky, or you explicitly detached then first, you can use sp_attach_db to attach the
database files.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RP" <rp@.nospam.com> wrote in message news:ue9YVIXqEHA.708@.tk2msftngp13.phx.gbl...
> Hi all, is it possible to restore a database to another server with only the
> .mdf & the .ldf files? Unfortunately I dont have a backup file, only the
> data files itself. Also another thing is the data files are from sql2k and
> the new server is sql2k sp3a. Am i SOL?
> TIA!
>|||You can try to use sp_attach_db to attach the files but this might not work
if the database wasn't cleanly detached using sp_detach_db. See the Books
Online for details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"RP" <rp@.nospam.com> wrote in message
news:ue9YVIXqEHA.708@.tk2msftngp13.phx.gbl...
> Hi all, is it possible to restore a database to another server with only
> the
> .mdf & the .ldf files? Unfortunately I dont have a backup file, only the
> data files itself. Also another thing is the data files are from sql2k and
> the new server is sql2k sp3a. Am i SOL?
> TIA!
>|||Hi!
As for your last problem. That will be no problem.
As for attach/detach procedure:
You will get the database with all data but you will get no users that reside in master. You have to create them and mapp them using following script:
--Run this in the faulty database
--Username is the databaseusername
--Loginname is the user in Security
sp_change_users_login 'Update_one', '<username>','<Loginna
me>'
If you database wasn=B4t cleanly closed you can not attach it directly. But that doesn=B4t make the database lost forever.
Do the following steps and you are homefree:
1. Reconfigure server to allow ad-hoc updates.
2. Create the database and set the physical names as those in the one you are restoring. 3. Close SQL-server and delete the .ldf file
4. Start SQL-server, the database is now in suspect mode.
5. Set the database to emergencymode
6. Activate traceflag 3604
7. Run DBCC Rebuild_log
8. Reset the database to normal operations.
9. Run DBCC Checkdb and correct any errornes indexes.
If you need further help drop me a mail.
Jesper Kr=E5khede, Capgemini
>--Original Message--
>Hi all, is it possible to restore a database to another server with only the
>..mdf & the .ldf files? Unfortunately I dont have a backup file, only the
>data files itself. Also another thing is the data files are from sql2k and
>the new server is sql2k sp3a. Am i SOL?
>TIA!
>
>.
>|||Note that rebuilding the log will compromise both logical and physical
database integrity. If RP can't successfully attach the database and the
data are important, I suggest he or she contact Microsoft PSS.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jesper Kråkhede Capgemini" <Jesper.krakhede@.capgemini.com> wrote in message
news:2fde01c4a979$588ed870$a401280a@.phx.gbl...
Hi!
As for your last problem. That will be no problem.
As for attach/detach procedure:
You will get the database with all data but you will get
no users that reside in master. You have to create them
and mapp them using following script:
--Run this in the faulty database
--Username is the databaseusername
--Loginname is the user in Security
sp_change_users_login 'Update_one', '<username>','<Loginna
me>'
If you database wasn´t cleanly closed you can not attach
it directly. But that doesn´t make the database lost
forever.
Do the following steps and you are homefree:
1. Reconfigure server to allow ad-hoc updates.
2. Create the database and set the physical names as
those in the one you are restoring.
3. Close SQL-server and delete the .ldf file
4. Start SQL-server, the database is now in suspect mode.
5. Set the database to emergencymode
6. Activate traceflag 3604
7. Run DBCC Rebuild_log
8. Reset the database to normal operations.
9. Run DBCC Checkdb and correct any errornes indexes.
If you need further help drop me a mail.
Jesper Kråkhede, Capgemini
>--Original Message--
>Hi all, is it possible to restore a database to another
server with only the
>..mdf & the .ldf files? Unfortunately I dont have a
backup file, only the
>data files itself. Also another thing is the data files
are from sql2k and
>the new server is sql2k sp3a. Am i SOL?
>TIA!
>
>.
>|||Thats correct. I did that once and this was the solution they gave me. Worked
as a charm but running DBCC CheckDB was needed afterwards.
J
"Dan Guzman" wrote:
> Note that rebuilding the log will compromise both logical and physical
> database integrity. If RP can't successfully attach the database and the
> data are important, I suggest he or she contact Microsoft PSS.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jesper KrÃ¥khede Capgemini" <Jesper.krakhede@.capgemini.com> wrote in message
> news:2fde01c4a979$588ed870$a401280a@.phx.gbl...
> Hi!
> As for your last problem. That will be no problem.
> As for attach/detach procedure:
> You will get the database with all data but you will get
> no users that reside in master. You have to create them
> and mapp them using following script:
> --Run this in the faulty database
> --Username is the databaseusername
> --Loginname is the user in Security
> sp_change_users_login 'Update_one', '<username>','<Loginna
> me>'
> If you database wasnÂ´t cleanly closed you can not attach
> it directly. But that doesnÂ´t make the database lost
> forever.
> Do the following steps and you are homefree:
> 1. Reconfigure server to allow ad-hoc updates.
> 2. Create the database and set the physical names as
> those in the one you are restoring.
> 3. Close SQL-server and delete the .ldf file
> 4. Start SQL-server, the database is now in suspect mode.
> 5. Set the database to emergencymode
> 6. Activate traceflag 3604
> 7. Run DBCC Rebuild_log
> 8. Reset the database to normal operations.
> 9. Run DBCC Checkdb and correct any errornes indexes.
> If you need further help drop me a mail.
> Jesper KrÃ¥khede, Capgemini
>
> >--Original Message--
> >Hi all, is it possible to restore a database to another
> server with only the
> >..mdf & the .ldf files? Unfortunately I dont have a
> backup file, only the
> >data files itself. Also another thing is the data files
> are from sql2k and
> >the new server is sql2k sp3a. Am i SOL?
> >
> >TIA!
> >
> >
> >.
> >
>
>

Db restore ques.

Is it possible to restore a database if you have not been
doing db backup but you were doing server backups.Or does
server backups include database backups. And how do you
restore the database. Please help.It depends on what you've been using to do the backups.
Ideally you backup SQL Server databases from within SQL Server, the
resulting backup file can then be put onto tape with your file backup
utility. Does your backup software have SQL Server Agents loaded or has it
been skipping the database files as they are in use..?
--
HTH
Ryan Waight, MCDBA, MCSE
"Naz" <milnaz@.hotmail.com> wrote in message
news:110f01c38cc7$0f82d980$a101280a@.phx.gbl...
> Is it possible to restore a database if you have not been
> doing db backup but you were doing server backups.Or does
> server backups include database backups. And how do you
> restore the database. Please help.|||Naz
If you have copies of your .mdf and .ldf files, you may be
able to use sp_attach_db to attach them. Most server
backups do not pick up these files as they show as being
in use, so you might not have them.
Regards
John|||got it. They were some database backups.
>--Original Message--
>Naz
>If you have copies of your .mdf and .ldf files, you may
be
>able to use sp_attach_db to attach them. Most server
>backups do not pick up these files as they show as being
>in use, so you might not have them.
>Regards
>John
>.
>

Db Restore Problems

Hi All,
i have backed up a database from SQL server 7.0 on a file.
now when i try to restore the same backup on other machine
it gives me the following error.
Microsoft SQL-DMO (ODBC SQLState: 42000)
The file on device is not a valid Microsoft Tape format backup set
pls could some1 help me out with this problem asap.
Thx.
KNIGHT
KNIGHT's Profile: http://www.mswebservertalk.com/member.php?userid=951
View this thread: http://www.mswebservertalk.com/t-1870403952
You could try RESTORE HEADERONLY and RESTORE FILELISTONLY and see if those also give you the same
error. If they do, and you are certain that you got the syntax right, I suggest you open a support
case with MS (as this seems to in need for a quick solution).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KNIGHT" <KNIGHT.1oi0wb@.no-mx.mswebservertalk.com> wrote in message
news:KNIGHT.1oi0wb@.no-mx.mswebservertalk.com...
> Hi All,
> i have backed up a database from SQL server 7.0 on a file.
> now when i try to restore the same backup on other machine
> it gives me the following error.
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> The file on device is not a valid Microsoft Tape format backup set
> pls could some1 help me out with this problem asap.
> Thx.
>
> --
> KNIGHT
> KNIGHT's Profile: http://www.mswebservertalk.com/member.php?userid=951
> View this thread: http://www.mswebservertalk.com/t-1870403952
>
|||hi, thx for the reply,
i tried the restore command you provided and following is the output of
the same.
NULLNULL1NULL012saHOMEPOS5152004-08-23
19:49:58.0002556928980000000151000019800000001530000198000000015100001670000000403000012005-05-02
21:39:49.000NULL1469103319660970460870623HOME0{DDBB3598-43BE-44B9-9B23-B51895A59011}NULLNULL
POS_DataC:\MSSQL7\data\POS_Data.MDFDPRIMARY255590435184372080640
POS_LogC:\MSSQL7\data\POS_Log.LDFLNULL104857635184372080640
what should be done next and what does this log represent, is my data
ok.
KNIGHT
KNIGHT's Profile: http://www.mswebservertalk.com/member.php?userid=951
View this thread: http://www.mswebservertalk.com/t-1870403952
|||Looking good so far (but you didn't post the RESTORE commands you executed). Now try doing the
actual RESTORE from Query Analyzer. Again, please post the command you executed and the exact error
messages from SQL Server. Another thing you can try is RESTORE VERIFYONLY.
To understand the result of these commands, just read about them in Books Online. They are
documented.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KNIGHT" <KNIGHT.1oic0c@.no-mx.mswebservertalk.com> wrote in message
news:KNIGHT.1oic0c@.no-mx.mswebservertalk.com...
> hi, thx for the reply,
> i tried the restore command you provided and following is the output of
> the same.
> NULL NULL 1 NULL 0 1 2 sa HOME POS 515 2004-08-23
> 19:49:58.000 2556928 98000000015100001 98000000015300001 98000000015100001 67000000040300001
> 2005-05-02
> 21:39:49.000 NULL 146 9 1033 196609 70 4608 7 0 623 HOME 0 {DDBB3598-43BE-44B9-9B23-B51895A59011}
> NULL NULL
>
> POS_Data C:\MSSQL7\data\POS_Data.MDF D PRIMARY 2555904 35184372080640
> POS_Log C:\MSSQL7\data\POS_Log.LDF L NULL 1048576 35184372080640
>
> what should be done next and what does this log represent, is my data
> ok.
>
> --
> KNIGHT
> KNIGHT's Profile: http://www.mswebservertalk.com/member.php?userid=951
> View this thread: http://www.mswebservertalk.com/t-1870403952
>
|||following is the message from the QA, when i try RESTORE and RESTORE
VERIFYONLY as you suggested.
Server: Msg 3242, Level 16, State 2, Line 1
The file on device 'c:\pos.bak' is not a valid Microsoft Tape Format
backup set.
Server: Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.
KNIGHT
KNIGHT's Profile: http://www.mswebservertalk.com/member.php?userid=951
View this thread: http://www.mswebservertalk.com/t-1870403952
|||Then you might just have a corrupt backup. I suggest you open a case with MS Support and see if they
can assist in any way.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KNIGHT" <KNIGHT.1oikch@.no-mx.mswebservertalk.com> wrote in message
news:KNIGHT.1oikch@.no-mx.mswebservertalk.com...
> following is the message from the QA, when i try RESTORE and RESTORE
> VERIFYONLY as you suggested.
> Server: Msg 3242, Level 16, State 2, Line 1
> The file on device 'c:\pos.bak' is not a valid Microsoft Tape Format
> backup set.
> Server: Msg 3013, Level 16, State 1, Line 1
> VERIFY DATABASE is terminating abnormally.
>
> --
> KNIGHT
> KNIGHT's Profile: http://www.mswebservertalk.com/member.php?userid=951
> View this thread: http://www.mswebservertalk.com/t-1870403952
>

Db Restore Problems

Hi All,
i have backed up a database from SQL server 7.0 on a file.
now when i try to restore the same backup on other machine
it gives me the following error.
Microsoft SQL-DMO (ODBC SQLState: 42000)
The file on device is not a valid Microsoft Tape format backup set
pls could some1 help me out with this problem asap.
Thx
--
KNIGH
----
KNIGHT's Profile: http://www.msusenet.com/member.php?userid=95
View this thread: http://www.msusenet.com/t-187040395You could try RESTORE HEADERONLY and RESTORE FILELISTONLY and see if those also give you the same
error. If they do, and you are certain that you got the syntax right, I suggest you open a support
case with MS (as this seems to in need for a quick solution).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KNIGHT" <KNIGHT.1oi0wb@.no-mx.msusenet.com> wrote in message
news:KNIGHT.1oi0wb@.no-mx.msusenet.com...
> Hi All,
> i have backed up a database from SQL server 7.0 on a file.
> now when i try to restore the same backup on other machine
> it gives me the following error.
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> The file on device is not a valid Microsoft Tape format backup set
> pls could some1 help me out with this problem asap.
> Thx.
>
> --
> KNIGHT
> ---
> KNIGHT's Profile: http://www.msusenet.com/member.php?userid=951
> View this thread: http://www.msusenet.com/t-1870403952
>|||hi, thx for the reply,
i tried the restore command you provided and following is the output o
the same.
NULL NULL 1 NULL 0 1 2 sa HOME POS 515 2004-08-2
19:49:58.000 2556928 98000000015100001 98000000015300001 98000000015100001 67000000040300001 2005-05-0
21:39:49.000 NULL 146 9 1033 196609 70 4608 7 0 623 HOME 0 {DDBB3598-43BE-44B9-9B23-B51895A59011} NULL NULL
POS_Data C:\MSSQL7\data\POS_Data.MDF D PRIMARY 2555904 35184372080640
POS_Log C:\MSSQL7\data\POS_Log.LDF L NULL 1048576 35184372080640
what should be done next and what does this log represent, is my dat
ok
--
KNIGH
----
KNIGHT's Profile: http://www.msusenet.com/member.php?userid=95
View this thread: http://www.msusenet.com/t-187040395|||Looking good so far (but you didn't post the RESTORE commands you executed). Now try doing the
actual RESTORE from Query Analyzer. Again, please post the command you executed and the exact error
messages from SQL Server. Another thing you can try is RESTORE VERIFYONLY.
To understand the result of these commands, just read about them in Books Online. They are
documented.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KNIGHT" <KNIGHT.1oic0c@.no-mx.msusenet.com> wrote in message
news:KNIGHT.1oic0c@.no-mx.msusenet.com...
> hi, thx for the reply,
> i tried the restore command you provided and following is the output of
> the same.
> NULL NULL 1 NULL 0 1 2 sa HOME POS 515 2004-08-23
> 19:49:58.000 2556928 98000000015100001 98000000015300001 98000000015100001 67000000040300001
> 2005-05-02
> 21:39:49.000 NULL 146 9 1033 196609 70 4608 7 0 623 HOME 0 {DDBB3598-43BE-44B9-9B23-B51895A59011}
> NULL NULL
>
> POS_Data C:\MSSQL7\data\POS_Data.MDF D PRIMARY 2555904 35184372080640
> POS_Log C:\MSSQL7\data\POS_Log.LDF L NULL 1048576 35184372080640
>
> what should be done next and what does this log represent, is my data
> ok.
>
> --
> KNIGHT
> ---
> KNIGHT's Profile: http://www.msusenet.com/member.php?userid=951
> View this thread: http://www.msusenet.com/t-1870403952
>|||following is the message from the QA, when i try RESTORE and RESTOR
VERIFYONLY as you suggested.
Server: Msg 3242, Level 16, State 2, Line 1
The file on device 'c:\pos.bak' is not a valid Microsoft Tape Forma
backup set.
Server: Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally
--
KNIGH
----
KNIGHT's Profile: http://www.msusenet.com/member.php?userid=95
View this thread: http://www.msusenet.com/t-187040395|||Then you might just have a corrupt backup. I suggest you open a case with MS Support and see if they
can assist in any way.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KNIGHT" <KNIGHT.1oikch@.no-mx.msusenet.com> wrote in message
news:KNIGHT.1oikch@.no-mx.msusenet.com...
> following is the message from the QA, when i try RESTORE and RESTORE
> VERIFYONLY as you suggested.
> Server: Msg 3242, Level 16, State 2, Line 1
> The file on device 'c:\pos.bak' is not a valid Microsoft Tape Format
> backup set.
> Server: Msg 3013, Level 16, State 1, Line 1
> VERIFY DATABASE is terminating abnormally.
>
> --
> KNIGHT
> ---
> KNIGHT's Profile: http://www.msusenet.com/member.php?userid=951
> View this thread: http://www.msusenet.com/t-1870403952
>

Db Restore Problems

Hi All,
i have backed up a database from SQL server 7.0 on a file.
now when i try to restore the same backup on other machine
it gives me the following error.
Microsoft SQL-DMO (ODBC SQLState: 42000)
The file on device is not a valid Microsoft Tape format backup set
pls could some1 help me out with this problem asap.
Thx.
KNIGHT
---
KNIGHT's Profile: http://www.msmcse.ms/member.php?userid=951
View this thread: http://www.msmcse.ms/t-1870403952You could try RESTORE HEADERONLY and RESTORE FILELISTONLY and see if those a
lso give you the same
error. If they do, and you are certain that you got the syntax right, I sugg
est you open a support
case with MS (as this seems to in need for a quick solution).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KNIGHT" <KNIGHT.1oi0wb@.no-mx.msmcse.ms> wrote in message
news:KNIGHT.1oi0wb@.no-mx.msmcse.ms...
> Hi All,
> i have backed up a database from SQL server 7.0 on a file.
> now when i try to restore the same backup on other machine
> it gives me the following error.
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> The file on device is not a valid Microsoft Tape format backup set
> pls could some1 help me out with this problem asap.
> Thx.
>
> --
> KNIGHT
> ---
> KNIGHT's Profile: http://www.msmcse.ms/member.php?userid=951
> View this thread: http://www.msmcse.ms/t-1870403952
>|||hi, thx for the reply,
i tried the restore command you provided and following is the output of
the same.
NULL NULL 1 NULL 0 1 2 sa HOME POS 515 2
004-08-23
19:49:58. 000 2556928 98000000015100001 9800000001
5300001 98000000015100001 6
7000000040300001 2005-05-02
21:39:49. 000 NULL 146 9 1033 196609 70 4608 7 0 6
23 HOME 0 {DDBB3598-43
BE-44B9-9B23-B51895A59011} NULL NULL
POS_Data C:\MSSQL7\data\POS_Data.MDF D PRIMARY 2555904 35184372080640
POS_Log C:\MSSQL7\data\POS_Log.LDF L NULL 1048576 35184372080640
what should be done next and what does this log represent, is my data
ok.
KNIGHT
---
KNIGHT's Profile: http://www.msmcse.ms/member.php?userid=951
View this thread: http://www.msmcse.ms/t-1870403952|||Looking good so far (but you didn't post the RESTORE commands you executed).
Now try doing the
actual RESTORE from Query Analyzer. Again, please post the command you execu
ted and the exact error
messages from SQL Server. Another thing you can try is RESTORE VERIFYONLY.
To understand the result of these commands, just read about them in Books On
line. They are
documented.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KNIGHT" <KNIGHT.1oic0c@.no-mx.msmcse.ms> wrote in message
news:KNIGHT.1oic0c@.no-mx.msmcse.ms...
> hi, thx for the reply,
> i tried the restore command you provided and following is the output of
> the same.
> NULL NULL 1 NULL 0 1 2 sa HOME POS 515 2004-08-23
> 19:49:58.000 2556928 98000000015100001 98000000015300001 98000000015100001
67000000040300001
> 2005-05-02
> 21:39:49.000 NULL 146 9 1033 196609 70 4608 7 0 623 HOME 0 {DDBB3598-
43BE-44B9-9B23-B51895A59011}
> NULL NULL
>
> POS_Data C:\MSSQL7\data\POS_Data.MDF D PRIMARY 2555904 35184372080640
> POS_Log C:\MSSQL7\data\POS_Log.LDF L NULL 1048576 35184372080640
>
> what should be done next and what does this log represent, is my data
> ok.
>
> --
> KNIGHT
> ---
> KNIGHT's Profile: http://www.msmcse.ms/member.php?userid=951
> View this thread: http://www.msmcse.ms/t-1870403952
>|||following is the message from the QA, when i try RESTORE and RESTORE
VERIFYONLY as you suggested.
Server: Msg 3242, Level 16, State 2, Line 1
The file on device 'c:\pos.bak' is not a valid Microsoft Tape Format
backup set.
Server: Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.
KNIGHT
---
KNIGHT's Profile: http://www.msmcse.ms/member.php?userid=951
View this thread: http://www.msmcse.ms/t-1870403952|||Then you might just have a corrupt backup. I suggest you open a case with MS
Support and see if they
can assist in any way.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KNIGHT" <KNIGHT.1oikch@.no-mx.msmcse.ms> wrote in message
news:KNIGHT.1oikch@.no-mx.msmcse.ms...
> following is the message from the QA, when i try RESTORE and RESTORE
> VERIFYONLY as you suggested.
> Server: Msg 3242, Level 16, State 2, Line 1
> The file on device 'c:\pos.bak' is not a valid Microsoft Tape Format
> backup set.
> Server: Msg 3013, Level 16, State 1, Line 1
> VERIFY DATABASE is terminating abnormally.
>
> --
> KNIGHT
> ---
> KNIGHT's Profile: http://www.msmcse.ms/member.php?userid=951
> View this thread: http://www.msmcse.ms/t-1870403952
>

DB Restore from multiple files

Hi,
I've got a pretty large database that i normally backup to 6 files and
restore it to another database. I've only done this through enterprise
manager so it's just point and click. But i was wondering if anyone knew what
the command line version of this would be? Would you use "stripe" ? Trying to
script the backup and restore part. Any help would be greatly appreciated.
-FS
Hello,
See RESTORE DATABASE command in books online. Use the below sample.
RESTORE DATABASE DBNAME FROM disk = 'C:\MSSQL\BACKUP\DBA_db_BKUP_BAK1.BAK',
disk = 'D:\MSSQL\MSSQL\BACKUP\DBA_db_BKUP_BAK2.BAK',disk =
'E:\MSSQL\MSSQL\BACKUP\DBA_db_BKUP_BAK3.BAK', Stats=1
Thanks
Hari
"FS" <FS@.discussions.microsoft.com> wrote in message
news:28DD5BE8-5B09-4262-81FE-B3C6CA5FB636@.microsoft.com...
> Hi,
> I've got a pretty large database that i normally backup to 6 files and
> restore it to another database. I've only done this through enterprise
> manager so it's just point and click. But i was wondering if anyone knew
> what
> the command line version of this would be? Would you use "stripe" ? Trying
> to
> script the backup and restore part. Any help would be greatly appreciated.
> -FS

Sunday, March 25, 2012

db_backupoperator question

db_backupoperator question

db_backupoperator question

Thursday, March 22, 2012

db stuck on loaded status

db stuck on loaded status

Wednesday, March 21, 2012

DB Size

db rights

db rights

Monday, March 19, 2012

db rights

DB Restored to New Server, ODBC Says Database is Not Valid

DB Restore Time

DB Restore Time

DB Restore Time

db restore question

db restore question

Db restore ques.

Db Restore Problems

Db Restore Problems

Db Restore Problems

DB Restore from multiple files

DBCC CHECKDB

Blog Archive

About Me