backup stored procedures?

Hey all,
I have to copy all the stored procs that i have on my development server to the clients server when I deploy my .NET app. I do not have remote access to the clients server. Anyone knows how I could do this? Are there any settings to take care of?
TIAScript your procedures to a file and then execute it through your install.

How are you getting the tables created?|||The tables have already been created by someone else.

Could you explain more about scripting the procedures to a file and executing through install??

Thanks !!|||In Enterprise Manager, select your stored procedures, right-click, and the follow the menu steps to generate a script. What you will get will be SQL statements that create your procedures. Save them to a file and then execute them during your install.

Backup Shared Server DB using SQL Server 2005 Management Express

Hi,

I want to backup and/or have a local copy of my DB which is on a shared host. I have an ip addreess and user/pwd, but I am at a loss to find any help for the steps to accomplish this in Management express.

Am I using the right tool?

You will have to keep in mind that backup / restore will only either reach local drives or accessible UNC shares. I do my backups using the Copy Database wizard in management Studio, but if you only have SQL Server Express you can only use the SQL Hosting Pusblishing kit to destribute the database to a hoster and not the way back unless you local database is also published to the internet and the remote server is SQL Express.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks.

So what is the recommended method/product to take a backup of a SQL DB from a shared host that uses a SQL 2005 Server?

|||I would recommend buying a CAL of SQL Server to have the advanced tools for importing data including the copy database wizard.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Backup Plan

I am having a hard time setting up a backup plan for my sql server databases. Right now I create a copy of each database on another machine on the network once a day. Once a week I upload a MSAccess version to a remote site. Obviously there are huge amounts of potential data loss if a crash of some sort would occur locally.

My new plan is looking like this:

1) Run a complete database backup once a day

2) Run Transaction Log backups every hour

My questions are as follows:

1) What role does a "Backup Device" play in this process (I currently do everything through the Enterprise Manager interface)?

2) I am having a hard time scheduling mydatabase_complete.bak and mydatabase_trans.TRN at all. I get several errors.

3) I would like to run these backups on another network machine but am not seeing how to access "My Network Places" from the "Backup Database" utility in Enterprise Manager.

Any help in the form of answers or pointing me towards a useful resource for this process would be much appreciated!

Thanks in advance~1)A backup device is a logical name for a physical backup location that can be either on disk (a disk device) or tape (a tape device).
Rather than than having to specify the full pathname to the physical device, you can refrer to it by the Backup Device name.

2) What errors are you getting? Assuming that you have created a Job for both your Full and Transaction Log backups, you can simply set the schedule you want on the 'Schedules' tab. If you are using SQL Server 2005 you can pick from a list of existing schedules.

3) If you want to backup to a share on a network, you will need to specify the location using the full UNC path. You will also need to make sure that the MSSQLServer service is running under a Domain account rather than LocalSystem and that the Domain account has sufficient access to the share (i.e. Write permission).

I would advise you to take a look at BOL (Books On Line) to find out more about Backup/Restore.

HTH

Backup or copy database

Is it really so hard to backup.
First let me say: I dont have any expensive programs lide VS, only the MSDE desktop engine and Web Matrix and a Community Starter Kit v1.0 VBSDK.
But i have the free SQLExec version 1 from laplas-soft.com. It looks like you are in full control with this program, but when according to help file i try to backup i get: "Device error or device off-line. See the SQL Server error log for more details". Of course i start up by connecting to the database.

Any ideas?You need to create a backup device first. From books online:

<quote>
A. Back up the entire MyNwind database

Note The MyNwind database is shown for illustration only.

This example creates a logical backup device in which a full backup of the MyNwind database is placed.

-- Create a logical backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_1',
DISK ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind_1.dat'

-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_1

</quote>|||Sorry, but i'm new into .net and i dont know how to make such a device without more details.
I can see that it is not a .bat file i have to make, though.|||You would need to run this script through osql. ALternately, the data only could be backed up using bcp, a command line tool that can be placed in a batch file. Find BCP.EXE on your machine, run BCP /? for details on what it can do.|||I would much prefer to backup the whole database instead of data only. Because i want to copy the backup'ed database too. Can i find this osql?
And thanks..

I mean, i would like to just work on the remote databaseses. So if i can backup/copy it remotely, that may be best.|||Can't he just copy the *.MDF and *.LDF files his site is using?

Regards

Wayne Phipps|||OSQL is a program that is installed whenever MSDE or SQL Server is installed. You should search Deja.com or similar for all the details of using it, or maybe even usethis link to download the SQL Server Books Online.|||Well, yes, but that implies stopping and restarting the database, which I generally presume folks do not want to do. In addition, he would want to explicitly detatch the database, to reliably be able to reattach.|||Thanks for the help so far. I will look into it, and hope you will return. Its a bit late evening here in Denmark, so goodnight.|||Well, it seems from my experience with this, that it could take me a week to learn just to make a simple backup. I know it is not as the easy days with an Acces database, but maybe i have to try the way Wayne Phipps suggested. I dont mind shutting down the site(s) an early morning for a few minutes.

On the other side. It seems a simple thing with OSQL. Found this on deva (or google.com):
"
You can use the OSQL command prompt utility. For example:

OSQL -S MyServer -E -Q "BACKUP DATABASE MyDatabase TO
DISK='\\MyLaptop\BackupShare\MyDatabase.bak' WITH INIT, STATS=5"

Note that the SQL Server service account will need permissions to the
share. Alternatively, you can backup to a local file and then copy the
backup file to the remote machine.

See the Books Online for details.
"
Where can i see the books online? The download link doesnt work, nothing happens.

Can someone give me directions to make it work for an online sql database?
It looks like some autentification is missing in the above?|||Is it really so hard to backup.
Well, it seems from my experience with this, that it could take me a week to learn just to make a simple backup.

A backup of a database (SQL) shouldn't be as hard as that. You have not got correct approach yet, otherwise, you will feel the backup is just as easy as 1, 2, 3.

First, let me clarify your situation:
1. You wanted a (SQL) database backup. Thus, this issue is not related to something likeVS, ..., Web Matrix or a Community Starter Kit v1.0 VBSDK.

2. You have a MSDE desktop engine. As my knowledge, it doesn't have a Enterprise Manager as SQL7/2000 do. However, you do have a SQLExec that is somewhat similar to SQL2000 Enterprise Manager.

I am not fimiliar with MSDE and SQLExec, I will give you possible solutions for SQL Server 7/2000. These solutions will probably be suitable for your case. Otherwise, we start further discussion from there.

Method 1: SQL server backup using Enterprise Manager
1. Open SQL Enterprise Manger, right click the database you wanted to backup, say, Northwind, --> All tasks --> Back Database ... --> lefe click to open the window "SQL Server Backup - Northwind".

2. To add the destination of your backup, click the "Add" button to open "Select Backup Destination" sub-window: there will be a default directory in the "File name" box. For SQL2000, it would be "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\"

YouNEED to specify the file name:
a. you could look up a file from your disk by clicking the browse button "..." at the right of the box;
b. if you made the backup first time and you didn't have a backup file in your disk anywhere, youNEED just simply to add a fine name into the box, say Northwind.bak (for Northwind database). Now, the "File Name" box would have the file name as

"C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Northwind.bak"

3. Click "OK" button in "Select Backup Destination" window to close it. Then click "OK" button in your "SQL Server Backup - Northwind" window, you have done your backup SUCCEESSFULLY.

For your case, if you used SQLExec from laplas-soft.com, it would be only one window like this:"Database Backup" window of SQLExec, where the destination box includes directory as

"D:\DBB\SQL7\mcp_23\"

If you clicked the "Apply" button now, I guess, you would probably got the error "Device error or device off-line. See the SQL Server error log for more details" that is exactly the error you could get in SQL Enterprise Manage without specifying the destination file name but only directory.

Type a fine name under the directory or another full path file name into the desitination box, click "Apply" button, you have 95% chance to have your job done, I assume laplas-soft.com made its SQLExec's behavior as same or close as SQL Server. If this was true, you probably would need five minutes to read this suggestion and another five minutes to do your test. 10 minutes, instead of one week, you have your job done. ;)

More, in the next post.|||Thanks very much for taking time with this ghan from NYC. I am greatful to get all the help here at the forums.

Now i got a backup!!!
Everything seemes to have its tricks to fix.

Now what to do with it?
I would like to restore that backup. Restore could may be putting it on the remote server instead of the current. Some day it may be neccecery.
And if possible make new databases from this .bak file.
I loooked twice in the SQLExec for a restore function, but found none.

Waiting for input_|||Oh, it seems you got your solution within less than 10 minutes. :)

For completeness, I will first finish my previous discussion about the backup topic in the next post, then go to your restore issue. Be a little bit patient, you will get your solution for your new question in another 10 minutes.|||Continue fromMethod 1:

So, the point is you need to specify your backup device, or the backup file (wiht full path name) in your case.

Back to SQL Server 6.5, there was a "Backup Devices" Directory (also a "Database Devices" directory), at the same level of "Databases" directory in Enterprise Manager, then user would explicitly to make a backup device over there.

From SQL 7, these two directories ("Backup Devices" and "Database Devices") have been gone. When you make a new database, you do not need to create a "Database Device" first, SQL Server make it for you automatically (as a yourDatabaseName.mdf file for your database and a yourDatabaseName.ldf file for your database log file). However, when you backup a database, you have to make your backup device manually in some extent.

Besides the way, I mentioned in the previous post, you could create a backup device as follows:
1. Manually created a empty backup file anywhere in your disk, say, under
"C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\"

create a new empty "test.bak" file (in fact, it could be any name and suffix). Then, you could look up the device file from your backup process.

2. In the "Select Backup Destination" window, discussed in the previous post, there is another choice "Backup device", from there, you could choose <New Backup Device> and create a new device. But, it is nothing more than the way I have introduced in the previous post.

3. You could use Transac-SQL to do it, as Douglas mentioned by system SP "sp_addumpdevice".

Method 2: SQL Server backup using Detach/Attach methods
As mentioned by Wayne Phipps.

The Detach/Attach methods are available for SQL 2000 in both Enterprise Manager choice and Transac-SQL choice, but only Transac-SQL choice for SQL 7 as

sp_detach_db and sp_attach_db

See details from the thread246218.

For this method, beside it could be used as an alternative to backup/restore, it is mostly used as an alternative to import/export functions in SQL EM.

Method 3: SQL Server backup using Transac-SQL code

You could get info from SQL online, Backup. Although one could do the job in that way, but, as a programmer, I have never done it by myself.|||Para7,

Actually, you have already had your solution for restore issue from my previous post. Especially, from the thread246218 for details.

Basically, that uses the Detach/Attach methods. But, you could use backup/restore to do the trick also, see my nest post in another 10 minutes.

Backup operation terminating abnormally

Hi.
I have a problem with sql when trie backup my Data. The message is follow:
"Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as
CREATIVE FILE) operations on a database must be serialized. Reissue the
statement after the current backup, checkalloc, or file file manipulation
operation is competed.
Backup or restore operation terminating abnormally."
What i do for resolve this problem?
Thanks a lot
Gerardo
Look up the help for BACKUP. Some operations will block backup operations,
such as those mentioned in the message you posted. Someone must've been
running one of those operations while you were trying to perform the backup.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Gerardo" <Gerardo @.discussions.microsoft.com> wrote in message
news:8C3D21F2-B2CA-40BF-A09B-BBFCFC98DD36@.microsoft.com...
> Hi.
> I have a problem with sql when trie backup my Data. The message is follow:
> "Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such
as
> CREATIVE FILE) operations on a database must be serialized. Reissue the
> statement after the current backup, checkalloc, or file file manipulation
> operation is competed.
> Backup or restore operation terminating abnormally."
>
> What i do for resolve this problem?
> Thanks a lot
> Gerardo

Backup operation terminating abnormally

Hi.
I have a problem with sql when trie backup my Data. The message is follow:
"Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as
CREATIVE FILE) operations on a database must be serialized. Reissue the
statement after the current backup, checkalloc, or file file manipulation
operation is competed.
Backup or restore operation terminating abnormally."
What i do for resolve this problem?
Thanks a lot
GerardoLook up the help for BACKUP. Some operations will block backup operations,
such as those mentioned in the message you posted. Someone must've been
running one of those operations while you were trying to perform the backup.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Gerardo" <Gerardo @.discussions.microsoft.com> wrote in message
news:8C3D21F2-B2CA-40BF-A09B-BBFCFC98DD36@.microsoft.com...
> Hi.
> I have a problem with sql when trie backup my Data. The message is follow:
> "Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such
as
> CREATIVE FILE) operations on a database must be serialized. Reissue the
> statement after the current backup, checkalloc, or file file manipulation
> operation is competed.
> Backup or restore operation terminating abnormally."
>
> What i do for resolve this problem?
> Thanks a lot
> Gerardo

Backup operation terminating abnormally

Hi.
I have a problem with sql when trie backup my Data. The message is follow:
"Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as
CREATIVE FILE) operations on a database must be serialized. Reissue the
statement after the current backup, checkalloc, or file file manipulation
operation is competed.
Backup or restore operation terminating abnormally."
What i do for resolve this problem?
Thanks a lot
GerardoLook up the help for BACKUP. Some operations will block backup operations,
such as those mentioned in the message you posted. Someone must've been
running one of those operations while you were trying to perform the backup.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Gerardo" <Gerardo @.discussions.microsoft.com> wrote in message
news:8C3D21F2-B2CA-40BF-A09B-BBFCFC98DD36@.microsoft.com...
> Hi.
> I have a problem with sql when trie backup my Data. The message is follow:
> "Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such
as
> CREATIVE FILE) operations on a database must be serialized. Reissue the
> statement after the current backup, checkalloc, or file file manipulation
> operation is competed.
> Backup or restore operation terminating abnormally."
>
> What i do for resolve this problem?
> Thanks a lot
> Gerardo

backup locally and copy file vs backup remotely

Is it more or less the same amount of time to backup a database locally and
then copy the file to another server vs backing up remotely to the
destination ?
ThanksIt is more time to backup locally and copy but I would rather take the extra
time if possible for reliability.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Hassan" <hassan@.test.com> wrote in message
news:egT4dqCRIHA.4880@.TK2MSFTNGP03.phx.gbl...
> Is it more or less the same amount of time to backup a database locally
> and then copy the file to another server vs backing up remotely to the
> destination ?
> Thanks|||If reliability in your backups is a concern you can use the (new in SQL
Server 2005) CHECKSUM option of the BACKUP command and the RESTORE VERIFYONL
Y
command. See BOL for more details.
And of course the only way to prove that your backup is valid is testing a
real restore on some other database server.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"jason" wrote:

> It is more time to backup locally and copy but I would rather take the ext
ra
> time if possible for reliability.
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
> "Hassan" <hassan@.test.com> wrote in message
> news:egT4dqCRIHA.4880@.TK2MSFTNGP03.phx.gbl...
>

backup locally and copy file vs backup remotely

Is it more or less the same amount of time to backup a database locally and
then copy the file to another server vs backing up remotely to the
destination ?
Thanks
It is more time to backup locally and copy but I would rather take the extra
time if possible for reliability.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Hassan" <hassan@.test.com> wrote in message
news:egT4dqCRIHA.4880@.TK2MSFTNGP03.phx.gbl...
> Is it more or less the same amount of time to backup a database locally
> and then copy the file to another server vs backing up remotely to the
> destination ?
> Thanks
|||If reliability in your backups is a concern you can use the (new in SQL
Server 2005) CHECKSUM option of the BACKUP command and the RESTORE VERIFYONLY
command. See BOL for more details.
And of course the only way to prove that your backup is valid is testing a
real restore on some other database server.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"jason" wrote:

> It is more time to backup locally and copy but I would rather take the extra
> time if possible for reliability.
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
> "Hassan" <hassan@.test.com> wrote in message
> news:egT4dqCRIHA.4880@.TK2MSFTNGP03.phx.gbl...
>

backup locally and copy file vs backup remotely

Is it more or less the same amount of time to backup a database locally and
then copy the file to another server vs backing up remotely to the
destination ?
ThanksIt is more time to backup locally and copy but I would rather take the extra
time if possible for reliability.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Hassan" <hassan@.test.com> wrote in message
news:egT4dqCRIHA.4880@.TK2MSFTNGP03.phx.gbl...
> Is it more or less the same amount of time to backup a database locally
> and then copy the file to another server vs backing up remotely to the
> destination ?
> Thanks|||If reliability in your backups is a concern you can use the (new in SQL
Server 2005) CHECKSUM option of the BACKUP command and the RESTORE VERIFYONLY
command. See BOL for more details.
And of course the only way to prove that your backup is valid is testing a
real restore on some other database server.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"jason" wrote:
> It is more time to backup locally and copy but I would rather take the extra
> time if possible for reliability.
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
> "Hassan" <hassan@.test.com> wrote in message
> news:egT4dqCRIHA.4880@.TK2MSFTNGP03.phx.gbl...
> > Is it more or less the same amount of time to backup a database locally
> > and then copy the file to another server vs backing up remotely to the
> > destination ?
> >
> > Thanks
>