I am currently looking to buy or build software that enables my firm to:
Run a set of scripts to create a base database - empty database with minimum amounts of data (i.e. lookup data and seed data). So that when a new development project begins all that is needed to build the database is to run these scripts.
The challenges we face is managing these scripts. It would be easier from a manual perspective to only have 5-6 scripts, so that we do not need to manually open and execute each script (representing an object). The problem with this is when you modify a stored procedure, you modify it in your version control platform (which is single object based) and then you need to find and replace the object in your script.
What we are looking to do is buy or build an application to manage these files for us. I was wondering if anyone has purchased a solution to accomodate this?
We currently have ApexSQL Diff - which works well to compare contents of databases (structurally it does well, seems to have a problem comparing data but that is another discussion). What it does not do well is script out an entire database, and if it were to do this, it would put it all in one file.The aPress book Real-World SQL-DMO for SQL-Server, by Mitchell & Allison, describes (and includes source on CD) the VB code to perform this very task. You would create your "empty" database once. Then, run the tool against the database to script, not only the database structure, but the data as well.
Then, just run the script on a new server to create your database.|||Thanks I just purchased that book based on your advice, we had a meeting to further discuss, this is what we came up with:
I. Structural
We agreed using a tool like Erwin is the best approach, have 1 point person (with a backup in place) that would own the database. When development teams are ready to merge up they would tell the point person which DB their project uses. The point person would then use Erwin to merge their structural changes into the master data model. Once that is complete, scripts can easily be generated to serve as master install scripts for new projects into empty DBs.
II. Functional
Currently we use subversion to store our views, UDFs and stored procedures. Regarding this piece we discussed creating a simple program to manage the creation of these scripts using the individual Subversion files as the repository. I purchased a book that comes with a CD that has VB code that utilizes the SQL DMO in order to create the necessary scripts. Ideally we would want an application that takes an INI type file with a list of objects and generates the necessary files to create those objects. This could be used in two situations the first would serve as a solution to the base DB install for UDFs, Views and Stored Procs. The second situation would be to aid in managing changes in the Development environment when we need to push changes to QA in the form of install scripts. Currently we have a very tedious process of generating the necessary SQL per object and having to find the object in our original install script, delete what is in the script and paste our new code into the script. By having an application in place to automate this process would be a great aid in generating the Base DB scripts and even more so with regards to ongoing developing.
III. Data
Depending on how the Functional phase described above goes (the aforementioned book also has code to perform similar actions on data) we would look to apply similar functionality to seed the Data. As an alternative we could look at automation of BCP file creation. There are two types of data mandatory lookup data that would also be used for QA / Production releases, and non-mandatory data, i.e. seed data, that would be used to improve basic functionality of the 3 sites.
IV. Test Scripts
This portion would need to be revisited when the time is appropriate I would like to look at solutions that would help in automating test scripts. Ideally the purpose of this phase would be to ensure all three sites are working post-merge. The easiest way to ensure proper functionality is to run automated test scripts that can return a PASS or FAIL result, rather then for each project having to manually follow a test plan.
Showing posts with label software. Show all posts
Showing posts with label software. Show all posts
Sunday, March 25, 2012
Saturday, February 25, 2012
Backups
Here is the question, what does SQL Server do with databases that are in use but I am performing a backup. Our company has some kind of software that cannot have a database in use during a backup. I suggested that SQL Server perform it's own backup and
then the can let the tape drives backup my back up. What does SQL Server do in this case?
You are right, SQL Server can do online backups. When you do a database
backup, it backups data files, and after that the portinon of the
transaction log that holds changes made during the data files backup.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"rich" <anonymous@.discussions.microsoft.com> wrote in message
news:FB7C977B-45A5-4DAD-9B9A-A87F6A229DA2@.microsoft.com...
> Here is the question, what does SQL Server do with databases that are in
use but I am performing a backup. Our company has some kind of software
that cannot have a database in use during a backup. I suggested that SQL
Server perform it's own backup and then the can let the tape drives backup
my back up. What does SQL Server do in this case?
|||SQL Server basically doesn't disturb the users at all while it does a backup. It does a checkpoint, backs up
all data pages and then all log records that were created during the time it took to backup the data pages.
What the users will see is possibly some decreased performance as SQL Server generated I/O while the backup
was running.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"rich" <anonymous@.discussions.microsoft.com> wrote in message
news:FB7C977B-45A5-4DAD-9B9A-A87F6A229DA2@.microsoft.com...
> Here is the question, what does SQL Server do with databases that are in use but I am performing a backup.
Our company has some kind of software that cannot have a database in use during a backup. I suggested that
SQL Server perform it's own backup and then the can let the tape drives backup my back up. What does SQL
Server do in this case?
then the can let the tape drives backup my back up. What does SQL Server do in this case?
You are right, SQL Server can do online backups. When you do a database
backup, it backups data files, and after that the portinon of the
transaction log that holds changes made during the data files backup.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"rich" <anonymous@.discussions.microsoft.com> wrote in message
news:FB7C977B-45A5-4DAD-9B9A-A87F6A229DA2@.microsoft.com...
> Here is the question, what does SQL Server do with databases that are in
use but I am performing a backup. Our company has some kind of software
that cannot have a database in use during a backup. I suggested that SQL
Server perform it's own backup and then the can let the tape drives backup
my back up. What does SQL Server do in this case?
|||SQL Server basically doesn't disturb the users at all while it does a backup. It does a checkpoint, backs up
all data pages and then all log records that were created during the time it took to backup the data pages.
What the users will see is possibly some decreased performance as SQL Server generated I/O while the backup
was running.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"rich" <anonymous@.discussions.microsoft.com> wrote in message
news:FB7C977B-45A5-4DAD-9B9A-A87F6A229DA2@.microsoft.com...
> Here is the question, what does SQL Server do with databases that are in use but I am performing a backup.
Our company has some kind of software that cannot have a database in use during a backup. I suggested that
SQL Server perform it's own backup and then the can let the tape drives backup my back up. What does SQL
Server do in this case?
Backups
Here is the question, what does SQL Server do with databases that are in use
but I am performing a backup. Our company has some kind of software that c
annot have a database in use during a backup. I suggested that SQL Server p
erform it's own backup and
then the can let the tape drives backup my back up. What does SQL Server do
in this case?You are right, SQL Server can do online backups. When you do a database
backup, it backups data files, and after that the portinon of the
transaction log that holds changes made during the data files backup.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"rich" <anonymous@.discussions.microsoft.com> wrote in message
news:FB7C977B-45A5-4DAD-9B9A-A87F6A229DA2@.microsoft.com...
> Here is the question, what does SQL Server do with databases that are in
use but I am performing a backup. Our company has some kind of software
that cannot have a database in use during a backup. I suggested that SQL
Server perform it's own backup and then the can let the tape drives backup
my back up. What does SQL Server do in this case?|||SQL Server basically doesn't disturb the users at all while it does a backup
. It does a checkpoint, backs up
all data pages and then all log records that were created during the time it
took to backup the data pages.
What the users will see is possibly some decreased performance as SQL Server
generated I/O while the backup
was running.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"rich" <anonymous@.discussions.microsoft.com> wrote in message
news:FB7C977B-45A5-4DAD-9B9A-A87F6A229DA2@.microsoft.com...
> Here is the question, what does SQL Server do with databases that are in use but I
am performing a backup.
Our company has some kind of software that cannot have a database in use dur
ing a backup. I suggested that
SQL Server perform it's own backup and then the can let the tape drives back
up my back up. What does SQL
Server do in this case?
but I am performing a backup. Our company has some kind of software that c
annot have a database in use during a backup. I suggested that SQL Server p
erform it's own backup and
then the can let the tape drives backup my back up. What does SQL Server do
in this case?You are right, SQL Server can do online backups. When you do a database
backup, it backups data files, and after that the portinon of the
transaction log that holds changes made during the data files backup.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"rich" <anonymous@.discussions.microsoft.com> wrote in message
news:FB7C977B-45A5-4DAD-9B9A-A87F6A229DA2@.microsoft.com...
> Here is the question, what does SQL Server do with databases that are in
use but I am performing a backup. Our company has some kind of software
that cannot have a database in use during a backup. I suggested that SQL
Server perform it's own backup and then the can let the tape drives backup
my back up. What does SQL Server do in this case?|||SQL Server basically doesn't disturb the users at all while it does a backup
. It does a checkpoint, backs up
all data pages and then all log records that were created during the time it
took to backup the data pages.
What the users will see is possibly some decreased performance as SQL Server
generated I/O while the backup
was running.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"rich" <anonymous@.discussions.microsoft.com> wrote in message
news:FB7C977B-45A5-4DAD-9B9A-A87F6A229DA2@.microsoft.com...
> Here is the question, what does SQL Server do with databases that are in use but I
am performing a backup.
Our company has some kind of software that cannot have a database in use dur
ing a backup. I suggested that
SQL Server perform it's own backup and then the can let the tape drives back
up my back up. What does SQL
Server do in this case?
Backups
Here is the question, what does SQL Server do with databases that are in use but I am performing a backup. Our company has some kind of software that cannot have a database in use during a backup. I suggested that SQL Server perform it's own backup and then the can let the tape drives backup my back up. What does SQL Server do in this case?You are right, SQL Server can do online backups. When you do a database
backup, it backups data files, and after that the portinon of the
transaction log that holds changes made during the data files backup.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"rich" <anonymous@.discussions.microsoft.com> wrote in message
news:FB7C977B-45A5-4DAD-9B9A-A87F6A229DA2@.microsoft.com...
> Here is the question, what does SQL Server do with databases that are in
use but I am performing a backup. Our company has some kind of software
that cannot have a database in use during a backup. I suggested that SQL
Server perform it's own backup and then the can let the tape drives backup
my back up. What does SQL Server do in this case?|||SQL Server basically doesn't disturb the users at all while it does a backup. It does a checkpoint, backs up
all data pages and then all log records that were created during the time it took to backup the data pages.
What the users will see is possibly some decreased performance as SQL Server generated I/O while the backup
was running.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"rich" <anonymous@.discussions.microsoft.com> wrote in message
news:FB7C977B-45A5-4DAD-9B9A-A87F6A229DA2@.microsoft.com...
> Here is the question, what does SQL Server do with databases that are in use but I am performing a backup.
Our company has some kind of software that cannot have a database in use during a backup. I suggested that
SQL Server perform it's own backup and then the can let the tape drives backup my back up. What does SQL
Server do in this case?
backup, it backups data files, and after that the portinon of the
transaction log that holds changes made during the data files backup.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"rich" <anonymous@.discussions.microsoft.com> wrote in message
news:FB7C977B-45A5-4DAD-9B9A-A87F6A229DA2@.microsoft.com...
> Here is the question, what does SQL Server do with databases that are in
use but I am performing a backup. Our company has some kind of software
that cannot have a database in use during a backup. I suggested that SQL
Server perform it's own backup and then the can let the tape drives backup
my back up. What does SQL Server do in this case?|||SQL Server basically doesn't disturb the users at all while it does a backup. It does a checkpoint, backs up
all data pages and then all log records that were created during the time it took to backup the data pages.
What the users will see is possibly some decreased performance as SQL Server generated I/O while the backup
was running.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"rich" <anonymous@.discussions.microsoft.com> wrote in message
news:FB7C977B-45A5-4DAD-9B9A-A87F6A229DA2@.microsoft.com...
> Here is the question, what does SQL Server do with databases that are in use but I am performing a backup.
Our company has some kind of software that cannot have a database in use during a backup. I suggested that
SQL Server perform it's own backup and then the can let the tape drives backup my back up. What does SQL
Server do in this case?
Thursday, February 16, 2012
backup without .bak
i have a database that's about 1.8GB and would like to back it up. I just
found out about it, but am reluctant to backup it up with the software GUI
part since it's my only database.
Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
to another disk manually to be safe, and then do a backup of the database
using SQL Enterprise?
Also, if I were to create a backup of the .mdf and the .ldf files if
something should happen to the .bak file can I use the data file to create a
new database from it? If so, how?Do the backup using the BACKUP command in SQL Server. Only copying the files
in not guaranteed. If
you at all want to go that way, look into sp_detach_db and sp_attach_db.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ae" <ae@.discussions.microsoft.com> wrote in message
news:CAF78B51-7924-4617-A4E4-BD8CCA77C95B@.microsoft.com...
>i have a database that's about 1.8GB and would like to back it up. I just
> found out about it, but am reluctant to backup it up with the software GUI
> part since it's my only database.
> Will it work if I copied the Data File (.mdf) and the Transaction Log (.ld
f)
> to another disk manually to be safe, and then do a backup of the database
> using SQL Enterprise?
> Also, if I were to create a backup of the .mdf and the .ldf files if
> something should happen to the .bak file can I use the data file to create
a
> new database from it? If so, how?|||ae wrote:
> i have a database that's about 1.8GB and would like to back it up. I just
> found out about it, but am reluctant to backup it up with the software GUI
> part since it's my only database.
> Will it work if I copied the Data File (.mdf) and the Transaction Log (.ld
f)
> to another disk manually to be safe, and then do a backup of the database
> using SQL Enterprise?
> Also, if I were to create a backup of the .mdf and the .ldf files if
> something should happen to the .bak file can I use the data file to create
a
> new database from it? If so, how?
Copying the .mdf and .ldf files to another disk is not to be safe - it's
actually the opposite...:-). When the SQL Server service is running, you
can't copy the files. If you stop the service to copy the files, your
database will be unavailable which means that you'll have some down
time. As Tibor mentions, if you just copy the files with out using
detach there are no guarantee that you will be able to attach the files
to the new database. Even if you use the detach/attach method it's
actually not as safe as using a backup. When you run sp_detach_db, you
have 2 database files that you need to attach before you have working
database again. This means that even your original database is not
available/working. Theoretically something could go wrong, so it fails
to run sp_attach_db when want to get you original database running
again. In that case you have lost everything - even your original database.
If you use the backup, you'll have your original database running all
the time. If the backup doesn't work, you can just do a new backup since
nothing has happened to your original database.
Regards
Steen|||hey thanks for the info., if i'm reading this correct when i do the backup i
f
something should go wrong (whatever that may be). the actual database will
never get damaged?
the worst thing that can happen i will have to reattempt to do a backup a
second time.
"Steen Persson (DK)" wrote:
> ae wrote:
>
> Copying the .mdf and .ldf files to another disk is not to be safe - it's
> actually the opposite...:-). When the SQL Server service is running, you
> can't copy the files. If you stop the service to copy the files, your
> database will be unavailable which means that you'll have some down
> time. As Tibor mentions, if you just copy the files with out using
> detach there are no guarantee that you will be able to attach the files
> to the new database. Even if you use the detach/attach method it's
> actually not as safe as using a backup. When you run sp_detach_db, you
> have 2 database files that you need to attach before you have working
> database again. This means that even your original database is not
> available/working. Theoretically something could go wrong, so it fails
> to run sp_attach_db when want to get you original database running
> again. In that case you have lost everything - even your original database
.
> If you use the backup, you'll have your original database running all
> the time. If the backup doesn't work, you can just do a new backup since
> nothing has happened to your original database.
> Regards
> Steen
>|||> hey thanks for the info., if i'm reading this correct when i do the backup ifen">
> something should go wrong (whatever that may be). the actual database wil
l
> never get damaged?
If depends on what you mean by "something goes wrong". If the disk where the
database sis crashes,
well, you cannot expect the database to be healthy... :-)
But I take it you mean if something goes wrong with the backup procedure. Th
en, don't worry. Just
redo the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ae" <ae@.discussions.microsoft.com> wrote in message
news:2A4B9FD5-BC7A-4130-B7F4-B32BA31610A5@.microsoft.com...[vbcol=seagreen]
> hey thanks for the info., if i'm reading this correct when i do the backup
if
> something should go wrong (whatever that may be). the actual database wil
l
> never get damaged?
> the worst thing that can happen i will have to reattempt to do a backup a
> second time.
> "Steen Persson (DK)" wrote:
>|||ae wrote:
> hey thanks for the info., if i'm reading this correct when i do the backup
if
> something should go wrong (whatever that may be). the actual database wil
l
> never get damaged?
> the worst thing that can happen i will have to reattempt to do a backup a
> second time.
>
That's correct.
Regards
Steen
found out about it, but am reluctant to backup it up with the software GUI
part since it's my only database.
Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
to another disk manually to be safe, and then do a backup of the database
using SQL Enterprise?
Also, if I were to create a backup of the .mdf and the .ldf files if
something should happen to the .bak file can I use the data file to create a
new database from it? If so, how?Do the backup using the BACKUP command in SQL Server. Only copying the files
in not guaranteed. If
you at all want to go that way, look into sp_detach_db and sp_attach_db.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ae" <ae@.discussions.microsoft.com> wrote in message
news:CAF78B51-7924-4617-A4E4-BD8CCA77C95B@.microsoft.com...
>i have a database that's about 1.8GB and would like to back it up. I just
> found out about it, but am reluctant to backup it up with the software GUI
> part since it's my only database.
> Will it work if I copied the Data File (.mdf) and the Transaction Log (.ld
f)
> to another disk manually to be safe, and then do a backup of the database
> using SQL Enterprise?
> Also, if I were to create a backup of the .mdf and the .ldf files if
> something should happen to the .bak file can I use the data file to create
a
> new database from it? If so, how?|||ae wrote:
> i have a database that's about 1.8GB and would like to back it up. I just
> found out about it, but am reluctant to backup it up with the software GUI
> part since it's my only database.
> Will it work if I copied the Data File (.mdf) and the Transaction Log (.ld
f)
> to another disk manually to be safe, and then do a backup of the database
> using SQL Enterprise?
> Also, if I were to create a backup of the .mdf and the .ldf files if
> something should happen to the .bak file can I use the data file to create
a
> new database from it? If so, how?
Copying the .mdf and .ldf files to another disk is not to be safe - it's
actually the opposite...:-). When the SQL Server service is running, you
can't copy the files. If you stop the service to copy the files, your
database will be unavailable which means that you'll have some down
time. As Tibor mentions, if you just copy the files with out using
detach there are no guarantee that you will be able to attach the files
to the new database. Even if you use the detach/attach method it's
actually not as safe as using a backup. When you run sp_detach_db, you
have 2 database files that you need to attach before you have working
database again. This means that even your original database is not
available/working. Theoretically something could go wrong, so it fails
to run sp_attach_db when want to get you original database running
again. In that case you have lost everything - even your original database.
If you use the backup, you'll have your original database running all
the time. If the backup doesn't work, you can just do a new backup since
nothing has happened to your original database.
Regards
Steen|||hey thanks for the info., if i'm reading this correct when i do the backup i
f
something should go wrong (whatever that may be). the actual database will
never get damaged?
the worst thing that can happen i will have to reattempt to do a backup a
second time.
"Steen Persson (DK)" wrote:
> ae wrote:
>
> Copying the .mdf and .ldf files to another disk is not to be safe - it's
> actually the opposite...:-). When the SQL Server service is running, you
> can't copy the files. If you stop the service to copy the files, your
> database will be unavailable which means that you'll have some down
> time. As Tibor mentions, if you just copy the files with out using
> detach there are no guarantee that you will be able to attach the files
> to the new database. Even if you use the detach/attach method it's
> actually not as safe as using a backup. When you run sp_detach_db, you
> have 2 database files that you need to attach before you have working
> database again. This means that even your original database is not
> available/working. Theoretically something could go wrong, so it fails
> to run sp_attach_db when want to get you original database running
> again. In that case you have lost everything - even your original database
.
> If you use the backup, you'll have your original database running all
> the time. If the backup doesn't work, you can just do a new backup since
> nothing has happened to your original database.
> Regards
> Steen
>|||> hey thanks for the info., if i'm reading this correct when i do the backup ifen">
> something should go wrong (whatever that may be). the actual database wil
l
> never get damaged?
If depends on what you mean by "something goes wrong". If the disk where the
database sis crashes,
well, you cannot expect the database to be healthy... :-)
But I take it you mean if something goes wrong with the backup procedure. Th
en, don't worry. Just
redo the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ae" <ae@.discussions.microsoft.com> wrote in message
news:2A4B9FD5-BC7A-4130-B7F4-B32BA31610A5@.microsoft.com...[vbcol=seagreen]
> hey thanks for the info., if i'm reading this correct when i do the backup
if
> something should go wrong (whatever that may be). the actual database wil
l
> never get damaged?
> the worst thing that can happen i will have to reattempt to do a backup a
> second time.
> "Steen Persson (DK)" wrote:
>|||ae wrote:
> hey thanks for the info., if i'm reading this correct when i do the backup
if
> something should go wrong (whatever that may be). the actual database wil
l
> never get damaged?
> the worst thing that can happen i will have to reattempt to do a backup a
> second time.
>
That's correct.
Regards
Steen
backup without .bak
i have a database that's about 1.8GB and would like to back it up. I just
found out about it, but am reluctant to backup it up with the software GUI
part since it's my only database.
Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
to another disk manually to be safe, and then do a backup of the database
using SQL Enterprise?
Also, if I were to create a backup of the .mdf and the .ldf files if
something should happen to the .bak file can I use the data file to create a
new database from it? If so, how?Do the backup using the BACKUP command in SQL Server. Only copying the files in not guaranteed. If
you at all want to go that way, look into sp_detach_db and sp_attach_db.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ae" <ae@.discussions.microsoft.com> wrote in message
news:CAF78B51-7924-4617-A4E4-BD8CCA77C95B@.microsoft.com...
>i have a database that's about 1.8GB and would like to back it up. I just
> found out about it, but am reluctant to backup it up with the software GUI
> part since it's my only database.
> Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
> to another disk manually to be safe, and then do a backup of the database
> using SQL Enterprise?
> Also, if I were to create a backup of the .mdf and the .ldf files if
> something should happen to the .bak file can I use the data file to create a
> new database from it? If so, how?|||ae wrote:
> i have a database that's about 1.8GB and would like to back it up. I just
> found out about it, but am reluctant to backup it up with the software GUI
> part since it's my only database.
> Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
> to another disk manually to be safe, and then do a backup of the database
> using SQL Enterprise?
> Also, if I were to create a backup of the .mdf and the .ldf files if
> something should happen to the .bak file can I use the data file to create a
> new database from it? If so, how?
Copying the .mdf and .ldf files to another disk is not to be safe - it's
actually the opposite...:-). When the SQL Server service is running, you
can't copy the files. If you stop the service to copy the files, your
database will be unavailable which means that you'll have some down
time. As Tibor mentions, if you just copy the files with out using
detach there are no guarantee that you will be able to attach the files
to the new database. Even if you use the detach/attach method it's
actually not as safe as using a backup. When you run sp_detach_db, you
have 2 database files that you need to attach before you have working
database again. This means that even your original database is not
available/working. Theoretically something could go wrong, so it fails
to run sp_attach_db when want to get you original database running
again. In that case you have lost everything - even your original database.
If you use the backup, you'll have your original database running all
the time. If the backup doesn't work, you can just do a new backup since
nothing has happened to your original database.
Regards
Steen|||hey thanks for the info., if i'm reading this correct when i do the backup if
something should go wrong (whatever that may be). the actual database will
never get damaged?
the worst thing that can happen i will have to reattempt to do a backup a
second time.
"Steen Persson (DK)" wrote:
> ae wrote:
> > i have a database that's about 1.8GB and would like to back it up. I just
> > found out about it, but am reluctant to backup it up with the software GUI
> > part since it's my only database.
> >
> > Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
> > to another disk manually to be safe, and then do a backup of the database
> > using SQL Enterprise?
> >
> > Also, if I were to create a backup of the .mdf and the .ldf files if
> > something should happen to the .bak file can I use the data file to create a
> > new database from it? If so, how?
>
> Copying the .mdf and .ldf files to another disk is not to be safe - it's
> actually the opposite...:-). When the SQL Server service is running, you
> can't copy the files. If you stop the service to copy the files, your
> database will be unavailable which means that you'll have some down
> time. As Tibor mentions, if you just copy the files with out using
> detach there are no guarantee that you will be able to attach the files
> to the new database. Even if you use the detach/attach method it's
> actually not as safe as using a backup. When you run sp_detach_db, you
> have 2 database files that you need to attach before you have working
> database again. This means that even your original database is not
> available/working. Theoretically something could go wrong, so it fails
> to run sp_attach_db when want to get you original database running
> again. In that case you have lost everything - even your original database.
> If you use the backup, you'll have your original database running all
> the time. If the backup doesn't work, you can just do a new backup since
> nothing has happened to your original database.
> Regards
> Steen
>|||> hey thanks for the info., if i'm reading this correct when i do the backup if
> something should go wrong (whatever that may be). the actual database will
> never get damaged?
If depends on what you mean by "something goes wrong". If the disk where the database sis crashes,
well, you cannot expect the database to be healthy... :-)
But I take it you mean if something goes wrong with the backup procedure. Then, don't worry. Just
redo the backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ae" <ae@.discussions.microsoft.com> wrote in message
news:2A4B9FD5-BC7A-4130-B7F4-B32BA31610A5@.microsoft.com...
> hey thanks for the info., if i'm reading this correct when i do the backup if
> something should go wrong (whatever that may be). the actual database will
> never get damaged?
> the worst thing that can happen i will have to reattempt to do a backup a
> second time.
> "Steen Persson (DK)" wrote:
>> ae wrote:
>> > i have a database that's about 1.8GB and would like to back it up. I just
>> > found out about it, but am reluctant to backup it up with the software GUI
>> > part since it's my only database.
>> >
>> > Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
>> > to another disk manually to be safe, and then do a backup of the database
>> > using SQL Enterprise?
>> >
>> > Also, if I were to create a backup of the .mdf and the .ldf files if
>> > something should happen to the .bak file can I use the data file to create a
>> > new database from it? If so, how?
>>
>> Copying the .mdf and .ldf files to another disk is not to be safe - it's
>> actually the opposite...:-). When the SQL Server service is running, you
>> can't copy the files. If you stop the service to copy the files, your
>> database will be unavailable which means that you'll have some down
>> time. As Tibor mentions, if you just copy the files with out using
>> detach there are no guarantee that you will be able to attach the files
>> to the new database. Even if you use the detach/attach method it's
>> actually not as safe as using a backup. When you run sp_detach_db, you
>> have 2 database files that you need to attach before you have working
>> database again. This means that even your original database is not
>> available/working. Theoretically something could go wrong, so it fails
>> to run sp_attach_db when want to get you original database running
>> again. In that case you have lost everything - even your original database.
>> If you use the backup, you'll have your original database running all
>> the time. If the backup doesn't work, you can just do a new backup since
>> nothing has happened to your original database.
>> Regards
>> Steen|||ae wrote:
> hey thanks for the info., if i'm reading this correct when i do the backup if
> something should go wrong (whatever that may be). the actual database will
> never get damaged?
> the worst thing that can happen i will have to reattempt to do a backup a
> second time.
>
That's correct.
Regards
Steen
found out about it, but am reluctant to backup it up with the software GUI
part since it's my only database.
Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
to another disk manually to be safe, and then do a backup of the database
using SQL Enterprise?
Also, if I were to create a backup of the .mdf and the .ldf files if
something should happen to the .bak file can I use the data file to create a
new database from it? If so, how?Do the backup using the BACKUP command in SQL Server. Only copying the files in not guaranteed. If
you at all want to go that way, look into sp_detach_db and sp_attach_db.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ae" <ae@.discussions.microsoft.com> wrote in message
news:CAF78B51-7924-4617-A4E4-BD8CCA77C95B@.microsoft.com...
>i have a database that's about 1.8GB and would like to back it up. I just
> found out about it, but am reluctant to backup it up with the software GUI
> part since it's my only database.
> Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
> to another disk manually to be safe, and then do a backup of the database
> using SQL Enterprise?
> Also, if I were to create a backup of the .mdf and the .ldf files if
> something should happen to the .bak file can I use the data file to create a
> new database from it? If so, how?|||ae wrote:
> i have a database that's about 1.8GB and would like to back it up. I just
> found out about it, but am reluctant to backup it up with the software GUI
> part since it's my only database.
> Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
> to another disk manually to be safe, and then do a backup of the database
> using SQL Enterprise?
> Also, if I were to create a backup of the .mdf and the .ldf files if
> something should happen to the .bak file can I use the data file to create a
> new database from it? If so, how?
Copying the .mdf and .ldf files to another disk is not to be safe - it's
actually the opposite...:-). When the SQL Server service is running, you
can't copy the files. If you stop the service to copy the files, your
database will be unavailable which means that you'll have some down
time. As Tibor mentions, if you just copy the files with out using
detach there are no guarantee that you will be able to attach the files
to the new database. Even if you use the detach/attach method it's
actually not as safe as using a backup. When you run sp_detach_db, you
have 2 database files that you need to attach before you have working
database again. This means that even your original database is not
available/working. Theoretically something could go wrong, so it fails
to run sp_attach_db when want to get you original database running
again. In that case you have lost everything - even your original database.
If you use the backup, you'll have your original database running all
the time. If the backup doesn't work, you can just do a new backup since
nothing has happened to your original database.
Regards
Steen|||hey thanks for the info., if i'm reading this correct when i do the backup if
something should go wrong (whatever that may be). the actual database will
never get damaged?
the worst thing that can happen i will have to reattempt to do a backup a
second time.
"Steen Persson (DK)" wrote:
> ae wrote:
> > i have a database that's about 1.8GB and would like to back it up. I just
> > found out about it, but am reluctant to backup it up with the software GUI
> > part since it's my only database.
> >
> > Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
> > to another disk manually to be safe, and then do a backup of the database
> > using SQL Enterprise?
> >
> > Also, if I were to create a backup of the .mdf and the .ldf files if
> > something should happen to the .bak file can I use the data file to create a
> > new database from it? If so, how?
>
> Copying the .mdf and .ldf files to another disk is not to be safe - it's
> actually the opposite...:-). When the SQL Server service is running, you
> can't copy the files. If you stop the service to copy the files, your
> database will be unavailable which means that you'll have some down
> time. As Tibor mentions, if you just copy the files with out using
> detach there are no guarantee that you will be able to attach the files
> to the new database. Even if you use the detach/attach method it's
> actually not as safe as using a backup. When you run sp_detach_db, you
> have 2 database files that you need to attach before you have working
> database again. This means that even your original database is not
> available/working. Theoretically something could go wrong, so it fails
> to run sp_attach_db when want to get you original database running
> again. In that case you have lost everything - even your original database.
> If you use the backup, you'll have your original database running all
> the time. If the backup doesn't work, you can just do a new backup since
> nothing has happened to your original database.
> Regards
> Steen
>|||> hey thanks for the info., if i'm reading this correct when i do the backup if
> something should go wrong (whatever that may be). the actual database will
> never get damaged?
If depends on what you mean by "something goes wrong". If the disk where the database sis crashes,
well, you cannot expect the database to be healthy... :-)
But I take it you mean if something goes wrong with the backup procedure. Then, don't worry. Just
redo the backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ae" <ae@.discussions.microsoft.com> wrote in message
news:2A4B9FD5-BC7A-4130-B7F4-B32BA31610A5@.microsoft.com...
> hey thanks for the info., if i'm reading this correct when i do the backup if
> something should go wrong (whatever that may be). the actual database will
> never get damaged?
> the worst thing that can happen i will have to reattempt to do a backup a
> second time.
> "Steen Persson (DK)" wrote:
>> ae wrote:
>> > i have a database that's about 1.8GB and would like to back it up. I just
>> > found out about it, but am reluctant to backup it up with the software GUI
>> > part since it's my only database.
>> >
>> > Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
>> > to another disk manually to be safe, and then do a backup of the database
>> > using SQL Enterprise?
>> >
>> > Also, if I were to create a backup of the .mdf and the .ldf files if
>> > something should happen to the .bak file can I use the data file to create a
>> > new database from it? If so, how?
>>
>> Copying the .mdf and .ldf files to another disk is not to be safe - it's
>> actually the opposite...:-). When the SQL Server service is running, you
>> can't copy the files. If you stop the service to copy the files, your
>> database will be unavailable which means that you'll have some down
>> time. As Tibor mentions, if you just copy the files with out using
>> detach there are no guarantee that you will be able to attach the files
>> to the new database. Even if you use the detach/attach method it's
>> actually not as safe as using a backup. When you run sp_detach_db, you
>> have 2 database files that you need to attach before you have working
>> database again. This means that even your original database is not
>> available/working. Theoretically something could go wrong, so it fails
>> to run sp_attach_db when want to get you original database running
>> again. In that case you have lost everything - even your original database.
>> If you use the backup, you'll have your original database running all
>> the time. If the backup doesn't work, you can just do a new backup since
>> nothing has happened to your original database.
>> Regards
>> Steen|||ae wrote:
> hey thanks for the info., if i'm reading this correct when i do the backup if
> something should go wrong (whatever that may be). the actual database will
> never get damaged?
> the worst thing that can happen i will have to reattempt to do a backup a
> second time.
>
That's correct.
Regards
Steen
backup without .bak
i have a database that's about 1.8GB and would like to back it up. I just
found out about it, but am reluctant to backup it up with the software GUI
part since it's my only database.
Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
to another disk manually to be safe, and then do a backup of the database
using SQL Enterprise?
Also, if I were to create a backup of the .mdf and the .ldf files if
something should happen to the .bak file can I use the data file to create a
new database from it? If so, how?
Do the backup using the BACKUP command in SQL Server. Only copying the files in not guaranteed. If
you at all want to go that way, look into sp_detach_db and sp_attach_db.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ae" <ae@.discussions.microsoft.com> wrote in message
news:CAF78B51-7924-4617-A4E4-BD8CCA77C95B@.microsoft.com...
>i have a database that's about 1.8GB and would like to back it up. I just
> found out about it, but am reluctant to backup it up with the software GUI
> part since it's my only database.
> Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
> to another disk manually to be safe, and then do a backup of the database
> using SQL Enterprise?
> Also, if I were to create a backup of the .mdf and the .ldf files if
> something should happen to the .bak file can I use the data file to create a
> new database from it? If so, how?
|||ae wrote:
> i have a database that's about 1.8GB and would like to back it up. I just
> found out about it, but am reluctant to backup it up with the software GUI
> part since it's my only database.
> Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
> to another disk manually to be safe, and then do a backup of the database
> using SQL Enterprise?
> Also, if I were to create a backup of the .mdf and the .ldf files if
> something should happen to the .bak file can I use the data file to create a
> new database from it? If so, how?
Copying the .mdf and .ldf files to another disk is not to be safe - it's
actually the opposite...:-). When the SQL Server service is running, you
can't copy the files. If you stop the service to copy the files, your
database will be unavailable which means that you'll have some down
time. As Tibor mentions, if you just copy the files with out using
detach there are no guarantee that you will be able to attach the files
to the new database. Even if you use the detach/attach method it's
actually not as safe as using a backup. When you run sp_detach_db, you
have 2 database files that you need to attach before you have working
database again. This means that even your original database is not
available/working. Theoretically something could go wrong, so it fails
to run sp_attach_db when want to get you original database running
again. In that case you have lost everything - even your original database.
If you use the backup, you'll have your original database running all
the time. If the backup doesn't work, you can just do a new backup since
nothing has happened to your original database.
Regards
Steen
|||hey thanks for the info., if i'm reading this correct when i do the backup if
something should go wrong (whatever that may be). the actual database will
never get damaged?
the worst thing that can happen i will have to reattempt to do a backup a
second time.
"Steen Persson (DK)" wrote:
> ae wrote:
>
> Copying the .mdf and .ldf files to another disk is not to be safe - it's
> actually the opposite...:-). When the SQL Server service is running, you
> can't copy the files. If you stop the service to copy the files, your
> database will be unavailable which means that you'll have some down
> time. As Tibor mentions, if you just copy the files with out using
> detach there are no guarantee that you will be able to attach the files
> to the new database. Even if you use the detach/attach method it's
> actually not as safe as using a backup. When you run sp_detach_db, you
> have 2 database files that you need to attach before you have working
> database again. This means that even your original database is not
> available/working. Theoretically something could go wrong, so it fails
> to run sp_attach_db when want to get you original database running
> again. In that case you have lost everything - even your original database.
> If you use the backup, you'll have your original database running all
> the time. If the backup doesn't work, you can just do a new backup since
> nothing has happened to your original database.
> Regards
> Steen
>
|||> hey thanks for the info., if i'm reading this correct when i do the backup if
> something should go wrong (whatever that may be). the actual database will
> never get damaged?
If depends on what you mean by "something goes wrong". If the disk where the database sis crashes,
well, you cannot expect the database to be healthy... :-)
But I take it you mean if something goes wrong with the backup procedure. Then, don't worry. Just
redo the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ae" <ae@.discussions.microsoft.com> wrote in message
news:2A4B9FD5-BC7A-4130-B7F4-B32BA31610A5@.microsoft.com...[vbcol=seagreen]
> hey thanks for the info., if i'm reading this correct when i do the backup if
> something should go wrong (whatever that may be). the actual database will
> never get damaged?
> the worst thing that can happen i will have to reattempt to do a backup a
> second time.
> "Steen Persson (DK)" wrote:
|||ae wrote:
> hey thanks for the info., if i'm reading this correct when i do the backup if
> something should go wrong (whatever that may be). the actual database will
> never get damaged?
> the worst thing that can happen i will have to reattempt to do a backup a
> second time.
>
That's correct.
Regards
Steen
found out about it, but am reluctant to backup it up with the software GUI
part since it's my only database.
Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
to another disk manually to be safe, and then do a backup of the database
using SQL Enterprise?
Also, if I were to create a backup of the .mdf and the .ldf files if
something should happen to the .bak file can I use the data file to create a
new database from it? If so, how?
Do the backup using the BACKUP command in SQL Server. Only copying the files in not guaranteed. If
you at all want to go that way, look into sp_detach_db and sp_attach_db.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ae" <ae@.discussions.microsoft.com> wrote in message
news:CAF78B51-7924-4617-A4E4-BD8CCA77C95B@.microsoft.com...
>i have a database that's about 1.8GB and would like to back it up. I just
> found out about it, but am reluctant to backup it up with the software GUI
> part since it's my only database.
> Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
> to another disk manually to be safe, and then do a backup of the database
> using SQL Enterprise?
> Also, if I were to create a backup of the .mdf and the .ldf files if
> something should happen to the .bak file can I use the data file to create a
> new database from it? If so, how?
|||ae wrote:
> i have a database that's about 1.8GB and would like to back it up. I just
> found out about it, but am reluctant to backup it up with the software GUI
> part since it's my only database.
> Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
> to another disk manually to be safe, and then do a backup of the database
> using SQL Enterprise?
> Also, if I were to create a backup of the .mdf and the .ldf files if
> something should happen to the .bak file can I use the data file to create a
> new database from it? If so, how?
Copying the .mdf and .ldf files to another disk is not to be safe - it's
actually the opposite...:-). When the SQL Server service is running, you
can't copy the files. If you stop the service to copy the files, your
database will be unavailable which means that you'll have some down
time. As Tibor mentions, if you just copy the files with out using
detach there are no guarantee that you will be able to attach the files
to the new database. Even if you use the detach/attach method it's
actually not as safe as using a backup. When you run sp_detach_db, you
have 2 database files that you need to attach before you have working
database again. This means that even your original database is not
available/working. Theoretically something could go wrong, so it fails
to run sp_attach_db when want to get you original database running
again. In that case you have lost everything - even your original database.
If you use the backup, you'll have your original database running all
the time. If the backup doesn't work, you can just do a new backup since
nothing has happened to your original database.
Regards
Steen
|||hey thanks for the info., if i'm reading this correct when i do the backup if
something should go wrong (whatever that may be). the actual database will
never get damaged?
the worst thing that can happen i will have to reattempt to do a backup a
second time.
"Steen Persson (DK)" wrote:
> ae wrote:
>
> Copying the .mdf and .ldf files to another disk is not to be safe - it's
> actually the opposite...:-). When the SQL Server service is running, you
> can't copy the files. If you stop the service to copy the files, your
> database will be unavailable which means that you'll have some down
> time. As Tibor mentions, if you just copy the files with out using
> detach there are no guarantee that you will be able to attach the files
> to the new database. Even if you use the detach/attach method it's
> actually not as safe as using a backup. When you run sp_detach_db, you
> have 2 database files that you need to attach before you have working
> database again. This means that even your original database is not
> available/working. Theoretically something could go wrong, so it fails
> to run sp_attach_db when want to get you original database running
> again. In that case you have lost everything - even your original database.
> If you use the backup, you'll have your original database running all
> the time. If the backup doesn't work, you can just do a new backup since
> nothing has happened to your original database.
> Regards
> Steen
>
|||> hey thanks for the info., if i'm reading this correct when i do the backup if
> something should go wrong (whatever that may be). the actual database will
> never get damaged?
If depends on what you mean by "something goes wrong". If the disk where the database sis crashes,
well, you cannot expect the database to be healthy... :-)
But I take it you mean if something goes wrong with the backup procedure. Then, don't worry. Just
redo the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ae" <ae@.discussions.microsoft.com> wrote in message
news:2A4B9FD5-BC7A-4130-B7F4-B32BA31610A5@.microsoft.com...[vbcol=seagreen]
> hey thanks for the info., if i'm reading this correct when i do the backup if
> something should go wrong (whatever that may be). the actual database will
> never get damaged?
> the worst thing that can happen i will have to reattempt to do a backup a
> second time.
> "Steen Persson (DK)" wrote:
|||ae wrote:
> hey thanks for the info., if i'm reading this correct when i do the backup if
> something should go wrong (whatever that may be). the actual database will
> never get damaged?
> the worst thing that can happen i will have to reattempt to do a backup a
> second time.
>
That's correct.
Regards
Steen
Subscribe to:
Posts (Atom)