How would I find how long my full backup is taking since I started doing
backup, Is there system table in SQL 2005 or SQL 2000 that will show that
Thanks,Sure. Check out the backup history tables in msdb:
select * from msdb.dbo.backupset
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<msnews.microsoft.com> wrote in message news:%23riFs7zGIHA.4476@.TK2MSFTNGP06.phx.gbl...
> How would I find how long my full backup is taking since I started doing
> backup, Is there system table in SQL 2005 or SQL 2000 that will show that
>
> Thanks,
>|||Check out:
http://msdn2.microsoft.com/en-us/library/ms177648.aspx
It's for SQL 2005 only.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
<msnews.microsoft.com> wrote in message
news:%23riFs7zGIHA.4476@.TK2MSFTNGP06.phx.gbl...
How would I find how long my full backup is taking since I started doing
backup, Is there system table in SQL 2005 or SQL 2000 that will show that
Thanks,
Showing posts with label table. Show all posts
Showing posts with label table. Show all posts
Thursday, March 22, 2012
Tuesday, March 20, 2012
Backup design
Hello, everyone:
I have a 1GB table that has to run in 24X7 environment. That means the users maybe use this table during backup. How should I design the backup strategy? Can users access the table when the backup is running?
Thanks
ZYTOriginally posted by yitongzhang
Hello, everyone:
I have a 1GB table that has to run in 24X7 environment. That means the users maybe use this table during backup. How should I design the backup strategy? Can users access the table when the backup is running?
Thanks
ZYT
yes
I have a 1GB table that has to run in 24X7 environment. That means the users maybe use this table during backup. How should I design the backup strategy? Can users access the table when the backup is running?
Thanks
ZYTOriginally posted by yitongzhang
Hello, everyone:
I have a 1GB table that has to run in 24X7 environment. That means the users maybe use this table during backup. How should I design the backup strategy? Can users access the table when the backup is running?
Thanks
ZYT
yes
Backup DB, but without one table
Hi,
I have a giant DB that I want to backup. One of it's table is taking a lot of space, and I don't need it in the backup, so I am searching for a way to backup everything but that table in particular.
Does any of you know of a way?
Thanks!
BTW, it's a Microsoft SQL Server 2000 - 8.00.2040 (Intel X86)Only way to do it is pretty ugly. You would need to BCP out all the data, and script all the objects in the database. This would still miss users and groups, however.|||Thanks for the quick answer man!
Then I'll change the question:
My original need is to setup a test DB server. I thought the best & easiest way to build it up was with a backup/restore. Of course, I came across this issue: my test server cannot hold all the data, and getting rid of this table was the solution.
Any bypass solution?
Thanks again,
Francisco.|||Slightly easier, then would be to generate the SQL script of all objects, and run that on the test database. After this, you can import any set of tables (and even a portion of the big table) into the test server. You will still need to create users, roles and make sure the permissions are correct, but the script will likely take care of most of that.|||There are ways to accomplish this IF you can change the production system. That isn't usually an option, so for now I'll disregard it.
I'd approach this problem by:
1) Scripting out the DDL needed to create the tables only (no constraints, no indicies, no code, no permissions, just the tables). Play this script into the currently empty test database.
2) Use either BCP or DTS to copy the data from the tables you need copied from the production database to the test database.
3) Script out the remainder of the schema (the parts you left out of the first script) from the production database, and play that script in the populated test database.
-PatPsql
I have a giant DB that I want to backup. One of it's table is taking a lot of space, and I don't need it in the backup, so I am searching for a way to backup everything but that table in particular.
Does any of you know of a way?
Thanks!
BTW, it's a Microsoft SQL Server 2000 - 8.00.2040 (Intel X86)Only way to do it is pretty ugly. You would need to BCP out all the data, and script all the objects in the database. This would still miss users and groups, however.|||Thanks for the quick answer man!
Then I'll change the question:
My original need is to setup a test DB server. I thought the best & easiest way to build it up was with a backup/restore. Of course, I came across this issue: my test server cannot hold all the data, and getting rid of this table was the solution.
Any bypass solution?
Thanks again,
Francisco.|||Slightly easier, then would be to generate the SQL script of all objects, and run that on the test database. After this, you can import any set of tables (and even a portion of the big table) into the test server. You will still need to create users, roles and make sure the permissions are correct, but the script will likely take care of most of that.|||There are ways to accomplish this IF you can change the production system. That isn't usually an option, so for now I'll disregard it.
I'd approach this problem by:
1) Scripting out the DDL needed to create the tables only (no constraints, no indicies, no code, no permissions, just the tables). Play this script into the currently empty test database.
2) Use either BCP or DTS to copy the data from the tables you need copied from the production database to the test database.
3) Script out the remainder of the schema (the parts you left out of the first script) from the production database, and play that script in the populated test database.
-PatPsql
Monday, March 19, 2012
Backup database to floppy
I am trying to back my SQL Server 2000 database (which contains one table) to
floppy disk.
I select SQL Server backup option and select destination backup to: A:
When I click OK, I see message that says 'backup in progrress', and then a
message box displays 'The backup operation has been completed successfully'.
At no time did my floppy drive light up and the data did not get backed up
to it.
I don't see where it got backed up to.
Does anyone know why it is not backing up to my floppy drive?
Thanks.
Are you performing this operation where the Server installation was made?
SQL Server takes the folders from the machine where its installed and not
that of the client.
thanks and regards
Chandra
"Ron" wrote:
> I am trying to back my SQL Server 2000 database (which contains one table) to
> floppy disk.
> I select SQL Server backup option and select destination backup to: A:
> When I click OK, I see message that says 'backup in progrress', and then a
> message box displays 'The backup operation has been completed successfully'.
> At no time did my floppy drive light up and the data did not get backed up
> to it.
> I don't see where it got backed up to.
> Does anyone know why it is not backing up to my floppy drive?
> Thanks.
|||Yes I am performing this operation where the Server installation was made.
So how do I backup to floppy?
"Chandra" wrote:
[vbcol=seagreen]
> Are you performing this operation where the Server installation was made?
> SQL Server takes the folders from the machine where its installed and not
> that of the client.
> thanks and regards
> Chandra
>
> "Ron" wrote:
|||Just see this:
http://msdn.microsoft.com/library/de...ackpt_1m0p.asp
thanks and regards
Chandra
"Ron" wrote:
[vbcol=seagreen]
> Yes I am performing this operation where the Server installation was made.
> So how do I backup to floppy?
>
> "Chandra" wrote:
|||Chandra
Those were the exact steps I already took and it didn't back up.
Just to try something different I just tried selecting schedule with todays
date & time and received the message
'SQL Server agent on target (local) is stopped. Make sure it is running
during scheduled execution of this job.'
My target is my floppy drive.
Any ideas whjat is wrong?
"Chandra" wrote:
[vbcol=seagreen]
> Just see this:
> http://msdn.microsoft.com/library/de...ackpt_1m0p.asp
> thanks and regards
> Chandra
> "Ron" wrote:
|||Ron,
Just back it up on a different drive and see whats the size of the backup
file. Is it lee that 1.44 MB
"Ron" wrote:
[vbcol=seagreen]
> Chandra
> Those were the exact steps I already took and it didn't back up.
> Just to try something different I just tried selecting schedule with todays
> date & time and received the message
> 'SQL Server agent on target (local) is stopped. Make sure it is running
> during scheduled execution of this job.'
> My target is my floppy drive.
> Any ideas whjat is wrong?
> "Chandra" wrote:
|||Add on to chandra, do the below steps:-
1. Backup the database to hard drive using below command (Execute from Query
Analyzer)
Backup Database <dbname> to disk = 'c:\dbname.bak' with init,stats=10
2. See the size of backup file, if the file size is less than 1.44 MB, copy
the file to floppy. If the file is greater than 1.44 MB then try zipping the
backup file.
If the file come down to < 1.44 MB copy the zip to floppy, else you cant
copy to floppy.
Thanks
Hari
SQL Server MVP
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:C14877A0-1792-4767-A995-AA5D467C734B@.microsoft.com...[vbcol=seagreen]
> Ron,
> Just back it up on a different drive and see whats the size of the backup
> file. Is it lee that 1.44 MB
>
> "Ron" wrote:
todays[vbcol=seagreen]
http://msdn.microsoft.com/library/de...ackpt_1m0p.asp[vbcol=seagreen]
made.[vbcol=seagreen]
was made?[vbcol=seagreen]
and not[vbcol=seagreen]
one table) to[vbcol=seagreen]
to: A:[vbcol=seagreen]
and then a[vbcol=seagreen]
successfully'.[vbcol=seagreen]
backed up[vbcol=seagreen]
|||I backed up to a folder on my hard drive and it worked fine.
The file size is 915 KB. I then copied it to my floppy drive successfully.
I don't know why I can't backup directly to floppy but at least it works in
2 steps.
Thanks.
"Chandra" wrote:
[vbcol=seagreen]
> Ron,
> Just back it up on a different drive and see whats the size of the backup
> file. Is it lee that 1.44 MB
>
> "Ron" wrote:
floppy disk.
I select SQL Server backup option and select destination backup to: A:
When I click OK, I see message that says 'backup in progrress', and then a
message box displays 'The backup operation has been completed successfully'.
At no time did my floppy drive light up and the data did not get backed up
to it.
I don't see where it got backed up to.
Does anyone know why it is not backing up to my floppy drive?
Thanks.
Are you performing this operation where the Server installation was made?
SQL Server takes the folders from the machine where its installed and not
that of the client.
thanks and regards
Chandra
"Ron" wrote:
> I am trying to back my SQL Server 2000 database (which contains one table) to
> floppy disk.
> I select SQL Server backup option and select destination backup to: A:
> When I click OK, I see message that says 'backup in progrress', and then a
> message box displays 'The backup operation has been completed successfully'.
> At no time did my floppy drive light up and the data did not get backed up
> to it.
> I don't see where it got backed up to.
> Does anyone know why it is not backing up to my floppy drive?
> Thanks.
|||Yes I am performing this operation where the Server installation was made.
So how do I backup to floppy?
"Chandra" wrote:
[vbcol=seagreen]
> Are you performing this operation where the Server installation was made?
> SQL Server takes the folders from the machine where its installed and not
> that of the client.
> thanks and regards
> Chandra
>
> "Ron" wrote:
|||Just see this:
http://msdn.microsoft.com/library/de...ackpt_1m0p.asp
thanks and regards
Chandra
"Ron" wrote:
[vbcol=seagreen]
> Yes I am performing this operation where the Server installation was made.
> So how do I backup to floppy?
>
> "Chandra" wrote:
|||Chandra
Those were the exact steps I already took and it didn't back up.
Just to try something different I just tried selecting schedule with todays
date & time and received the message
'SQL Server agent on target (local) is stopped. Make sure it is running
during scheduled execution of this job.'
My target is my floppy drive.
Any ideas whjat is wrong?
"Chandra" wrote:
[vbcol=seagreen]
> Just see this:
> http://msdn.microsoft.com/library/de...ackpt_1m0p.asp
> thanks and regards
> Chandra
> "Ron" wrote:
|||Ron,
Just back it up on a different drive and see whats the size of the backup
file. Is it lee that 1.44 MB
"Ron" wrote:
[vbcol=seagreen]
> Chandra
> Those were the exact steps I already took and it didn't back up.
> Just to try something different I just tried selecting schedule with todays
> date & time and received the message
> 'SQL Server agent on target (local) is stopped. Make sure it is running
> during scheduled execution of this job.'
> My target is my floppy drive.
> Any ideas whjat is wrong?
> "Chandra" wrote:
|||Add on to chandra, do the below steps:-
1. Backup the database to hard drive using below command (Execute from Query
Analyzer)
Backup Database <dbname> to disk = 'c:\dbname.bak' with init,stats=10
2. See the size of backup file, if the file size is less than 1.44 MB, copy
the file to floppy. If the file is greater than 1.44 MB then try zipping the
backup file.
If the file come down to < 1.44 MB copy the zip to floppy, else you cant
copy to floppy.
Thanks
Hari
SQL Server MVP
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:C14877A0-1792-4767-A995-AA5D467C734B@.microsoft.com...[vbcol=seagreen]
> Ron,
> Just back it up on a different drive and see whats the size of the backup
> file. Is it lee that 1.44 MB
>
> "Ron" wrote:
todays[vbcol=seagreen]
http://msdn.microsoft.com/library/de...ackpt_1m0p.asp[vbcol=seagreen]
made.[vbcol=seagreen]
was made?[vbcol=seagreen]
and not[vbcol=seagreen]
one table) to[vbcol=seagreen]
to: A:[vbcol=seagreen]
and then a[vbcol=seagreen]
successfully'.[vbcol=seagreen]
backed up[vbcol=seagreen]
|||I backed up to a folder on my hard drive and it worked fine.
The file size is 915 KB. I then copied it to my floppy drive successfully.
I don't know why I can't backup directly to floppy but at least it works in
2 steps.
Thanks.
"Chandra" wrote:
[vbcol=seagreen]
> Ron,
> Just back it up on a different drive and see whats the size of the backup
> file. Is it lee that 1.44 MB
>
> "Ron" wrote:
Backup database to floppy
I am trying to back my SQL Server 2000 database (which contains one table) t
o
floppy disk.
I select SQL Server backup option and select destination backup to: A:
When I click OK, I see message that says 'backup in progrress', and then a
message box displays 'The backup operation has been completed successfully'.
At no time did my floppy drive light up and the data did not get backed up
to it.
I don't see where it got backed up to.
Does anyone know why it is not backing up to my floppy drive?
Thanks.Are you performing this operation where the Server installation was made?
SQL Server takes the folders from the machine where its installed and not
that of the client.
thanks and regards
Chandra
"Ron" wrote:
> I am trying to back my SQL Server 2000 database (which contains one table)
to
> floppy disk.
> I select SQL Server backup option and select destination backup to: A:
> When I click OK, I see message that says 'backup in progrress', and then a
> message box displays 'The backup operation has been completed successfully
'.
> At no time did my floppy drive light up and the data did not get backed up
> to it.
> I don't see where it got backed up to.
> Does anyone know why it is not backing up to my floppy drive?
> Thanks.|||Yes I am performing this operation where the Server installation was made.
So how do I backup to floppy?
"Chandra" wrote:
[vbcol=seagreen]
> Are you performing this operation where the Server installation was made?
> SQL Server takes the folders from the machine where its installed and not
> that of the client.
> thanks and regards
> Chandra
>
> "Ron" wrote:
>|||Just see this:
kpt_1m0p.asp" target="_blank">http://msdn.microsoft.com/library/d...>
kpt_1m0p.asp
thanks and regards
Chandra
"Ron" wrote:
[vbcol=seagreen]
> Yes I am performing this operation where the Server installation was made.
> So how do I backup to floppy?
>
> "Chandra" wrote:
>|||Chandra
Those were the exact steps I already took and it didn't back up.
Just to try something different I just tried selecting schedule with todays
date & time and received the message
'SQL Server agent on target (local) is stopped. Make sure it is running
during scheduled execution of this job.'
My target is my floppy drive.
Any ideas whjat is wrong?
"Chandra" wrote:
[vbcol=seagreen]
> Just see this:
> ackpt_1m0p.asp" target="_blank">http://msdn.microsoft.com/library/d...
ackpt_1m0p.asp
> thanks and regards
> Chandra
> "Ron" wrote:
>|||Ron,
Just back it up on a different drive and see whats the size of the backup
file. Is it lee that 1.44 MB
"Ron" wrote:
[vbcol=seagreen]
> Chandra
> Those were the exact steps I already took and it didn't back up.
> Just to try something different I just tried selecting schedule with today
s
> date & time and received the message
> 'SQL Server agent on target (local) is stopped. Make sure it is running
> during scheduled execution of this job.'
> My target is my floppy drive.
> Any ideas whjat is wrong?
> "Chandra" wrote:
>|||Add on to chandra, do the below steps:-
1. Backup the database to hard drive using below command (Execute from Query
Analyzer)
Backup Database <dbname> to disk = 'c:\dbname.bak' with init,stats=10
2. See the size of backup file, if the file size is less than 1.44 MB, copy
the file to floppy. If the file is greater than 1.44 MB then try zipping the
backup file.
If the file come down to < 1.44 MB copy the zip to floppy, else you cant
copy to floppy.
Thanks
Hari
SQL Server MVP
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:C14877A0-1792-4767-A995-AA5D467C734B@.microsoft.com...[vbcol=seagreen]
> Ron,
> Just back it up on a different drive and see whats the size of the backup
> file. Is it lee that 1.44 MB
>
> "Ron" wrote:
>
todays[vbcol=seagreen]
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpt_1m0p.asp[/ur
l][vbcol=seagreen]
made.[vbcol=seagreen]
was made?[vbcol=seagreen]
and not[vbcol=seagreen]
one table) to[vbcol=seagreen]
to: A:[vbcol=seagreen]
and then a[vbcol=seagreen]
successfully'.[vbcol=seagreen]
backed up[vbcol=seagreen]|||I backed up to a folder on my hard drive and it worked fine.
The file size is 915 KB. I then copied it to my floppy drive successfully.
I don't know why I can't backup directly to floppy but at least it works in
2 steps.
Thanks.
"Chandra" wrote:
[vbcol=seagreen]
> Ron,
> Just back it up on a different drive and see whats the size of the backup
> file. Is it lee that 1.44 MB
>
> "Ron" wrote:
>
o
floppy disk.
I select SQL Server backup option and select destination backup to: A:
When I click OK, I see message that says 'backup in progrress', and then a
message box displays 'The backup operation has been completed successfully'.
At no time did my floppy drive light up and the data did not get backed up
to it.
I don't see where it got backed up to.
Does anyone know why it is not backing up to my floppy drive?
Thanks.Are you performing this operation where the Server installation was made?
SQL Server takes the folders from the machine where its installed and not
that of the client.
thanks and regards
Chandra
"Ron" wrote:
> I am trying to back my SQL Server 2000 database (which contains one table)
to
> floppy disk.
> I select SQL Server backup option and select destination backup to: A:
> When I click OK, I see message that says 'backup in progrress', and then a
> message box displays 'The backup operation has been completed successfully
'.
> At no time did my floppy drive light up and the data did not get backed up
> to it.
> I don't see where it got backed up to.
> Does anyone know why it is not backing up to my floppy drive?
> Thanks.|||Yes I am performing this operation where the Server installation was made.
So how do I backup to floppy?
"Chandra" wrote:
[vbcol=seagreen]
> Are you performing this operation where the Server installation was made?
> SQL Server takes the folders from the machine where its installed and not
> that of the client.
> thanks and regards
> Chandra
>
> "Ron" wrote:
>|||Just see this:
kpt_1m0p.asp" target="_blank">http://msdn.microsoft.com/library/d...>
kpt_1m0p.asp
thanks and regards
Chandra
"Ron" wrote:
[vbcol=seagreen]
> Yes I am performing this operation where the Server installation was made.
> So how do I backup to floppy?
>
> "Chandra" wrote:
>|||Chandra
Those were the exact steps I already took and it didn't back up.
Just to try something different I just tried selecting schedule with todays
date & time and received the message
'SQL Server agent on target (local) is stopped. Make sure it is running
during scheduled execution of this job.'
My target is my floppy drive.
Any ideas whjat is wrong?
"Chandra" wrote:
[vbcol=seagreen]
> Just see this:
> ackpt_1m0p.asp" target="_blank">http://msdn.microsoft.com/library/d...
ackpt_1m0p.asp
> thanks and regards
> Chandra
> "Ron" wrote:
>|||Ron,
Just back it up on a different drive and see whats the size of the backup
file. Is it lee that 1.44 MB
"Ron" wrote:
[vbcol=seagreen]
> Chandra
> Those were the exact steps I already took and it didn't back up.
> Just to try something different I just tried selecting schedule with today
s
> date & time and received the message
> 'SQL Server agent on target (local) is stopped. Make sure it is running
> during scheduled execution of this job.'
> My target is my floppy drive.
> Any ideas whjat is wrong?
> "Chandra" wrote:
>|||Add on to chandra, do the below steps:-
1. Backup the database to hard drive using below command (Execute from Query
Analyzer)
Backup Database <dbname> to disk = 'c:\dbname.bak' with init,stats=10
2. See the size of backup file, if the file size is less than 1.44 MB, copy
the file to floppy. If the file is greater than 1.44 MB then try zipping the
backup file.
If the file come down to < 1.44 MB copy the zip to floppy, else you cant
copy to floppy.
Thanks
Hari
SQL Server MVP
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:C14877A0-1792-4767-A995-AA5D467C734B@.microsoft.com...[vbcol=seagreen]
> Ron,
> Just back it up on a different drive and see whats the size of the backup
> file. Is it lee that 1.44 MB
>
> "Ron" wrote:
>
todays[vbcol=seagreen]
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpt_1m0p.asp[/ur
l][vbcol=seagreen]
made.[vbcol=seagreen]
was made?[vbcol=seagreen]
and not[vbcol=seagreen]
one table) to[vbcol=seagreen]
to: A:[vbcol=seagreen]
and then a[vbcol=seagreen]
successfully'.[vbcol=seagreen]
backed up[vbcol=seagreen]|||I backed up to a folder on my hard drive and it worked fine.
The file size is 915 KB. I then copied it to my floppy drive successfully.
I don't know why I can't backup directly to floppy but at least it works in
2 steps.
Thanks.
"Chandra" wrote:
[vbcol=seagreen]
> Ron,
> Just back it up on a different drive and see whats the size of the backup
> file. Is it lee that 1.44 MB
>
> "Ron" wrote:
>
Backup database to floppy
I am trying to back my SQL Server 2000 database (which contains one table) to
floppy disk.
I select SQL Server backup option and select destination backup to: A:
When I click OK, I see message that says 'backup in progrress', and then a
message box displays 'The backup operation has been completed successfully'.
At no time did my floppy drive light up and the data did not get backed up
to it.
I don't see where it got backed up to.
Does anyone know why it is not backing up to my floppy drive?
Thanks.Are you performing this operation where the Server installation was made?
SQL Server takes the folders from the machine where its installed and not
that of the client.
thanks and regards
Chandra
"Ron" wrote:
> I am trying to back my SQL Server 2000 database (which contains one table) to
> floppy disk.
> I select SQL Server backup option and select destination backup to: A:
> When I click OK, I see message that says 'backup in progrress', and then a
> message box displays 'The backup operation has been completed successfully'.
> At no time did my floppy drive light up and the data did not get backed up
> to it.
> I don't see where it got backed up to.
> Does anyone know why it is not backing up to my floppy drive?
> Thanks.|||Yes I am performing this operation where the Server installation was made.
So how do I backup to floppy?
"Chandra" wrote:
> Are you performing this operation where the Server installation was made?
> SQL Server takes the folders from the machine where its installed and not
> that of the client.
> thanks and regards
> Chandra
>
> "Ron" wrote:
> > I am trying to back my SQL Server 2000 database (which contains one table) to
> > floppy disk.
> > I select SQL Server backup option and select destination backup to: A:
> > When I click OK, I see message that says 'backup in progrress', and then a
> > message box displays 'The backup operation has been completed successfully'.
> > At no time did my floppy drive light up and the data did not get backed up
> > to it.
> > I don't see where it got backed up to.
> > Does anyone know why it is not backing up to my floppy drive?
> > Thanks.|||Just see this
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpt_1m0p.asp
thanks and regards
Chandra
"Ron" wrote:
> Yes I am performing this operation where the Server installation was made.
> So how do I backup to floppy?
>
> "Chandra" wrote:
> >
> > Are you performing this operation where the Server installation was made?
> > SQL Server takes the folders from the machine where its installed and not
> > that of the client.
> >
> > thanks and regards
> > Chandra
> >
> >
> >
> > "Ron" wrote:
> >
> > > I am trying to back my SQL Server 2000 database (which contains one table) to
> > > floppy disk.
> > > I select SQL Server backup option and select destination backup to: A:
> > > When I click OK, I see message that says 'backup in progrress', and then a
> > > message box displays 'The backup operation has been completed successfully'.
> > > At no time did my floppy drive light up and the data did not get backed up
> > > to it.
> > > I don't see where it got backed up to.
> > > Does anyone know why it is not backing up to my floppy drive?
> > > Thanks.|||Chandra
Those were the exact steps I already took and it didn't back up.
Just to try something different I just tried selecting schedule with todays
date & time and received the message
'SQL Server agent on target (local) is stopped. Make sure it is running
during scheduled execution of this job.'
My target is my floppy drive.
Any ideas whjat is wrong?
"Chandra" wrote:
> Just see this:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpt_1m0p.asp
> thanks and regards
> Chandra
> "Ron" wrote:
> > Yes I am performing this operation where the Server installation was made.
> > So how do I backup to floppy?
> >
> >
> > "Chandra" wrote:
> >
> > >
> > > Are you performing this operation where the Server installation was made?
> > > SQL Server takes the folders from the machine where its installed and not
> > > that of the client.
> > >
> > > thanks and regards
> > > Chandra
> > >
> > >
> > >
> > > "Ron" wrote:
> > >
> > > > I am trying to back my SQL Server 2000 database (which contains one table) to
> > > > floppy disk.
> > > > I select SQL Server backup option and select destination backup to: A:
> > > > When I click OK, I see message that says 'backup in progrress', and then a
> > > > message box displays 'The backup operation has been completed successfully'.
> > > > At no time did my floppy drive light up and the data did not get backed up
> > > > to it.
> > > > I don't see where it got backed up to.
> > > > Does anyone know why it is not backing up to my floppy drive?
> > > > Thanks.|||Ron,
Just back it up on a different drive and see whats the size of the backup
file. Is it lee that 1.44 MB
"Ron" wrote:
> Chandra
> Those were the exact steps I already took and it didn't back up.
> Just to try something different I just tried selecting schedule with todays
> date & time and received the message
> 'SQL Server agent on target (local) is stopped. Make sure it is running
> during scheduled execution of this job.'
> My target is my floppy drive.
> Any ideas whjat is wrong?
> "Chandra" wrote:
> >
> > Just see this:
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpt_1m0p.asp
> >
> > thanks and regards
> > Chandra
> >
> > "Ron" wrote:
> >
> > > Yes I am performing this operation where the Server installation was made.
> > > So how do I backup to floppy?
> > >
> > >
> > > "Chandra" wrote:
> > >
> > > >
> > > > Are you performing this operation where the Server installation was made?
> > > > SQL Server takes the folders from the machine where its installed and not
> > > > that of the client.
> > > >
> > > > thanks and regards
> > > > Chandra
> > > >
> > > >
> > > >
> > > > "Ron" wrote:
> > > >
> > > > > I am trying to back my SQL Server 2000 database (which contains one table) to
> > > > > floppy disk.
> > > > > I select SQL Server backup option and select destination backup to: A:
> > > > > When I click OK, I see message that says 'backup in progrress', and then a
> > > > > message box displays 'The backup operation has been completed successfully'.
> > > > > At no time did my floppy drive light up and the data did not get backed up
> > > > > to it.
> > > > > I don't see where it got backed up to.
> > > > > Does anyone know why it is not backing up to my floppy drive?
> > > > > Thanks.|||Add on to chandra, do the below steps:-
1. Backup the database to hard drive using below command (Execute from Query
Analyzer)
Backup Database <dbname> to disk = 'c:\dbname.bak' with init,stats=10
2. See the size of backup file, if the file size is less than 1.44 MB, copy
the file to floppy. If the file is greater than 1.44 MB then try zipping the
backup file.
If the file come down to < 1.44 MB copy the zip to floppy, else you cant
copy to floppy.
Thanks
Hari
SQL Server MVP
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:C14877A0-1792-4767-A995-AA5D467C734B@.microsoft.com...
> Ron,
> Just back it up on a different drive and see whats the size of the backup
> file. Is it lee that 1.44 MB
>
> "Ron" wrote:
> > Chandra
> > Those were the exact steps I already took and it didn't back up.
> > Just to try something different I just tried selecting schedule with
todays
> > date & time and received the message
> > 'SQL Server agent on target (local) is stopped. Make sure it is running
> > during scheduled execution of this job.'
> > My target is my floppy drive.
> > Any ideas whjat is wrong?
> >
> > "Chandra" wrote:
> >
> > >
> > > Just see this:
> > >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpt_1m0p.asp
> > >
> > > thanks and regards
> > > Chandra
> > >
> > > "Ron" wrote:
> > >
> > > > Yes I am performing this operation where the Server installation was
made.
> > > > So how do I backup to floppy?
> > > >
> > > >
> > > > "Chandra" wrote:
> > > >
> > > > >
> > > > > Are you performing this operation where the Server installation
was made?
> > > > > SQL Server takes the folders from the machine where its installed
and not
> > > > > that of the client.
> > > > >
> > > > > thanks and regards
> > > > > Chandra
> > > > >
> > > > >
> > > > >
> > > > > "Ron" wrote:
> > > > >
> > > > > > I am trying to back my SQL Server 2000 database (which contains
one table) to
> > > > > > floppy disk.
> > > > > > I select SQL Server backup option and select destination backup
to: A:
> > > > > > When I click OK, I see message that says 'backup in progrress',
and then a
> > > > > > message box displays 'The backup operation has been completed
successfully'.
> > > > > > At no time did my floppy drive light up and the data did not get
backed up
> > > > > > to it.
> > > > > > I don't see where it got backed up to.
> > > > > > Does anyone know why it is not backing up to my floppy drive?
> > > > > > Thanks.|||I backed up to a folder on my hard drive and it worked fine.
The file size is 915 KB. I then copied it to my floppy drive successfully.
I don't know why I can't backup directly to floppy but at least it works in
2 steps.
Thanks.
"Chandra" wrote:
> Ron,
> Just back it up on a different drive and see whats the size of the backup
> file. Is it lee that 1.44 MB
>
> "Ron" wrote:
> > Chandra
> > Those were the exact steps I already took and it didn't back up.
> > Just to try something different I just tried selecting schedule with todays
> > date & time and received the message
> > 'SQL Server agent on target (local) is stopped. Make sure it is running
> > during scheduled execution of this job.'
> > My target is my floppy drive.
> > Any ideas whjat is wrong?
> >
> > "Chandra" wrote:
> >
> > >
> > > Just see this:
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpt_1m0p.asp
> > >
> > > thanks and regards
> > > Chandra
> > >
> > > "Ron" wrote:
> > >
> > > > Yes I am performing this operation where the Server installation was made.
> > > > So how do I backup to floppy?
> > > >
> > > >
> > > > "Chandra" wrote:
> > > >
> > > > >
> > > > > Are you performing this operation where the Server installation was made?
> > > > > SQL Server takes the folders from the machine where its installed and not
> > > > > that of the client.
> > > > >
> > > > > thanks and regards
> > > > > Chandra
> > > > >
> > > > >
> > > > >
> > > > > "Ron" wrote:
> > > > >
> > > > > > I am trying to back my SQL Server 2000 database (which contains one table) to
> > > > > > floppy disk.
> > > > > > I select SQL Server backup option and select destination backup to: A:
> > > > > > When I click OK, I see message that says 'backup in progrress', and then a
> > > > > > message box displays 'The backup operation has been completed successfully'.
> > > > > > At no time did my floppy drive light up and the data did not get backed up
> > > > > > to it.
> > > > > > I don't see where it got backed up to.
> > > > > > Does anyone know why it is not backing up to my floppy drive?
> > > > > > Thanks.
floppy disk.
I select SQL Server backup option and select destination backup to: A:
When I click OK, I see message that says 'backup in progrress', and then a
message box displays 'The backup operation has been completed successfully'.
At no time did my floppy drive light up and the data did not get backed up
to it.
I don't see where it got backed up to.
Does anyone know why it is not backing up to my floppy drive?
Thanks.Are you performing this operation where the Server installation was made?
SQL Server takes the folders from the machine where its installed and not
that of the client.
thanks and regards
Chandra
"Ron" wrote:
> I am trying to back my SQL Server 2000 database (which contains one table) to
> floppy disk.
> I select SQL Server backup option and select destination backup to: A:
> When I click OK, I see message that says 'backup in progrress', and then a
> message box displays 'The backup operation has been completed successfully'.
> At no time did my floppy drive light up and the data did not get backed up
> to it.
> I don't see where it got backed up to.
> Does anyone know why it is not backing up to my floppy drive?
> Thanks.|||Yes I am performing this operation where the Server installation was made.
So how do I backup to floppy?
"Chandra" wrote:
> Are you performing this operation where the Server installation was made?
> SQL Server takes the folders from the machine where its installed and not
> that of the client.
> thanks and regards
> Chandra
>
> "Ron" wrote:
> > I am trying to back my SQL Server 2000 database (which contains one table) to
> > floppy disk.
> > I select SQL Server backup option and select destination backup to: A:
> > When I click OK, I see message that says 'backup in progrress', and then a
> > message box displays 'The backup operation has been completed successfully'.
> > At no time did my floppy drive light up and the data did not get backed up
> > to it.
> > I don't see where it got backed up to.
> > Does anyone know why it is not backing up to my floppy drive?
> > Thanks.|||Just see this
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpt_1m0p.asp
thanks and regards
Chandra
"Ron" wrote:
> Yes I am performing this operation where the Server installation was made.
> So how do I backup to floppy?
>
> "Chandra" wrote:
> >
> > Are you performing this operation where the Server installation was made?
> > SQL Server takes the folders from the machine where its installed and not
> > that of the client.
> >
> > thanks and regards
> > Chandra
> >
> >
> >
> > "Ron" wrote:
> >
> > > I am trying to back my SQL Server 2000 database (which contains one table) to
> > > floppy disk.
> > > I select SQL Server backup option and select destination backup to: A:
> > > When I click OK, I see message that says 'backup in progrress', and then a
> > > message box displays 'The backup operation has been completed successfully'.
> > > At no time did my floppy drive light up and the data did not get backed up
> > > to it.
> > > I don't see where it got backed up to.
> > > Does anyone know why it is not backing up to my floppy drive?
> > > Thanks.|||Chandra
Those were the exact steps I already took and it didn't back up.
Just to try something different I just tried selecting schedule with todays
date & time and received the message
'SQL Server agent on target (local) is stopped. Make sure it is running
during scheduled execution of this job.'
My target is my floppy drive.
Any ideas whjat is wrong?
"Chandra" wrote:
> Just see this:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpt_1m0p.asp
> thanks and regards
> Chandra
> "Ron" wrote:
> > Yes I am performing this operation where the Server installation was made.
> > So how do I backup to floppy?
> >
> >
> > "Chandra" wrote:
> >
> > >
> > > Are you performing this operation where the Server installation was made?
> > > SQL Server takes the folders from the machine where its installed and not
> > > that of the client.
> > >
> > > thanks and regards
> > > Chandra
> > >
> > >
> > >
> > > "Ron" wrote:
> > >
> > > > I am trying to back my SQL Server 2000 database (which contains one table) to
> > > > floppy disk.
> > > > I select SQL Server backup option and select destination backup to: A:
> > > > When I click OK, I see message that says 'backup in progrress', and then a
> > > > message box displays 'The backup operation has been completed successfully'.
> > > > At no time did my floppy drive light up and the data did not get backed up
> > > > to it.
> > > > I don't see where it got backed up to.
> > > > Does anyone know why it is not backing up to my floppy drive?
> > > > Thanks.|||Ron,
Just back it up on a different drive and see whats the size of the backup
file. Is it lee that 1.44 MB
"Ron" wrote:
> Chandra
> Those were the exact steps I already took and it didn't back up.
> Just to try something different I just tried selecting schedule with todays
> date & time and received the message
> 'SQL Server agent on target (local) is stopped. Make sure it is running
> during scheduled execution of this job.'
> My target is my floppy drive.
> Any ideas whjat is wrong?
> "Chandra" wrote:
> >
> > Just see this:
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpt_1m0p.asp
> >
> > thanks and regards
> > Chandra
> >
> > "Ron" wrote:
> >
> > > Yes I am performing this operation where the Server installation was made.
> > > So how do I backup to floppy?
> > >
> > >
> > > "Chandra" wrote:
> > >
> > > >
> > > > Are you performing this operation where the Server installation was made?
> > > > SQL Server takes the folders from the machine where its installed and not
> > > > that of the client.
> > > >
> > > > thanks and regards
> > > > Chandra
> > > >
> > > >
> > > >
> > > > "Ron" wrote:
> > > >
> > > > > I am trying to back my SQL Server 2000 database (which contains one table) to
> > > > > floppy disk.
> > > > > I select SQL Server backup option and select destination backup to: A:
> > > > > When I click OK, I see message that says 'backup in progrress', and then a
> > > > > message box displays 'The backup operation has been completed successfully'.
> > > > > At no time did my floppy drive light up and the data did not get backed up
> > > > > to it.
> > > > > I don't see where it got backed up to.
> > > > > Does anyone know why it is not backing up to my floppy drive?
> > > > > Thanks.|||Add on to chandra, do the below steps:-
1. Backup the database to hard drive using below command (Execute from Query
Analyzer)
Backup Database <dbname> to disk = 'c:\dbname.bak' with init,stats=10
2. See the size of backup file, if the file size is less than 1.44 MB, copy
the file to floppy. If the file is greater than 1.44 MB then try zipping the
backup file.
If the file come down to < 1.44 MB copy the zip to floppy, else you cant
copy to floppy.
Thanks
Hari
SQL Server MVP
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:C14877A0-1792-4767-A995-AA5D467C734B@.microsoft.com...
> Ron,
> Just back it up on a different drive and see whats the size of the backup
> file. Is it lee that 1.44 MB
>
> "Ron" wrote:
> > Chandra
> > Those were the exact steps I already took and it didn't back up.
> > Just to try something different I just tried selecting schedule with
todays
> > date & time and received the message
> > 'SQL Server agent on target (local) is stopped. Make sure it is running
> > during scheduled execution of this job.'
> > My target is my floppy drive.
> > Any ideas whjat is wrong?
> >
> > "Chandra" wrote:
> >
> > >
> > > Just see this:
> > >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpt_1m0p.asp
> > >
> > > thanks and regards
> > > Chandra
> > >
> > > "Ron" wrote:
> > >
> > > > Yes I am performing this operation where the Server installation was
made.
> > > > So how do I backup to floppy?
> > > >
> > > >
> > > > "Chandra" wrote:
> > > >
> > > > >
> > > > > Are you performing this operation where the Server installation
was made?
> > > > > SQL Server takes the folders from the machine where its installed
and not
> > > > > that of the client.
> > > > >
> > > > > thanks and regards
> > > > > Chandra
> > > > >
> > > > >
> > > > >
> > > > > "Ron" wrote:
> > > > >
> > > > > > I am trying to back my SQL Server 2000 database (which contains
one table) to
> > > > > > floppy disk.
> > > > > > I select SQL Server backup option and select destination backup
to: A:
> > > > > > When I click OK, I see message that says 'backup in progrress',
and then a
> > > > > > message box displays 'The backup operation has been completed
successfully'.
> > > > > > At no time did my floppy drive light up and the data did not get
backed up
> > > > > > to it.
> > > > > > I don't see where it got backed up to.
> > > > > > Does anyone know why it is not backing up to my floppy drive?
> > > > > > Thanks.|||I backed up to a folder on my hard drive and it worked fine.
The file size is 915 KB. I then copied it to my floppy drive successfully.
I don't know why I can't backup directly to floppy but at least it works in
2 steps.
Thanks.
"Chandra" wrote:
> Ron,
> Just back it up on a different drive and see whats the size of the backup
> file. Is it lee that 1.44 MB
>
> "Ron" wrote:
> > Chandra
> > Those were the exact steps I already took and it didn't back up.
> > Just to try something different I just tried selecting schedule with todays
> > date & time and received the message
> > 'SQL Server agent on target (local) is stopped. Make sure it is running
> > during scheduled execution of this job.'
> > My target is my floppy drive.
> > Any ideas whjat is wrong?
> >
> > "Chandra" wrote:
> >
> > >
> > > Just see this:
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpt_1m0p.asp
> > >
> > > thanks and regards
> > > Chandra
> > >
> > > "Ron" wrote:
> > >
> > > > Yes I am performing this operation where the Server installation was made.
> > > > So how do I backup to floppy?
> > > >
> > > >
> > > > "Chandra" wrote:
> > > >
> > > > >
> > > > > Are you performing this operation where the Server installation was made?
> > > > > SQL Server takes the folders from the machine where its installed and not
> > > > > that of the client.
> > > > >
> > > > > thanks and regards
> > > > > Chandra
> > > > >
> > > > >
> > > > >
> > > > > "Ron" wrote:
> > > > >
> > > > > > I am trying to back my SQL Server 2000 database (which contains one table) to
> > > > > > floppy disk.
> > > > > > I select SQL Server backup option and select destination backup to: A:
> > > > > > When I click OK, I see message that says 'backup in progrress', and then a
> > > > > > message box displays 'The backup operation has been completed successfully'.
> > > > > > At no time did my floppy drive light up and the data did not get backed up
> > > > > > to it.
> > > > > > I don't see where it got backed up to.
> > > > > > Does anyone know why it is not backing up to my floppy drive?
> > > > > > Thanks.
Backup database to CSV files
Hi
What is the easiest option for Exporting ALL of my SQL Server tables into a CSV file (either separate CSV files for each table or one big file with all the table columns and data) ?
I just want to Backup my SQL Server database like we backup MySQL database using phpMyAdmin. Unfortunately, my SQL hosting company does not allow backups for free.
Thanks for help
Do you have a remote access via the SQL Management Studio?
Also, FREE backup can be provided by any hosting company nowadays.
Regards
Sunday, March 11, 2012
Backup database design
How do I export/create a script into a file with create table...index...
views etc for an existing database ?
Hi,
script the database using the tools from either Enterprise Manager or
Managment Studio (you did not specify the Sl Server version). They have
a task to script out the data, in multiple steps can be defined which
objects should be select for the script creation.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
views etc for an existing database ?
Hi,
script the database using the tools from either Enterprise Manager or
Managment Studio (you did not specify the Sl Server version). They have
a task to script out the data, in multiple steps can be defined which
objects should be select for the script creation.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Backup database design
How do I export/create a script into a file with create table...index...
views etc for an existing database ?Hi,
script the database using the tools from either Enterprise Manager or
Managment Studio (you did not specify the Sl Server version). They have
a task to script out the data, in multiple steps can be defined which
objects should be select for the script creation.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
views etc for an existing database ?Hi,
script the database using the tools from either Enterprise Manager or
Managment Studio (you did not specify the Sl Server version). They have
a task to script out the data, in multiple steps can be defined which
objects should be select for the script creation.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
Thursday, March 8, 2012
Backup Contains .mdf .ldf and .ndf dont want .ndf
Hello,
I have a database(.mdf and .ldf) with a large table that is attached to
its own filegroup(.ndf). I have created a full database backup and used
Restore FilelistOnly From Disk = 'C:\MSSQL\Data\Backup\Contact.bak'
I see 3 files
contact.mdf
contact.ldf
cont.ndf
I restored just the .mdf and .ldf to another database and when I look at
the properties and goto file groups it shows that the filegroup is still
there too. How is the ndf a part of this database when I never restored
the .ndf
I wan to restore only .mdf and .ldf and leave the .ndf behind is this
possible and how. Thanks for all HELP!
*** Sent via Developersdex http://www.examnotes.net ***First, if you don't supply a "WITH MOVE" clause for the .ndf file, it will
use the original file path from where the backup was created. Second, if
you do a full database restore, you must restore all the files. You cannot
omit any files. You can delete the file/filegroup after the restore is
completed.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Sean John" <sj@.aol.com> wrote in message
news:eoNsFaTHGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a database(.mdf and .ldf) with a large table that is attached to
> its own filegroup(.ndf). I have created a full database backup and used
> Restore FilelistOnly From Disk = 'C:\MSSQL\Data\Backup\Contact.bak'
> I see 3 files
> contact.mdf
> contact.ldf
> cont.ndf
> I restored just the .mdf and .ldf to another database and when I look at
> the properties and goto file groups it shows that the filegroup is still
> there too. How is the ndf a part of this database when I never restored
> the .ndf
> I wan to restore only .mdf and .ldf and leave the .ndf behind is this
> possible and how. Thanks for all HELP!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||When trying to delete file it says that
The file 'Contact'cannot be removed because it is not empty.
Is there a way to backup only .mdf or .ldf or restore just .mdf or .ldf
and no .ndf?
*** Sent via Developersdex http://www.examnotes.net ***|||Have a look in BOL under DBCC SHRINKFILE specifically the EMPTYFILE option
and then ALTER DATABASE specifically the REMOVE FILE option.
Andrew J. Kelly SQL MVP
"Sean John" <sj@.aol.com> wrote in message
news:O3$1kXUHGHA.1424@.TK2MSFTNGP12.phx.gbl...
> When trying to delete file it says that
> The file 'Contact'cannot be removed because it is not empty.
> Is there a way to backup only .mdf or .ldf or restore just .mdf or .ldf
> and no .ndf?
>
> *** Sent via Developersdex http://www.examnotes.net ***|||You have to delete all user objects from the file.filegroup. Then folow
Andrew's instructions.
Backups and restores always require a full database to work with. You can
do a filegroup backup, but it must be restored to a full database. The
short answer is no, you cannot transfer part of a database using backup and
restore. The tools simply do not work that way.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Sean John" <sj@.aol.com> wrote in message
news:O3$1kXUHGHA.1424@.TK2MSFTNGP12.phx.gbl...
> When trying to delete file it says that
> The file 'Contact'cannot be removed because it is not empty.
> Is there a way to backup only .mdf or .ldf or restore just .mdf or .ldf
> and no .ndf?
>
> *** Sent via Developersdex http://www.examnotes.net ***
I have a database(.mdf and .ldf) with a large table that is attached to
its own filegroup(.ndf). I have created a full database backup and used
Restore FilelistOnly From Disk = 'C:\MSSQL\Data\Backup\Contact.bak'
I see 3 files
contact.mdf
contact.ldf
cont.ndf
I restored just the .mdf and .ldf to another database and when I look at
the properties and goto file groups it shows that the filegroup is still
there too. How is the ndf a part of this database when I never restored
the .ndf
I wan to restore only .mdf and .ldf and leave the .ndf behind is this
possible and how. Thanks for all HELP!
*** Sent via Developersdex http://www.examnotes.net ***First, if you don't supply a "WITH MOVE" clause for the .ndf file, it will
use the original file path from where the backup was created. Second, if
you do a full database restore, you must restore all the files. You cannot
omit any files. You can delete the file/filegroup after the restore is
completed.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Sean John" <sj@.aol.com> wrote in message
news:eoNsFaTHGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a database(.mdf and .ldf) with a large table that is attached to
> its own filegroup(.ndf). I have created a full database backup and used
> Restore FilelistOnly From Disk = 'C:\MSSQL\Data\Backup\Contact.bak'
> I see 3 files
> contact.mdf
> contact.ldf
> cont.ndf
> I restored just the .mdf and .ldf to another database and when I look at
> the properties and goto file groups it shows that the filegroup is still
> there too. How is the ndf a part of this database when I never restored
> the .ndf
> I wan to restore only .mdf and .ldf and leave the .ndf behind is this
> possible and how. Thanks for all HELP!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||When trying to delete file it says that
The file 'Contact'cannot be removed because it is not empty.
Is there a way to backup only .mdf or .ldf or restore just .mdf or .ldf
and no .ndf?
*** Sent via Developersdex http://www.examnotes.net ***|||Have a look in BOL under DBCC SHRINKFILE specifically the EMPTYFILE option
and then ALTER DATABASE specifically the REMOVE FILE option.
Andrew J. Kelly SQL MVP
"Sean John" <sj@.aol.com> wrote in message
news:O3$1kXUHGHA.1424@.TK2MSFTNGP12.phx.gbl...
> When trying to delete file it says that
> The file 'Contact'cannot be removed because it is not empty.
> Is there a way to backup only .mdf or .ldf or restore just .mdf or .ldf
> and no .ndf?
>
> *** Sent via Developersdex http://www.examnotes.net ***|||You have to delete all user objects from the file.filegroup. Then folow
Andrew's instructions.
Backups and restores always require a full database to work with. You can
do a filegroup backup, but it must be restored to a full database. The
short answer is no, you cannot transfer part of a database using backup and
restore. The tools simply do not work that way.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Sean John" <sj@.aol.com> wrote in message
news:O3$1kXUHGHA.1424@.TK2MSFTNGP12.phx.gbl...
> When trying to delete file it says that
> The file 'Contact'cannot be removed because it is not empty.
> Is there a way to backup only .mdf or .ldf or restore just .mdf or .ldf
> and no .ndf?
>
> *** Sent via Developersdex http://www.examnotes.net ***
Saturday, February 25, 2012
backup and resore encrypted data
I have two databases, one production and other a production copy.
One column of the production table is encrypted by the following syntax.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password!'
CREATE CERTIFICATE cert_demo
WITH SUBJECT = 'encryption demo'
CREATE SYMMETRIC KEY symkey_demo
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE cert_demo
--
I have stored procedure to add records to a production table like this.
ALTER Procedure AddRecord
@.id int,
@.var1 varchar(100)
AS
BEGIN
DECLARE @.var2 varbinary(256)
OPEN SYMMETRIC KEY symkey_demo
DECRYPTION BY CERTIFICATE cert_demo
SET @.var2 = EncryptByKey(
key_guid( 'symkey_demo' ),
@.var1 )
insert into dbo.Test(id,Data,EData)
values(@.id,@.var1,@.var2)
CLOSE SYMMETRIC KEY symkey_demo
END
Now I need to decript the data in the production copy database. So I had export production data to the production copy database and also generates the same key and cerificate. When i tried to decrypt the data, no results has returned.
Create procedure ViewData
@.id int
AS
BEGIN
DECLARE @.var1 varbinary(256)
DECLARE @.var2 varchar(100)
OPEN SYMMETRIC KEY symkey_demo
DECRYPTION BY CERTIFICATE cert_demo
select @.var1=EData from Test where id=@.id
Set @.var2 = convert( varchar(100), DecryptByKey( @.var1))
print @.var2
END
It doesn't print the enctypted data. Please suggest what goes wrong.
The certificate protecting the key encrypting your data is protected by the DB master key (DBMK). In the server where the DBMK was created, the DBMK is protected by the password and by the service master kit (SMK). The SMK protection allows to use the DBMK automatically (i.e. without the need to open the DBMK explicitly).
When you move the DB to a different server, the protection by the SMK is no longer valid, and you need to manually open the DBMK and encrypt it with the current SMK. This is a one-time operation.
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<password>>'
Go
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Go
For more detailed information go to BOL:
http://msdn2.microsoft.com/en-us/library/ms174433.aspx
http://msdn2.microsoft.com/en-us/library/ms186937.aspx
Thanks,
-Raul Garcia
SDE/T
SQL Server Engine
|||Thanks Raul.
I had done two things.
1.The service master key doesn't working. So I have to manually open the DBMK and encrypt with the current SMK.
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password!'
Go
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
2. Again the symmetric key doesn't work.
Because keys cannot be individually backed up and restored, there is no direct way of moving a key from one database to another. However, by specifying the same values for the ALGORITHM, KEY_SOURCE, and IDENTITY_VALUE clauses of CREATE SYMMETRIC KEY, the same key can be generated on different databases.
so I have to change the symmetric key generation code to,
CREATE SYMMETRIC KEY symkey_demo
WITH ALGORITHM = TRIPLE_DES,
identity_value = 'Test Data encryption key',
key_source = 'test key'
ENCRYPTION BY CERTIFICATE cert_demo
Now I backed up the production database and restore it in a different server. I followed the stpe 1 and it works. Now I am able to decrypt the data in the production copy database.
Thanks
Amit
|||I see, when you described a copy of the datbase I assumed (incorrectly) that you were referring to a copy based on a backup.
Thanks a lot for letting us know, and let us know if you have further questions or comments.
-Raul Garcia
SDE/T
SQL Server Engine
backup and resore encrypted data
I have two databases, one production and other a production copy.
One column of the production table is encrypted by the following syntax.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password!'
CREATE CERTIFICATE cert_demo
WITH SUBJECT = 'encryption demo'
CREATE SYMMETRIC KEY symkey_demo
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE cert_demo
--
I have stored procedure to add records to a production table like this.
ALTER Procedure AddRecord
@.id int,
@.var1 varchar(100)
AS
BEGIN
DECLARE @.var2 varbinary(256)
OPEN SYMMETRIC KEY symkey_demo
DECRYPTION BY CERTIFICATE cert_demo
SET @.var2 = EncryptByKey(
key_guid( 'symkey_demo' ),
@.var1 )
insert into dbo.Test(id,Data,EData)
values(@.id,@.var1,@.var2)
CLOSE SYMMETRIC KEY symkey_demo
END
Now I need to decript the data in the production copy database. So I had export production data to the production copy database and also generates the same key and cerificate. When i tried to decrypt the data, no results has returned.
Create procedure ViewData
@.id int
AS
BEGIN
DECLARE @.var1 varbinary(256)
DECLARE @.var2 varchar(100)
OPEN SYMMETRIC KEY symkey_demo
DECRYPTION BY CERTIFICATE cert_demo
select @.var1=EData from Test where id=@.id
Set @.var2 = convert( varchar(100), DecryptByKey( @.var1))
print @.var2
END
It doesn't print the enctypted data. Please suggest what goes wrong.
The certificate protecting the key encrypting your data is protected by the DB master key (DBMK). In the server where the DBMK was created, the DBMK is protected by the password and by the service master kit (SMK). The SMK protection allows to use the DBMK automatically (i.e. without the need to open the DBMK explicitly).
When you move the DB to a different server, the protection by the SMK is no longer valid, and you need to manually open the DBMK and encrypt it with the current SMK. This is a one-time operation.
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<password>>'
Go
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Go
For more detailed information go to BOL:
http://msdn2.microsoft.com/en-us/library/ms174433.aspx
http://msdn2.microsoft.com/en-us/library/ms186937.aspx
Thanks,
-Raul Garcia
SDE/T
SQL Server Engine
|||Thanks Raul.
I had done two things.
1.The service master key doesn't working. So I have to manually open the DBMK and encrypt with the current SMK.
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password!'
Go
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
2. Again the symmetric key doesn't work.
Because keys cannot be individually backed up and restored, there is no direct way of moving a key from one database to another. However, by specifying the same values for the ALGORITHM, KEY_SOURCE, and IDENTITY_VALUE clauses of CREATE SYMMETRIC KEY, the same key can be generated on different databases.
so I have to change the symmetric key generation code to,
CREATE SYMMETRIC KEY symkey_demo
WITH ALGORITHM = TRIPLE_DES,
identity_value = 'Test Data encryption key',
key_source = 'test key'
ENCRYPTION BY CERTIFICATE cert_demo
Now I backed up the production database and restore it in a different server. I followed the stpe 1 and it works. Now I am able to decrypt the data in the production copy database.
Thanks
Amit
|||I see, when you described a copy of the datbase I assumed (incorrectly) that you were referring to a copy based on a backup.
Thanks a lot for letting us know, and let us know if you have further questions or comments.
-Raul Garcia
SDE/T
SQL Server Engine
Friday, February 24, 2012
Backup and availability
Technically what exactly does the backup do ?, and what can users/sysadmins
do in a database when a backup is in progress ?
Does it put table/page locks on data when it is backing up that specific
table/page or does it back up the actual mdf/ndf file ?.
How does this change with Full, differencial, filegroup and transaction
backups ?
When backing up a database I have read that it is not possible to run some
ALTER DATABASE commands, inserts and deletes etc, but i have also read that
backups maximize their rate of data transfer with minimal effect on
transaction throughput.
I am starting suspect that you are able to use DML statements and not DDLs
during a backup.
As long as Ive been a DBA i have always done Backups at times when there are
the least amount of users and when the server is not being utilised so much,
this make perfect sense as I'm sure theres an overhead.
Ive looked on the web and in BOL but cant find anything specific.
any knowledge or help on this would be appreciated
thanks
Hi!
You will find a lot of info at
http://www.microsoft.com/technet/pro...ackuprest.mspx
and
http://www.microsoft.com/mspress/boo...hap/4297b.asp.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"piripi" <piripi@.discussions.microsoft.com> wrote in message
news:EA51C857-126E-4835-AF3F-C46DA996C439@.microsoft.com...
> Technically what exactly does the backup do ?, and what can
> users/sysadmins
> do in a database when a backup is in progress ?
> Does it put table/page locks on data when it is backing up that specific
> table/page or does it back up the actual mdf/ndf file ?.
> How does this change with Full, differencial, filegroup and transaction
> backups ?
> When backing up a database I have read that it is not possible to run some
> ALTER DATABASE commands, inserts and deletes etc, but i have also read
> that
> backups maximize their rate of data transfer with minimal effect on
> transaction throughput.
> I am starting suspect that you are able to use DML statements and not DDLs
> during a backup.
> As long as Ive been a DBA i have always done Backups at times when there
> are
> the least amount of users and when the server is not being utilised so
> much,
> this make perfect sense as I'm sure theres an overhead.
> Ive looked on the web and in BOL but cant find anything specific.
> any knowledge or help on this would be appreciated
> thanks
do in a database when a backup is in progress ?
Does it put table/page locks on data when it is backing up that specific
table/page or does it back up the actual mdf/ndf file ?.
How does this change with Full, differencial, filegroup and transaction
backups ?
When backing up a database I have read that it is not possible to run some
ALTER DATABASE commands, inserts and deletes etc, but i have also read that
backups maximize their rate of data transfer with minimal effect on
transaction throughput.
I am starting suspect that you are able to use DML statements and not DDLs
during a backup.
As long as Ive been a DBA i have always done Backups at times when there are
the least amount of users and when the server is not being utilised so much,
this make perfect sense as I'm sure theres an overhead.
Ive looked on the web and in BOL but cant find anything specific.
any knowledge or help on this would be appreciated
thanks
Hi!
You will find a lot of info at
http://www.microsoft.com/technet/pro...ackuprest.mspx
and
http://www.microsoft.com/mspress/boo...hap/4297b.asp.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"piripi" <piripi@.discussions.microsoft.com> wrote in message
news:EA51C857-126E-4835-AF3F-C46DA996C439@.microsoft.com...
> Technically what exactly does the backup do ?, and what can
> users/sysadmins
> do in a database when a backup is in progress ?
> Does it put table/page locks on data when it is backing up that specific
> table/page or does it back up the actual mdf/ndf file ?.
> How does this change with Full, differencial, filegroup and transaction
> backups ?
> When backing up a database I have read that it is not possible to run some
> ALTER DATABASE commands, inserts and deletes etc, but i have also read
> that
> backups maximize their rate of data transfer with minimal effect on
> transaction throughput.
> I am starting suspect that you are able to use DML statements and not DDLs
> during a backup.
> As long as Ive been a DBA i have always done Backups at times when there
> are
> the least amount of users and when the server is not being utilised so
> much,
> this make perfect sense as I'm sure theres an overhead.
> Ive looked on the web and in BOL but cant find anything specific.
> any knowledge or help on this would be appreciated
> thanks
Backup and availability
Technically what exactly does the backup do ?, and what can users/sysadmins
do in a database when a backup is in progress ?
Does it put table/page locks on data when it is backing up that specific
table/page or does it back up the actual mdf/ndf file ?.
How does this change with Full, differencial, filegroup and transaction
backups ?
When backing up a database I have read that it is not possible to run some
ALTER DATABASE commands, inserts and deletes etc, but i have also read that
backups maximize their rate of data transfer with minimal effect on
transaction throughput.
I am starting suspect that you are able to use DML statements and not DDLs
during a backup.
As long as Ive been a DBA i have always done Backups at times when there are
the least amount of users and when the server is not being utilised so much,
this make perfect sense as I'm sure theres an overhead.
Ive looked on the web and in BOL but cant find anything specific.
any knowledge or help on this would be appreciated
thanksHi!
You will find a lot of info at
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx
and
http://www.microsoft.com/mspress/books/sampchap/4297b.asp.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"piripi" <piripi@.discussions.microsoft.com> wrote in message
news:EA51C857-126E-4835-AF3F-C46DA996C439@.microsoft.com...
> Technically what exactly does the backup do ?, and what can
> users/sysadmins
> do in a database when a backup is in progress ?
> Does it put table/page locks on data when it is backing up that specific
> table/page or does it back up the actual mdf/ndf file ?.
> How does this change with Full, differencial, filegroup and transaction
> backups ?
> When backing up a database I have read that it is not possible to run some
> ALTER DATABASE commands, inserts and deletes etc, but i have also read
> that
> backups maximize their rate of data transfer with minimal effect on
> transaction throughput.
> I am starting suspect that you are able to use DML statements and not DDLs
> during a backup.
> As long as Ive been a DBA i have always done Backups at times when there
> are
> the least amount of users and when the server is not being utilised so
> much,
> this make perfect sense as I'm sure theres an overhead.
> Ive looked on the web and in BOL but cant find anything specific.
> any knowledge or help on this would be appreciated
> thanks
do in a database when a backup is in progress ?
Does it put table/page locks on data when it is backing up that specific
table/page or does it back up the actual mdf/ndf file ?.
How does this change with Full, differencial, filegroup and transaction
backups ?
When backing up a database I have read that it is not possible to run some
ALTER DATABASE commands, inserts and deletes etc, but i have also read that
backups maximize their rate of data transfer with minimal effect on
transaction throughput.
I am starting suspect that you are able to use DML statements and not DDLs
during a backup.
As long as Ive been a DBA i have always done Backups at times when there are
the least amount of users and when the server is not being utilised so much,
this make perfect sense as I'm sure theres an overhead.
Ive looked on the web and in BOL but cant find anything specific.
any knowledge or help on this would be appreciated
thanksHi!
You will find a lot of info at
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx
and
http://www.microsoft.com/mspress/books/sampchap/4297b.asp.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"piripi" <piripi@.discussions.microsoft.com> wrote in message
news:EA51C857-126E-4835-AF3F-C46DA996C439@.microsoft.com...
> Technically what exactly does the backup do ?, and what can
> users/sysadmins
> do in a database when a backup is in progress ?
> Does it put table/page locks on data when it is backing up that specific
> table/page or does it back up the actual mdf/ndf file ?.
> How does this change with Full, differencial, filegroup and transaction
> backups ?
> When backing up a database I have read that it is not possible to run some
> ALTER DATABASE commands, inserts and deletes etc, but i have also read
> that
> backups maximize their rate of data transfer with minimal effect on
> transaction throughput.
> I am starting suspect that you are able to use DML statements and not DDLs
> during a backup.
> As long as Ive been a DBA i have always done Backups at times when there
> are
> the least amount of users and when the server is not being utilised so
> much,
> this make perfect sense as I'm sure theres an overhead.
> Ive looked on the web and in BOL but cant find anything specific.
> any knowledge or help on this would be appreciated
> thanks
Backup and availability
Technically what exactly does the backup do ?, and what can users/sysadmins
do in a database when a backup is in progress ?
Does it put table/page locks on data when it is backing up that specific
table/page or does it back up the actual mdf/ndf file ?.
How does this change with Full, differencial, filegroup and transaction
backups ?
When backing up a database I have read that it is not possible to run some
ALTER DATABASE commands, inserts and deletes etc, but i have also read that
backups maximize their rate of data transfer with minimal effect on
transaction throughput.
I am starting suspect that you are able to use DML statements and not DDLs
during a backup.
As long as Ive been a DBA i have always done Backups at times when there are
the least amount of users and when the server is not being utilised so much,
this make perfect sense as I'm sure theres an overhead.
Ive looked on the web and in BOL but cant find anything specific.
any knowledge or help on this would be appreciated
thanksHi!
You will find a lot of info at
http://www.microsoft.com/technet/pr...chap/4297b.asp.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"piripi" <piripi@.discussions.microsoft.com> wrote in message
news:EA51C857-126E-4835-AF3F-C46DA996C439@.microsoft.com...
> Technically what exactly does the backup do ?, and what can
> users/sysadmins
> do in a database when a backup is in progress ?
> Does it put table/page locks on data when it is backing up that specific
> table/page or does it back up the actual mdf/ndf file ?.
> How does this change with Full, differencial, filegroup and transaction
> backups ?
> When backing up a database I have read that it is not possible to run some
> ALTER DATABASE commands, inserts and deletes etc, but i have also read
> that
> backups maximize their rate of data transfer with minimal effect on
> transaction throughput.
> I am starting suspect that you are able to use DML statements and not DDLs
> during a backup.
> As long as Ive been a DBA i have always done Backups at times when there
> are
> the least amount of users and when the server is not being utilised so
> much,
> this make perfect sense as I'm sure theres an overhead.
> Ive looked on the web and in BOL but cant find anything specific.
> any knowledge or help on this would be appreciated
> thanks
do in a database when a backup is in progress ?
Does it put table/page locks on data when it is backing up that specific
table/page or does it back up the actual mdf/ndf file ?.
How does this change with Full, differencial, filegroup and transaction
backups ?
When backing up a database I have read that it is not possible to run some
ALTER DATABASE commands, inserts and deletes etc, but i have also read that
backups maximize their rate of data transfer with minimal effect on
transaction throughput.
I am starting suspect that you are able to use DML statements and not DDLs
during a backup.
As long as Ive been a DBA i have always done Backups at times when there are
the least amount of users and when the server is not being utilised so much,
this make perfect sense as I'm sure theres an overhead.
Ive looked on the web and in BOL but cant find anything specific.
any knowledge or help on this would be appreciated
thanksHi!
You will find a lot of info at
http://www.microsoft.com/technet/pr...chap/4297b.asp.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"piripi" <piripi@.discussions.microsoft.com> wrote in message
news:EA51C857-126E-4835-AF3F-C46DA996C439@.microsoft.com...
> Technically what exactly does the backup do ?, and what can
> users/sysadmins
> do in a database when a backup is in progress ?
> Does it put table/page locks on data when it is backing up that specific
> table/page or does it back up the actual mdf/ndf file ?.
> How does this change with Full, differencial, filegroup and transaction
> backups ?
> When backing up a database I have read that it is not possible to run some
> ALTER DATABASE commands, inserts and deletes etc, but i have also read
> that
> backups maximize their rate of data transfer with minimal effect on
> transaction throughput.
> I am starting suspect that you are able to use DML statements and not DDLs
> during a backup.
> As long as Ive been a DBA i have always done Backups at times when there
> are
> the least amount of users and when the server is not being utilised so
> much,
> this make perfect sense as I'm sure theres an overhead.
> Ive looked on the web and in BOL but cant find anything specific.
> any knowledge or help on this would be appreciated
> thanks
Backup a single table?
In SQL 6.5 you could backup a single table in a db to a
backupdevice or a file. I can not find this in the newer
versions SQL 7.0 and SQL 2000. Is this feature removed?
I know that the command has changed from DUMP to BACKUP
and that you nowadays often backup db-files or filegroups
instead.
This feature has been removed with SQL2000
"Stefan" <stefan@.discussions.microsoft.com> wrote in message
news:033c01c4b815$4a8a0600$a601280a@.phx.gbl...
> In SQL 6.5 you could backup a single table in a db to a
> backupdevice or a file. I can not find this in the newer
> versions SQL 7.0 and SQL 2000. Is this feature removed?
> I know that the command has changed from DUMP to BACKUP
> and that you nowadays often backup db-files or filegroups
> instead.
>
|||> I know that the command has changed from DUMP to BACKUP
> and that you nowadays often backup db-files or filegroups
> instead.
You will have to either (a) put it onto its own filegroup and backup the
filegroup, or (b) DTS a copy into an empty database and backup that
database.
http://www.aspfaq.com/
(Reverse address to reply.)
backupdevice or a file. I can not find this in the newer
versions SQL 7.0 and SQL 2000. Is this feature removed?
I know that the command has changed from DUMP to BACKUP
and that you nowadays often backup db-files or filegroups
instead.
This feature has been removed with SQL2000
"Stefan" <stefan@.discussions.microsoft.com> wrote in message
news:033c01c4b815$4a8a0600$a601280a@.phx.gbl...
> In SQL 6.5 you could backup a single table in a db to a
> backupdevice or a file. I can not find this in the newer
> versions SQL 7.0 and SQL 2000. Is this feature removed?
> I know that the command has changed from DUMP to BACKUP
> and that you nowadays often backup db-files or filegroups
> instead.
>
|||> I know that the command has changed from DUMP to BACKUP
> and that you nowadays often backup db-files or filegroups
> instead.
You will have to either (a) put it onto its own filegroup and backup the
filegroup, or (b) DTS a copy into an empty database and backup that
database.
http://www.aspfaq.com/
(Reverse address to reply.)
Backup a single table?
In SQL 6.5 you could backup a single table in a db to a
backupdevice or a file. I can not find this in the newer
versions SQL 7.0 and SQL 2000. Is this feature removed?
I know that the command has changed from DUMP to BACKUP
and that you nowadays often backup db-files or filegroups
instead.This feature has been removed with SQL2000
"Stefan" <stefan@.discussions.microsoft.com> wrote in message
news:033c01c4b815$4a8a0600$a601280a@.phx.gbl...
> In SQL 6.5 you could backup a single table in a db to a
> backupdevice or a file. I can not find this in the newer
> versions SQL 7.0 and SQL 2000. Is this feature removed?
> I know that the command has changed from DUMP to BACKUP
> and that you nowadays often backup db-files or filegroups
> instead.
>|||> I know that the command has changed from DUMP to BACKUP
> and that you nowadays often backup db-files or filegroups
> instead.
You will have to either (a) put it onto its own filegroup and backup the
filegroup, or (b) DTS a copy into an empty database and backup that
database.
--
http://www.aspfaq.com/
(Reverse address to reply.)
backupdevice or a file. I can not find this in the newer
versions SQL 7.0 and SQL 2000. Is this feature removed?
I know that the command has changed from DUMP to BACKUP
and that you nowadays often backup db-files or filegroups
instead.This feature has been removed with SQL2000
"Stefan" <stefan@.discussions.microsoft.com> wrote in message
news:033c01c4b815$4a8a0600$a601280a@.phx.gbl...
> In SQL 6.5 you could backup a single table in a db to a
> backupdevice or a file. I can not find this in the newer
> versions SQL 7.0 and SQL 2000. Is this feature removed?
> I know that the command has changed from DUMP to BACKUP
> and that you nowadays often backup db-files or filegroups
> instead.
>|||> I know that the command has changed from DUMP to BACKUP
> and that you nowadays often backup db-files or filegroups
> instead.
You will have to either (a) put it onto its own filegroup and backup the
filegroup, or (b) DTS a copy into an empty database and backup that
database.
--
http://www.aspfaq.com/
(Reverse address to reply.)
Backup a single table?
In SQL 6.5 you could backup a single table in a db to a
backupdevice or a file. I can not find this in the newer
versions SQL 7.0 and SQL 2000. Is this feature removed?
I know that the command has changed from DUMP to BACKUP
and that you nowadays often backup db-files or filegroups
instead.This feature has been removed with SQL2000
"Stefan" <stefan@.discussions.microsoft.com> wrote in message
news:033c01c4b815$4a8a0600$a601280a@.phx.gbl...
> In SQL 6.5 you could backup a single table in a db to a
> backupdevice or a file. I can not find this in the newer
> versions SQL 7.0 and SQL 2000. Is this feature removed?
> I know that the command has changed from DUMP to BACKUP
> and that you nowadays often backup db-files or filegroups
> instead.
>|||> I know that the command has changed from DUMP to BACKUP
> and that you nowadays often backup db-files or filegroups
> instead.
You will have to either (a) put it onto its own filegroup and backup the
filegroup, or (b) DTS a copy into an empty database and backup that
database.
http://www.aspfaq.com/
(Reverse address to reply.)
backupdevice or a file. I can not find this in the newer
versions SQL 7.0 and SQL 2000. Is this feature removed?
I know that the command has changed from DUMP to BACKUP
and that you nowadays often backup db-files or filegroups
instead.This feature has been removed with SQL2000
"Stefan" <stefan@.discussions.microsoft.com> wrote in message
news:033c01c4b815$4a8a0600$a601280a@.phx.gbl...
> In SQL 6.5 you could backup a single table in a db to a
> backupdevice or a file. I can not find this in the newer
> versions SQL 7.0 and SQL 2000. Is this feature removed?
> I know that the command has changed from DUMP to BACKUP
> and that you nowadays often backup db-files or filegroups
> instead.
>|||> I know that the command has changed from DUMP to BACKUP
> and that you nowadays often backup db-files or filegroups
> instead.
You will have to either (a) put it onto its own filegroup and backup the
filegroup, or (b) DTS a copy into an empty database and backup that
database.
http://www.aspfaq.com/
(Reverse address to reply.)
Sunday, February 19, 2012
Backup / Restore select SP
Hi,
Any suggestions on Backup / Restore of a select Table /
Stored Procedure etc?
RaoFor a non-data carrying object (everything except table), generate script.
To do this from code, use the DMO API.
For a table, (I assume you also want data), you can generate script and also
use BCP, DTS or some similar method for the data.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rao" <chtvrao@.valona.com> wrote in message
news:08d901c3a44b$d8cd6df0$a501280a@.phx.gbl...
> Hi,
> Any suggestions on Backup / Restore of a select Table /
> Stored Procedure etc?
> Rao|||Thanks for the suggestions. While these give me clues
related to the topic, my requirement is somewaht specific
and it is like this.
We are in consulting and while we want to send updates to
clients, we do not want to send the source code. So to
send an encrypted Stored Procedure(s) only, I am looking
at a way.
Any more suggestions Please?
Rao
>--Original Message--
>For a non-data carrying object (everything except table),
generate script.
>To do this from code, use the DMO API.
>For a table, (I assume you also want data), you can
generate script and also
>use BCP, DTS or some similar method for the data.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Rao" <chtvrao@.valona.com> wrote in message
>news:08d901c3a44b$d8cd6df0$a501280a@.phx.gbl...
>> Hi,
>> Any suggestions on Backup / Restore of a select Table /
>> Stored Procedure etc?
>> Rao
>
>.
>|||> We are in consulting and while we want to send updates to
> clients, we do not want to send the source code. So to
> send an encrypted Stored Procedure(s) only, I am looking
> at a way.
There's really no way to send an encrypted stored procedure. The closes you
probably can come is to have your customers execute an exe file where the
CREATE statements are inside the EXE file. I.e., having the EXE file be the
"scrambler". Unfortunately, Profiler will show these statements, but on the
other side there are tools out there that crack this encryption, so if your
customer really wants to...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rao" <chtvrao@.valona.com> wrote in message
news:053901c3a4cc$aa8fa490$a401280a@.phx.gbl...
> Thanks for the suggestions. While these give me clues
> related to the topic, my requirement is somewaht specific
> and it is like this.
> We are in consulting and while we want to send updates to
> clients, we do not want to send the source code. So to
> send an encrypted Stored Procedure(s) only, I am looking
> at a way.
> Any more suggestions Please?
> Rao
> >--Original Message--
> >For a non-data carrying object (everything except table),
> generate script.
> >To do this from code, use the DMO API.
> >
> >For a table, (I assume you also want data), you can
> generate script and also
> >use BCP, DTS or some similar method for the data.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Rao" <chtvrao@.valona.com> wrote in message
> >news:08d901c3a44b$d8cd6df0$a501280a@.phx.gbl...
> >> Hi,
> >> Any suggestions on Backup / Restore of a select Table /
> >> Stored Procedure etc?
> >>
> >> Rao
> >
> >
> >.
> >|||Hi Tibor,
Thanks for the details. I know the existence of these
tools to decrypt the SPs. Anyway we have to live with it.
Could you please throw me some light on how others in the
consulting field offer their work to clients and still
safeguard their IP(Intelectual Proprty)?
Rao
>--Original Message--
>> We are in consulting and while we want to send updates
to
>> clients, we do not want to send the source code. So to
>> send an encrypted Stored Procedure(s) only, I am looking
>> at a way.
>There's really no way to send an encrypted stored
procedure. The closes you
>probably can come is to have your customers execute an
exe file where the
>CREATE statements are inside the EXE file. I.e., having
the EXE file be the
>"scrambler". Unfortunately, Profiler will show these
statements, but on the
>other side there are tools out there that crack this
encryption, so if your
>customer really wants to...
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Rao" <chtvrao@.valona.com> wrote in message
>news:053901c3a4cc$aa8fa490$a401280a@.phx.gbl...
>> Thanks for the suggestions. While these give me clues
>> related to the topic, my requirement is somewaht
specific
>> and it is like this.
>> We are in consulting and while we want to send updates
to
>> clients, we do not want to send the source code. So to
>> send an encrypted Stored Procedure(s) only, I am looking
>> at a way.
>> Any more suggestions Please?
>> Rao
>> >--Original Message--
>> >For a non-data carrying object (everything except
table),
>> generate script.
>> >To do this from code, use the DMO API.
>> >
>> >For a table, (I assume you also want data), you can
>> generate script and also
>> >use BCP, DTS or some similar method for the data.
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at:
>> >http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"Rao" <chtvrao@.valona.com> wrote in message
>> >news:08d901c3a44b$d8cd6df0$a501280a@.phx.gbl...
>> >> Hi,
>> >> Any suggestions on Backup / Restore of a select
Table /
>> >> Stored Procedure etc?
>> >>
>> >> Rao
>> >
>> >
>> >.
>> >
>
>.
>|||> Could you please throw me some light on how others in the
> consulting field offer their work to clients and still
> safeguard their IP(Intelectual Proprty)?
You should start a new thread on that, with proper subject. That would
probably be the best way to poll. Also, programming is probably a better
group for that. I've used encryption just to "make it harder". But even .exe
file can be "decompiled", so technical solutions only takes you so far. The
general recommendation is to do it by legal measures.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rao" <chtvrao@.valona.com> wrote in message
news:07bf01c3a506$ee94ecb0$a401280a@.phx.gbl...
> Hi Tibor,
> Thanks for the details. I know the existence of these
> tools to decrypt the SPs. Anyway we have to live with it.
> Could you please throw me some light on how others in the
> consulting field offer their work to clients and still
> safeguard their IP(Intelectual Proprty)?
> Rao
> >--Original Message--
> >> We are in consulting and while we want to send updates
> to
> >> clients, we do not want to send the source code. So to
> >> send an encrypted Stored Procedure(s) only, I am looking
> >> at a way.
> >
> >There's really no way to send an encrypted stored
> procedure. The closes you
> >probably can come is to have your customers execute an
> exe file where the
> >CREATE statements are inside the EXE file. I.e., having
> the EXE file be the
> >"scrambler". Unfortunately, Profiler will show these
> statements, but on the
> >other side there are tools out there that crack this
> encryption, so if your
> >customer really wants to...
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Rao" <chtvrao@.valona.com> wrote in message
> >news:053901c3a4cc$aa8fa490$a401280a@.phx.gbl...
> >> Thanks for the suggestions. While these give me clues
> >> related to the topic, my requirement is somewaht
> specific
> >> and it is like this.
> >>
> >> We are in consulting and while we want to send updates
> to
> >> clients, we do not want to send the source code. So to
> >> send an encrypted Stored Procedure(s) only, I am looking
> >> at a way.
> >>
> >> Any more suggestions Please?
> >>
> >> Rao
> >>
> >> >--Original Message--
> >> >For a non-data carrying object (everything except
> table),
> >> generate script.
> >> >To do this from code, use the DMO API.
> >> >
> >> >For a table, (I assume you also want data), you can
> >> generate script and also
> >> >use BCP, DTS or some similar method for the data.
> >> >
> >> >--
> >> >Tibor Karaszi, SQL Server MVP
> >> >Archive at:
> >> >http://groups.google.com/groups?
> >> oi=djq&as_ugroup=microsoft.public.sqlserver
> >> >
> >> >
> >> >"Rao" <chtvrao@.valona.com> wrote in message
> >> >news:08d901c3a44b$d8cd6df0$a501280a@.phx.gbl...
> >> >> Hi,
> >> >> Any suggestions on Backup / Restore of a select
> Table /
> >> >> Stored Procedure etc?
> >> >>
> >> >> Rao
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Hi Tibor,
Thanks for all the info. Let me see what I can do.
Rao
>--Original Message--
>> Could you please throw me some light on how others in
the
>> consulting field offer their work to clients and still
>> safeguard their IP(Intelectual Proprty)?
>You should start a new thread on that, with proper
subject. That would
>probably be the best way to poll. Also, programming is
probably a better
>group for that. I've used encryption just to "make it
harder". But even .exe
>file can be "decompiled", so technical solutions only
takes you so far. The
>general recommendation is to do it by legal measures.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Rao" <chtvrao@.valona.com> wrote in message
>news:07bf01c3a506$ee94ecb0$a401280a@.phx.gbl...
>> Hi Tibor,
>> Thanks for the details. I know the existence of these
>> tools to decrypt the SPs. Anyway we have to live with
it.
>> Could you please throw me some light on how others in
the
>> consulting field offer their work to clients and still
>> safeguard their IP(Intelectual Proprty)?
>> Rao
>> >--Original Message--
>> >> We are in consulting and while we want to send
updates
>> to
>> >> clients, we do not want to send the source code. So
to
>> >> send an encrypted Stored Procedure(s) only, I am
looking
>> >> at a way.
>> >
>> >There's really no way to send an encrypted stored
>> procedure. The closes you
>> >probably can come is to have your customers execute an
>> exe file where the
>> >CREATE statements are inside the EXE file. I.e., having
>> the EXE file be the
>> >"scrambler". Unfortunately, Profiler will show these
>> statements, but on the
>> >other side there are tools out there that crack this
>> encryption, so if your
>> >customer really wants to...
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at:
>> >http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"Rao" <chtvrao@.valona.com> wrote in message
>> >news:053901c3a4cc$aa8fa490$a401280a@.phx.gbl...
>> >> Thanks for the suggestions. While these give me clues
>> >> related to the topic, my requirement is somewaht
>> specific
>> >> and it is like this.
>> >>
>> >> We are in consulting and while we want to send
updates
>> to
>> >> clients, we do not want to send the source code. So
to
>> >> send an encrypted Stored Procedure(s) only, I am
looking
>> >> at a way.
>> >>
>> >> Any more suggestions Please?
>> >>
>> >> Rao
>> >>
>> >> >--Original Message--
>> >> >For a non-data carrying object (everything except
>> table),
>> >> generate script.
>> >> >To do this from code, use the DMO API.
>> >> >
>> >> >For a table, (I assume you also want data), you can
>> >> generate script and also
>> >> >use BCP, DTS or some similar method for the data.
>> >> >
>> >> >--
>> >> >Tibor Karaszi, SQL Server MVP
>> >> >Archive at:
>> >> >http://groups.google.com/groups?
>> >> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >> >
>> >> >
>> >> >"Rao" <chtvrao@.valona.com> wrote in message
>> >> >news:08d901c3a44b$d8cd6df0$a501280a@.phx.gbl...
>> >> >> Hi,
>> >> >> Any suggestions on Backup / Restore of a select
>> Table /
>> >> >> Stored Procedure etc?
>> >> >>
>> >> >> Rao
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>
Any suggestions on Backup / Restore of a select Table /
Stored Procedure etc?
RaoFor a non-data carrying object (everything except table), generate script.
To do this from code, use the DMO API.
For a table, (I assume you also want data), you can generate script and also
use BCP, DTS or some similar method for the data.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rao" <chtvrao@.valona.com> wrote in message
news:08d901c3a44b$d8cd6df0$a501280a@.phx.gbl...
> Hi,
> Any suggestions on Backup / Restore of a select Table /
> Stored Procedure etc?
> Rao|||Thanks for the suggestions. While these give me clues
related to the topic, my requirement is somewaht specific
and it is like this.
We are in consulting and while we want to send updates to
clients, we do not want to send the source code. So to
send an encrypted Stored Procedure(s) only, I am looking
at a way.
Any more suggestions Please?
Rao
>--Original Message--
>For a non-data carrying object (everything except table),
generate script.
>To do this from code, use the DMO API.
>For a table, (I assume you also want data), you can
generate script and also
>use BCP, DTS or some similar method for the data.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Rao" <chtvrao@.valona.com> wrote in message
>news:08d901c3a44b$d8cd6df0$a501280a@.phx.gbl...
>> Hi,
>> Any suggestions on Backup / Restore of a select Table /
>> Stored Procedure etc?
>> Rao
>
>.
>|||> We are in consulting and while we want to send updates to
> clients, we do not want to send the source code. So to
> send an encrypted Stored Procedure(s) only, I am looking
> at a way.
There's really no way to send an encrypted stored procedure. The closes you
probably can come is to have your customers execute an exe file where the
CREATE statements are inside the EXE file. I.e., having the EXE file be the
"scrambler". Unfortunately, Profiler will show these statements, but on the
other side there are tools out there that crack this encryption, so if your
customer really wants to...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rao" <chtvrao@.valona.com> wrote in message
news:053901c3a4cc$aa8fa490$a401280a@.phx.gbl...
> Thanks for the suggestions. While these give me clues
> related to the topic, my requirement is somewaht specific
> and it is like this.
> We are in consulting and while we want to send updates to
> clients, we do not want to send the source code. So to
> send an encrypted Stored Procedure(s) only, I am looking
> at a way.
> Any more suggestions Please?
> Rao
> >--Original Message--
> >For a non-data carrying object (everything except table),
> generate script.
> >To do this from code, use the DMO API.
> >
> >For a table, (I assume you also want data), you can
> generate script and also
> >use BCP, DTS or some similar method for the data.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Rao" <chtvrao@.valona.com> wrote in message
> >news:08d901c3a44b$d8cd6df0$a501280a@.phx.gbl...
> >> Hi,
> >> Any suggestions on Backup / Restore of a select Table /
> >> Stored Procedure etc?
> >>
> >> Rao
> >
> >
> >.
> >|||Hi Tibor,
Thanks for the details. I know the existence of these
tools to decrypt the SPs. Anyway we have to live with it.
Could you please throw me some light on how others in the
consulting field offer their work to clients and still
safeguard their IP(Intelectual Proprty)?
Rao
>--Original Message--
>> We are in consulting and while we want to send updates
to
>> clients, we do not want to send the source code. So to
>> send an encrypted Stored Procedure(s) only, I am looking
>> at a way.
>There's really no way to send an encrypted stored
procedure. The closes you
>probably can come is to have your customers execute an
exe file where the
>CREATE statements are inside the EXE file. I.e., having
the EXE file be the
>"scrambler". Unfortunately, Profiler will show these
statements, but on the
>other side there are tools out there that crack this
encryption, so if your
>customer really wants to...
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Rao" <chtvrao@.valona.com> wrote in message
>news:053901c3a4cc$aa8fa490$a401280a@.phx.gbl...
>> Thanks for the suggestions. While these give me clues
>> related to the topic, my requirement is somewaht
specific
>> and it is like this.
>> We are in consulting and while we want to send updates
to
>> clients, we do not want to send the source code. So to
>> send an encrypted Stored Procedure(s) only, I am looking
>> at a way.
>> Any more suggestions Please?
>> Rao
>> >--Original Message--
>> >For a non-data carrying object (everything except
table),
>> generate script.
>> >To do this from code, use the DMO API.
>> >
>> >For a table, (I assume you also want data), you can
>> generate script and also
>> >use BCP, DTS or some similar method for the data.
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at:
>> >http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"Rao" <chtvrao@.valona.com> wrote in message
>> >news:08d901c3a44b$d8cd6df0$a501280a@.phx.gbl...
>> >> Hi,
>> >> Any suggestions on Backup / Restore of a select
Table /
>> >> Stored Procedure etc?
>> >>
>> >> Rao
>> >
>> >
>> >.
>> >
>
>.
>|||> Could you please throw me some light on how others in the
> consulting field offer their work to clients and still
> safeguard their IP(Intelectual Proprty)?
You should start a new thread on that, with proper subject. That would
probably be the best way to poll. Also, programming is probably a better
group for that. I've used encryption just to "make it harder". But even .exe
file can be "decompiled", so technical solutions only takes you so far. The
general recommendation is to do it by legal measures.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rao" <chtvrao@.valona.com> wrote in message
news:07bf01c3a506$ee94ecb0$a401280a@.phx.gbl...
> Hi Tibor,
> Thanks for the details. I know the existence of these
> tools to decrypt the SPs. Anyway we have to live with it.
> Could you please throw me some light on how others in the
> consulting field offer their work to clients and still
> safeguard their IP(Intelectual Proprty)?
> Rao
> >--Original Message--
> >> We are in consulting and while we want to send updates
> to
> >> clients, we do not want to send the source code. So to
> >> send an encrypted Stored Procedure(s) only, I am looking
> >> at a way.
> >
> >There's really no way to send an encrypted stored
> procedure. The closes you
> >probably can come is to have your customers execute an
> exe file where the
> >CREATE statements are inside the EXE file. I.e., having
> the EXE file be the
> >"scrambler". Unfortunately, Profiler will show these
> statements, but on the
> >other side there are tools out there that crack this
> encryption, so if your
> >customer really wants to...
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Rao" <chtvrao@.valona.com> wrote in message
> >news:053901c3a4cc$aa8fa490$a401280a@.phx.gbl...
> >> Thanks for the suggestions. While these give me clues
> >> related to the topic, my requirement is somewaht
> specific
> >> and it is like this.
> >>
> >> We are in consulting and while we want to send updates
> to
> >> clients, we do not want to send the source code. So to
> >> send an encrypted Stored Procedure(s) only, I am looking
> >> at a way.
> >>
> >> Any more suggestions Please?
> >>
> >> Rao
> >>
> >> >--Original Message--
> >> >For a non-data carrying object (everything except
> table),
> >> generate script.
> >> >To do this from code, use the DMO API.
> >> >
> >> >For a table, (I assume you also want data), you can
> >> generate script and also
> >> >use BCP, DTS or some similar method for the data.
> >> >
> >> >--
> >> >Tibor Karaszi, SQL Server MVP
> >> >Archive at:
> >> >http://groups.google.com/groups?
> >> oi=djq&as_ugroup=microsoft.public.sqlserver
> >> >
> >> >
> >> >"Rao" <chtvrao@.valona.com> wrote in message
> >> >news:08d901c3a44b$d8cd6df0$a501280a@.phx.gbl...
> >> >> Hi,
> >> >> Any suggestions on Backup / Restore of a select
> Table /
> >> >> Stored Procedure etc?
> >> >>
> >> >> Rao
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Hi Tibor,
Thanks for all the info. Let me see what I can do.
Rao
>--Original Message--
>> Could you please throw me some light on how others in
the
>> consulting field offer their work to clients and still
>> safeguard their IP(Intelectual Proprty)?
>You should start a new thread on that, with proper
subject. That would
>probably be the best way to poll. Also, programming is
probably a better
>group for that. I've used encryption just to "make it
harder". But even .exe
>file can be "decompiled", so technical solutions only
takes you so far. The
>general recommendation is to do it by legal measures.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Rao" <chtvrao@.valona.com> wrote in message
>news:07bf01c3a506$ee94ecb0$a401280a@.phx.gbl...
>> Hi Tibor,
>> Thanks for the details. I know the existence of these
>> tools to decrypt the SPs. Anyway we have to live with
it.
>> Could you please throw me some light on how others in
the
>> consulting field offer their work to clients and still
>> safeguard their IP(Intelectual Proprty)?
>> Rao
>> >--Original Message--
>> >> We are in consulting and while we want to send
updates
>> to
>> >> clients, we do not want to send the source code. So
to
>> >> send an encrypted Stored Procedure(s) only, I am
looking
>> >> at a way.
>> >
>> >There's really no way to send an encrypted stored
>> procedure. The closes you
>> >probably can come is to have your customers execute an
>> exe file where the
>> >CREATE statements are inside the EXE file. I.e., having
>> the EXE file be the
>> >"scrambler". Unfortunately, Profiler will show these
>> statements, but on the
>> >other side there are tools out there that crack this
>> encryption, so if your
>> >customer really wants to...
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at:
>> >http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"Rao" <chtvrao@.valona.com> wrote in message
>> >news:053901c3a4cc$aa8fa490$a401280a@.phx.gbl...
>> >> Thanks for the suggestions. While these give me clues
>> >> related to the topic, my requirement is somewaht
>> specific
>> >> and it is like this.
>> >>
>> >> We are in consulting and while we want to send
updates
>> to
>> >> clients, we do not want to send the source code. So
to
>> >> send an encrypted Stored Procedure(s) only, I am
looking
>> >> at a way.
>> >>
>> >> Any more suggestions Please?
>> >>
>> >> Rao
>> >>
>> >> >--Original Message--
>> >> >For a non-data carrying object (everything except
>> table),
>> >> generate script.
>> >> >To do this from code, use the DMO API.
>> >> >
>> >> >For a table, (I assume you also want data), you can
>> >> generate script and also
>> >> >use BCP, DTS or some similar method for the data.
>> >> >
>> >> >--
>> >> >Tibor Karaszi, SQL Server MVP
>> >> >Archive at:
>> >> >http://groups.google.com/groups?
>> >> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >> >
>> >> >
>> >> >"Rao" <chtvrao@.valona.com> wrote in message
>> >> >news:08d901c3a44b$d8cd6df0$a501280a@.phx.gbl...
>> >> >> Hi,
>> >> >> Any suggestions on Backup / Restore of a select
>> Table /
>> >> >> Stored Procedure etc?
>> >> >>
>> >> >> Rao
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>
Friday, February 10, 2012
Backing up tables
I use SQL Server 2000 and I create scripts on a daily basis to do the following
1. Rename a table
2. Create table with original name and add new field
3. Copy data from renamed table to new table
4. Build indexes
-------------
execute sp_rename ORIGINAL, OLD
GO
CREATE TABLE [dbo].[ORIGINAL] (
[A] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[B] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[C] [int] NOT NULL ,
[Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT INTO ORIGINAL (A, B, NOTES)
SELECT A, B, NOTES
FROM OLD
CREATE CLUSTERED INDEX [ORIGINAL_A_B] ON [dbo].[ORIGINAL]([A], [B] DESC ) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
-------------
ON A TABLE WITH 100,000 RECORDS THIS KIND OF SCRIPT TAKES A LONG TIME TO DO THE INSERT/SELECT PART
I WOULD USE ALTER/ADD TO UPDATE THE TABLES, BUT MY SUPERIOR INSISTS THAT NOT HAVING TEXT FIELDS AS THE LAST FIELD IN THE TABLE WILL CAUSE QUERKY PROBLEMS WITH HIS SQL STATEMENTS
DOES ANYONE HAVE ANY KNOWLEDGE THAT THIS COULD BE TRUE?
IF SO,
DOES ANYONE HAVE ANY IDEAS FOR DOING THIS SAME PROCESS TO ADD A NEW FIELD (C) BEFORE THE FINAL FIELD (NOTES - TEXT FIELD), AND BACKUP THE TABLE BUT WITHOUT IT TAKING SUCH A LONG TIME ON THE INSERT/SELECT PART?
Forgive me if I'm asking a lot, but I'm lost and these scripts take way too long for what seems like such a simple task.
Thanks,
MitchWow sounds like he has a DB2 OS/390 v2.3 background
Look up the datatype in BOL...
They store pointer values, not the data...it's stored separatley...
If you don't mind being fired, call him a scrub...
The order of columns in a table should have very little impact...
But being anal goes with the job...
Try a bcp out and in with a format file...
The reason it's slow is because it's all being logged...the bcp will log only the pages...not every row....|||EDIT:
Oh, and btw, I use ALTER
1. Rename a table
2. Create table with original name and add new field
3. Copy data from renamed table to new table
4. Build indexes
-------------
execute sp_rename ORIGINAL, OLD
GO
CREATE TABLE [dbo].[ORIGINAL] (
[A] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[B] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[C] [int] NOT NULL ,
[Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT INTO ORIGINAL (A, B, NOTES)
SELECT A, B, NOTES
FROM OLD
CREATE CLUSTERED INDEX [ORIGINAL_A_B] ON [dbo].[ORIGINAL]([A], [B] DESC ) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
-------------
ON A TABLE WITH 100,000 RECORDS THIS KIND OF SCRIPT TAKES A LONG TIME TO DO THE INSERT/SELECT PART
I WOULD USE ALTER/ADD TO UPDATE THE TABLES, BUT MY SUPERIOR INSISTS THAT NOT HAVING TEXT FIELDS AS THE LAST FIELD IN THE TABLE WILL CAUSE QUERKY PROBLEMS WITH HIS SQL STATEMENTS
DOES ANYONE HAVE ANY KNOWLEDGE THAT THIS COULD BE TRUE?
IF SO,
DOES ANYONE HAVE ANY IDEAS FOR DOING THIS SAME PROCESS TO ADD A NEW FIELD (C) BEFORE THE FINAL FIELD (NOTES - TEXT FIELD), AND BACKUP THE TABLE BUT WITHOUT IT TAKING SUCH A LONG TIME ON THE INSERT/SELECT PART?
Forgive me if I'm asking a lot, but I'm lost and these scripts take way too long for what seems like such a simple task.
Thanks,
MitchWow sounds like he has a DB2 OS/390 v2.3 background
Look up the datatype in BOL...
They store pointer values, not the data...it's stored separatley...
If you don't mind being fired, call him a scrub...
The order of columns in a table should have very little impact...
But being anal goes with the job...
Try a bcp out and in with a format file...
The reason it's slow is because it's all being logged...the bcp will log only the pages...not every row....|||EDIT:
Oh, and btw, I use ALTER
Subscribe to:
Posts (Atom)