Resotring a backup file to a DB on a differerent server

Hi,
I'm trying to restore a backup file to a data base on a different server by
overwriting it.
In order to do this I need to move the logical file I am restoring to the
location of the physical database file I am restoring to (as per the prompt I
am receiving)
When it say "MOVE", will this in fact move the file over & possibly damage
the database from which the restore is being taken? Does it move any files or
just copy?
Is there any danger here?
Below is what I'm doing:
RESTORE DATABASE oneDB
FROM DISK = 'C:\myPhysicalBackupFile'
WITH REPLACE,
MOVE 'myLogicalFile' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\PhysicalFile.MDF'
Many thanks for any help on this
AntIt's a little confusing the first time, but all the MOVE is doing is telling
the engine where to place the MDF file. The FROM is the .BAK file.
And if you're overwriting an exiting database, then yes, you're gonna whack
it. But that is expected behavior.
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:69B97720-9594-48A8-9181-53F874B8EB3E@.microsoft.com...
> Hi,
> I'm trying to restore a backup file to a data base on a different server
> by
> overwriting it.
> In order to do this I need to move the logical file I am restoring to the
> location of the physical database file I am restoring to (as per the
> prompt I
> am receiving)
> When it say "MOVE", will this in fact move the file over & possibly damage
> the database from which the restore is being taken? Does it move any files
> or
> just copy?
> Is there any danger here?
> Below is what I'm doing:
> RESTORE DATABASE oneDB
> FROM DISK = 'C:\myPhysicalBackupFile'
> WITH REPLACE,
> MOVE 'myLogicalFile' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\PhysicalFile.MDF'
>
> Many thanks for any help on this
> Ant
>|||Hi Jay, thanks very much for the reply.
It's not the Database I'm overwriting that I'm concerned about, so long as
it doesn't affect the database from where I'm using the Backup from. I was
concerned that it might damage that. Sounds safe.
Many thanks for your answer Jay.
"Jay" wrote:
> It's a little confusing the first time, but all the MOVE is doing is telling
> the engine where to place the MDF file. The FROM is the .BAK file.
> And if you're overwriting an exiting database, then yes, you're gonna whack
> it. But that is expected behavior.
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:69B97720-9594-48A8-9181-53F874B8EB3E@.microsoft.com...
> > Hi,
> >
> > I'm trying to restore a backup file to a data base on a different server
> > by
> > overwriting it.
> >
> > In order to do this I need to move the logical file I am restoring to the
> > location of the physical database file I am restoring to (as per the
> > prompt I
> > am receiving)
> >
> > When it say "MOVE", will this in fact move the file over & possibly damage
> > the database from which the restore is being taken? Does it move any files
> > or
> > just copy?
> >
> > Is there any danger here?
> >
> > Below is what I'm doing:
> >
> > RESTORE DATABASE oneDB
> >
> > FROM DISK = 'C:\myPhysicalBackupFile'
> > WITH REPLACE,
> > MOVE 'myLogicalFile' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL\data\PhysicalFile.MDF'
> >
> >
> > Many thanks for any help on this
> >
> > Ant
> >
>
>|||Ant
Please read also
http://dimantdatabasesolutions.blogspot.com/2007/04/sql-or-windows-authentication.html
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:A48539B7-996B-4A16-8F99-1100515146C7@.microsoft.com...
> Hi Jay, thanks very much for the reply.
> It's not the Database I'm overwriting that I'm concerned about, so long as
> it doesn't affect the database from where I'm using the Backup from. I was
> concerned that it might damage that. Sounds safe.
> Many thanks for your answer Jay.
>
> "Jay" wrote:
>> It's a little confusing the first time, but all the MOVE is doing is
>> telling
>> the engine where to place the MDF file. The FROM is the .BAK file.
>> And if you're overwriting an exiting database, then yes, you're gonna
>> whack
>> it. But that is expected behavior.
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:69B97720-9594-48A8-9181-53F874B8EB3E@.microsoft.com...
>> > Hi,
>> >
>> > I'm trying to restore a backup file to a data base on a different
>> > server
>> > by
>> > overwriting it.
>> >
>> > In order to do this I need to move the logical file I am restoring to
>> > the
>> > location of the physical database file I am restoring to (as per the
>> > prompt I
>> > am receiving)
>> >
>> > When it say "MOVE", will this in fact move the file over & possibly
>> > damage
>> > the database from which the restore is being taken? Does it move any
>> > files
>> > or
>> > just copy?
>> >
>> > Is there any danger here?
>> >
>> > Below is what I'm doing:
>> >
>> > RESTORE DATABASE oneDB
>> >
>> > FROM DISK = 'C:\myPhysicalBackupFile'
>> > WITH REPLACE,
>> > MOVE 'myLogicalFile' TO 'C:\Program Files\Microsoft SQL
>> > Server\MSSQL\data\PhysicalFile.MDF'
>> >
>> >
>> > Many thanks for any help on this
>> >
>> > Ant
>> >
>>sql

resotre the user in the database

Before formatting the server the user took the backup of the database.
After the installation the database was restored but we can find the all the
user in resored database obivously the all user are not in the logins under
the security. Is there anyway to copy the all the user from resoted database
to logins
Thanks in advance
NizhamTake a look at sp_change_users_login system stored procedure in the BOL
"Nizham" <junkmn@.gmail.com> wrote in message
news:%23Ue8kb%23SGHA.792@.TK2MSFTNGP10.phx.gbl...
> Before formatting the server the user took the backup of the database.
> After the installation the database was restored but we can find the all
> the user in resored database obivously the all user are not in the logins
> under the security. Is there anyway to copy the all the user from resoted
> database to logins
> Thanks in advance
> Nizham
>|||There are more than 100 user in the databse any can help me on this
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uQ4l%23e%23SGHA.1236@.TK2MSFTNGP11.phx.gbl...
> Take a look at sp_change_users_login system stored procedure in the BOL
>
>
> "Nizham" <junkmn@.gmail.com> wrote in message
> news:%23Ue8kb%23SGHA.792@.TK2MSFTNGP10.phx.gbl...
>|||There's a GUI over sp_change_users_login available at www.dbmaint.com.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nizham" <mnizham@.gmail.com> wrote in message news:e%236XkwDTGHA.4340@.tk2msftngp13.phx.gbl.
.
> There are more than 100 user in the databse any can help me on this
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uQ4l%23e%23SGHA.1236@.TK2MSFTNGP11.phx.gbl...
>

Resoring transaction logs

We believe we lost data yesterday in one of our tables, but only have a full
backup from 7/12 and have transaction logs from 7/14. Is there anything we
can do here? Don't you need the transaction logs from the last full backup
time in order to restore from the logs?
Help!Yes, you should be able to apply those transaction logs to that full backup
since you have a break in the sequence.(please someone correct me if I'm
wrong). You don't have logs backed up to tape anywhere?
If not, then you might want to look into Lumigent's Log Explorer to examine
the transaction logs. www.lumigent.com
"Mark" <maxmann@.kc.rr.com> wrote in message
news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> We believe we lost data yesterday in one of our tables, but only have a
full
> backup from 7/12 and have transaction logs from 7/14. Is there anything
we
> can do here? Don't you need the transaction logs from the last full
backup
> time in order to restore from the logs?
>
> Help!
>|||hi Mark. First you need to backup the transaction log (should be available
regardless of the state of the failed db) before you do anything else. Then
you can restore the db from the full specifying no recovery to allow you to
continue restoring. Finally you can restore the transaction logs with a
recovery so that transactions will be rolled forward or back and the db will
then become available.
Please refer to books online for this procedure
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Mark" <maxmann@.kc.rr.com> wrote in message
news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> We believe we lost data yesterday in one of our tables, but only have a
full
> backup from 7/12 and have transaction logs from 7/14. Is there anything
we
> can do here? Don't you need the transaction logs from the last full
backup
> time in order to restore from the logs?
>
> Help!
>|||Yes and No.
Yes - If 7/14 logs are the immediate logs backed up after 7/12 full backup.
You can apply logs all the way up to the most recent backup.
No - You backed up logs two days later? Logs should be backed up much more
frequently. That's what log backup is meant. If at least one log backup was
missing between 7/12 and 7/14, then you can not use the 7/14 logs at all.
Richard
"Mark" <maxmann@.kc.rr.com> wrote in message
news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> We believe we lost data yesterday in one of our tables, but only have a
full
> backup from 7/12 and have transaction logs from 7/14. Is there anything
we
> can do here? Don't you need the transaction logs from the last full
backup
> time in order to restore from the logs?
>
> Help!
>|||..agreed log backup should be performed at LEAST daily, however there should
be no reason why you should be missing any logs since your last full backup,
assuming of course you are not using the simple recovery model -in which
case you can kiss your logs goodbye.
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Richard Ding" <dingr@.cleanharbors.com> wrote in message
news:eRtXVqsUDHA.1484@.TK2MSFTNGP12.phx.gbl...
> Yes and No.
> Yes - If 7/14 logs are the immediate logs backed up after 7/12 full
backup.
> You can apply logs all the way up to the most recent backup.
> No - You backed up logs two days later? Logs should be backed up much more
> frequently. That's what log backup is meant. If at least one log backup
was
> missing between 7/12 and 7/14, then you can not use the 7/14 logs at all.
>
> Richard
> "Mark" <maxmann@.kc.rr.com> wrote in message
> news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> > We believe we lost data yesterday in one of our tables, but only have a
> full
> > backup from 7/12 and have transaction logs from 7/14. Is there anything
> we
> > can do here? Don't you need the transaction logs from the last full
> backup
> > time in order to restore from the logs?
> >
> >
> > Help!
> >
> >
>|||Well, just to explain the situation a little further.
1. We are using Full Recover mode, which should be a good thing as far as
I'm concerned.
2. The reason we were missing the two days of logs is because we have two
jobs set in sql server.
One to do a daily full backup and 1 to do hourly trans log backups,
which is cool. The problem is
that on the 12th, somehow the job to do the full backups go disabled,
and we only have trans log backups
on tape back to the 14th. So that's how the gap happened. It's not
that we were doing trans log backups
every two days. It was just the issue w/ that job being disabled.
3. We have contacted lumigent and they said since we have the db in full
recovery mode that we should
be able to completely restore the database table that got deleted simply
by using the transaction log
that we have of when it happened.
Thanks for all the help.
Mark
"Mark" <maxmann@.kc.rr.com> wrote in message
news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> We believe we lost data yesterday in one of our tables, but only have a
full
> backup from 7/12 and have transaction logs from 7/14. Is there anything
we
> can do here? Don't you need the transaction logs from the last full
backup
> time in order to restore from the logs?
>
> Help!
>|||James, firstly just because Mark states "full
backup from 7/12 and have transaction logs from 7/14" doesnt mean to suggest
that there is a break in the sequence only that he had a one day gap before
had did transaction log backups.
Secondly it doesnt matter whether he has got subsequent logs to tape or not
since the transaction log is *still* available for backup before he starts
the recovery. The Log Explorer tool (very good) is probably unnecessary in
this scenario).
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"James Luetkehoelter" <james.luetkehoetler@.NOSPAMspyglassllc.com> wrote in
message news:eEF4jHtUDHA.964@.TK2MSFTNGP09.phx.gbl...
> Er, this should have read "Shouldn't be able to apply". If there's a
break
> in the logs, you shouldn't be able to do a point-in-time recovery.
> "James Luetkehoelter" <james.luetkehoetler@.NOSPAMspyglassllc.com> wrote in
> message news:%23OK$bcsUDHA.1872@.TK2MSFTNGP12.phx.gbl...
> > Yes, you should be able to apply those transaction logs to that full
> backup
> > since you have a break in the sequence.(please someone correct me if I'm
> > wrong). You don't have logs backed up to tape anywhere?
> >
> > If not, then you might want to look into Lumigent's Log Explorer to
> examine
> > the transaction logs. www.lumigent.com
> >
> >
> > "Mark" <maxmann@.kc.rr.com> wrote in message
> > news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> > > We believe we lost data yesterday in one of our tables, but only have
a
> > full
> > > backup from 7/12 and have transaction logs from 7/14. Is there
anything
> > we
> > > can do here? Don't you need the transaction logs from the last full
> > backup
> > > time in order to restore from the logs?
> > >
> > >
> > > Help!
> > >
> > >
> >
> >
>|||Hi Mark, as you state your gap is literary that you "missed a backup". This
doesnt mean that you have missing backup data -just less backups. please
refer to and follow my previous posts, there is no reason why you should not
be able to recover point in time of your choosing.
***REMEMBER TO TAKE A FINAL TRANSACTION LOG BACKUP BEFORE ATTEMPTING YOUR
RECOVER***
You should read BOL, section Administering SQL Server/ Backing up and
Restoring Databases/ Using Recovery Models/ Full Recovery
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Mark" <maxmann@.kc.rr.com> wrote in message
news:ethVT$8UDHA.2320@.TK2MSFTNGP12.phx.gbl...
> Well, just to explain the situation a little further.
> 1. We are using Full Recover mode, which should be a good thing as far as
> I'm concerned.
> 2. The reason we were missing the two days of logs is because we have two
> jobs set in sql server.
> One to do a daily full backup and 1 to do hourly trans log backups,
> which is cool. The problem is
> that on the 12th, somehow the job to do the full backups go disabled,
> and we only have trans log backups
> on tape back to the 14th. So that's how the gap happened. It's not
> that we were doing trans log backups
> every two days. It was just the issue w/ that job being disabled.
> 3. We have contacted lumigent and they said since we have the db in full
> recovery mode that we should
> be able to completely restore the database table that got deleted
simply
> by using the transaction log
> that we have of when it happened.
> Thanks for all the help.
> Mark
>
> "Mark" <maxmann@.kc.rr.com> wrote in message
> news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> > We believe we lost data yesterday in one of our tables, but only have a
> full
> > backup from 7/12 and have transaction logs from 7/14. Is there anything
> we
> > can do here? Don't you need the transaction logs from the last full
> backup
> > time in order to restore from the logs?
> >
> >
> > Help!
> >
> >
>

Resize DB in SQL 7

I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
leaves just enough room on the partition for the nightly backup files, but I
keep getting a msg saying that the drive is almost full. I cannot resize the
partition, and shrinking the db does not change the allocation size. How can
I reduce the allocation size and free up some disk space? Thanks in advance
for any suggestions.
Back it up
remove it
recreate it, smaller
restore it
Jan
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
> I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but
I
> keep getting a msg saying that the drive is almost full. I cannot resize
the
> partition, and shrinking the db does not change the allocation size. How
can
> I reduce the allocation size and free up some disk space? Thanks in
advance
> for any suggestions.
|||That will not do anything, as restore will create database files with same size as when you took the
backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jan Doggen" <j.doggen@.BLOCKqsa.nl> wrote in message news:%2386SdCEuEHA.2072@.tk2msftngp13.phx.gbl...
> Back it up
> remove it
> recreate it, smaller
> restore it
> Jan
> "Ron B." <RonB@.discussions.microsoft.com> wrote in message
> news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
> I
> the
> can
> advance
>
|||One thing to try is to rebuild your indexes. Read about DBCC DBREINDEX. And then try a shrink again.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but I
> keep getting a msg saying that the drive is almost full. I cannot resize the
> partition, and shrinking the db does not change the allocation size. How can
> I reduce the allocation size and free up some disk space? Thanks in advance
> for any suggestions.
|||You can try to use DTS copy the whole database to
another. detach two DBs and reattach the new DB with old
DB name.
Good luck
Mike
>--Original Message--
>One thing to try is to rebuild your indexes. Read about
DBCC DBREINDEX. And then try a shrink again.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Ron B." <RonB@.discussions.microsoft.com> wrote in
message
>news:636A452B-109A-439C-8DA4-
B1401C97E44C@.microsoft.com...[vbcol=seagreen]
2.9 GB used. This[vbcol=seagreen]
nightly backup files, but I[vbcol=seagreen]
full. I cannot resize the[vbcol=seagreen]
allocation size. How can[vbcol=seagreen]
space? Thanks in advance
>
>.
>
|||In message <636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com>, Ron B.
<RonB@.discussions.microsoft.com> writes
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
>leaves just enough room on the partition for the nightly backup files, but I
>keep getting a msg saying that the drive is almost full. I cannot resize the
>partition, and shrinking the db does not change the allocation size. How can
>I reduce the allocation size and free up some disk space? Thanks in advance
>for any suggestions.
Using Enterprise Manager and Query Analyser:
1) EM - Backup the database and log file in question.
2) QA - Use DBCC DBREINDEX on each table in your database.
(May be better using a cursor in sproc - saves typing).
3) EM - Backup the database and log file again.
4) EM - Use tools 'Truncate Transaction Log' if log file too big.
5) EM - Use tools 'Shrink Database'.
Obviously, the backup at step 1 should be kept for good measure (just in
case) however the backup at step 3 can be deleted.
In SQL Server 7, the Shrink Database does not work very well, if at all,
until a backup has been performed. The re-indexing is required to reduce
fragmentation before a shrink is tried. I have found from time to time
that the above process needs to be run twice to get the actual physical
files to shrink any, specially if it has not been done for a very long
time.
Kind Regards,
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com

Resize DB in SQL 7

I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
leaves just enough room on the partition for the nightly backup files, but I
keep getting a msg saying that the drive is almost full. I cannot resize th
e
partition, and shrinking the db does not change the allocation size. How ca
n
I reduce the allocation size and free up some disk space? Thanks in advance
for any suggestions.Back it up
remove it
recreate it, smaller
restore it
Jan
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
> I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but
I
> keep getting a msg saying that the drive is almost full. I cannot resize
the
> partition, and shrinking the db does not change the allocation size. How
can
> I reduce the allocation size and free up some disk space? Thanks in
advance
> for any suggestions.|||That will not do anything, as restore will create database files with same s
ize as when you took the
backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jan Doggen" <j.doggen@.BLOCKqsa.nl> wrote in message news:%2386SdCEuEHA.2072@.tk2msftngp13.ph
x.gbl...
> Back it up
> remove it
> recreate it, smaller
> restore it
> Jan
> "Ron B." <RonB@.discussions.microsoft.com> wrote in message
> news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
> I
> the
> can
> advance
>|||One thing to try is to rebuild your indexes. Read about DBCC DBREINDEX. And
then try a shrink again.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but
I
> keep getting a msg saying that the drive is almost full. I cannot resize
the
> partition, and shrinking the db does not change the allocation size. How
can
> I reduce the allocation size and free up some disk space? Thanks in advan
ce
> for any suggestions.|||You can try to use DTS copy the whole database to
another. detach two DBs and reattach the new DB with old
DB name.
Good luck
Mike
>--Original Message--
>One thing to try is to rebuild your indexes. Read about
DBCC DBREINDEX. And then try a shrink again.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Ron B." <RonB@.discussions.microsoft.com> wrote in
message
>news:636A452B-109A-439C-8DA4-
B1401C97E44C@.microsoft.com...
2.9 GB used. This[vbcol=seagreen]
nightly backup files, but I[vbcol=seagreen]
full. I cannot resize the[vbcol=seagreen]
allocation size. How can[vbcol=seagreen]
space? Thanks in advance[vbcol=seagreen]
>
>.
>|||In message <636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com>, Ron B.
<RonB@.discussions.microsoft.com> writes
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
>leaves just enough room on the partition for the nightly backup files, but
I
>keep getting a msg saying that the drive is almost full. I cannot resize t
he
>partition, and shrinking the db does not change the allocation size. How c
an
>I reduce the allocation size and free up some disk space? Thanks in advanc
e
>for any suggestions.
Using Enterprise Manager and Query Analyser:
1) EM - Backup the database and log file in question.
2) QA - Use DBCC DBREINDEX on each table in your database.
(May be better using a cursor in sproc - saves typing).
3) EM - Backup the database and log file again.
4) EM - Use tools 'Truncate Transaction Log' if log file too big.
5) EM - Use tools 'Shrink Database'.
Obviously, the backup at step 1 should be kept for good measure (just in
case) however the backup at step 3 can be deleted.
In SQL Server 7, the Shrink Database does not work very well, if at all,
until a backup has been performed. The re-indexing is required to reduce
fragmentation before a shrink is tried. I have found from time to time
that the above process needs to be run twice to get the actual physical
files to shrink any, specially if it has not been done for a very long
time.
Kind Regards,
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.comsql

Resize DB in SQL 7

I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
leaves just enough room on the partition for the nightly backup files, but I
keep getting a msg saying that the drive is almost full. I cannot resize the
partition, and shrinking the db does not change the allocation size. How can
I reduce the allocation size and free up some disk space? Thanks in advance
for any suggestions.Back it up
remove it
recreate it, smaller
restore it
Jan
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
> I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but
I
> keep getting a msg saying that the drive is almost full. I cannot resize
the
> partition, and shrinking the db does not change the allocation size. How
can
> I reduce the allocation size and free up some disk space? Thanks in
advance
> for any suggestions.|||That will not do anything, as restore will create database files with same size as when you took the
backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jan Doggen" <j.doggen@.BLOCKqsa.nl> wrote in message news:%2386SdCEuEHA.2072@.tk2msftngp13.phx.gbl...
> Back it up
> remove it
> recreate it, smaller
> restore it
> Jan
> "Ron B." <RonB@.discussions.microsoft.com> wrote in message
> news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
>> I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
>> leaves just enough room on the partition for the nightly backup files, but
> I
>> keep getting a msg saying that the drive is almost full. I cannot resize
> the
>> partition, and shrinking the db does not change the allocation size. How
> can
>> I reduce the allocation size and free up some disk space? Thanks in
> advance
>> for any suggestions.
>|||One thing to try is to rebuild your indexes. Read about DBCC DBREINDEX. And then try a shrink again.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but I
> keep getting a msg saying that the drive is almost full. I cannot resize the
> partition, and shrinking the db does not change the allocation size. How can
> I reduce the allocation size and free up some disk space? Thanks in advance
> for any suggestions.|||You can try to use DTS copy the whole database to
another. detach two DBs and reattach the new DB with old
DB name.
Good luck
Mike
>--Original Message--
>One thing to try is to rebuild your indexes. Read about
DBCC DBREINDEX. And then try a shrink again.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Ron B." <RonB@.discussions.microsoft.com> wrote in
message
>news:636A452B-109A-439C-8DA4-
B1401C97E44C@.microsoft.com...
>>I have a db with an allocated size of 24.3 GB, but only
2.9 GB used. This
>> leaves just enough room on the partition for the
nightly backup files, but I
>> keep getting a msg saying that the drive is almost
full. I cannot resize the
>> partition, and shrinking the db does not change the
allocation size. How can
>> I reduce the allocation size and free up some disk
space? Thanks in advance
>> for any suggestions.
>
>.
>|||In message <636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com>, Ron B.
<RonB@.discussions.microsoft.com> writes
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
>leaves just enough room on the partition for the nightly backup files, but I
>keep getting a msg saying that the drive is almost full. I cannot resize the
>partition, and shrinking the db does not change the allocation size. How can
>I reduce the allocation size and free up some disk space? Thanks in advance
>for any suggestions.
Using Enterprise Manager and Query Analyser:
1) EM - Backup the database and log file in question.
2) QA - Use DBCC DBREINDEX on each table in your database.
(May be better using a cursor in sproc - saves typing).
3) EM - Backup the database and log file again.
4) EM - Use tools 'Truncate Transaction Log' if log file too big.
5) EM - Use tools 'Shrink Database'.
Obviously, the backup at step 1 should be kept for good measure (just in
case) however the backup at step 3 can be deleted.
In SQL Server 7, the Shrink Database does not work very well, if at all,
until a backup has been performed. The re-indexing is required to reduce
fragmentation before a shrink is tried. I have found from time to time
that the above process needs to be run twice to get the actual physical
files to shrink any, specially if it has not been done for a very long
time.
Kind Regards,
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com

Reset Identity

Hi, there is a way to reset identity field of many tables via storeprocedure
?
i try with the scripts below, but they don't work !
BACKUP LOG test_dbWITH TRUNCATE_ONLY
DBCC shrinkdatabase (test_db)
and also with
create table #table(
idTabella int,
nome varchar(4000)
)
insert into #table
SELECT dbo.sysobjects.id, dbo.sysobjects.name
FROM dbo.sysobjects INNER JOIN
dbo.syscolumns ON dbo.sysobjects.id =
dbo.syscolumns.id INNER JOIN
dbo.systypes ON dbo.syscolumns.xtype =
dbo.systypes.xtype
WHERE (dbo.syscolumns.status = 128)
declare @.NomeTabella as varchar(4000)
declare @.TabellaID int
select @.TabellaID =idTabella,@.NomeTabella =nome from #table
while exists(select idTabella from #table)
begin
DBCC CHECKIDENT(@.NomeTabella, RESEED)
delete from #table where idTabella = @.TabellaID
select @.TabellaID =idTabella,@.NomeTabella =nome from #table
end"Alessandro" schrieb:
> Hi, there is a way to reset identity field of many tables via storeprocedu
re
> ?
> i try with the scripts below, but they don't work !
> BACKUP LOG test_dbWITH TRUNCATE_ONLY
> DBCC shrinkdatabase (test_db)
> and also with
> create table #table(
> idTabella int,
> nome varchar(4000)
> )
> insert into #table
> SELECT dbo.sysobjects.id, dbo.sysobjects.name
> FROM dbo.sysobjects INNER JOIN
> dbo.syscolumns ON dbo.sysobjects.id =
> dbo.syscolumns.id INNER JOIN
> dbo.systypes ON dbo.syscolumns.xtype =
> dbo.systypes.xtype
> WHERE (dbo.syscolumns.status = 128)
> declare @.NomeTabella as varchar(4000)
> declare @.TabellaID int
> select @.TabellaID =idTabella,@.NomeTabella =nome from #table
> while exists(select idTabella from #table)
> begin
> DBCC CHECKIDENT(@.NomeTabella, RESEED)
> delete from #table where idTabella = @.TabellaID
> select @.TabellaID =idTabella,@.NomeTabella =nome from #table
> end
The follwing procedure reseeds all ID-cols in the db. Tables without an
ID-col return an error that you can ignore ...
declare @.table varchar(256)
declare cu cursor for select [name] from sysobjects where xtype = 'U'
open cu
fetch next from cu into @.table
while @.@.fetch_status = 0
begin
dbcc checkident (@.table, RESEED)
fetch next from cu into @.table
end
close cu deallocate cu

Requsting advice on nightly backup

This is our current schedule for SQL backup:
Backups created by Enterprise Manager
Nightly backup 12:00midnight - Complete backup checked, Transaction Log Backup not checked
Weekly optimization on Saturday - Optimization and Ingetrigy checked.
Transaction Logs Every hour 8am to 9pm - Transaction Log Backup checked.
Is it overkill to include the Transaction Log backup on our Nightly backup ?
Thanks
--
Message posted via http://www.sqlmonster.comHi Jeffrey,
If you are ending transaction log backup at 9 PM and want to take
transaction log backup again at midnight, it will have 3 hrs of transactions.
But you are anyways taking full back up at midnight. I do not understand why
you want to take nightly transaction log backup.
If my understanding is wrong, please update me with more information.
--
Thanks
GYK|||Hi GYK,
I don't want to do transaction logs again. Sorry a bit dizzy doing this backup plan. You confirmed what I was second guessing that if I have 3 hours of transactions between 9pm and 12 (which is rare) they would be part of the complete backup at midnight with no need for the TRN backup.
Thanks
Jeff
--
Message posted via http://www.sqlmonster.com|||Keep in mind that the FULL and DIFFERNTIAL database backups are independent
from the LOG transaction log backups.
The transaction log will not remove transactions from the files until after
a transaction log backup is performed if you are running any recovery mode
except simple. A FULL nor a DIFFERENTIAL database backup does NOT remove
transactions from the transaction log regardless of the recovery model.
Sincerely,
Anthony Thomas
"Jeffrey Sheldon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:418f0f5db2de404da1b2043e344372d5@.SQLMonster.com...
Hi GYK,
I don't want to do transaction logs again. Sorry a bit dizzy doing this
backup plan. You confirmed what I was second guessing that if I have 3
hours of transactions between 9pm and 12 (which is rare) they would be part
of the complete backup at midnight with no need for the TRN backup.
Thanks
Jeff
--
Message posted via http://www.sqlmonster.com

Requsting advice on nightly backup

This is our current schedule for SQL backup:
Backups created by Enterprise Manager
Nightly backup 12:00midnight - Complete backup checked, Transaction Log Backup not checked
Weekly optimization on Saturday - Optimization and Ingetrigy checked.
Transaction Logs Every hour 8am to 9pm - Transaction Log Backup checked.
Is it overkill to include the Transaction Log backup on our Nightly backup ?
Thanks
Message posted via http://www.sqlmonster.com
Hi Jeffrey,
If you are ending transaction log backup at 9 PM and want to take
transaction log backup again at midnight, it will have 3 hrs of transactions.
But you are anyways taking full back up at midnight. I do not understand why
you want to take nightly transaction log backup.
If my understanding is wrong, please update me with more information.
Thanks
GYK
|||Hi GYK,
I don't want to do transaction logs again. Sorry a bit dizzy doing this backup plan. You confirmed what I was second guessing that if I have 3 hours of transactions between 9pm and 12 (which is rare) they would be part of the complete backup at midnight
with no need for the TRN backup.
Thanks
Jeff
Message posted via http://www.sqlmonster.com
|||Keep in mind that the FULL and DIFFERNTIAL database backups are independent
from the LOG transaction log backups.
The transaction log will not remove transactions from the files until after
a transaction log backup is performed if you are running any recovery mode
except simple. A FULL nor a DIFFERENTIAL database backup does NOT remove
transactions from the transaction log regardless of the recovery model.
Sincerely,
Anthony Thomas

"Jeffrey Sheldon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:418f0f5db2de404da1b2043e344372d5@.SQLMonster.c om...
Hi GYK,
I don't want to do transaction logs again. Sorry a bit dizzy doing this
backup plan. You confirmed what I was second guessing that if I have 3
hours of transactions between 9pm and 12 (which is rare) they would be part
of the complete backup at midnight with no need for the TRN backup.
Thanks
Jeff
Message posted via http://www.sqlmonster.com
|||Don't think of tr log backups as being connected to the full / diff
backups.
One of the good things about tr log backups is that if you have the full
sequence you can retore even if you have a corrupt full backup by going
back to the previous full backup.
Are you testing backups? Until you restore a backup you don't know
whether it is good or not. On important databases I do test restores on
every full backup. You also might consider log shipping the tr log
backups and applying them to the restored full.
I don't like doing integrity checks on production databases but do them
on the restored backup.
Have a look at
http://www.mindsdoor.net/#Administration
http://www.mindsdoor.net/SQLAdmin/Ba...Databases.html
http://www.mindsdoor.net/SQLAdmin/s_TestRestore.html
http://www.mindsdoor.net/SQLAdmin/s_nrSyncDir.html
There's stuff there to backup databases, copy backups and do test
restores and to copy the log backups - I'll at the sp to restores to
complete the log shipping sometime.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Requsting advice on nightly backup

This is our current schedule for SQL backup:
Backups created by Enterprise Manager
Nightly backup 12:00midnight - Complete backup checked, Transaction Log Back
up not checked
Weekly optimization on Saturday - Optimization and Ingetrigy checked.
Transaction Logs Every hour 8am to 9pm - Transaction Log Backup checked.
Is it overkill to include the Transaction Log backup on our Nightly backup ?
Thanks
Message posted via http://www.droptable.comHi Jeffrey,
If you are ending transaction log backup at 9 PM and want to take
transaction log backup again at midnight, it will have 3 hrs of transactions
.
But you are anyways taking full back up at midnight. I do not understand why
you want to take nightly transaction log backup.
If my understanding is wrong, please update me with more information.
Thanks
GYK|||Hi GYK,
I don't want to do transaction logs again. Sorry a bit dizzy doing this bac
kup plan. You confirmed what I was second guessing that if I have 3 hours o
f transactions between 9pm and 12 (which is rare) they would be part of the
complete backup at midnight
with no need for the TRN backup.
Thanks
Jeff
Message posted via http://www.droptable.com|||Keep in mind that the FULL and DIFFERNTIAL database backups are independent
from the LOG transaction log backups.
The transaction log will not remove transactions from the files until after
a transaction log backup is performed if you are running any recovery mode
except simple. A FULL nor a DIFFERENTIAL database backup does NOT remove
transactions from the transaction log regardless of the recovery model.
Sincerely,
Anthony Thomas
"Jeffrey Sheldon via droptable.com" <forum@.droptable.com> wrote in message
news:418f0f5db2de404da1b2043e344372d5@.SQ
droptable.com...
Hi GYK,
I don't want to do transaction logs again. Sorry a bit dizzy doing this
backup plan. You confirmed what I was second guessing that if I have 3
hours of transactions between 9pm and 12 (which is rare) they would be part
of the complete backup at midnight with no need for the TRN backup.
Thanks
Jeff
Message posted via http://www.droptable.com|||Don't think of tr log backups as being connected to the full / diff
backups.
One of the good things about tr log backups is that if you have the full
sequence you can retore even if you have a corrupt full backup by going
back to the previous full backup.
Are you testing backups? Until you restore a backup you don't know
whether it is good or not. On important databases I do test restores on
every full backup. You also might consider log shipping the tr log
backups and applying them to the restored full.
I don't like doing integrity checks on production databases but do them
on the restored backup.
Have a look at
http://www.mindsdoor.net/#Administration
http://www.mindsdoor.net/SQLAdmin/B...lDatabases.html
http://www.mindsdoor.net/SQLAdmin/s_TestRestore.html
http://www.mindsdoor.net/SQLAdmin/s_nrSyncDir.html
There's stuff there to backup databases, copy backups and do test
restores and to copy the log backups - I'll at the sp to restores to
complete the log shipping sometime.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

reposting: bkup and resore strategy. Please reply

Hi,
My vb.net app will use database which will be deployed using MSDE on client
machines. I will provide UI to client in my app to backup and restore the d
atabase. My app will work with one database, for e.g. MyAppDB. My question
is what are the best pract
ices for doing this:
a) Should I choose Full or Simple mode. There is no mission critical data.
But as my clients will be using MSDE, will setting up the recovery mode as
Full cause the database and log size limits to cross the 2GB limit soon. Is
it better if I choose SIMP
LE recovery mode. What do you suggest.
b) Should I back up the system databases also, if yes then what should be th
e order of backing these up (and then in what order should the databases be
restored)
c) Does the MSDE login information also needs to be backed up. At max I wil
l have just one custom login and sa. I am using SQL Authentication.
d) Will I need to close my application and make sure that my apps' MSDE inst
ance is not running before backing up the databases.
d) I want to provide user the option to choose the backup location. How wil
l I figure out if they chose something like tape drive or zip drive. How sh
ould I handle this scenario.
e) Also how can I find out if there is sufficient space on the hard drive to
backup the database. Are there any standard sql functions to check that.
f) Should I do database log file backup too while backing up the database.
sorry for so many questions.
thanks
newbie
Print | Copy URL of this post
Expand All Collapse AllSee answers inline:
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:3DFC5E47-73A3-4CFC-9161-0AF13E789AD5@.microsoft.com...
> Hi,
> My vb.net app will use database which will be deployed using MSDE on
client machines. I will provide UI to client in my app to backup and
restore the database. My app will work with one database, for e.g. MyAppDB.
My question is what are the best practices for doing this:
> a) Should I choose Full or Simple mode. There is no mission critical
data. But as my clients will be using MSDE, will setting up the recovery
mode as Full cause the database and log size limits to cross the 2GB limit
soon. Is it better if I choose SIMPLE recovery mode. What do you suggest.
I almost always use either full or bulk-logged, although there are a few
instances where simple is appropriate. Basically you need to determine in
the event of failure do you want to be able to recover to the point of
failure, or is it good engoug to recover from the last full backup? As for
size limits, you can control the size of the log by backing it up regularly.
Besides, I am pretty sure that the 2Gb size limit is on the data file, not
the log.

> b) Should I back up the system databases also, if yes then what should be
the order of backing these up (and then in what order should the databases
be restored)
Yes, at least master and msdb, but the order of backing them up is not
important. Master would be the first to be restored

> c) Does the MSDE login information also needs to be backed up. At max I
will have just one custom login and sa. I am using SQL Authentication.
The logins are stored in master.

> d) Will I need to close my application and make sure that my apps' MSDE
instance is not running before backing up the databases.
No, backups are an online operation.

> d) I want to provide user the option to choose the backup location. How
will I figure out if they chose something like tape drive or zip drive. How
should I handle this scenario.
That's an application issue, you stated that you were providing them a front
end UI to backup the databases. The destination of the backup files is just
one of the BACKUP command arguments. There are also several arguments that
apply only to tape backup operations that you might want to take into
consideration.

> e) Also how can I find out if there is sufficient space on the hard drive
to backup the database. Are there any standard sql functions to check that.
There isn't a function that will estimate the size of the backup, but you
can read the msdb..backupset table to get the size of the last backup (or an
average of the last several) and estimate the size based on that.

> f) Should I do database log file backup too while backing up the database.
Not at the same time, although it won't hurt anything, there's just no
reason to do them at the same time.

> sorry for so many questions.
> thanks
> newbie
>
> Print | Copy URL of this post
>
> Expand All Collapse All
>|||Thanks don. Here are few more concerns that I have:
1> Is there any way to zip the .bak files into just 1 file ising T-sql or wi
ll I have to resort to 3rd party solutions for compression (as .Net doesn't
have any compression lib)
2> What happens if one user chooses to backup while others are still working
on the same database (imagine a small office setup with few computers and m
y app on all of them and the db on one central machine). Will the backup wor
k fine in this situation or
will the users need to log off.
3> Also we are not aiming at schedule backups option right now. Our clients
will have to go to the UI screen and backup the database themselves when the
y want to. So in this situation should I restore system databases also when
the users choose to restore
my app's db from the backed up location. And do I need to backup the system
databases always when they choose to backup my apps db or just before any ma
jor important changes.
Thanks

reposting: bkup and resore strategy. Please reply

Hi,
My vb.net app will use database which will be deployed using MSDE on client machines. I will provide UI to client in my app to backup and restore the database. My app will work with one database, for e.g. MyAppDB. My question is what are the best pract
ices for doing this:
a) Should I choose Full or Simple mode. There is no mission critical data. But as my clients will be using MSDE, will setting up the recovery mode as Full cause the database and log size limits to cross the 2GB limit soon. Is it better if I choose SIMP
LE recovery mode. What do you suggest.
b) Should I back up the system databases also, if yes then what should be the order of backing these up (and then in what order should the databases be restored)
c) Does the MSDE login information also needs to be backed up. At max I will have just one custom login and sa. I am using SQL Authentication.
d) Will I need to close my application and make sure that my apps' MSDE instance is not running before backing up the databases.
d) I want to provide user the option to choose the backup location. How will I figure out if they chose something like tape drive or zip drive. How should I handle this scenario.
e) Also how can I find out if there is sufficient space on the hard drive to backup the database. Are there any standard sql functions to check that.
f) Should I do database log file backup too while backing up the database.
sorry for so many questions.
thanks
newbie
Print | Copy URL of this post
Expand All Collapse All
See answers inline:
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:3DFC5E47-73A3-4CFC-9161-0AF13E789AD5@.microsoft.com...
> Hi,
> My vb.net app will use database which will be deployed using MSDE on
client machines. I will provide UI to client in my app to backup and
restore the database. My app will work with one database, for e.g. MyAppDB.
My question is what are the best practices for doing this:
> a) Should I choose Full or Simple mode. There is no mission critical
data. But as my clients will be using MSDE, will setting up the recovery
mode as Full cause the database and log size limits to cross the 2GB limit
soon. Is it better if I choose SIMPLE recovery mode. What do you suggest.
I almost always use either full or bulk-logged, although there are a few
instances where simple is appropriate. Basically you need to determine in
the event of failure do you want to be able to recover to the point of
failure, or is it good engoug to recover from the last full backup? As for
size limits, you can control the size of the log by backing it up regularly.
Besides, I am pretty sure that the 2Gb size limit is on the data file, not
the log.

> b) Should I back up the system databases also, if yes then what should be
the order of backing these up (and then in what order should the databases
be restored)
Yes, at least master and msdb, but the order of backing them up is not
important. Master would be the first to be restored

> c) Does the MSDE login information also needs to be backed up. At max I
will have just one custom login and sa. I am using SQL Authentication.
The logins are stored in master.

> d) Will I need to close my application and make sure that my apps' MSDE
instance is not running before backing up the databases.
No, backups are an online operation.

> d) I want to provide user the option to choose the backup location. How
will I figure out if they chose something like tape drive or zip drive. How
should I handle this scenario.
That's an application issue, you stated that you were providing them a front
end UI to backup the databases. The destination of the backup files is just
one of the BACKUP command arguments. There are also several arguments that
apply only to tape backup operations that you might want to take into
consideration.

> e) Also how can I find out if there is sufficient space on the hard drive
to backup the database. Are there any standard sql functions to check that.
There isn't a function that will estimate the size of the backup, but you
can read the msdb..backupset table to get the size of the last backup (or an
average of the last several) and estimate the size based on that.

> f) Should I do database log file backup too while backing up the database.
Not at the same time, although it won't hurt anything, there's just no
reason to do them at the same time.

> sorry for so many questions.
> thanks
> newbie
>
> Print | Copy URL of this post
>
> Expand All Collapse All
>
|||Thanks don. Here are few more concerns that I have:
1> Is there any way to zip the .bak files into just 1 file ising T-sql or will I have to resort to 3rd party solutions for compression (as .Net doesn't have any compression lib)
2> What happens if one user chooses to backup while others are still working on the same database (imagine a small office setup with few computers and my app on all of them and the db on one central machine). Will the backup work fine in this situation or
will the users need to log off.
3> Also we are not aiming at schedule backups option right now. Our clients will have to go to the UI screen and backup the database themselves when they want to. So in this situation should I restore system databases also when the users choose to restore
my app's db from the backed up location. And do I need to backup the system databases always when they choose to backup my apps db or just before any major important changes.
Thanks