Backing Up Sys Databases

I currently back up the Master and MSDB sys databases, why do some people say
to back up Model as well? I though this was a standard non enhanceable
database. What are the reprocussions of not backing this database up?
Also what is the best way to back up transaction logs? I am doing an hourly
back up of a production database that has a lot changes through out the day.
I do a full back up once each night. The tran log seems to grow bigger than
the data file when I choose the append option for the log. Can I create
seperate tran log back ups or should I choose overwrite instead of append?
Thanks for your help
-ChrisHi Chris
Model is not non-enhanceable. Model is used as the template whenever a new
database is created, so you can put users, stored procedures, permissions,
datatypes, etc, into model, as well as setting the database properties of
model with ALTER DATABASE, and every new database will inherit those objects
and settings.
The tempdb database is built from model every time your SQL Server starts,
so if you want objects, users, user defined datatypes or special permissions
in tempdb, put them in model.
And then, if you do put any of these objects in model, you can backup model
to make it faster to recreate it in case of system failure.
In the future, I would suggest two different messages for different
questions like you have here. It will make it much easier for everybody, you
and anyone trying to help, to keep track of the discussion.
Do you really mean that your tran log is bigger than the data file, or that
the tran log backup file is bigger than the database backup file? Or
something else?
For a heavily updated database and a day's worth of log backups, it could
happen. But without knowing the size of the db, the rate of changes, the
types of changes and the size of the rows changing, the recovery model of
the database, and probably a few other things, there is no way to tell if it
is TOO big.
To restore from backups to the point of a failure, you need ALL the log
backups since the last full backup. If you use the overwrite option when
backing up the log, you will only have the most recent log back, not the
whole set, and you will be unable to recover anything after the last full
backup. Please read about transaction log backups in the Books Online.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:01280324-7A0E-4EAD-B9A7-567FD9480FFE@.microsoft.com...
>I currently back up the Master and MSDB sys databases, why do some people
>say
> to back up Model as well? I though this was a standard non enhanceable
> database. What are the reprocussions of not backing this database up?
> Also what is the best way to back up transaction logs? I am doing an
> hourly
> back up of a production database that has a lot changes through out the
> day.
> I do a full back up once each night. The tran log seems to grow bigger
> than
> the data file when I choose the append option for the log. Can I create
> seperate tran log back ups or should I choose overwrite instead of append?
> Thanks for your help
> -Chris

Backing Up Sys Databases

I currently back up the Master and MSDB sys databases, why do some people sa
y
to back up Model as well? I though this was a standard non enhanceable
database. What are the reprocussions of not backing this database up?
Also what is the best way to back up transaction logs? I am doing an hourly
back up of a production database that has a lot changes through out the day.
I do a full back up once each night. The tran log seems to grow bigger than
the data file when I choose the append option for the log. Can I create
seperate tran log back ups or should I choose overwrite instead of append?
Thanks for your help
-ChrisHi Chris
Model is not non-enhanceable. Model is used as the template whenever a new
database is created, so you can put users, stored procedures, permissions,
datatypes, etc, into model, as well as setting the database properties of
model with ALTER DATABASE, and every new database will inherit those objects
and settings.
The tempdb database is built from model every time your SQL Server starts,
so if you want objects, users, user defined datatypes or special permissions
in tempdb, put them in model.
And then, if you do put any of these objects in model, you can backup model
to make it faster to recreate it in case of system failure.
In the future, I would suggest two different messages for different
questions like you have here. It will make it much easier for everybody, you
and anyone trying to help, to keep track of the discussion.
Do you really mean that your tran log is bigger than the data file, or that
the tran log backup file is bigger than the database backup file? Or
something else?
For a heavily updated database and a day's worth of log backups, it could
happen. But without knowing the size of the db, the rate of changes, the
types of changes and the size of the rows changing, the recovery model of
the database, and probably a few other things, there is no way to tell if it
is TOO big.
To restore from backups to the point of a failure, you need ALL the log
backups since the last full backup. If you use the overwrite option when
backing up the log, you will only have the most recent log back, not the
whole set, and you will be unable to recover anything after the last full
backup. Please read about transaction log backups in the Books Online.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:01280324-7A0E-4EAD-B9A7-567FD9480FFE@.microsoft.com...
>I currently back up the Master and MSDB sys databases, why do some people
>say
> to back up Model as well? I though this was a standard non enhanceable
> database. What are the reprocussions of not backing this database up?
> Also what is the best way to back up transaction logs? I am doing an
> hourly
> back up of a production database that has a lot changes through out the
> day.
> I do a full back up once each night. The tran log seems to grow bigger
> than
> the data file when I choose the append option for the log. Can I create
> seperate tran log back ups or should I choose overwrite instead of append?
> Thanks for your help
> -Chris

Backing up SSRS objects

I'm using sqlserver 2005 developer edition.
Unlike databases, there appears to be no way, in Management Studio, to back
up ReportSever.
How is it done?
--
Regards,
Gary BlakelyHi Gary,
Thank you for your posting!
You could backup the ReportServer database as you backup other databases.
In the databases, right-click ReportServer and then click Back up. Then you
could backup the Reporty Server Database.
Here is an article for your reference:
Back Up Database (General Page)
http://msdn2.microsoft.com/en-us/library/ms183383(d=ide).aspx
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Backing up SQL with Batch - and Backup Name Prompt

Hello. I update our SQL QA server about four times a day and back it up
before every update. There are three DBs that I have to backup. Each
backup is given a specific name accoring to the 'Update Name and Number".
This take a lot of time to go through Enterprise Manager every time.
For one backup session the the backup names would be:
DDMMYY_QA_UpdateNumber_DB1.bak
DDMMYY_QA_UpdateNumber_DB2.bak
DDMMYY_QA_UpdateNember_DB3.bak
I know how to backup the DBs with a dos batch, but I have to then change all
the names which is still a pain.
If I could have a batch file that I click on and it prompts me for:
DDMMYY_QA_UpdateNumber and I could plug that in say: 092404_QA_Update283 and
hit enter, and it would produce:
092404_QA_Update283_DB1.bak
092404_QA_Update283_DB2.bak
092404_QA_Update283_DB3.bak
My life would change!
Is this possible? How could it be done?
Thanks in advance for any help!!!
Hi,
No need to write a batch program, Go with a small procedure which generates
the backup file names based on MMDDYYHHMinSS
See the below site for script and details:-
http://www.microsoft.com/india/msdn/articles/190.aspx
Note:
Change the script slightly to backup to local drives...
Thanks
Hari
MCDBA
"Tom" <none@.none.com> wrote in message
news:ewQtjdmoEHA.1800@.TK2MSFTNGP15.phx.gbl...
> Hello. I update our SQL QA server about four times a day and back it up
> before every update. There are three DBs that I have to backup. Each
> backup is given a specific name accoring to the 'Update Name and Number".
> This take a lot of time to go through Enterprise Manager every time.
> For one backup session the the backup names would be:
> DDMMYY_QA_UpdateNumber_DB1.bak
> DDMMYY_QA_UpdateNumber_DB2.bak
> DDMMYY_QA_UpdateNember_DB3.bak
> I know how to backup the DBs with a dos batch, but I have to then change
> all
> the names which is still a pain.
> If I could have a batch file that I click on and it prompts me for:
> DDMMYY_QA_UpdateNumber and I could plug that in say: 092404_QA_Update283
> and
> hit enter, and it would produce:
> 092404_QA_Update283_DB1.bak
> 092404_QA_Update283_DB2.bak
> 092404_QA_Update283_DB3.bak
> My life would change!
> Is this possible? How could it be done?
> Thanks in advance for any help!!!
>

Backing up SQL Server 2005 Express

How can I back up existing SQL Server 2005 Express tables? I'm presently developing on a 4 year old laptop which is toooo sloooow and need to transfer the tables and data to a desktop system.

Copy the MyDatabase.mdf and MyDatabase.ldf files to the new system.

In the new system, use Attach... from SQL studio, to connect to the copied database.

backing up SQL server

Hi, I'm new to SQL server and hope someone can help me. I have SQL 7 and am
wanting to back it up remotely. I'm currently using a batch script that sh
uts down the service and then uses an xcopy command to copy the SQL director
y to a temporary location.
The script then uses a net start command to restart the services on a schedu
le. I then backup the temporary files that the xcopy command created. My q
uestion is, in the event of a System Crash, can I reinstall and configure SQ
L server and then restore
the temporary files and have them work? If not, is there another way to suc
cesfully back up SQL Server? Any help is very much appreciated. Thanks in
advance.Hi,
Please go thu the script will take the UNC path as the parameter and will
Backup Master, MSDB and all the User databases to the remote machine. This
script will create the unique Backup files names, this will ensure that old
backup sets were not overwritten.
Prerequisites
1.. SQL server and SQL Server Agent should be configured to start in
Domain Account
2.. This Domain account should have change privileges to add files to the
Remote machine
Script
CREATE PROCEDURE BACKUP_SP @.UNCPATH VARCHAR(200) AS
BEGIN
SET NOCOUNT ON
DECLARE @.NAME VARCHAR(100),
DECLARE @.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM master..Sysdatabases where name not in
('model','pubs','tempdb','northwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
NAME=@.UNCPATH+@.DBNAME+'_'+ltrim (rtrim (convert (char,
getdate(),105)))+'Dump.bak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD ,
NAME
= @.DBNAME, NOSKIP, STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
END
How to Execute
This procedure will take @.UNCPATH as the input parameter, Say you have to
backup the database to machine BACKUPSERVER in to share SQLBACKUP then the
execution will be
EXEC BACKUP_SP '\\BACKUPSERVER\SQLBACKUP'
This will backup all the databases to the SQLBACKUP folder in BACKUPSERVER.
Thanks
Hari
MCDBA
"Mike" <mhebner@.getnet.net> wrote in message
news:9CB58890-10EF-477E-84E1-C953D7EE6534@.microsoft.com...
> Hi, I'm new to SQL server and hope someone can help me. I have SQL 7 and
am wanting to back it up remotely. I'm currently using a batch script that
shuts down the service and then uses an xcopy command to copy the SQL
directory to a temporary location. The script then uses a net start command
to restart the services on a schedule. I then backup the temporary files
that the xcopy command created. My question is, in the event of a System
Crash, can I reinstall and configure SQL server and then restore the
temporary files and have them work? If not, is there another way to
succesfully back up SQL Server? Any help is very much appreciated. Thanks
in advance.|||"Mike" <mhebner@.getnet.net> wrote in message
news:9CB58890-10EF-477E-84E1-C953D7EE6534@.microsoft.com...
My question is, in the event of a System Crash, can I reinstall and
configure SQL server and then restore the temporary files and have them
work?
You should try out restoring the files to a different server and see if it
works. There is no substitute to this. It is always better if you have
carried out some test restores to confirm that your process will work, even
if somebody else says that it will, as it gives you some experience for when
you might have to do it for real.

Backing Up SQL DBs With Retrospecct

I have SBS2003 and am using Dantz Retrospect to back up the SQL databases to
a NAS box outside of our building. I have never been sucessful doing this
because Retrospect throws an error saying that it cant read the database due
to "an unknown Windows error". I have tried talking to the folks at Dantz
and they said that they have done all they can and that I should call the SQL
tech support.
Any ideas? TIA
Scott
I don't know what Retrospect is but it is most likely trying to just backup
the sql files directly. That is never a good way to back up a database.
You need to either use the native SQL Server BACKUP command to dump the
backup file to disk (or tape if directly attached) and then you can back
that file up with your backup software. There are some 3rd party backup
tools that have a Plug-In for Sql Server that does the equivalent of a
native backup but I doubt that Retrospect has that.
Andrew J. Kelly SQL MVP
"scottdog129" <scottdog129@.discussions.microsoft.com> wrote in message
news:5D4CDEFB-3390-4AC4-B554-BB3427CE3A21@.microsoft.com...
>I have SBS2003 and am using Dantz Retrospect to back up the SQL databases
>to
> a NAS box outside of our building. I have never been sucessful doing this
> because Retrospect throws an error saying that it cant read the database
> due
> to "an unknown Windows error". I have tried talking to the folks at Dantz
> and they said that they have done all they can and that I should call the
> SQL
> tech support.
> Any ideas? TIA
> Scott
|||Retrospect does (or is supposed to do) the same thing as the Backup Database
option in EM, however, it doesnt work and I have to manually do the backup
every day.
"Andrew J. Kelly" wrote:

> I don't know what Retrospect is but it is most likely trying to just backup
> the sql files directly. That is never a good way to back up a database.
> You need to either use the native SQL Server BACKUP command to dump the
> backup file to disk (or tape if directly attached) and then you can back
> that file up with your backup software. There are some 3rd party backup
> tools that have a Plug-In for Sql Server that does the equivalent of a
> native backup but I doubt that Retrospect has that.
> --
> Andrew J. Kelly SQL MVP
>
> "scottdog129" <scottdog129@.discussions.microsoft.com> wrote in message
> news:5D4CDEFB-3390-4AC4-B554-BB3427CE3A21@.microsoft.com...
>
>
|||Well it's hard to say what this is doing. If you can do a native backup
then it must be their code. It's pretty lame that they can't help you.
Andrew J. Kelly SQL MVP
"scottdog129" <scottdog129@.discussions.microsoft.com> wrote in message
news:0A5D2BBA-12DD-4819-B4E2-6737CC59C5F7@.microsoft.com...[vbcol=seagreen]
> Retrospect does (or is supposed to do) the same thing as the Backup
> Database
> option in EM, however, it doesnt work and I have to manually do the
> backup
> every day.
> "Andrew J. Kelly" wrote:
sql

Backing up one db table

Is it possible to only backup one table in a SQL database, or do you
have to back up everything?
TIA,
-BenBem
You can put the table on separate filegroup and then perfom backup of this
filegroup.
For more details please refer to the BOL.
"Ben" <bmilliron@.gmail.com> wrote in message
news:%23RG806QiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> Is it possible to only backup one table in a SQL database, or do you
> have to back up everything?
> TIA,
> -Ben|||No, backup is not possible for a single table. The lowest level possible is
a single data file. If you need a single table backup, you have to do it
with ther tools, not backup. You can use Data Transformation Services, a
T-SQL query, bcp.exe...
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Ben" <bmilliron@.gmail.com> wrote in message
news:%23RG806QiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> Is it possible to only backup one table in a SQL database, or do you
> have to back up everything?
> TIA,
> -Ben|||Just be aware that RESTORE might not be possible, they way the OP wants...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:OPrgM%23QiEHA.1276@.TK2MSFTNGP09.phx.gb
l...
> Bem
> You can put the table on separate filegroup and then perfom backup of this
> filegroup.
> For more details please refer to the BOL.
>
> "Ben" <bmilliron@.gmail.com> wrote in message
> news:%23RG806QiEHA.3928@.TK2MSFTNGP11.phx.gbl...
>sql

Backing up one db table

Is it possible to only backup one table in a SQL database, or do you
have to back up everything?
TIA,
-Ben
Bem
You can put the table on separate filegroup and then perfom backup of this
filegroup.
For more details please refer to the BOL.
"Ben" <bmilliron@.gmail.com> wrote in message
news:%23RG806QiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> Is it possible to only backup one table in a SQL database, or do you
> have to back up everything?
> TIA,
> -Ben
|||No, backup is not possible for a single table. The lowest level possible is
a single data file. If you need a single table backup, you have to do it
with ther tools, not backup. You can use Data Transformation Services, a
T-SQL query, bcp.exe...
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Ben" <bmilliron@.gmail.com> wrote in message
news:%23RG806QiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> Is it possible to only backup one table in a SQL database, or do you
> have to back up everything?
> TIA,
> -Ben
|||Just be aware that RESTORE might not be possible, they way the OP wants...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:OPrgM%23QiEHA.1276@.TK2MSFTNGP09.phx.gbl...
> Bem
> You can put the table on separate filegroup and then perfom backup of this
> filegroup.
> For more details please refer to the BOL.
>
> "Ben" <bmilliron@.gmail.com> wrote in message
> news:%23RG806QiEHA.3928@.TK2MSFTNGP11.phx.gbl...
>

Backing up one db table

Is it possible to only backup one table in a SQL database, or do you
have to back up everything?
TIA,
-BenBem
You can put the table on separate filegroup and then perfom backup of this
filegroup.
For more details please refer to the BOL.
"Ben" <bmilliron@.gmail.com> wrote in message
news:%23RG806QiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> Is it possible to only backup one table in a SQL database, or do you
> have to back up everything?
> TIA,
> -Ben|||No, backup is not possible for a single table. The lowest level possible is
a single data file. If you need a single table backup, you have to do it
with ther tools, not backup. You can use Data Transformation Services, a
T-SQL query, bcp.exe...
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Ben" <bmilliron@.gmail.com> wrote in message
news:%23RG806QiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> Is it possible to only backup one table in a SQL database, or do you
> have to back up everything?
> TIA,
> -Ben|||Just be aware that RESTORE might not be possible, they way the OP wants...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:OPrgM%23QiEHA.1276@.TK2MSFTNGP09.phx.gbl...
> Bem
> You can put the table on separate filegroup and then perfom backup of this
> filegroup.
> For more details please refer to the BOL.
>
> "Ben" <bmilliron@.gmail.com> wrote in message
> news:%23RG806QiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> > Is it possible to only backup one table in a SQL database, or do you
> > have to back up everything?
> >
> > TIA,
> >
> > -Ben
>

Backing Up of Snapshot History

Hi,

Is there a way to back up the snapshot history of a specific report? I'm thinking on an operations perspective where we need to back up snapshot histories and restore them in case something happens on a production environment. Also, I'm thinking of saving only for specific reports and not for all reports in the same environment/server.

Thanks.

There isn't a supported way. You can get innovative by archiving tables Snapshot, History, and ChunkData in the ReportServer database (see CopyExecutionSnapshot stored procedure) but a future service pack may break your implementation if the schema changes.

|||

Thanks Teo. But I can't seem to search any documentation on CopyExecutionSnapshot in SQL Server Books Online or MSDN. Would you be able to point me to the right place?

|||It is a stored procedure in the Report Catalog and no, you won't find the Report Server database documented. Use it at your own risk.

Backing up log files

Hi,
I'd like to know what happens to a log file when you back it up. I received
an error that my log file had grown too much & that I should back it up,
which I did & I no longer received the error.
But what happens when you simply back up a log file? Does it also truncate
unused space or what? It obviously doesn't simply make a backup of it.
Many thanks for helping me understand what is happening here
AntBACKUP LOG will indeed empty the log file (also known as "truncate" the file). If you for some
reason don't want to do transaction log backup then set the recovery model for the database to
"simple".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
> Hi,
> I'd like to know what happens to a log file when you back it up. I received
> an error that my log file had grown too much & that I should back it up,
> which I did & I no longer received the error.
> But what happens when you simply back up a log file? Does it also truncate
> unused space or what? It obviously doesn't simply make a backup of it.
> Many thanks for helping me understand what is happening here
> Ant|||Tibor
> BACKUP LOG will indeed empty the log file (also known as "truncate" the
> file).
I was thinking that by issuing BACKUP LOG , SQL Server is able to re-use
virtual logs file that LOG file is built by and delete only inactive
(written to disk) transaction
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
> BACKUP LOG will indeed empty the log file (also known as "truncate" the
> file). If you for some reason don't want to do transaction log backup then
> set the recovery model for the database to "simple".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I
>> received
>> an error that my log file had grown too much & that I should back it up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also
>> truncate
>> unused space or what? It obviously doesn't simply make a backup of it.
>> Many thanks for helping me understand what is happening here
>> Ant
>|||Hi Uri,
It all depends on what abstraction level we want to be. :-)
At a higher abstraction level, I think it suffices to say "empty the log" (or "truncate", whichever
in English gets the message through). I prefer to say "empty", since I believe it makes it clearer
that the file isn't made smaller.
There are of course much more details what happens when you BACKUP LOG. I stay away from those
details when I see a basic question and I suspect the OP has limited experience with transaction log
internals - like virtual log files. The reasoning is that more details will only cloud the message.
:-)
As for the details, this is how I believe it works. Anyone is of course free to add and correct,
below if off the top of my head:
A VLF can be in four states:
A. Never ever used
B. Used, and all log records are prior than the last checkpoint and have also been written to disk.
C. Used, but some log records are more recent than last checkpoint and/or not written to disk.
D. The active log (where the current log record, "head of the log", is).
BACKUP will not actually delete anything from the log file, it will just allow for re-use of the
virtual log file. Basically turning C above into B. A and B can be re-used (overwritten whenever the
head of the log moves to this virtual log file). These have status 0 in DBCC LOGINFO, while C and D
have status 2.
Now, above takes some thinking and some visualization in the head (or whiteboard). Which is why I
tend to stick with the higher abstraction level for basic questions. :-).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:ucYuxAlYIHA.3964@.TK2MSFTNGP03.phx.gbl...
> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the file).
> I was thinking that by issuing BACKUP LOG , SQL Server is able to re-use virtual logs file that
> LOG file is built by and delete only inactive (written to disk) transaction
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the file). If you for some
>> reason don't want to do transaction log backup then set the recovery model for the database to
>> "simple".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I received
>> an error that my log file had grown too much & that I should back it up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also truncate
>> unused space or what? It obviously doesn't simply make a backup of it.
>> Many thanks for helping me understand what is happening here
>> Ant
>>
>|||Tibor
The ability to think on abstaction level given from God, I mean you canot
learn this. :-)
And you I think have it.
> BACKUP will not actually delete anything from the log file, it will just
> allow for re-use of the virtual log file
Yep, that it was my understanding as well
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eAHQNOlYIHA.5160@.TK2MSFTNGP05.phx.gbl...
> Hi Uri,
> It all depends on what abstraction level we want to be. :-)
> At a higher abstraction level, I think it suffices to say "empty the log"
> (or "truncate", whichever in English gets the message through). I prefer
> to say "empty", since I believe it makes it clearer that the file isn't
> made smaller.
> There are of course much more details what happens when you BACKUP LOG. I
> stay away from those details when I see a basic question and I suspect the
> OP has limited experience with transaction log internals - like virtual
> log files. The reasoning is that more details will only cloud the message.
> :-)
> As for the details, this is how I believe it works. Anyone is of course
> free to add and correct, below if off the top of my head:
> A VLF can be in four states:
> A. Never ever used
> B. Used, and all log records are prior than the last checkpoint and have
> also been written to disk.
> C. Used, but some log records are more recent than last checkpoint and/or
> not written to disk.
> D. The active log (where the current log record, "head of the log", is).
> BACKUP will not actually delete anything from the log file, it will just
> allow for re-use of the virtual log file. Basically turning C above into
> B. A and B can be re-used (overwritten whenever the head of the log moves
> to this virtual log file). These have status 0 in DBCC LOGINFO, while C
> and D have status 2.
> Now, above takes some thinking and some visualization in the head (or
> whiteboard). Which is why I tend to stick with the higher abstraction
> level for basic questions. :-).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ucYuxAlYIHA.3964@.TK2MSFTNGP03.phx.gbl...
>> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the
>> file).
>> I was thinking that by issuing BACKUP LOG , SQL Server is able to re-use
>> virtual logs file that LOG file is built by and delete only inactive
>> (written to disk) transaction
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the
>> file). If you for some reason don't want to do transaction log backup
>> then set the recovery model for the database to "simple".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I
>> received
>> an error that my log file had grown too much & that I should back it
>> up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also
>> truncate
>> unused space or what? It obviously doesn't simply make a backup of it.
>> Many thanks for helping me understand what is happening here
>> Ant
>>
>>
>|||Hi Tibor
The two middle states have nothing to do with Checkpoint. The have to do
with whether those transactions have been backed up, so the log space can be
reused.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eAHQNOlYIHA.5160@.TK2MSFTNGP05.phx.gbl...
> Hi Uri,
> It all depends on what abstraction level we want to be. :-)
> At a higher abstraction level, I think it suffices to say "empty the log"
> (or "truncate", whichever in English gets the message through). I prefer
> to say "empty", since I believe it makes it clearer that the file isn't
> made smaller.
> There are of course much more details what happens when you BACKUP LOG. I
> stay away from those details when I see a basic question and I suspect the
> OP has limited experience with transaction log internals - like virtual
> log files. The reasoning is that more details will only cloud the message.
> :-)
> As for the details, this is how I believe it works. Anyone is of course
> free to add and correct, below if off the top of my head:
> A VLF can be in four states:
> A. Never ever used
> B. Used, and all log records are prior than the last checkpoint and have
> also been written to disk.
> C. Used, but some log records are more recent than last checkpoint and/or
> not written to disk.
> D. The active log (where the current log record, "head of the log", is).
> BACKUP will not actually delete anything from the log file, it will just
> allow for re-use of the virtual log file. Basically turning C above into
> B. A and B can be re-used (overwritten whenever the head of the log moves
> to this virtual log file). These have status 0 in DBCC LOGINFO, while C
> and D have status 2.
> Now, above takes some thinking and some visualization in the head (or
> whiteboard). Which is why I tend to stick with the higher abstraction
> level for basic questions. :-).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ucYuxAlYIHA.3964@.TK2MSFTNGP03.phx.gbl...
>> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the
>> file).
>> I was thinking that by issuing BACKUP LOG , SQL Server is able to re-use
>> virtual logs file that LOG file is built by and delete only inactive
>> (written to disk) transaction
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the
>> file). If you for some reason don't want to do transaction log backup
>> then set the recovery model for the database to "simple".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I
>> received
>> an error that my log file had grown too much & that I should back it
>> up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also
>> truncate
>> unused space or what? It obviously doesn't simply make a backup of it.
>> Many thanks for helping me understand what is happening here
>> Ant
>>
>>
>|||Hi Kalen,
Thanks. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23F5ejyrYIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Hi Tibor
> The two middle states have nothing to do with Checkpoint. The have to do with whether those
> transactions have been backed up, so the log space can be reused.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eAHQNOlYIHA.5160@.TK2MSFTNGP05.phx.gbl...
>> Hi Uri,
>> It all depends on what abstraction level we want to be. :-)
>> At a higher abstraction level, I think it suffices to say "empty the log" (or "truncate",
>> whichever in English gets the message through). I prefer to say "empty", since I believe it makes
>> it clearer that the file isn't made smaller.
>> There are of course much more details what happens when you BACKUP LOG. I stay away from those
>> details when I see a basic question and I suspect the OP has limited experience with transaction
>> log internals - like virtual log files. The reasoning is that more details will only cloud the
>> message. :-)
>> As for the details, this is how I believe it works. Anyone is of course free to add and correct,
>> below if off the top of my head:
>> A VLF can be in four states:
>> A. Never ever used
>> B. Used, and all log records are prior than the last checkpoint and have also been written to
>> disk.
>> C. Used, but some log records are more recent than last checkpoint and/or not written to disk.
>> D. The active log (where the current log record, "head of the log", is).
>> BACKUP will not actually delete anything from the log file, it will just allow for re-use of the
>> virtual log file. Basically turning C above into B. A and B can be re-used (overwritten whenever
>> the head of the log moves to this virtual log file). These have status 0 in DBCC LOGINFO, while C
>> and D have status 2.
>> Now, above takes some thinking and some visualization in the head (or whiteboard). Which is why I
>> tend to stick with the higher abstraction level for basic questions. :-).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:ucYuxAlYIHA.3964@.TK2MSFTNGP03.phx.gbl...
>> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the file).
>> I was thinking that by issuing BACKUP LOG , SQL Server is able to re-use virtual logs file that
>> LOG file is built by and delete only inactive (written to disk) transaction
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the file). If you for some
>> reason don't want to do transaction log backup then set the recovery model for the database to
>> "simple".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I received
>> an error that my log file had grown too much & that I should back it up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also truncate
>> unused space or what? It obviously doesn't simply make a backup of it.
>> Many thanks for helping me understand what is happening here
>> Ant
>>
>>
>>
>|||You're welcome.
:-)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eWx%23pC1YIHA.3880@.TK2MSFTNGP05.phx.gbl...
> Hi Kalen,
> Thanks. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23F5ejyrYIHA.1212@.TK2MSFTNGP05.phx.gbl...
>> Hi Tibor
>> The two middle states have nothing to do with Checkpoint. The have to do
>> with whether those transactions have been backed up, so the log space can
>> be reused.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:eAHQNOlYIHA.5160@.TK2MSFTNGP05.phx.gbl...
>> Hi Uri,
>> It all depends on what abstraction level we want to be. :-)
>> At a higher abstraction level, I think it suffices to say "empty the
>> log" (or "truncate", whichever in English gets the message through). I
>> prefer to say "empty", since I believe it makes it clearer that the file
>> isn't made smaller.
>> There are of course much more details what happens when you BACKUP LOG.
>> I stay away from those details when I see a basic question and I suspect
>> the OP has limited experience with transaction log internals - like
>> virtual log files. The reasoning is that more details will only cloud
>> the message. :-)
>> As for the details, this is how I believe it works. Anyone is of course
>> free to add and correct, below if off the top of my head:
>> A VLF can be in four states:
>> A. Never ever used
>> B. Used, and all log records are prior than the last checkpoint and have
>> also been written to disk.
>> C. Used, but some log records are more recent than last checkpoint
>> and/or not written to disk.
>> D. The active log (where the current log record, "head of the log", is).
>> BACKUP will not actually delete anything from the log file, it will just
>> allow for re-use of the virtual log file. Basically turning C above into
>> B. A and B can be re-used (overwritten whenever the head of the log
>> moves to this virtual log file). These have status 0 in DBCC LOGINFO,
>> while C and D have status 2.
>> Now, above takes some thinking and some visualization in the head (or
>> whiteboard). Which is why I tend to stick with the higher abstraction
>> level for basic questions. :-).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:ucYuxAlYIHA.3964@.TK2MSFTNGP03.phx.gbl...
>> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate"
>> the file).
>> I was thinking that by issuing BACKUP LOG , SQL Server is able to
>> re-use virtual logs file that LOG file is built by and delete only
>> inactive (written to disk) transaction
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> BACKUP LOG will indeed empty the log file (also known as "truncate"
>> the file). If you for some reason don't want to do transaction log
>> backup then set the recovery model for the database to "simple".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I
>> received
>> an error that my log file had grown too much & that I should back it
>> up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also
>> truncate
>> unused space or what? It obviously doesn't simply make a backup of
>> it.
>> Many thanks for helping me understand what is happening here
>> Ant
>>
>>
>>
>>
>

backing up everything to sql script

is there a way to back up the entire database, data, relations, tables, and
procedures to an sql script? and if so how do i do it? thanksBrian,
> is there a way to back up the entire database, data, relations,
> tables, and procedures to an sql script? and if so how do i do
> it? thanks
Yes, in Enterprise Manager. Right-click on the database, select All
Tasks | Generate SQL Script... from the menu.
Linda|||Brian,
Just one oversite - This does not get you the DATA.
You can back up the data using:
1) A full database backup - also gets you everything else
2) Detach database and file copy mdf, ndf, ldf files (or just stop SQL server rather than detach)
3) bcp
4) dts
5) Access import via odbc ( okay not so easy to get back, but still useful )
With 3) to 5) backup is on a table by table basis.
Regards
AJ
"lindawie" <lindawie@.my-deja.com> wrote in message news:OYUXTpEkDHA.976@.tk2msftngp13.phx.gbl...
> Brian,
> > is there a way to back up the entire database, data, relations,
> > tables, and procedures to an sql script? and if so how do i do
> > it? thanks
> Yes, in Enterprise Manager. Right-click on the database, select All
> Tasks | Generate SQL Script... from the menu.
> Linda
>

Backing Up DTS packages

I am looking for a way to back up local DTS packages. Are they stored in
msdb? Has anyone dealt with the backup issue for DTS packages?
Larry Menzin
American Techsystems Corp.
I save them out to disk as .dts file and store them in Visual Source Safe.
Greg Jackson
PDX, Oregon
|||Hi,
All the DTS packages will be stored in MSDB. So backing up the MSDB database
will be enough.
See the below link for more info:-
http://www.databasejournal.com/featu...le.php/1462601
Thanks
Hari
SQL Server MVP
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:75673569-040D-458F-B62D-1AB1EA76D285@.microsoft.com...
>I am looking for a way to back up local DTS packages. Are they stored in
> msdb? Has anyone dealt with the backup issue for DTS packages?
> --
> Larry Menzin
> American Techsystems Corp.
|||Yes, packages are in msdb. So just make sure you have backup of your msdb database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:75673569-040D-458F-B62D-1AB1EA76D285@.microsoft.com...
>I am looking for a way to back up local DTS packages. Are they stored in
> msdb? Has anyone dealt with the backup issue for DTS packages?
> --
> Larry Menzin
> American Techsystems Corp.
|||Also have a look at DTSBackup 2000
http://www.sqldts.com/default.aspx?242
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:75673569-040D-458F-B62D-1AB1EA76D285@.microsoft.com...
>I am looking for a way to back up local DTS packages. Are they stored in
> msdb? Has anyone dealt with the backup issue for DTS packages?
> --
> Larry Menzin
> American Techsystems Corp.

Backing Up DTS packages

I am looking for a way to back up local DTS packages. Are they stored in
msdb? Has anyone dealt with the backup issue for DTS packages?
Larry Menzin
American Techsystems Corp.I save them out to disk as .dts file and store them in Visual Source Safe.
Greg Jackson
PDX, Oregon|||Hi,
All the DTS packages will be stored in MSDB. So backing up the MSDB database
will be enough.
See the below link for more info:-
http://www.databasejournal.com/feat...cle.php/1462601
Thanks
Hari
SQL Server MVP
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:75673569-040D-458F-B62D-1AB1EA76D285@.microsoft.com...
>I am looking for a way to back up local DTS packages. Are they stored in
> msdb? Has anyone dealt with the backup issue for DTS packages?
> --
> Larry Menzin
> American Techsystems Corp.|||Yes, packages are in msdb. So just make sure you have backup of your msdb da
tabase.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:75673569-040D-458F-B62D-1AB1EA76D285@.microsoft.com...
>I am looking for a way to back up local DTS packages. Are they stored in
> msdb? Has anyone dealt with the backup issue for DTS packages?
> --
> Larry Menzin
> American Techsystems Corp.|||Also have a look at DTSBackup 2000
http://www.sqldts.com/default.aspx?242
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:75673569-040D-458F-B62D-1AB1EA76D285@.microsoft.com...
>I am looking for a way to back up local DTS packages. Are they stored in
> msdb? Has anyone dealt with the backup issue for DTS packages?
> --
> Larry Menzin
> American Techsystems Corp.

Backing Up DTS packages

I am looking for a way to back up local DTS packages. Are they stored in
msdb? Has anyone dealt with the backup issue for DTS packages?
--
Larry Menzin
American Techsystems Corp.I save them out to disk as .dts file and store them in Visual Source Safe.
Greg Jackson
PDX, Oregon|||Hi,
All the DTS packages will be stored in MSDB. So backing up the MSDB database
will be enough.
See the below link for more info:-
http://www.databasejournal.com/features/mssql/article.php/1462601
Thanks
Hari
SQL Server MVP
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:75673569-040D-458F-B62D-1AB1EA76D285@.microsoft.com...
>I am looking for a way to back up local DTS packages. Are they stored in
> msdb? Has anyone dealt with the backup issue for DTS packages?
> --
> Larry Menzin
> American Techsystems Corp.|||Yes, packages are in msdb. So just make sure you have backup of your msdb database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:75673569-040D-458F-B62D-1AB1EA76D285@.microsoft.com...
>I am looking for a way to back up local DTS packages. Are they stored in
> msdb? Has anyone dealt with the backup issue for DTS packages?
> --
> Larry Menzin
> American Techsystems Corp.|||Also have a look at DTSBackup 2000
http://www.sqldts.com/default.aspx?242
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:75673569-040D-458F-B62D-1AB1EA76D285@.microsoft.com...
>I am looking for a way to back up local DTS packages. Are they stored in
> msdb? Has anyone dealt with the backup issue for DTS packages?
> --
> Larry Menzin
> American Techsystems Corp.

Backing up design changes

Is there a way to back up the design changes in a SQL Server database
without backing up all the data? It's just test data at this point.The way I usually do it is to use the scripting feature in Enterprise Manager.
This will generate SQL scripts that can be run on a new database to rebuild
the database structure.

On 4 Jan 2005 11:38:05 -0800, "Jerry Porter" <jerryp@.personablepc.com> wrote:

>Is there a way to back up the design changes in a SQL Server database
>without backing up all the data? It's just test data at this point.|||There are some thrid party tools like RegGate Compare that will push
just the changes and keep the data intact...!|||Right click on the db --> all tasks --> generate SQL script. You can
script the whole db, you can do that before and after changes to keep
track of versions. You can create a job to do it to.

TGru

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Thanks Steve,

I had used that tool, but hadn't taken it all the way.
[Right-click on database, All Tasks, Generate SQL Script, Show All,
Script all objects]

I don't suppose there's a way to automate this?|||I think you mean SQL Compare at www.red-gate.com. Thanks for the tip.|||On 4 Jan 2005 12:19:21 -0800, "Jerry Porter" <jerryp@.personablepc.com> wrote:

>Thanks Steve,
>I had used that tool, but hadn't taken it all the way.
>[Right-click on database, All Tasks, Generate SQL Script, Show All,
>Script all objects]
>I don't suppose there's a way to automate this?

Unfortunately, it seems not. Most other database servers make this a feature
of the database server itself, not the GUI tool, but this is Microsoft we're
talking about.|||Rightclick and generating script will give you scripts but when you run
them in production environment, it will drop tables and recreate
them... You still need to figure out a way to save the data... So I
wouldnt recomend that unless, you just wanted to keep a log of changes
made to the DB...!

As far as I know, there are no direct way to do this unless you go for
some thrid party tool...|||I found some old threads suggesting the use of SQL-DMO to generate
scripts, so I looked into it. The following VBA code generates a text
file with scripts for all (?) database objects:

Dim srv As SQLDMO.SQLServer2
Dim db As SQLDMO.Database2
Dim tr As SQLDMO.Transfer2

Set srv = New SQLDMO.SQLServer2
srv.Connect "MyServer", "sa", ""
Set db = srv.Databases("MyDatabase")

Set tr = New SQLDMO.Transfer2

With tr
..CopyAllObjects = True
..DestDatabase = "test"
End With

db.ScriptTransfer tr, SQLDMOXfrFile_Default _
Or SQLDMOXfrFile_SingleSummaryFile, _
"c:\temp\dbscript.txt"

Set tr = Nothing
Set db = Nothing
Set srv = Nothing

There are at least 3 scripting methods (Script, GenerateSQL,
ScriptTransfer), and a long list of options, so there's a good change I
don't have it exactly right.

Jerry|||Look into the SCPTXFR.EXE utility. For an example, see the end of this
article: http://www.dbazine.com/larsen4.shtmlsql

backing up databases

hey all,
i have several databases in my sql server i need to back up. Do i have to do
them individually or is there a way i can back them up all at once?
thanks,
rodchar
You can use the Maintenance Plan wizard to back them all up in one plan but
in reality you back them up one at a time.
Andrew J. Kelly SQL MVP
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:4F09B789-F51A-40F7-8076-465D188FF4E9@.microsoft.com...
> hey all,
> i have several databases in my sql server i need to back up. Do i have to
> do
> them individually or is there a way i can back them up all at once?
> thanks,
> rodchar
|||thank you.
"Andrew J. Kelly" wrote:

> You can use the Maintenance Plan wizard to back them all up in one plan but
> in reality you back them up one at a time.
> --
> Andrew J. Kelly SQL MVP
>
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:4F09B789-F51A-40F7-8076-465D188FF4E9@.microsoft.com...
>
>

Backing up database

Hi All. I currently have a SQL server database which I
back up nightly using a job defined in SQL server and a
USB 2.0 external HD. My question is: I have created a .bat
file to do the backup to the external drive. The file
shuts down all SQL services that are running first, it
backes up the file (both .mdf/.ldf) then it restarts the
server and the servces (sql agent etc). The database is
only about 1/2 GB at this point so it takes about 10
minutes to back the whole \Data directory up. What I'd
like to know is, is there any implications for the
database or server using such a procedure every night 5
nights a week? Or it it completely fine? Thank you for
your help
Leo"Leo" <anonymous@.discussions.microsoft.com> wrote in message
news:03b501c3ae2a$61c166b0$a501280a@.phx.gbl...
> Hi All. I currently have a SQL server database which I
> back up nightly using a job defined in SQL server and a
> USB 2.0 external HD. My question is: I have created a .bat
> file to do the backup to the external drive. The file
> shuts down all SQL services that are running first, it
> backes up the file (both .mdf/.ldf) then it restarts the
> server and the servces (sql agent etc). The database is
> only about 1/2 GB at this point so it takes about 10
> minutes to back the whole \Data directory up. What I'd
> like to know is, is there any implications for the
> database or server using such a procedure every night 5
> nights a week? Or it it completely fine? Thank you for
> your help
>
Well that's not how most people do SQL Server backups.
First off, you only have 1 backup. If you have a problem you don't catch
for a couple of days, or a datafile corruption that prevents SqlServer from
restarting you would be in trouble.
Second, your transaction logs will just keep growing. You have to do a log
file backup to truncate them.
Third, you have to shut down the database.
In Enterprise Manager you can easilly set up a Database Maintence Plan for
the server. This will create SQL Agent jobs to perform database and/or log
backups on a schedule. For each schedule you can set a retention period for
the backup files. So you can set up one schedule to do nightly backups
which are retained for say, 2 weeks, and another to to monthly backups which
are retained for a year.
David|||Leo,
This is kind of offline backup strategy that you are opting for. If your business permits taking
application offline then you can implement this strategy otherwise you can opt for native SQL Server
backup (Inclusive of full /differential/transaction log backups). This can be done while database is
online and no need to shutdown the server. Taking backups through SQL Server native backups will
give you flexibility to restore database to a particular point of time or taking differential
backups etc, which will not be possible if you are taking cold / OS level backup by shutting down
the server. You can see more help on backup and restore commands in Books online.
Refer to following url as well.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_63eh.asp
--
- Vishal|||Thank you gentlemen. I should have clarified that I do
daily backups through SQL server of course. I back up all
the databases that way. This *cold* way of backing up is
just an *extra* step I take just in case my whole drive
fails, or something happens that I cannot in no way
restore from the backup tape or the backup folder in SQL.
All I wanted to know was if there would be a problem for
the database shutting down SQL server every night and
doing the cold backup. I have no problem shutting it down
for 15 minutes at night since the production server is
only used from 9-5. Thanks anyway..
Leo
>--Original Message--
>Hi All. I currently have a SQL server database which I
>back up nightly using a job defined in SQL server and a
>USB 2.0 external HD. My question is: I have created
a .bat
>file to do the backup to the external drive. The file
>shuts down all SQL services that are running first, it
>backes up the file (both .mdf/.ldf) then it restarts the
>server and the servces (sql agent etc). The database is
>only about 1/2 GB at this point so it takes about 10
>minutes to back the whole \Data directory up. What I'd
>like to know is, is there any implications for the
>database or server using such a procedure every night 5
>nights a week? Or it it completely fine? Thank you for
>your help
>Leo
>.
>|||This (may) be better than using a bat file, I use it to chuck a copy over to
another server, as a secondary backup to a Backup Job, I just run it in task
scheduler.
'************************************************************************
'VBSCRIP to backup database's and email results
'1 Novenber 2003 Don Grover
'************************************************************************
'Open connection to SQL Server
Set Conn = CreateObject("ADODB.Connection")
Conn.Open _
"Provider=SQLOLEDB" & _
";Data Source=assoftsvr" & _
";Integrated Security=SSPI"
'Call the backup routine
DoBackup "GTS"
DoBackup "CokeShop"
DoBackup "BGINFO"
Conn.Close
Set Conn = Nothing
Sub DoBackup(sDbName)
'Sub accepts a databse name and backs up to predefined location on another
server
On Error Resume Next
Conn.Execute _
"BACKUP DATABASE " & sDbName & _
" TO DISK='\\nassvr\Backups\" & sDbName & "_db_" &
formatdatetime(now,1) & ".bak'" & _
" WITH INIT"
Conn.Execute _
"BACKUP LOG " & sDbName & _
" TO DISK='\\nassvr\Backups\" & sDbName & "_log_" &
formatdatetime(now,1) & ".bak'" & _
" "
If Err.Number = 0 Then
SendEmail sDbName,"SUCCESS"
Else
SendEmail sDbName,"BACKUP FAILED " & Err.Number & "Desc: " &
Err.Description
End If
Err.Clear
On Error Goto 0
End Sub
Sub SendEmail(sDatabaseName,sMessage)
Set objEmail = CreateObject("CDO.Message")
objEmail.From = "support@.assoft.com.au"
objEmail.To = "Don Grover <dgrover@.assoft.com.au>"
objEmail.Subject = "Backed up " & sDatabaseName & " on " &
formatdatetime(now,1)
objEmail.Textbody = "Backup " & sMessage & " of " & sDatabaseName & " to
\\NASSvr...."
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
"assoftsvr"
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.Send
Set objEmail = Nothing
End Sub
'Wscript.Echo "Backup complete"sql

Backing up and restoring 50 databases to a new server

I would like to replace my SQL 2000 server with a faster box. I currently
use a combination of full and transactional backups to back up the server
every day. I know that I could build a new box and restore each database
from backup file one at a time but was wondering if there was a quicker way
to store all the databases
Hi,
Look in to the below web sites for various strategies to move all the
databases to new server.
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Thanks
Hari
MCDBA
"Richard Zellmer" <tdmailbox@.yahoo.com> wrote in message
news:#$TGJe7IEHA.1412@.TK2MSFTNGP12.phx.gbl...
> I would like to replace my SQL 2000 server with a faster box. I currently
> use a combination of full and transactional backups to back up the server
> every day. I know that I could build a new box and restore each database
> from backup file one at a time but was wondering if there was a quicker
way
> to store all the databases
>
>
|||If you have installed SQL on the new server with the same paths... I have stopped the SQL services on both servers and copied all DB's including system to the new server. I have done this on many occasions. I always double check @.@.servername on the ne
w server when I am done.
On the new server just rename the SQL Directories before you copy the old ones over. This will give you something to fall back on just in case of failure. Copy all the mdf's and ldf's to the same location on the new box.
Jeff
MCDBA, MCSE+I