Basic Anatomy of Sql Server

A series of articles examining some basic concepts in Sql Server.

Basic Anatomy of Sql Server, part I
What is a stored procedure?
http://beyondsql.blogspot.com/2007/...ver-part-i.html
Basic Anatomy of Sql Server, part II
The unit test as part of the database.
http://beyondsql.blogspot.com/2007/...er-part-ii.html
Basic Anatomy of Sql Server, part III
What does deferred name resolution really mean?
http://beyondsql.blogspot.com/2007/...r-part-iii.htmlsteve wrote:

Quote:

Originally Posted by

A series of articles examining some basic concepts in Sql Server.
>
Basic Anatomy of Sql Server, part I
What is a stored procedure?
http://beyondsql.blogspot.com/2007/...ver-part-i.html
>
Basic Anatomy of Sql Server, part II
The unit test as part of the database.
http://beyondsql.blogspot.com/2007/...er-part-ii.html
>
Basic Anatomy of Sql Server, part III
What does deferred name resolution really mean?
http://beyondsql.blogspot.com/2007/...r-part-iii.html

Regarding this specific idea, you could work around this issue by
performing a unit test one level up (writing a bit of stub code if
needed). Yes, it'd be nice to have more support for it in the DB
layer, but that alone doesn't justify the (apparent) huge migration
cost for the large number of existing systems out there. (I asked
you about this before, got no response.)

Regarding your general project, I was going to ask about LINQ (which
will presumably have the huge advantage of ubiquity due to being an
Official Microsoft Thingy), but I see you've already dismissed it at
http://beyondsql.blogspot.com/2007_08_01_archive.html

Bad Stored procedure

I get this error "Incorrect syntax near @.TopNews"
Here is my SP:

CREATE PROCEDURE sp_TopNews
@.TopNews int
AS
SELECT TOP @.TopNews [Title], [Content] FROM [Documents]
WHERE [DatePublication] >= GETDATE() AND [Type] = "News"
AND [DateArchived] = NULL

It's my first SP so it's probably a stupid error, can anyone help me ?You cannot use a variable for the number in TOP. You could use dynamic SQL.|||Based on http://www.sqlteam.com/item.asp?ItemID=233
Try this :

CREATE PROCEDURE sp_TopNews
 @.TopNews int
AS
SET rowcount @.TopNews
SELECT [Title], [Content]
 FROM [Documents]
 WHERE [DatePublication] >= GETDATE() AND
 [Type] = "News" AND
 [DateArchived] = NULL
SET rowcount 0
GO

|||Of course, without an order by statement, this is worthless...|||Thanks that worked :)|||indeed

Bad Proc Template

There is a base-installed template for a stored procedure called "Create Procedure with CURSOR OUT" which has this snippet:

EXEC <Schema_Name, sysname, Schema_Name>.<Procedure_Name, sysname, Procedure_Name> <@.proc_cursor_name, , @.sample_procedure_cursor> = <@.variable_cursor_name, , @.test_cursor_variable> OUTPUT

WHILE (@.@.FETCH_STATUS = 0)

BEGIN

FETCH NEXT FROM <@.variable_cursor_name, , @.test_cursor_variable>

PRINT 'put user defined code here'

END

But even after instantiating it - it doesn't work. The "FETCH_STATUS" must be seeded with a prior "FETCH" before it has any meaning. This leads me to ask: (1) how could such a widely circulated template have such a conspicuous error and (2) why doesn't TSQL have a looping mechanism that does not require such awkward FETCH "pre-seeding"?

p.s. How do I insert code into a forum post that is single-spaced, rather than duoble-spaced (like my ugly double-spaced snippet above)

The template is wrong. You may want to file a bug at http://connect.microsoft.com. Or you could post this in the Tools forum since the template is owned by the tools team and part of SSMS. The looping mechanism without FETCH has been requested frequently by customers and we are considering adding such a syntax in a future version of SQL Server.

As far the copy & paste, if you paste the text from say SSMS query editor as text (not formatted text/html) it will work fine. Quick and dirty way to do it is to paste the text in notepad and copy from there. Of course you will lose the color formatting. I like the behavior of ISQLW and often use it rather than SSMS. You could post about the copy / paste issue with SSMS in the Tools forum also.

Btw, you can also use the utility below to paste some copied content (html, rtf or such formatted text) as plain text easily using different hot key combination:

http://www.stevemiller.net/puretext/

Bad Practice: Calling Views From w/in Stored Procs

In general, is it bad practice to call a view from w/in a stored proc?
Some sites imply that SQL server creates two connections thereby
straining system resources that much more, some sites claim it doesn't.There is no penalty to using a view, whether inside a stored procedure
or not. There is no second connection from using a view.
If you have a source that actually says that, I would be concerned
about counting on that source for information.
Roy Harvey
Beacon Falls, CT
On 2 May 2006 10:45:32 -0700, "roy.@.nderson@.gm@.il.com"
<roy.anderson@.gmail.com> wrote:

>In general, is it bad practice to call a view from w/in a stored proc?
>Some sites imply that SQL server creates two connections thereby
>straining system resources that much more, some sites claim it doesn't.|||> Some sites imply that SQL server creates two connections thereby
> straining system resources that much more
Can you reference such a site / source?
A

Bad code clogs replication

I am using Transaction replication on sql2005 SP2.
When deploying a new stored procedure that is supposed to be tested, I
got an error stateing that the number of columns in the select did not
match the number in the insert. I checked the SP and it was fine.
1) Was this really relating to the code, or was there an error in
replication?
2) How do you clear 'bad' transactions from replication?
Thanks,
Peter Cwik
Use the skiperror parameter on the distribution agent to skip these
errors for transactional replication.
Can you provide me with the code for the proc in question. I have not
encountered such an error yet in SQL Server.
I normally deploy changes to procs using sp_addscriptexec.
On Jan 8, 2:15 pm, PeterCwik <pjc...@.gmail.com> wrote:
> I am using Transaction replication on sql2005 SP2.
> When deploying a new stored procedure that is supposed to be tested, I
> got an error stateing that the number of columns in the select did not
> match the number in the insert. I checked the SP and it was fine.
> 1) Was this really relating to the code, or was there an error in
> replication?
> 2) How do you clear 'bad' transactions from replication?
> Thanks,
> Peter Cwik

backupset

Here is my situation:
I have a stored procedure that runs full, differential and
log backups for a particular database.
Each backup has unique name based on the type, day of the
week and the time (e.g. Full_Backup_Monday_12-30).
Thus, backups are kept for one week.
When I review the backup set table, I see the name of my
backup, but in many cases I see an old date for the backup
and start and completion dates. However, when I review
the server, the newer backups are present.
My questions are:
Why isn't the backupset table being updated with the most
recent information?
When is the backupset table updated?
Thanks,
MichaelHi Michael,
Could you create a database backup as following steps?
1. Expand a server group, and then expand a server.
2. Expand databases, right-click the database, point to all tasks, and then
click backup database
3. Type the backup set name in the Name box and Select Database -complete
4. Under Destination, click Tape or Disk, and then specify a backup
destination.
If no backup destinations appear, click Add to add an existing backup
device or to create a new one.
5. Click Ok to create (Don't select the Schedule check box)
6. Check to see the backupset table again.
Is the backupset table updated immediately?
According to my test, the backupset table is updated on my side. When a
real backup finishes, a new record will be inserted in the table. Does it
work on your side? Generally, we do not recommended to directly query
system table. Could you tell me your detailed scenario?
This posting is provided "AS IS" with no warranties, and confers no rights.
Sincerely,
Michael Shao
Microsoft Support Engineer
| Content-Class: urn:content-classes:message
| From: "Michael" <michael_schall@.unionsanitary.com>
| Sender: "Michael" <michael_schall@.unionsanitary.com>
| Subject: backupset
| Date: Mon, 7 Jul 2003 16:51:46 -0700
| Lines: 22
| Message-ID: <06c801c344e2$b9f90bf0$a301280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcNE4rn5YC8MduACSZi2OM0YFuqZFQ==| Newsgroups: microsoft.public.sqlserver.server
| Path: cpmsftngxa09.phx.gbl
| Xref: cpmsftngxa09.phx.gbl microsoft.public.sqlserver.server:23185
| NNTP-Posting-Host: TK2MSFTNGXA11 10.40.1.163
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Here is my situation:
|
| I have a stored procedure that runs full, differential and
| log backups for a particular database.
|
| Each backup has unique name based on the type, day of the
| week and the time (e.g. Full_Backup_Monday_12-30).
| Thus, backups are kept for one week.
|
| When I review the backup set table, I see the name of my
| backup, but in many cases I see an old date for the backup
| and start and completion dates. However, when I review
| the server, the newer backups are present.
|
| My questions are:
| Why isn't the backupset table being updated with the most
| recent information?
|
| When is the backupset table updated?
|
| Thanks,
| Michael
|

Backups

I have created a backup schedule for all databases on a
SQL server. I have Created a Backup device for each db.
and a single stored procedure that is called with database
name, device & retain days passed as parameters. All
Devices are network locations. I didn't want these backups
to swallow the entire disk space so i set the retain days
to 14. After 2 weeks i hoped that each backup would have
been overwriten. Well that was my logic. 2 weeks are up
and the files created are still growing. After some
investigation it is apparent that the expiry dates have
been passed but the backups sets have not been
overwritten. So much for my plan. I have now re-read BOL
and realised that i have got my wires crossed, the entire
media is overwriten when the expiry dates of all backups
within have been reached. To me this is topsy turvy, why
would i wish to overwrite an entire backup file, maybe if
i had taken a back up of the backup then i would wish to
delete it. Have i yet again misunderstood BOL. What i want
to do is maintain a dynamic history of backups. I back up
my database to a device, this backup lasts for 2 weeks and
then is overwriten. so the actual physical file is never
deleted, only the contents within when the expiry date is
reached.
Help!!!!!!!Expiredays and retaindays are only there to not allow you to overwrite using
the INIT before a certain day. If you aren't using INIT or if you are using
NOINIT, it will always be append. And, it is all or nothing.
If you want generation handling, either use the Maint Wizard, a 3:rd party
like www.dbmaint.com or some TSQL programming to handle this (using more
than one backup device).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:a5ce01c40a8e$5c4c13e0$a601280a@.phx.gbl...
> I have created a backup schedule for all databases on a
> SQL server. I have Created a Backup device for each db.
> and a single stored procedure that is called with database
> name, device & retain days passed as parameters. All
> Devices are network locations. I didn't want these backups
> to swallow the entire disk space so i set the retain days
> to 14. After 2 weeks i hoped that each backup would have
> been overwriten. Well that was my logic. 2 weeks are up
> and the files created are still growing. After some
> investigation it is apparent that the expiry dates have
> been passed but the backups sets have not been
> overwritten. So much for my plan. I have now re-read BOL
> and realised that i have got my wires crossed, the entire
> media is overwriten when the expiry dates of all backups
> within have been reached. To me this is topsy turvy, why
> would i wish to overwrite an entire backup file, maybe if
> i had taken a back up of the backup then i would wish to
> delete it. Have i yet again misunderstood BOL. What i want
> to do is maintain a dynamic history of backups. I back up
> my database to a device, this backup lasts for 2 weeks and
> then is overwriten. so the actual physical file is never
> deleted, only the contents within when the expiry date is
> reached.
> Help!!!!!!!
>|||Thanks Tibor
Are there any sys SP's or XP's that can be used to edit
backup files? Maybe i could remove expired files..
I have created a stored procedure that looks at a backup
device and tells me which full, DIff and TL backups need
to be applied to restore to a specified point in time. It
looks as if this will nor work if i have to create new
devices..rats...
I pull my hair out some times with the illogical-ness of
SQL server

>--Original Message--
>Expiredays and retaindays are only there to not allow you
to overwrite using
>the INIT before a certain day. If you aren't using INIT
or if you are using
>NOINIT, it will always be append. And, it is all or
nothing.
>If you want generation handling, either use the Maint
Wizard, a 3:rd party
>like www.dbmaint.com or some TSQL programming to handle
this (using more
>than one backup device).
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message
>news:a5ce01c40a8e$5c4c13e0$a601280a@.phx.gbl...
database
backups
days
entire
why
if
want
up
and
is
>
>.
>|||There are no tool with which you can remove selective backups inside a
backup file, I'm afraid.
One alternative is to do append, say over one day (assume db backup once per
day and log backup once per hour). Then after one day, you rename the Active
to give it a timestamp (so you have generations) and then do INIT to the
active one. This is how we did it in Db Maint up until the current version,
where we decided to not do append anymore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:d7ca01c40a92$7eb09060$a101280a@.phx.gbl...
> Thanks Tibor
> Are there any sys SP's or XP's that can be used to edit
> backup files? Maybe i could remove expired files..
> I have created a stored procedure that looks at a backup
> device and tells me which full, DIff and TL backups need
> to be applied to restore to a specified point in time. It
> looks as if this will nor work if i have to create new
> devices..rats...
> I pull my hair out some times with the illogical-ness of
> SQL server
>
> to overwrite using
> or if you are using
> nothing.
> Wizard, a 3:rd party
> this (using more
> message
> database
> backups
> days
> entire
> why
> if
> want
> up
> and
> is|||Thanks tibor, thats great. Part of my backup script now
contains code to create a backup device every time it is
run that is named depending on a variable passed.
Alternating every week the physical file names change and
the retain days are set to 7. So as you advised i create a
file and add my backups. After a week i swich to a second
file and use this for a week. After another 7 days i
switch back to the original file that is now ready tbe
overwriten.
My SP that advises me of what backup files to aply now
works in pretty much the same way. It creates a device
based on the parameters and returns the backup history. it
then re-creates the device with the second file name and
apends this to the first run.
Thanks so much, you have been a great help..

>--Original Message--
>There are no tool with which you can remove selective
backups inside a
>backup file, I'm afraid.
>One alternative is to do append, say over one day (assume
db backup once per
>day and log backup once per hour). Then after one day,
you rename the Active
>to give it a timestamp (so you have generations) and then
do INIT to the
>active one. This is how we did it in Db Maint up until
the current version,
>where we decided to not do append anymore.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message
>news:d7ca01c40a92$7eb09060$a101280a@.phx.gbl...
It
you
on a
db.
have
are up
have
BOL
backups
maybe
wish to
back
weeks
never
date
>
>.
>|||I'm glad I could help, mat. Just one thing: As far as I can see, retains
days doesn't really buy you anything. I only mention this so you don't read
anything into this parameter which isn't there... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:d55d01c40aad$fb535920$a501280a@.phx.gbl...
> Thanks tibor, thats great. Part of my backup script now
> contains code to create a backup device every time it is
> run that is named depending on a variable passed.
> Alternating every week the physical file names change and
> the retain days are set to 7. So as you advised i create a
> file and add my backups. After a week i swich to a second
> file and use this for a week. After another 7 days i
> switch back to the original file that is now ready tbe
> overwriten.
> My SP that advises me of what backup files to aply now
> works in pretty much the same way. It creates a device
> based on the parameters and returns the backup history. it
> then re-creates the device with the second file name and
> apends this to the first run.
> Thanks so much, you have been a great help..
>
> backups inside a
> db backup once per
> you rename the Active
> do INIT to the
> the current version,
> message
> It
> you
> on a
> db.
> have
> are up
> have
> BOL
> backups
> maybe
> wish to
> back
> weeks
> never
> date

backups

Dear all,
I would like to know in what table the backups are stored.
I mean, I see in the SQL Server log registry these lines:
Database backed up: Database: DATA1, creation date(time):
2005/07/26(17:11:49), pages dumped: 63699, first LSN: 2724:195:1, last LSN:
2724:197:1, number of dump devices: 1, device information: (FILE=1,
TYPE=VIRTUAL_DEVICE: {'Legato#af0fc469-fcdc-4b4e-a05f-4a774487e651'}).
..
..
That's fine but from what table is retrieving SQL that information?
Best regards,Enric,
The info is scatted across a few tables in the msdb database.
Mainly backupset, but have a look at the others whose name starts with backu
p :)
Regards
AJ
"Enric" <Enric@.discussions.microsoft.com> wrote in message news:8AA0C8CB-9368-4C70-8883-444
2B4CAA10E@.microsoft.com...
> Dear all,
> I would like to know in what table the backups are stored.
> I mean, I see in the SQL Server log registry these lines:
> Database backed up: Database: DATA1, creation date(time):
> 2005/07/26(17:11:49), pages dumped: 63699, first LSN: 2724:195:1, last LSN
:
> 2724:197:1, number of dump devices: 1, device information: (FILE=1,
> TYPE=VIRTUAL_DEVICE: {'Legato#af0fc469-fcdc-4b4e-a05f-4a774487e651'}).
> ..
> ..
> That's fine but from what table is retrieving SQL that information?
> Best regards,
>
>|||Below four tables in the msdb database:
dbo.backupfile
dbo.backupmediafamily
dbo.backupmediaset
dbo.backupset
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:8AA0C8CB-9368-4C70-8883-4442B4CAA10E@.microsoft.com...
> Dear all,
> I would like to know in what table the backups are stored.
> I mean, I see in the SQL Server log registry these lines:
> Database backed up: Database: DATA1, creation date(time):
> 2005/07/26(17:11:49), pages dumped: 63699, first LSN: 2724:195:1, last LSN
:
> 2724:197:1, number of dump devices: 1, device information: (FILE=1,
> TYPE=VIRTUAL_DEVICE: {'Legato#af0fc469-fcdc-4b4e-a05f-4a774487e651'}).
> ..
> ..
> That's fine but from what table is retrieving SQL that information?
> Best regards,
>
>|||Thanks a lot to both,
"Tibor Karaszi" wrote:

> Below four tables in the msdb database:
> dbo.backupfile
> dbo.backupmediafamily
> dbo.backupmediaset
> dbo.backupset
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:8AA0C8CB-9368-4C70-8883-4442B4CAA10E@.microsoft.com...
>

Backup-only ID

I have a stored procedure that I use to execute database backups. It delete
s
old backup files and then creates the full backups of all the databases.
I want to create an ID that can only execute this procedure. I tried
creating a SQL ID with execute permission on this SP and then put the ID in
the db_backupoperator role in each database. But I keep running into
permission issues.
Any suggestions?What issues are you running into? Give specific examples and error messages
.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Stephanie" wrote:

> I have a stored procedure that I use to execute database backups. It dele
tes
> old backup files and then creates the full backups of all the databases.
> I want to create an ID that can only execute this procedure. I tried
> creating a SQL ID with execute permission on this SP and then put the ID i
n
> the db_backupoperator role in each database. But I keep running into
> permission issues.
> Any suggestions?
>

Backup/Restore Stored Procedure

Hi there

I just want to create Stored Procedure which will do following thing

i) Create a text file, which will have a Schema Script(including all the constraint & others, for a database(by passing Parameter)

ii) the same file must also have, all the data from the selected database tables,in again plain text format (could be CSV, or Tab SV, etc...)

I mean to say, the file should be act as the full backup of database, in plain text.

The same need to be done in the reverse order,this SP will do following things

i)Open the file created by first SP, create new database, with same name as backup in SP one

ii) create the tables, by reading the Schema script.

iii) Insert all the data in corrsponding tables.

For EX:

--creating backup

sp_CreateDatbaseBackup('DatabaseName')

--Restoring

sp_RestoreDatabaseBackup('FileName')

I know abou the Backup & restore in SQL Server 2005, but they gives me backup as .bkp file which is not editable. I too cant use the SSIS(just only SPs)

I want an editable full Backup and Restore.

Please help me in creating these SPs or any other idea, solution ?

OR

any other FREE third party tool which will make the Full database backup & restore in one go & in plain text file.

Regards,

Thanks.

Gurpreet S. Gill

Hi,

I had done something familiar to what you are looking for but i only backed up data. I used bcp to copy data to a file and read data from the file. I am not sure as to how u can back up the schema. If you need more details on the bcp code i can email it to you. Please give me ur email address.

Sapna

|||Sorry, actually the file created by bcp is not editable.|||

Hi Sapna

Please send me the SP or the related to it, hope it will help me. my mail ID are

gsgill76@.yahoo.com , gsgill76@.gmail.com

Thanks,

Regards.

Gurpreet S. Gill

|||I am able to solve the problem i.e the script of all the tables other like constaints, Indexs etc.

this is done with the help of Scptxfr.exe(i copied it from V2000), its working very fine in V2005 too.

now, regarding the other problem, scripting the data into, i use BCP & loop through all the table & merge all the files generated into one.
now the problem are
1) i am not able to distinguish between the data for table, i mean the data to which that table belongs to.
2)how can i restore that file?

Regards,
Thanks.
Gurpreet S. Gill|||Can you create individual files for each table using bcp or do you have to create one file. Also you can name the file name same as the table name. This will help you distinguish the tables. While reading the data back u need to specify the file name in the bcp command to open the particular file.

For my application: We used the c sharp code to zip all the individual (tables) files to one file and then encrypt it.

Backup/Restore Stored Procedure

Hi there

I just want to create Stored Procedure which will do following thing

i) Create a text file, which will have a Schema Script(including all the constraint & others, for a database(by passing Parameter)

ii) the same file must also have, all the data from the selected database tables,in again plain text format (could be CSV, or Tab SV, etc...)

I mean to say, the file should be act as the full backup of database, in plain text.

The same need to be done in the reverse order,this SP will do following things

i)Open the file created by first SP, create new database, with same name as backup in SP one

ii) create the tables, by reading the Schema script.

iii) Insert all the data in corrsponding tables.

For EX:

--creating backup

sp_CreateDatbaseBackup('DatabaseName')

--Restoring

sp_RestoreDatabaseBackup('FileName')

I know abou the Backup & restore in SQL Server 2005, but they gives me backup as .bkp file which is not editable. I too cant use the SSIS(just only SPs)

I want an editable full Backup and Restore.

Please help me in creating these SPs or any other idea, solution ?

OR

any other FREE third party tool which will make the Full database backup & restore in one go & in plain text file.

Regards,

Thanks.

Gurpreet S. Gill

Hi,

I had done something familiar to what you are looking for but i only backed up data. I used bcp to copy data to a file and read data from the file. I am not sure as to how u can back up the schema. If you need more details on the bcp code i can email it to you. Please give me ur email address.

Sapna

|||Sorry, actually the file created by bcp is not editable.|||

Hi Sapna

Please send me the SP or the related to it, hope it will help me. my mail ID are

gsgill76@.yahoo.com , gsgill76@.gmail.com

Thanks,

Regards.

Gurpreet S. Gill

|||I am able to solve the problem i.e the script of all the tables other like constaints, Indexs etc.

this is done with the help of Scptxfr.exe(i copied it from V2000), its working very fine in V2005 too.

now, regarding the other problem, scripting the data into, i use BCP & loop through all the table & merge all the files generated into one.
now the problem are
1) i am not able to distinguish between the data for table, i mean the data to which that table belongs to.
2)how can i restore that file?

Regards,
Thanks.
Gurpreet S. Gill|||Can you create individual files for each table using bcp or do you have to create one file. Also you can name the file name same as the table name. This will help you distinguish the tables. While reading the data back u need to specify the file name in the bcp command to open the particular file.

For my application: We used the c sharp code to zip all the individual (tables) files to one file and then encrypt it.

Backup/restore just stored procedures

How can I backup and restore just stored procedures. I know in the old
Enterprise Manager it was easy to script just all stored procedures so I
could do it that way but in the new Management Studio Stored Procedures are
now one of the object types that can be selected.
Any ways to get this done?
Regards,
Gary Blakely
You can script sps in SSMS as well. I would look again.
Andrew J. Kelly SQL MVP
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>
|||Hello,
In SSMS, expand the databases, right click above the database name..
Tasks -- Choose Generate SQL Script and follow based on the wizard.
Thanks
Hari
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>
|||Use F7 maybe when in the stored procedures folder? The right pain will then
be a selectable list of all sprocs that you can generate code for.
TheSQLGuru
President
Indicium Resources, Inc.
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>
|||F7. Yes. that's it.
Thanks
Gary
Regards,
Gary Blakely
Dean Blakely & Associates
www.deanblakely.com
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:OL5V$5CrHHA.4180@.TK2MSFTNGP04.phx.gbl...
> Use F7 maybe when in the stored procedures folder? The right pain will
> then be a selectable list of all sprocs that you can generate code for.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
>

Backup/restore just stored procedures

How can I backup and restore just stored procedures. I know in the old
Enterprise Manager it was easy to script just all stored procedures so I
could do it that way but in the new Management Studio Stored Procedures are
now one of the object types that can be selected.
Any ways to get this done?
Regards,
Gary BlakelyYou can script sps in SSMS as well. I would look again.
Andrew J. Kelly SQL MVP
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>|||Hello,
In SSMS, expand the databases, right click above the database name..
Tasks -- Choose Generate SQL Script and follow based on the wizard.
Thanks
Hari
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>|||Use F7 maybe when in the stored procedures folder? The right pain will then
be a selectable list of all sprocs that you can generate code for.
TheSQLGuru
President
Indicium Resources, Inc.
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>|||F7. Yes. that's it.
Thanks
Gary
Regards,
Gary Blakely
Dean Blakely & Associates
www.deanblakely.com
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:OL5V$5CrHHA.4180@.TK2MSFTNGP04.phx.gbl...
> Use F7 maybe when in the stored procedures folder? The right pain will
> then be a selectable list of all sprocs that you can generate code for.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
>

Backup/restore just stored procedures

How can I backup and restore just stored procedures. I know in the old
Enterprise Manager it was easy to script just all stored procedures so I
could do it that way but in the new Management Studio Stored Procedures are
now one of the object types that can be selected.
Any ways to get this done?
--
Regards,
Gary BlakelyYou can script sps in SSMS as well. I would look again.
--
Andrew J. Kelly SQL MVP
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>|||Hello,
In SSMS, expand the databases, right click above the database name..
Tasks -- Choose Generate SQL Script and follow based on the wizard.
Thanks
Hari
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>|||Use F7 maybe when in the stored procedures folder? The right pain will then
be a selectable list of all sprocs that you can generate code for.
--
TheSQLGuru
President
Indicium Resources, Inc.
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>|||F7. Yes. that's it.
Thanks
Gary
--
Regards,
Gary Blakely
Dean Blakely & Associates
www.deanblakely.com
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:OL5V$5CrHHA.4180@.TK2MSFTNGP04.phx.gbl...
> Use F7 maybe when in the stored procedures folder? The right pain will
> then be a selectable list of all sprocs that you can generate code for.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
>> How can I backup and restore just stored procedures. I know in the old
>> Enterprise Manager it was easy to script just all stored procedures so I
>> could do it that way but in the new Management Studio Stored Procedures
>> are now one of the object types that can be selected.
>> Any ways to get this done?
>> --
>> Regards,
>> Gary Blakely
>>
>

Backup, data and log files

Should data and log files be stored on different drives in
SQL Server 2000. And should the backup files be stored on
yet another drive? I have 2 drives available and have put
the data and log on one and the backup files on the other.
All of these are backed up to tape as well. Thanks.Ideally, your data files should be on one or more drives and log on separate
drives.
It really depends how large your data set is and how important absolute
performance is
to your database. If you have no performance problems with the current setup
and don't
foresee any, then there is no reason to change it.
"Mark" <Mark.Neale@.rspb.org.uk> wrote in message
news:073a01c39f91$5b98ca00$a601280a@.phx.gbl...
> Should data and log files be stored on different drives in
> SQL Server 2000. And should the backup files be stored on
> yet another drive? I have 2 drives available and have put
> the data and log on one and the backup files on the other.
> All of these are backed up to tape as well. Thanks.|||Its better to have log on different drive and db on different if it is
largely growing db
--
Shaju Thomas
e-Cosmos Technologies Ltd.,
Mail: shaju@.e-cosmostech.com
Phone(Off) : 51217038/39 Extn: 132
Mobile: +91 98455 21794
"Mark" <Mark.Neale@.rspb.org.uk> wrote in message
news:073a01c39f91$5b98ca00$a601280a@.phx.gbl...
> Should data and log files be stored on different drives in
> SQL Server 2000. And should the backup files be stored on
> yet another drive? I have 2 drives available and have put
> the data and log on one and the backup files on the other.
> All of these are backed up to tape as well. Thanks.|||On Fri, 31 Oct 2003 01:28:35 -0800, "Mark" <Mark.Neale@.rspb.org.uk>
wrote:
>Should data and log files be stored on different drives in
>SQL Server 2000. And should the backup files be stored on
>yet another drive? I have 2 drives available and have put
>the data and log on one and the backup files on the other.
>All of these are backed up to tape as well. Thanks.
If you had the money to spend, a general baseline suggestion would be
data on one drive, transaction log on another, and backups on yet
another... however, the "yet another" would probably be better if it
was a network share.
Separating the DB file and the transaction log helps with performance,
if you're using SCSI drives.(It might also help with performance if
you are using IDE drives, and each drive is on a separate IDE channel,
however, I can not make any guarantee on this. I *can* guarantee that
it won't do jack for performance if you have two separate IDE drives
on the same IDE channel.)
Separating the backup from both helps with reliability; if your
backups are on your database or transaction log disk, and that disk
dies, you've lost both. Putting the backups on another machine also
means you can restore from backup without having to retrieve a drive
from the machine that crashed.
Of course, you can get even better, more expensive, setups if you want
them... this is just the baseline.

backup to restore a blank database

Hi,
I want to backup the tables, stored procedures, and views from a database
without the data in tables. So, I can restore it to a different development
server.
What is the correct syntax on backup and restore for it?
Thanks.There's no TSQL command to backup only DDL. Use the scripting options instead. You didn't mention
version, so I assume 2005. Right-click the database, All Tasks, and from here you can get to the
scripting wizard. Also, there has been some improvements to the scripting in sp2.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:BD2FD877-52C4-4266-A4C4-F10B7E79801A@.microsoft.com...
> Hi,
> I want to backup the tables, stored procedures, and views from a database
> without the data in tables. So, I can restore it to a different development
> server.
> What is the correct syntax on backup and restore for it?
> Thanks.
>|||Thanks, Tibor.
Opps... :) I am trying to get an empty database from 2000 as well as 2005...
Thanks.|||Well, both Enterprise Manager as well as Management Studio has scripting options.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:9D7E379E-2505-4C24-B74C-2D98410B8336@.microsoft.com...
> Thanks, Tibor.
> Opps... :) I am trying to get an empty database from 2000 as well as 2005...
> Thanks.|||the script option will only do one at a time. :)
i wish to get all objects in one step, if tables or indexed views, i don't
want any data.|||Open the script wizard, this let you script several objects.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:0A62DB80-8A95-4256-826F-CF31F4D72ED7@.microsoft.com...
> the script option will only do one at a time. :)
> i wish to get all objects in one step, if tables or indexed views, i don't
> want any data.