Scripting

Hi all
How do I send a script to a SQL 7 engine?
I want to script a DB backup
Thanks!Hi,
Did you meant to backup a SQL 7 database if yes then login to query analyzer
and execute below script,
BACKUP database <dbname> to disk='d:\backup\dbname.bak' with init
You can also execute the above command using OSQL
OSQL -Usa -Ppass -Sserver (enter)
1> BACKUP database <dbname> to disk='d:\backup\dbname.bak' with init (enter)
2>go (enter)
Did I answered your question?
Thanks
Hari
MCDBA
"steve simpson" <simpsonst3@.comcast.net> wrote in message
news:410aaee7.1203726707@.msnews.microsoft.com...
> Hi all
> How do I send a script to a SQL 7 engine?
> I want to script a DB backup
> Thanks!|||I would like to put this osql script into a perl (or shell) script.
How'd I do that?
- manzoor|||If you just want to run OSQL in perl, here is one possible syntax.
$execString="-U$user -P$pass -S$server -d$db -i$fullName -n";
$result = `osql $execString`;
Bill
"Manzoorul Hassan" <manzoorul.hassan@.gmail.com> wrote in message
news:1105115335.911230.256540@.z14g2000cwz.googlegroups.com...
>I would like to put this osql script into a perl (or shell) script.
> How'd I do that?
> - manzoor
>

Script when database backup fails

Hello,

I would like to have a script , that sends a mail to the dba mail box when the database backup fails . The mail should be sent to the SMTP server.

I have the script which gives the whole output of the backup status but I would like it to change so that it fires only when a backup fails. Please suggest me what to do..

select
bmf.physical_device_name,
RIGHT(bmf.physical_device_name, CHARINDEX('\', REVERSE(bmf.physical_device_name))-1) as physical_device_file,
bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
bs.type,
bs.first_lsn,
bs.last_lsn,
bs.checkpoint_lsn,
bs.database_backup_lsn
into #backup
from
msdb.dbo.backupset bs,
msdb.dbo.backupmediafamily bmf
where bmf.media_set_id = bs.media_set_id
and bs.backup_finish_date is not null
AND bs.type = 'D'
AND bs.backup_start_date = (select max(backup_start_date) from msdb.dbo.backupset WHERE type = bs.type and database_name = bs.database_name)
order by bs.database_name, bs.backup_start_date asc

select @.message = @.message + char(13) + Char(13) + 'Backup Status' + Char(13)

DECLARE GetBackup CURSOR FOR
select database_name, backup_finish_date from #backup order by database_name

OPEN GetBackup
FETCH NEXT FROM GetBackup INTO @.dbname, @.Status

WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.message = @.message + @.dbname + ' backup up on ' + @.Status + Char(13)
FETCH NEXT FROM GetBackup INTO @.dbname, @.Status
END
Close GetBackup
Deallocate GetBackup

drop table #backup

print @.message

EXEC master.dbo.xp_smtp_sendmail
@.FROM = N'testsql2000@.is.depaul.edu',
@.TO = N'dvaddi@.depaul.edu',
@.server = N'smtp.depaul.edu',
@.subject = N'Status of sqlserver!',
@.type = N'text/html',
@.message = @.message

Thanks

How are you performing the backup? If you are using say SQLAgent jobs then why don't you just include an additional step at the end after the backup step which fires only if the backup fails. This can email the details and you don't even have to use extended SPs etc. I believe that SQLAgent has ways to send emails. Otherwise, you will have to check for status of backup also in the msdb tables since you cannot create triggers on system tables.

script to take backup of a database

Hi,

I am trying to create a script that takes backup of a sql database. The script is failing with message "backup failed with message..."

Option Explicit

Dim server

Dim backup

Set server = CreateObject("Microsoft.SQLServer.Management.SMO.Server")

Set backup = CreateObject("Microsoft.SQLServer.Management.SMO.Backup")

backup.Action = 0

backup.Database = "test_old"

backup.SqlBackup(server)

I think this is failing because i did not specify the backup file name. Can u please let me know how can i specify it?

Also it would be great if you can point me to some relevant documentation which shows how to use SMO with vb script.

Regards

Aseem Bansal

You can't use SMO with VBScript. (Well, you can, but you can't use it to communicate with anything but the default server on your local machine.)

Books Online has good examples how to backup a database.

|||

I do not have one in vbscript, but here is one in C#...

using System;

using System.Data;

using System.Collections;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Smo;

class Program

{

static void Main(string[] args)

{

BackupDeviceItem bdi =

new BackupDeviceItem("AdventureWorks.bak", DeviceType.File);

Backup bu = new Backup( );

bu.Database = "AdventureWorks";

bu.Devices.Add(bdi);

bu.Initialize = true;

// add percent complete and complete event handlers

bu.PercentComplete +=

new PercentCompleteEventHandler(Backup_PercentComplete);

bu.Complete +=new ServerMessageEventHandler(Backup_Complete);

Server server = new Server("localhost");

bu.SqlBackup(server);

Console.WriteLine(Environment.NewLine + "Press any key to continue.");

Console.ReadKey( );

}

protected static void Backup_PercentComplete(

object sender, PercentCompleteEventArgs e)

{

Console.WriteLine(e.Percent + "% processed.");

}

protected static void Backup_Complete(object sender, ServerMessageEventArgs e)

{

Console.WriteLine(Environment.NewLine + e.ToString( ));

}

}

|||

Can you do this in a windows form application please..? Seems like it doesn't work...I don't know.. If can please let me know..thank you...darshaka..

---

---

script to take backup of a database

Hi,

I am trying to create a script that takes backup of a sql database. The script is failing with message "backup failed with message..."

Option Explicit

Dim server

Dim backup

Set server = CreateObject("Microsoft.SQLServer.Management.SMO.Server")

Set backup = CreateObject("Microsoft.SQLServer.Management.SMO.Backup")

backup.Action = 0

backup.Database = "test_old"

backup.SqlBackup(server)

I think this is failing because i did not specify the backup file name. Can u please let me know how can i specify it?

Also it would be great if you can point me to some relevant documentation which shows how to use SMO with vb script.

Regards

Aseem Bansal

You can't use SMO with VBScript. (Well, you can, but you can't use it to communicate with anything but the default server on your local machine.)

Books Online has good examples how to backup a database.

|||

I do not have one in vbscript, but here is one in C#...

using System;

using System.Data;

using System.Collections;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Smo;

class Program

{

static void Main(string[] args)

{

BackupDeviceItem bdi =

new BackupDeviceItem("AdventureWorks.bak", DeviceType.File);

Backup bu = new Backup( );

bu.Database = "AdventureWorks";

bu.Devices.Add(bdi);

bu.Initialize = true;

// add percent complete and complete event handlers

bu.PercentComplete +=

new PercentCompleteEventHandler(Backup_PercentComplete);

bu.Complete +=new ServerMessageEventHandler(Backup_Complete);

Server server = new Server("localhost");

bu.SqlBackup(server);

Console.WriteLine(Environment.NewLine + "Press any key to continue.");

Console.ReadKey( );

}

protected static void Backup_PercentComplete(

object sender, PercentCompleteEventArgs e)

{

Console.WriteLine(e.Percent + "% processed.");

}

protected static void Backup_Complete(object sender, ServerMessageEventArgs e)

{

Console.WriteLine(Environment.NewLine + e.ToString( ));

}

}

|||

Can you do this in a windows form application please..? Seems like it doesn't work...I don't know.. If can please let me know..thank you...darshaka..

---

---

sql

Script to automatically schedule SSIS tasks?

Hi,

I have a SQL 2000 script which I use to automatically schedule various backup tasks. This script adds and schedules a full backup once a week, differential backups nightly, and log backups hourly, in addition to a couple other maintenance tasks such as rebuilding indexes.

The idea is that a less technically savvy person can set a few variables at the top of the script (such as DB name and backup file folders) and click 'execute' to run the script and schedule the backups etc all in one go, for different clients.

Since some of the stored procs I use in this script are deprecated in SQL 2005, I am trying to replicate this functionality in SSIS but am having trouble figuring out how I can get all of this functionality encapsulated in the same 'click and go' manner where the user can simply execute the package and all the jobs will be scheduled without any user interaction.

Is this even possible? Where should I be looking for examples of how to do this?

Thanks!

dtexecui.exe is the closest thing there is to a "click-and-go" interface. I suggest you evaluate that.

-Jamie

|||Yes, I was assuming that's how I would run the package, but what I am trying to figure out is how to set up scheduling of the different jobs within the package so that when I run it, they are all scheduled at the correct times (like one job is scheduled for nightly, one for weekly, etc etc).

There does not seem to be a 'scheduler' task in the toolbox of VS.
|||

graemeo wrote:

Yes, I was assuming that's how I would run the package, but what I am trying to figure out is how to set up scheduling of the different jobs within the package so that when I run it, they are all scheduled at the correct times (like one job is scheduled for nightly, one for weekly, etc etc).

There does not seem to be a 'scheduler' task in the toolbox of VS.

If you want to use a scheduler then use SQL Server Agent. There is no scheduler within a SSIS package and nor should there be.

-Jamie

|||Is there any way to do what I am attempting (provide the user with a simple script to schedule different backup jobs at varying times?) in SQL 2005 without using deprecated SP's?
|||

graemeo wrote:

Is there any way to do what I am attempting (provide the user with a simple script to schedule different backup jobs at varying times?) in SQL 2005 without using deprecated SP's?

Yes. This is a question about SQL Server agent (i.e. the scheduler). There are a bunch of sprocs dedicated to maintenance of SQL Server Agent jobs.

http://search.live.com/results.aspx?q=agent+stored+procedures&form=QBRE&q1=macro%3Asql_server_user_education.booksonline

-Jamie

Script for Adding Users after Restore?

When I backup a database to a single file in SQL2K, then restore that
database onto another machine, I lose users I have created (and I understand
that is expected behavior). Then I manually remove the name of the user
under "Name" since it doesn't have a "Login Name" and then just add this
same user as a New user and both the Name and Login Name appear and
everything is fine.
Is there a way to script this using SQL so if someone unfamiliar with EM
could simply run a script?This happens only with SQL Server authentication, not with Windows
authentication. So the first recommendation to avoid this problem is to use
Windows authentication.
In both cases, the login must exist on the new server.
For SQL Server authentication only use the sp_change_users_login to update
the link between logins and users of a database. For example,
sp_change_users_login 'auto_fix', myuser
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"Don Miller" wrote:

> When I backup a database to a single file in SQL2K, then restore that
> database onto another machine, I lose users I have created (and I understa
nd
> that is expected behavior). Then I manually remove the name of the user
> under "Name" since it doesn't have a "Login Name" and then just add this
> same user as a New user and both the Name and Login Name appear and
> everything is fine.
> Is there a way to script this using SQL so if someone unfamiliar with EM
> could simply run a script?
>
>|||Hope this helps!
http://support.microsoft.com/kb/246133/|||Thanks.
"Don Miller" <nospam@.nospam.com> wrote in message
news:e5$tB1scGHA.3888@.TK2MSFTNGP02.phx.gbl...
> When I backup a database to a single file in SQL2K, then restore that
> database onto another machine, I lose users I have created (and I
understand
> that is expected behavior). Then I manually remove the name of the user
> under "Name" since it doesn't have a "Login Name" and then just add this
> same user as a New user and both the Name and Login Name appear and
> everything is fine.
> Is there a way to script this using SQL so if someone unfamiliar with EM
> could simply run a script?
>

Script for Adding Users after Restore?

When I backup a database to a single file in SQL2K, then restore that
database onto another machine, I lose users I have created (and I understand
that is expected behavior). Then I manually remove the name of the user
under "Name" since it doesn't have a "Login Name" and then just add this
same user as a New user and both the Name and Login Name appear and
everything is fine.
Is there a way to script this using SQL so if someone unfamiliar with EM
could simply run a script?Hope this helps!
http://support.microsoft.com/kb/246133/|||This happens only with SQL Server authentication, not with Windows
authentication. So the first recommendation to avoid this problem is to use
Windows authentication.
In both cases, the login must exist on the new server.
For SQL Server authentication only use the sp_change_users_login to update
the link between logins and users of a database. For example,
sp_change_users_login 'auto_fix', myuser
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"Don Miller" wrote:
> When I backup a database to a single file in SQL2K, then restore that
> database onto another machine, I lose users I have created (and I understand
> that is expected behavior). Then I manually remove the name of the user
> under "Name" since it doesn't have a "Login Name" and then just add this
> same user as a New user and both the Name and Login Name appear and
> everything is fine.
> Is there a way to script this using SQL so if someone unfamiliar with EM
> could simply run a script?
>
>|||Thanks.
"Don Miller" <nospam@.nospam.com> wrote in message
news:e5$tB1scGHA.3888@.TK2MSFTNGP02.phx.gbl...
> When I backup a database to a single file in SQL2K, then restore that
> database onto another machine, I lose users I have created (and I
understand
> that is expected behavior). Then I manually remove the name of the user
> under "Name" since it doesn't have a "Login Name" and then just add this
> same user as a New user and both the Name and Login Name appear and
> everything is fine.
> Is there a way to script this using SQL so if someone unfamiliar with EM
> could simply run a script?
>

SCPTXFR.EXE-- Problem with Drop procedure

hi there
I am using SCPTXFR.EXE to generate script of the database(for backup),using following command.

SCPTXFR.EXE /s abc /d aaa /P abc12345 /f D:\HSE0607\SCHEMA.sql /q/r/T

Now the script is generating fine, but what i find that there is no statment like
"if not exists(...) "
for the Stored Procedure( i had applied the /r parameter), for the Create Tables it is there.

Why there is no statment IF NOT EXISTS(...) for Stored Procedure?
I need this Line, how can i do this?
Regards,
Thanks.
Gurpreet S. Gill

hey nobody help me here, but i find the solution at

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73290