Beginner's query.
please explain the OFA (open file agent) or lock rule when databases are being backed up.
I know files cannot be backed up if open, but what about database tables-not metadata, but data.
and what about the images accessed by a database-the reports or the docuemnts-they are backed up separately?
where can i find some basic rules for DB's...Short of DB's for dummies.Databases can be backed up while on-line.
Don't try to copy the .mdf/.ldf files - they won't be restorable probably - see backup database in bol.|||What concequences (if any) does open file agent or perhaps locks in this case have when backups are in operation and records are being updated?
For example on NT a file will not be backed up if open. I know the mdf and ldf files (or is it trn also) take logs, and snapshots for transactions, so that db's can be restored to a past point in time (rollback?). i know that bak files can be copied and used to create a database (restore maybe), but what about in db's?
Or is it simply that at that moment a backup is being written to file, and if the transaction is not fully committed prior to or at that time, it is not backed up, but will be included in the next back up...|||mdf file is the database file
ldf is the log file
bak is the database backup
trn is the transaction log file backup
It is not advisable to restore the db based on the mdf and ldf files - as they may be open at the time of backup. The only way to be sure you can restore is to use the bak and trn files. These will also take care of database locking and incomplete transactions at the time of the backup.|||Any pages updated while the backup is taking place are marked and written again to the end of the backup file.
Enough of the transaction log is backed up to allow a restore. Uncommitted transactions are rolled back at the restore.
The backup will slow down all processes on the server but will not stop any activity on the database.|||thanks all
appreciate your time :)
have just been on a sql 2000 admin course so it all sounds alot smipler now!
cheers
Showing posts with label explain. Show all posts
Showing posts with label explain. Show all posts
Wednesday, March 7, 2012
backups and restore DB in SQL EXPRESS 2005
can anyone explain me how can i make a DB backup in sql express 2005 ?
The sql express instance and my web aplication are running on the same server.
I need a script for directly execute it (and if possible also to restore the DB ) from my web aplication.
The sql express instance and my web aplication are running on the same server.
I need a script for directly execute it (and if possible also to restore the DB ) from my web aplication.
other question:
any idea how can i tell to sql express to do the backup automatically every day?
To backup SQL Server you either use a stored proc to do it or you use one of the best part of SQL Server called the backup and restore wizard to backup the database. To backup daily you need to schedule the backup with SQL Server Agent Job. To do all of the above you need the new Enterprise Manager called Management Studio but Express did not come with it so donwload the eval version from the link below it is good for 180 days install it as a named instance. Then right click at the top of the Management Studio and register the Express so you can use the Management Studio to do all of the above. Hope this helps.
http://www.microsoft.com/downloads/details.aspx?familyid=6931FA7F-C094-49A2-A050-2D07993566EC&displaylang=en
Sunday, February 19, 2012
Backup/restore rids free space?
Can someone explain this? I'm trying to do a backup and
restore to get rid of the 20GB of free space.
In EM, Databases/Space Allocated..
Used Free Total
10GB 20GB 30GB
If I backup the DB to tape that has a 15GB capacity, it
backs up just fine, i.e., the free space isn't being
backed up or else it wouldn't fit on the tape.
But, if I restore the DB from tape, it restores 30GB.
Not sure I understand how the free space is being treated
by the backup/restore.
Thanks,
Don
SQL 7.0/NT 4.0
Hi,
I think Transaction log file will be grown high.
How to check the size:-
DBCC SQLPERF(LOGSPACE)
If the transaction file size is huge then use the below commands to backup
the transaction log and shrink the file:-
Backup log <dbname> to disk='c:\backup\dbname.trn'
go
DBCC SHRINKFILE('LDF_FILE_NAME',truncateonly)
Thanks
Hari
MCDBA
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
|||Don,
Backup makes some optimization decisions on what it writes to backup. It
does not need to write all the empty space, eating up tape and time, but it
(as I understand it) is recording enough information to rebuild the
freespace. Think of it as an easy compression algorithm.
However, backup / restore will not change the definition of the database and
the backup knows that it is restoring 30 GB.
Hari gives you guidance on shrinking the log file. The data file can also
be shrunk using DBCC SHRINKFILE if it has become bloated.
Russell Fields
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
|||You might want to look here
(http://www.karaszi.com/SQLServer/info_dont_shrink.asp) first for some
caveats when shrinking data and log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
restore to get rid of the 20GB of free space.
In EM, Databases/Space Allocated..
Used Free Total
10GB 20GB 30GB
If I backup the DB to tape that has a 15GB capacity, it
backs up just fine, i.e., the free space isn't being
backed up or else it wouldn't fit on the tape.
But, if I restore the DB from tape, it restores 30GB.
Not sure I understand how the free space is being treated
by the backup/restore.
Thanks,
Don
SQL 7.0/NT 4.0
Hi,
I think Transaction log file will be grown high.
How to check the size:-
DBCC SQLPERF(LOGSPACE)
If the transaction file size is huge then use the below commands to backup
the transaction log and shrink the file:-
Backup log <dbname> to disk='c:\backup\dbname.trn'
go
DBCC SHRINKFILE('LDF_FILE_NAME',truncateonly)
Thanks
Hari
MCDBA
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
|||Don,
Backup makes some optimization decisions on what it writes to backup. It
does not need to write all the empty space, eating up tape and time, but it
(as I understand it) is recording enough information to rebuild the
freespace. Think of it as an easy compression algorithm.
However, backup / restore will not change the definition of the database and
the backup knows that it is restoring 30 GB.
Hari gives you guidance on shrinking the log file. The data file can also
be shrunk using DBCC SHRINKFILE if it has become bloated.
Russell Fields
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
|||You might want to look here
(http://www.karaszi.com/SQLServer/info_dont_shrink.asp) first for some
caveats when shrinking data and log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
Backup/restore rids free space?
Can someone explain this? I'm trying to do a backup and
restore to get rid of the 20GB of free space.
In EM, Databases/Space Allocated..
Used Free Total
10GB 20GB 30GB
If I backup the DB to tape that has a 15GB capacity, it
backs up just fine, i.e., the free space isn't being
backed up or else it wouldn't fit on the tape.
But, if I restore the DB from tape, it restores 30GB.
Not sure I understand how the free space is being treated
by the backup/restore.
Thanks,
Don
SQL 7.0/NT 4.0Hi,
I think Transaction log file will be grown high.
How to check the size:-
DBCC SQLPERF(LOGSPACE)
If the transaction file size is huge then use the below commands to backup
the transaction log and shrink the file:-
Backup log <dbname> to disk='c:\backup\dbname.trn'
go
DBCC SHRINKFILE('LDF_FILE_NAME',truncateonly)
Thanks
Hari
MCDBA
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx
.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||Don,
Backup makes some optimization decisions on what it writes to backup. It
does not need to write all the empty space, eating up tape and time, but it
(as I understand it) is recording enough information to rebuild the
freespace. Think of it as an easy compression algorithm.
However, backup / restore will not change the definition of the database and
the backup knows that it is restoring 30 GB.
Hari gives you guidance on shrinking the log file. The data file can also
be shrunk using DBCC SHRINKFILE if it has become bloated.
Russell Fields
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx
.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||You might want to look here
(http://www.karaszi.com/SQLServer/info_dont_shrink.asp) first for some
caveats when shrinking data and log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx
.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
restore to get rid of the 20GB of free space.
In EM, Databases/Space Allocated..
Used Free Total
10GB 20GB 30GB
If I backup the DB to tape that has a 15GB capacity, it
backs up just fine, i.e., the free space isn't being
backed up or else it wouldn't fit on the tape.
But, if I restore the DB from tape, it restores 30GB.
Not sure I understand how the free space is being treated
by the backup/restore.
Thanks,
Don
SQL 7.0/NT 4.0Hi,
I think Transaction log file will be grown high.
How to check the size:-
DBCC SQLPERF(LOGSPACE)
If the transaction file size is huge then use the below commands to backup
the transaction log and shrink the file:-
Backup log <dbname> to disk='c:\backup\dbname.trn'
go
DBCC SHRINKFILE('LDF_FILE_NAME',truncateonly)
Thanks
Hari
MCDBA
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx
.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||Don,
Backup makes some optimization decisions on what it writes to backup. It
does not need to write all the empty space, eating up tape and time, but it
(as I understand it) is recording enough information to rebuild the
freespace. Think of it as an easy compression algorithm.
However, backup / restore will not change the definition of the database and
the backup knows that it is restoring 30 GB.
Hari gives you guidance on shrinking the log file. The data file can also
be shrunk using DBCC SHRINKFILE if it has become bloated.
Russell Fields
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx
.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||You might want to look here
(http://www.karaszi.com/SQLServer/info_dont_shrink.asp) first for some
caveats when shrinking data and log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx
.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
Backup/restore rids free space?
Can someone explain this? I'm trying to do a backup and
restore to get rid of the 20GB of free space.
In EM, Databases/Space Allocated..
Used Free Total
10GB 20GB 30GB
If I backup the DB to tape that has a 15GB capacity, it
backs up just fine, i.e., the free space isn't being
backed up or else it wouldn't fit on the tape.
But, if I restore the DB from tape, it restores 30GB.
Not sure I understand how the free space is being treated
by the backup/restore.
Thanks,
Don
SQL 7.0/NT 4.0Hi,
I think Transaction log file will be grown high.
How to check the size:-
DBCC SQLPERF(LOGSPACE)
If the transaction file size is huge then use the below commands to backup
the transaction log and shrink the file:-
Backup log <dbname> to disk='c:\backup\dbname.trn'
go
DBCC SHRINKFILE('LDF_FILE_NAME',truncateonly)
Thanks
Hari
MCDBA
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||Don,
Backup makes some optimization decisions on what it writes to backup. It
does not need to write all the empty space, eating up tape and time, but it
(as I understand it) is recording enough information to rebuild the
freespace. Think of it as an easy compression algorithm.
However, backup / restore will not change the definition of the database and
the backup knows that it is restoring 30 GB.
Hari gives you guidance on shrinking the log file. The data file can also
be shrunk using DBCC SHRINKFILE if it has become bloated.
Russell Fields
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||You might want to look here
(http://www.karaszi.com/SQLServer/info_dont_shrink.asp) first for some
caveats when shrinking data and log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
restore to get rid of the 20GB of free space.
In EM, Databases/Space Allocated..
Used Free Total
10GB 20GB 30GB
If I backup the DB to tape that has a 15GB capacity, it
backs up just fine, i.e., the free space isn't being
backed up or else it wouldn't fit on the tape.
But, if I restore the DB from tape, it restores 30GB.
Not sure I understand how the free space is being treated
by the backup/restore.
Thanks,
Don
SQL 7.0/NT 4.0Hi,
I think Transaction log file will be grown high.
How to check the size:-
DBCC SQLPERF(LOGSPACE)
If the transaction file size is huge then use the below commands to backup
the transaction log and shrink the file:-
Backup log <dbname> to disk='c:\backup\dbname.trn'
go
DBCC SHRINKFILE('LDF_FILE_NAME',truncateonly)
Thanks
Hari
MCDBA
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||Don,
Backup makes some optimization decisions on what it writes to backup. It
does not need to write all the empty space, eating up tape and time, but it
(as I understand it) is recording enough information to rebuild the
freespace. Think of it as an easy compression algorithm.
However, backup / restore will not change the definition of the database and
the backup knows that it is restoring 30 GB.
Hari gives you guidance on shrinking the log file. The data file can also
be shrunk using DBCC SHRINKFILE if it has become bloated.
Russell Fields
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||You might want to look here
(http://www.karaszi.com/SQLServer/info_dont_shrink.asp) first for some
caveats when shrinking data and log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0
Subscribe to:
Posts (Atom)