Showing posts with label backup. Show all posts
Showing posts with label backup. Show all posts

Wednesday, March 28, 2012

Property IsPrimaryFile is not available for DataFile

I backup an SQL Server 2000 database (MY_DATABASE) and restore it in
SQLServer 2005.
Then, when I try to access its properties I get:
Property IsPrimaryFile is not available for DataFile '[MY_DATABASE]'. This
property may not exist for this object, or may not be retrievable due to
insufficient access rights. (Microsoft.SqlServer.Smo)
- I'm using a windows 2003 administrator account
- I tried setting compatibility level to 90 but it didn't help.
- If I connect with a sql-level user (not windows 2003 login) a can view the
properties, but I can't change anything.
Any ideas?
Thanks!http://www.dbtalk.net/microsoft-public-sqlserver-setup/copying-databse-sql-2000-sql-265404.html
"Gaspar" <Gaspar@.Gaspar.com> wrote in message
news:uOX2SbJaGHA.3612@.TK2MSFTNGP03.phx.gbl...
>I backup an SQL Server 2000 database (MY_DATABASE) and restore it in
>SQLServer 2005.
> Then, when I try to access its properties I get:
> Property IsPrimaryFile is not available for DataFile '[MY_DATABASE]'. This
> property may not exist for this object, or may not be retrievable due to
> insufficient access rights. (Microsoft.SqlServer.Smo)
> - I'm using a windows 2003 administrator account
> - I tried setting compatibility level to 90 but it didn't help.
> - If I connect with a sql-level user (not windows 2003 login) a can view
> the properties, but I can't change anything.
> Any ideas?
> Thanks!
>

Property IsPrimaryFile is not available for DataFile

I backup an SQL Server 2000 database (MY_DATABASE) and restore it in
SQLServer 2005.
Then, when I try to access its properties I get:
Property IsPrimaryFile is not available for DataFile '[MY_DATABASE]'. Th
is
property may not exist for this object, or may not be retrievable due to
insufficient access rights. (Microsoft.SqlServer.Smo)
- I'm using a windows 2003 administrator account
- I tried setting compatibility level to 90 but it didn't help.
- If I connect with a sql-level user (not windows 2003 login) a can view the
properties, but I can't change anything.
Any ideas?
Thanks!http://www.dbtalk.net/microsoft-pub...
l-265404.html
"Gaspar" <Gaspar@.Gaspar.com> wrote in message
news:uOX2SbJaGHA.3612@.TK2MSFTNGP03.phx.gbl...
>I backup an SQL Server 2000 database (MY_DATABASE) and restore it in
>SQLServer 2005.
> Then, when I try to access its properties I get:
> Property IsPrimaryFile is not available for DataFile '[MY_DATABASE]'.
This
> property may not exist for this object, or may not be retrievable due to
> insufficient access rights. (Microsoft.SqlServer.Smo)
> - I'm using a windows 2003 administrator account
> - I tried setting compatibility level to 90 but it didn't help.
> - If I connect with a sql-level user (not windows 2003 login) a can view
> the properties, but I can't change anything.
> Any ideas?
> Thanks!
>

Monday, March 26, 2012

Proper Way to Truncate Log After Performing Full Backup (SQL Server 2000)

Hello all - I have a SQL Server 2000 database setup using the Full Recovery Model. Each night, we backup the entire database, and as such would like to truncate the log at this time as well.

Is the best way to do this to also backup the Transaction Log, and then perform a DBCC SHRINKFILE command? It just seems like there should be an easier way...?

Thanks!

Let's back up a few steps here.

What are you gaining by shrinking the transaction log every night?

The transaction log is designed to continually reuse its space internally. Whenever you back up the transaction log, all virtual log files (logical areas within the physical log file) which are not involved in any current transaction are marked as "free" and can be reused.

So, one way to manage the growth of a transaction log is to vary the frequency of log backups. The more frequent the log backup, the less data needs to be stored in the log (and the less data is lost in the event of a disaster).

Once the log reaches a steady-state, it shouldn't grow unless some exceptional update happens.

When you continually shrink and grow the file, you end up with filesystem fragmentation, which isn't good for performance. Also, the log file growth itself takes time.

Proper way to backup and restore Broker-Enabled database

For developers, we often have a need to backup a production database and restore it on local or integration machines. This production database is enabled for service broker and operates at a relatively high traffic level. When the database is backed up, the size is nearly 12GB; when SET NEW_BROKER is subsequently executed on the restored database, the size goes down to about 800MB. It appears that most of this is residing in the xmit queue. So, my question is: how best to backup a production database with queues activated, etc. without ending up with a 12GB backup?

Thanks.

There is no way to back up the database without saving the data. A full backup is all or nothing. If you want to shrink the database, as you say, you need to do the post-processing step of NEW_BROKER. If other folks need to use the database, you should always be doing NEW_BROKER. There should not be more than one copy of a database with the same broker instance ID running.

-mike

|||Move your production data into a separate filgroup and backup only that filegroup. The Service Broker xmit queue will always reside on the primary filgroup.

Proper way to backup and restore Broker-Enabled database

For developers, we often have a need to backup a production database and restore it on local or integration machines. This production database is enabled for service broker and operates at a relatively high traffic level. When the database is backed up, the size is nearly 12GB; when SET NEW_BROKER is subsequently executed on the restored database, the size goes down to about 800MB. It appears that most of this is residing in the xmit queue. So, my question is: how best to backup a production database with queues activated, etc. without ending up with a 12GB backup?

Thanks.

There is no way to back up the database without saving the data. A full backup is all or nothing. If you want to shrink the database, as you say, you need to do the post-processing step of NEW_BROKER. If other folks need to use the database, you should always be doing NEW_BROKER. There should not be more than one copy of a database with the same broker instance ID running.

-mike

|||Move your production data into a separate filgroup and backup only that filegroup. The Service Broker xmit queue will always reside on the primary filgroup.

Tuesday, March 20, 2012

Progress bar for backup operation

Hi,
I am creating a simple form that user will backup his database using that. I
need to show a progress bar when the backup is in progress. How can I read a
value which indicates that? I saw that BACKUP statement has a STATS option,
but I don't know how to capture its value from the server and bring it to
client.
Any help would be greatly appreciated.
Thanks in advance,
Amin
Amin,
Use the Complete event of the Backup object in SQL-DMO.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Amin Sobati wrote:
> Hi,
> I am creating a simple form that user will backup his database using that. I
> need to show a progress bar when the backup is in progress. How can I read a
> value which indicates that? I saw that BACKUP statement has a STATS option,
> but I don't know how to capture its value from the server and bring it to
> client.
> Any help would be greatly appreciated.
> Thanks in advance,
> Amin
>
>
|||Amin,
Oops, I meant use the PercentComplete event of the Backup object. Ignore
my other post in this thread.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Amin Sobati wrote:
> Hi,
> I am creating a simple form that user will backup his database using that. I
> need to show a progress bar when the backup is in progress. How can I read a
> value which indicates that? I saw that BACKUP statement has a STATS option,
> but I don't know how to capture its value from the server and bring it to
> client.
> Any help would be greatly appreciated.
> Thanks in advance,
> Amin
>
>
|||Thanks Mark! That was great tip :-)
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OZDT9GabEHA.4092@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Amin,
> Oops, I meant use the PercentComplete event of the Backup object. Ignore
> my other post in this thread.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Amin Sobati wrote:
that. I[vbcol=seagreen]
read a[vbcol=seagreen]
option,[vbcol=seagreen]
to[vbcol=seagreen]

Progress bar for backup operation

Hi,
I am creating a simple form that user will backup his database using that. I
need to show a progress bar when the backup is in progress. How can I read a
value which indicates that? I saw that BACKUP statement has a STATS option,
but I don't know how to capture its value from the server and bring it to
client.
Any help would be greatly appreciated.
Thanks in advance,
AminAmin,
Use the Complete event of the Backup object in SQL-DMO.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Amin Sobati wrote:
> Hi,
> I am creating a simple form that user will backup his database using that.
I
> need to show a progress bar when the backup is in progress. How can I read
a
> value which indicates that? I saw that BACKUP statement has a STATS option
,
> but I don't know how to capture its value from the server and bring it to
> client.
> Any help would be greatly appreciated.
> Thanks in advance,
> Amin
>
>|||Amin,
Oops, I meant use the PercentComplete event of the Backup object. Ignore
my other post in this thread.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Amin Sobati wrote:
> Hi,
> I am creating a simple form that user will backup his database using that.
I
> need to show a progress bar when the backup is in progress. How can I read
a
> value which indicates that? I saw that BACKUP statement has a STATS option
,
> but I don't know how to capture its value from the server and bring it to
> client.
> Any help would be greatly appreciated.
> Thanks in advance,
> Amin
>
>|||Thanks Mark! That was great tip :-)
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OZDT9GabEHA.4092@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Amin,
> Oops, I meant use the PercentComplete event of the Backup object. Ignore
> my other post in this thread.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Amin Sobati wrote:
that. I[vbcol=seagreen]
read a[vbcol=seagreen]
option,[vbcol=seagreen]
to[vbcol=seagreen]

Progress bar for backup operation

Hi,
I am creating a simple form that user will backup his database using that. I
need to show a progress bar when the backup is in progress. How can I read a
value which indicates that? I saw that BACKUP statement has a STATS option,
but I don't know how to capture its value from the server and bring it to
client.
Any help would be greatly appreciated.
Thanks in advance,
AminAmin,
Use the Complete event of the Backup object in SQL-DMO.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Amin Sobati wrote:
> Hi,
> I am creating a simple form that user will backup his database using that. I
> need to show a progress bar when the backup is in progress. How can I read a
> value which indicates that? I saw that BACKUP statement has a STATS option,
> but I don't know how to capture its value from the server and bring it to
> client.
> Any help would be greatly appreciated.
> Thanks in advance,
> Amin
>
>|||Amin,
Oops, I meant use the PercentComplete event of the Backup object. Ignore
my other post in this thread.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Amin Sobati wrote:
> Hi,
> I am creating a simple form that user will backup his database using that. I
> need to show a progress bar when the backup is in progress. How can I read a
> value which indicates that? I saw that BACKUP statement has a STATS option,
> but I don't know how to capture its value from the server and bring it to
> client.
> Any help would be greatly appreciated.
> Thanks in advance,
> Amin
>
>|||Thanks Mark! That was great tip :-)
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OZDT9GabEHA.4092@.TK2MSFTNGP11.phx.gbl...
> Amin,
> Oops, I meant use the PercentComplete event of the Backup object. Ignore
> my other post in this thread.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Amin Sobati wrote:
> > Hi,
> > I am creating a simple form that user will backup his database using
that. I
> > need to show a progress bar when the backup is in progress. How can I
read a
> > value which indicates that? I saw that BACKUP statement has a STATS
option,
> > but I don't know how to capture its value from the server and bring it
to
> > client.
> > Any help would be greatly appreciated.
> > Thanks in advance,
> > Amin
> >
> >
> >

Monday, March 12, 2012

programmatically restore a database

I recently had to find ways to programmatically backup and restore an
SQL Server Express database.
For backup, I found a one-liner stored procedure that does the job
(BACKUP DATABASE Toy to disk = 'c:\Projects\Toy.bak'.
My question is, is there something as simple for a restore operation?

Thanks in Advance

-KostasYes, use : RESTORE DATABASE MyNwind FROM MyNwind_1 (i've taken this from
BOL) for more involved examples check BOL

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm

<kskartsiounis@.yahoo.com> wrote in message
news:1144224541.969405.253280@.g10g2000cwb.googlegr oups.com...
> I recently had to find ways to programmatically backup and restore an
> SQL Server Express database.
> For backup, I found a one-liner stored procedure that does the job
> (BACKUP DATABASE Toy to disk = 'c:\Projects\Toy.bak'.
> My question is, is there something as simple for a restore operation?
> Thanks in Advance
> -Kostas|||Pardon my ignorance, but what is BOL?|||BOL is the SQL Server Books Online documentation.

Note you may need to specify additional RESTORE options in some situations.
For example, database files will be restored to the original locations by
default. If you are restoring to a machine with a different drive/folder
structure, you'll need specify different file paths using the WITH MOVE
option.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<kskartsiounis@.yahoo.com> wrote in message
news:1144229322.586326.67220@.u72g2000cwu.googlegro ups.com...
> Pardon my ignorance, but what is BOL?|||We use

RESTORE DATABASE DBName FROM DISK='d:\files\Backups\DBName.bak' WITH
REPLACE

Monday, February 20, 2012

Programatically Backup and Restore User Logins Problem...

I'm sure your all aware of the bug in SQL 2000 where if you take a backup an
d
then do a restore on a different database server, the users accounts are not
added to the server's own security (login) section.
The user accounts are though added properly to the databases "users" list
however. And yes I do need to use SQL Server authentication, instead of
windows authentication.
Does anybody know how to get around this' I wish to do it programatically
without enterprise manager.Well I wouldnpt call it a bug, remember that the users, which are specific t
o
the database, map to logins, which are server-based... So whn you restore a
database on a different server than where the backup was made, there is no
way for the softwae to know which login on the other server the dataabse use
r
should be mapped to... If you're lucky enough that the server Login list is
identical on the other server including Login IDs, (pure coincidence) it
actually does fix things up properly, but this is rare.
So you need to use a system-level Stored proc called sp_adduser, this can be
called programatiicaly.
"David Dolheguy" wrote:

> I'm sure your all aware of the bug in SQL 2000 where if you take a backup
and
> then do a restore on a different database server, the users accounts are n
ot
> added to the server's own security (login) section.
> The user accounts are though added properly to the databases "users" list
> however. And yes I do need to use SQL Server authentication, instead of
> windows authentication.
> Does anybody know how to get around this' I wish to do it programaticall
y
> without enterprise manager.
>|||This is not a bug at all. Logins are at the Server level and Users are at
the DB level. When you backup and restore a db it knows nothing of the
server. These might help:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL
Andrew J. Kelly SQL MVP
"David Dolheguy" <DavidDolheguy@.discussions.microsoft.com> wrote in message
news:5C877C2E-EE7D-4658-9E31-AF46F0060E18@.microsoft.com...
> I'm sure your all aware of the bug in SQL 2000 where if you take a backup
> and
> then do a restore on a different database server, the users accounts are
> not
> added to the server's own security (login) section.
> The user accounts are though added properly to the databases "users" list
> however. And yes I do need to use SQL Server authentication, instead of
> windows authentication.
> Does anybody know how to get around this' I wish to do it
> programatically
> without enterprise manager.
>