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 SQL Database in asp.net

Hi ,

Anyone knows ,How to backup sql Database in asp.net .and I need to get a .bak file through asp.net.

Pls help me.

Tamil

Assuming that the client has the required permissions to perform a backup (database permissions and permissions on the directory where you're saving the file), then you can run the [BACKUP DATABASE] command via the SQLCommand object.

Set the command type to Text, provide the appropriate SQL, and executeNonQuery. There's an MSDN example here (search the page for BACKUP) :http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.deletecommand.aspx

But note that if you have a large-ish database, you may want to up the timeout property to ensure the backup completes.

|||

Hi I got the backup database locally.But now i need to get backup for remote server.

Here is my code.

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.IO;

using System.Collections.Generic;

using System.Text;

using System.Text.RegularExpressions;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

using s= SQLDMO;

publicpartialclassDefault3 : System.Web.UI.Page

{

protectedvoid Page_Load(object sender,EventArgs e)

{

Panel1.Visible =false;

Label3.Visible =false;

if (!IsPostBack)

{

SQLDMO.SQLServer oSQLServer =new SQLDMO.SQLServer();

SQLDMO.Restore oRestore =new SQLDMO.Restore();

SQLDMO.Database oDatabase =new SQLDMO.Database();

oSQLServer.Connect("COMP11\\SQLEXPRESS","sa","123");

foreach (SQLDMO.Database dbin oSQLServer.Databases)

{

drpdb.Items.Add(db.Name);

}

}

}

protectedvoid Butback_Click(object sender,EventArgs e)

{

Microsoft.SqlServer.Management.Common.ServerConnection serverConnection =new Microsoft.SqlServer.Management.Common.ServerConnection("COMP11\\SQLEXPRESS");

Server s;

s =newServer(serverConnection);Database db;

db = s.Databases[drpdb.SelectedItem.Text];

int rec;

rec =Convert.ToInt32(db.DatabaseOptions.RecoveryModel);

Backup sbackup =newBackup();

sbackup.Action =BackupActionType.Database;

sbackup.BackupSetDescription ="Full Backup";

sbackup.BackupSetName ="Database Backup";

sbackup.Database = drpdb.SelectedItem.Text;

BackupDeviceItem dbi;

string path = drpdb.SelectedItem.Text +".bak";

string checkpath ="D:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Backup\\" + path;if (File.Exists(checkpath)==true)

{

Panel1.Visible =true;

Label2.Text ="";Label3.Visible =false;

}

else

{

Label3.Visible =true;

Panel1.Visible =false;dbi =newBackupDeviceItem(path,DeviceType.File);

sbackup.Devices.Add(dbi);

sbackup.Incremental =false;

sbackup.LogTruncation =BackupTruncateLogType.Truncate;

sbackup.SqlBackup(s);

Label3.Text ="Full Backup complete.";

sbackup.Devices.Remove(dbi);

}

}

protectedvoid Button1_Click(object sender,EventArgs e)

{

Microsoft.SqlServer.Management.Common.ServerConnection serverConnection =new Microsoft.SqlServer.Management.Common.ServerConnection("COMP11\\SQLEXPRESS");

Server s;

s =newServer(serverConnection);Database db;

db = s.Databases[drpdb.SelectedItem.Text];

int rec;

rec =Convert.ToInt32(db.DatabaseOptions.RecoveryModel);

Backup sbackup =newBackup();

sbackup.Action =BackupActionType.Database;

sbackup.BackupSetDescription ="Full Backup";

sbackup.BackupSetName ="Database Backup";

sbackup.Database = drpdb.SelectedItem.Text;

BackupDeviceItem dbi;

string path = drpdb.SelectedItem.Text +".bak";

string checkpath ="D:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Backup\\" + path;

File.Delete(checkpath);

dbi =newBackupDeviceItem(path,DeviceType.File);

sbackup.Devices.Add(dbi);

sbackup.Incremental =false;sbackup.LogTruncation =BackupTruncateLogType.Truncate;

sbackup.SqlBackup(s);

Label3.Visible =true;

Label3.Text = drpdb.SelectedItem.Text +"Backup complete.";

sbackup.Devices.Remove(dbi);

}

protectedvoid Button2_Click(object sender,EventArgs e)

{

Panel1.Visible =true;Label2.Text ="Selected " + drpdb.SelectedItem.Text +" Database fully not Completed!";

}

}

Please help me!

Tamil

|||

Hi

Anyoneknows

I need to get Backup a database from Remote Server.

Tamil

|||

Hi,

check this

http://www.codeplex.com/SqlWebAdmin

|||

hi Mehedi Hasan

I opened that URL.but i didnt understand ,Is it match my requirement?I need to get a backup for SQL Database from remote Server.I already done for local backup.But i need to get a backup for SQL Databases from REmote Server.

Please help me with example.

Tamil

|||

hi friends,,

I got it how to backup database in remote Server.We need to give login secure false then it connects the other server.

Tamil

Backup SQL database from ASP.NET Pages

Is there possibility to make backup of MS SQL database directly from ASP.NET pages?

Yes, if you create a DTS package to backup the database you can always call that package in code:

http://www.c-sharpcorner.com/UploadFile/fbulovic/DTSnCS12062005072324AM/DTSnCS.aspx

Hope this helps

|||

If you're using SQL Server 2005, you can also use SQL Management Objects (SMO) to write code to directly backup the database. TheBacking Up and Restoring Databases and Transaction Logs help topic in BOL has the general information and links on the objects to use.

Don

Backup MSDE database from .bat file

Hi,
I have seen hundreds of suggestions on how to backup a MSDE database on
the net, most of them have different approaches.
Does it exist an 'approved' way of doing this?
I need to run a .bat file which backs up my MSDE database (i.e. stops
the server, performs copying and the starts the MSDE engine again).
Thanks for hints on where to find the 'bulletproof' way of doing this.
I also need to know how to restore such a backup ;-)
Thanks if there is a kind soul somewhere
best regards
Tor
What you are doing is called a "cold" backup. Which doesn=B4t check the
integrity of the backup afterwards. If you are not concerned about
second or minutes of performance in your backup time frame you can do a
"hot" backup which does a normal backup on SQL Server (Sqls server
sercice running) and additionally can do a verify afterwards the
backup.
Look for the BACKUP statement in the BOL for more information.
Depending on how time critical your backup and your data is, I would
also consider using a transactional / differential backup on top of the
Full backup. This allows you (with the right recovery mode) to do a
restore point-in-time. Look for Restore and backup considerations in
the BOL for further information.
HTH, Jens Suessmeyer.
|||Jens,
Thanks for your quick reply. As this is a small database I assume that
it wouldn't take too long to do a 'hot' backup. However this is a MSDE
install made from a third party application and I don't know what 'BOL'
is.
If you or someone else can point me in a direction where I can find
approved scripts for backup and restore in my sceanrio I would
appreciate that. I don't want to find out when it is too late that the
performed scripts were inadequate ;-|
Thanks again
Tor
|||hi,
bushtor wrote:
> Jens,
> Thanks for your quick reply. As this is a small database I assume
> that it wouldn't take too long to do a 'hot' backup. However this is
> a MSDE install made from a third party application and I don't know
> what 'BOL' is.
> If you or someone else can point me in a direction where I can find
> approved scripts for backup and restore in my sceanrio I would
> appreciate that. I don't want to find out when it is too late that
> the performed scripts were inadequate ;-|
http://support.microsoft.com/default...-US;q325003#12
have a look at the entire article about oSql.exe "uses" so that you can pass
a .sql file to oSql.exe itself from a bat file to perform the required
task..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
Thanks for the
http://support.microsoft.com/default...-US;q325003#12
link.
I played a bit with the osql commands but I couldn't get it to work due
to lack of access rights.
This MSDE backup should be run by a 'normal' user with no admin
priviledges. I hoped to put the necessary commands in a .bat file.
However the logged in user did not have the appropriate rights when I
tried to execute the osql backup command. Can I somehow explicit input
the db's 'sa' password and execute:
osql -E -q "EXEC sp_grantdbaccess 'sa', 'sapassword'"
osql -E -q "BACKUP DATABASE mydb TO DISK = 'C:\mydb.bak'"
-- logout command ?
... or how do I do this to have an unpliviledged user to perform a
backup?
regards
Tor
|||hi,
bushtor wrote:
> tried to execute the osql backup command. Can I somehow explicit
> input the db's 'sa' password and execute:
> osql -E -q "EXEC sp_grantdbaccess 'sa', 'sapassword'"
> osql -E -q "BACKUP DATABASE mydb TO DISK = 'C:\mydb.bak'"
> -- logout command ?
define a SQL Server Login for backup operations and map it to a database
user (and make it) member of the db_backupoperator fixed role..
(http://msdn.microsoft.com/library/de...urity_6ndx.asp)
this will "limit" permissions and privileges for that login so that you can
create a bat file including username and password in clear..
osql -S(Local)\InstanceName -Uthe_backup_user -Pthe_wpd -Q "BACKUP DATABASE
mydb TO DISK = 'C:\mydb.bak'" >c:\BckLog.txt
oSql is case sensitive for the supplyed parameters..
http://msdn.microsoft.com/library/de..._osql_1wxl.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> skrev i melding
news:44rt36F3liojU1@.individual.net...

> define a SQL Server Login for backup operations and map it to a database
> user (and make it) member of the db_backupoperator fixed role..
>
(http://msdn.microsoft.com/library/de...-us/adminsql/a
d_security_6ndx.asp)
> this will "limit" permissions and privileges for that login so that you
can
> create a bat file including username and password in clear..
> osql -S(Local)\InstanceName -Uthe_backup_user -Pthe_wpd -Q "BACKUP
DATABASE
> mydb TO DISK = 'C:\mydb.bak'" >c:\BckLog.txt
Hi,
Thanks for the example. But can you elaborate '(Local)\InstanceName' ? Is
this a literal parameter or should I replace it with something?
Will 'the _backup_user' be logged in with MSDE until the computer restarts
or is this a persistent db user from the first time this command is run?
And we can run this command once a day without problems?
What will the restore command look like for a backup like this?
Thanks again for comments on this issue
best regards
Tor
|||Just an additional question to my post just above this one:
Is 'the_backup_user' in the command above the windows user name (who has no
admin rights)?
Isn't it necessary to specify the MSDE SA username and its password
somewhere with these commands?
Thanks for clarification on this in addition to my issues in the post above
regards
Tor
|||hi,
bushtor wrote:
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> skrev i melding
> news:44rt36F3liojU1@.individual.net...
> (http://msdn.microsoft.com/library/de...-us/adminsql/a
> d_security_6ndx.asp)
> Hi,
> Thanks for the example. But can you elaborate '(Local)\InstanceName'
> ? Is this a literal parameter or should I replace it with something?
it's not a literal.. SQL Server 2000 grants you to install one or more
instances of the engine per machine.. you can have up to 16 instances per
machine where only 1 can be the default instance and all the other will be
named instances
http://msdn.microsoft.com/library/de...setup_2xmb.asp
default instances are "known" to users as "(Local)" for local connections
and "ComputerName" for remote connections...
named instances are "known" to users as "(Local)\InstanceName" for local
connections and "ComputerName\InstanceName" for remote connections...

> Will 'the _backup_user' be logged in with MSDE until the computer
> restarts or is this a persistent db user from the first time this
> command is run?

> Is 'the_backup_user' in the command above the windows user name (who
> has no admin rights)?
>
the "the_backup_user" is intended as a SQL Server Login, so that you do not
have to reboot, disconnect or switch to that Windows account.. there will
be no corresponding Windows account but just a SQL Server login you provide
for this quite-admin task..

> And we can run this command once a day without problems?
yes... or even schedule it via AT/SCHTASKS...

> What will the restore command look like for a backup like this?
osql -S(Local)\InstanceName -Uthe_Restore_user -Pthe_wpd -Q "RESTORE
DATABASE [MyDbl] FROM DISK = N'c:\MydB,bak' WITH FILE = 1" >c:\RestLog.txt
to restore a database the logged user has to be member of the sysadmin or
dbcreatore server role or be member of the db_owner of the database to be
restored..
no one must be connected to the database while restoring it..
http://msdn.microsoft.com/library/de...ra-rz_25rm.asp
usually the restore operation is not performed on a "routine" base but only
on demand when required, so you usually do not set up an apposit script for
it but perform it if and when required to.. and in this case you can use
"sa" SQL Server login as well without the security hole to store it's
credential (the pwd) in a bat file (which is not a good idea indeed :D)

> Isn't it necessary to specify the MSDE SA username and its password
> somewhere with these commands?
as long as you use another (the_backup_user) login/user with enogth
permissions you are not required to use "sa"... "sa" is a special "system"
SQL Server login member of the sysadim server role and can perform whatever
tast on a SQL Server instance... usually you should provide a policy of
least privileges and thus only grant the permissions as required, no more,
better less :D (you can later adjust)... and I will always stress this
politics :D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote
news:453od6F4oru9U1@.individual.net...

> it's not a literal.. SQL Server 2000 grants you to install one or more
> instances of the engine per machine.. you can have up to 16 instances per
> machine where only 1 can be the default instance and all the other will be
> named instances
Will the name(s) of the(se) instance(s) be listed in the small MSDE SQL
'manager' applet opened from the systray icon?
I haven't had access to the concerned computer for a week but I will try
this tomorrow. Thanks a lot for all your hints.
Tor

Backup MSDE database

How add backup job schedule MSDE database with sqldmo(vb.net). Freq_type =
daily and file name (.bak and .log) is different for all days.
hi Viktor,
Viktor Zadro wrote:
> How add backup job schedule MSDE database with sqldmo(vb.net).
> Freq_type = daily and file name (.bak and .log) is different for all
> days.
hi Viktor,
as you are doing a scheduled backup job, you should resort on
Transact-SQL...
create your job and job schedules as needed and, regarding the step, add a
T-SQL step with a command that will auto-modify the backup file name at each
execution like
SET NOCOUNT ON
DECLARE @.fName VARCHAR(256)
SELECT @.fName = CONVERT(VARCHAR(19), getdate() , 120)
SELECT @.fName = REPLACE( @.fName , ' ' , '_')
SELECT @.fName = REPLACE( @.fName , '-' , '_')
SELECT @.fName = REPLACE( @.fName , ':' , '_')
SELECT @.fName = 'C:\' + @.fName + '.bak'
SELECT @.fName
BACKUP DATABASE [a] TO DISK = @.fName WITH INIT ,
NOUNLOAD ,
NAME = N'a BackUp',
NOSKIP ,
STATS = 10,
NOFORMAT
that will backup to
C:\2005_01_13_12_50_25.bak
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

backup log

I'm trying to export my database to a server (WebMatrixHosting.net) ...
So I'm using the DTS - Import/Export Wizard of MSSQL Tools ...
It generates the script ok, but when the copy starts I receive an error: Failed to copy objects from SQL Server to SQL Server ...
Looking at the log file I found out the error: BACKUP LOG permission denied in database 'database_name' ...
So I set Backup log permission using the Enterprise Manager to my database. But the error remains.
What's wrong and how to fix it?
ThanksCont.

I set Backup log permission for my database locally, not the database_name that is returning an error. (stupid isn't?)
I need the command line to set permission for the backup log for database_name (using the query analyser), because I'm unable to use Enterprise Manager.
Thanks