Automating Generate Script in Command Line

Hello,
Is there a way to automate Generate Script in 2005, so by clicking a link it
generates script which is using a command line and not using Management
Studio Tasks->Generate Script ?
--
MikeMike
There are a new objects library SQL SMO (.NET) to deal with such reports.
Previously we used ( and if you still have SQL Server 2000) SQL DMO object
library
"Mike9900" <Mike9900@.discussions.microsoft.com> wrote in message
news:3F3E26B8-C60A-4E37-A7DC-87BA9A515A1D@.microsoft.com...
> Hello,
> Is there a way to automate Generate Script in 2005, so by clicking a link
> it
> generates script which is using a command line and not using Management
> Studio Tasks->Generate Script ?
> --
> Mike|||What do you want to script ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||SQl Server 2005 database.
--
Mike
"Jens" wrote:

> What do you want to script ?
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

Automating Bkp & restore from command line

Currently developing a small app (Access) I'm using the same app on 3 SQL
Databases:
1 - Production: MS SQL 2000 server
2 - Test: MS SQL 2000 server
3 - Test: MSDE
As I'm modifyng extensively db structure I have to manually move modified
dbs between the 3 servers daily. multiple times, and got bored to manually
select each time paths and options (overwrite, force restore, devices etc)
So I wrote a small script in QA and thought if possible to runnit from
commandline (OSQL would do the job?). But I still miss a statement to
specifiy to wich DB the script must connect before doing th rest:
Here's my code and I've got a trutsed connection, so y would run:
osql -q "Restore.sql" -E:
The script contains:
USE MASTER
GO
RESTORE DATABASE Test
FROM DISK = 'C:\test.BAK'
WITH REPLACE,
MOVE 'Test_Log' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.LDF',
MOVE 'Test_dat' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.MDF'
What am I missing to connect to the prper server?
Thanks
You first say:

> But I still miss a statement to
> specifiy to wich DB the script must connect before doing th rest:
And then:

> What am I missing to connect to the proper server?
Which one is it? Database or server? To specify a server, you use the -S switch for OSQL. The db is a bit more
problematic as it is included in the RESTORE command, but my guess is that it is server you are after.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Atlas" <atlaspeak@.my-deja.com> wrote in message news:CMJuc.17145$G%.462@.tornado.fastwebnet.it...
> Currently developing a small app (Access) I'm using the same app on 3 SQL
> Databases:
> 1 - Production: MS SQL 2000 server
> 2 - Test: MS SQL 2000 server
> 3 - Test: MSDE
> As I'm modifyng extensively db structure I have to manually move modified
> dbs between the 3 servers daily. multiple times, and got bored to manually
> select each time paths and options (overwrite, force restore, devices etc)
> So I wrote a small script in QA and thought if possible to runnit from
> commandline (OSQL would do the job?). But I still miss a statement to
> specifiy to wich DB the script must connect before doing th rest:
> Here's my code and I've got a trutsed connection, so y would run:
> osql -q "Restore.sql" -E:
> The script contains:
> USE MASTER
> GO
> RESTORE DATABASE Test
> FROM DISK = 'C:\test.BAK'
> WITH REPLACE,
> MOVE 'Test_Log' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.LDF',
> MOVE 'Test_dat' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.MDF'
>
> What am I missing to connect to the prper server?
> Thanks
>
|||osql -S [servername] ?
Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup
"Atlas" <atlaspeak@.my-deja.com> wrote in message
news:CMJuc.17145$G%.462@.tornado.fastwebnet.it...
> Currently developing a small app (Access) I'm using the same app on 3 SQL
> Databases:
> 1 - Production: MS SQL 2000 server
> 2 - Test: MS SQL 2000 server
> 3 - Test: MSDE
> As I'm modifyng extensively db structure I have to manually move modified
> dbs between the 3 servers daily. multiple times, and got bored to manually
> select each time paths and options (overwrite, force restore, devices etc)
> So I wrote a small script in QA and thought if possible to runnit from
> commandline (OSQL would do the job?). But I still miss a statement to
> specifiy to wich DB the script must connect before doing th rest:
> Here's my code and I've got a trutsed connection, so y would run:
> osql -q "Restore.sql" -E:
> The script contains:
> USE MASTER
> GO
> RESTORE DATABASE Test
> FROM DISK = 'C:\test.BAK'
> WITH REPLACE,
> MOVE 'Test_Log' TO 'C:\DBMS\Microsoft SQL Server
2000\MSSQL\Data\test.LDF',
> MOVE 'Test_dat' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.MDF'
>
> What am I missing to connect to the prper server?
> Thanks
>
|||Hi,
OSQL -S Server_name -E -i c:\Restore.sql -oc:\restore.log
Note: -q option is to give the query in the OSQL itself. Use iption "i" to
input a file.
Thanks
Hari
MCDBA
"Atlas" <atlaspeak@.my-deja.com> wrote in message
news:CMJuc.17145$G%.462@.tornado.fastwebnet.it...
> Currently developing a small app (Access) I'm using the same app on 3 SQL
> Databases:
> 1 - Production: MS SQL 2000 server
> 2 - Test: MS SQL 2000 server
> 3 - Test: MSDE
> As I'm modifyng extensively db structure I have to manually move modified
> dbs between the 3 servers daily. multiple times, and got bored to manually
> select each time paths and options (overwrite, force restore, devices etc)
> So I wrote a small script in QA and thought if possible to runnit from
> commandline (OSQL would do the job?). But I still miss a statement to
> specifiy to wich DB the script must connect before doing th rest:
> Here's my code and I've got a trutsed connection, so y would run:
> osql -q "Restore.sql" -E:
> The script contains:
> USE MASTER
> GO
> RESTORE DATABASE Test
> FROM DISK = 'C:\test.BAK'
> WITH REPLACE,
> MOVE 'Test_Log' TO 'C:\DBMS\Microsoft SQL Server
2000\MSSQL\Data\test.LDF',
> MOVE 'Test_dat' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.MDF'
>
> What am I missing to connect to the prper server?
> Thanks
>

automatically compress backups

I have a maintenance plan for handling backups. I need to be able to amend the jobs so that the latest backup can be compressed (e.g. command line compression utility) before it is copied off across the network to a DR machine.

The issue is that the Maintenance Plan produces data/time specific backup file names - mulitiple backups stored in the same directory. Therefore, within a job step, I'm finding it difficult to code it such that only the latest backup is compressed. The command line syntax of these compression utilities is somewhat limited...

e.g. wzzip zipname.zip dbbackup_*.bak

nb. The '*' represents the wild card for the date/time part of the backup files created by the maintenance plan.

The problem is that the above command would keep adding multiple backups to the same zip archive. What I want is to simply add only the latest backup to the archive but to achieve this I would have to know the name of the backup file (created by the maintenance plan) programatically within the job.

I would have thought this was a fairly common requirement/problem. Has anyone got some suggestions/solutions?

CliveRE:
Q1 Has anyone got some suggestions/solutions?

A1 I doubt you'll like hearing this, my suggestion is don't compress production backups if you can possibly avoid it in any production setting (especially certain kinds of software based compression).

I've seen too many failed validation restores that seem to have only the fact of having been compressed and decompressed in common to heavily rely on any compression of backup dumps. (Granted, it does work 'most' of the time, which is generally fine for many development environments. However, in some situations where compression is implemented with larger backups I've seen routine daily restore validations fail or restore with problems every few weeks.)
---

If you are intent on going ahead anyway, what Sql Server version you are running is important. (In 2k installs I've implemented UDF functions instead of stored procedures for extracting dump information.)

Unfortunately no version has provided particularly rich built in backup / maintenence procedures to address the information needs you would require. (There are lots of undocumented ones that are worth looking at, but they are as subject to unannounced changes as the underlying MSDB table structures. I think you may be stuck implementing and maintaining your own special functions / stored procedures). Current Maintenance Plan Procedures (ver 7 / 2k) include:
sp_add_maintenance_plan
sp_add_maintenance_plan_db
sp_add_maintenance_plan_job
sp_delete_maintenance_plan
sp_delete_maintenance_plan_db
sp_delete_maintenance_plan_job
sp_help_maintenance_plan

The following user special stored procedure (implemented on some ver. 7 installs) returns the last DB or TL dumpfor a specified DB and Full or Log Dump on 7.0 and 2k (but not 6.x) installs:

Use
Master
Go
DROP PROCEDURE sp_LastDump
go
CREATE PROCEDURE sp_LastDump
--Fully Qualified Select most recent DBDump or TLDump
--@.pBkpSetTyp = I, D, L, @.pDBNam = DB name,
-- sp parameters:
@.pDBNam VarChar(512) = 'Master',
@.pBkpSetTyp VarChar(50) = 'D'
AS
exec ('Set NoCount On')
SELECT MsDb..backupmediafamily.physical_device_name
FROM MsDb..backupmediafamily INNER JOIN
MsDb..backupset ON
MsDb..backupmediafamily.media_set_id = MsDb..backupset.media_set_id
WHERE MsDb..backupset.backup_finish_date =
(SELECT MAX(MsDb..backupset.backup_finish_date)
AS Mxbackup_finish_date
FROM MsDb..backupmediafamily INNER JOIN
MsDb..backupset ON
MsDb..backupmediafamily.media_set_id = MsDb..backupset.media_set_id
WHERE (MsDb..backupset.database_name = @.pDBNam) AND
(MsDb..backupset.type = @.pBkpSetTyp))

Note: If running 6.x or earlier, you'll need to create a proc referencing 6.x msdb tables instead e.g.(sysbackuphistory sysbackupdetail). If later upgrade to 7.0 or 2k, or later versions, you'll need to rdefine any special stored procs you create to reflect newer MSDB tables.|||How is the currently developed maintenance plan programmed - through the maintenance wizard / dts / stored procedure ... ? Which compression software package are you using ?

Automated Backup to Remote Server

Hi all ~
I've been able to use the command line listed below to automate a
backup. One snag - I'm trying to backup to an alternate server. In
my case, my servers are grab-02 (database node) and grab-01 (location
of desired backup).
I do have shared folders for both folders.
How can I use this command to backup from grab-02 to grab-01 using the
following command?
What would be the fully quanlified path for the shared docs folder on
grab-01?
C:\Documents and Settings\GB>osql -U sa -P password -S grab-02 -Q
"BACKUP
DATABASE gb_production TO DISK = 'c:\database_backup'"
Hi,
Provide UNC path along with the BACKUP DATABASE command.
Eg:-
Backup database gb_production TO DISK = '\\grab-01\share_name\dbname.bak'
Please go thu the below script will take the UNC path as the parameter and
will Backup Master, MSDB and all the User databases to the remote machine.
This
script will create the unique Backup files names, this will ensure that old
backup sets were not overwritten.
Prerequisites
1. SQL server and SQL Server Agent should be configured to start in Domain
Account
2.. This Domain account should have change privileges to add files to the
Remote machine
Script
CREATE PROCEDURE BACKUP_SP @.UNCPATH VARCHAR(200) AS
BEGIN
SET NOCOUNT ON
DECLARE @.NAME VARCHAR(100),
DECLARE @.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM master..Sysdatabases where name not in
('model','pubs','tempdb','northwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
NAME=@.UNCPATH+@.DBNAME+'_'+ltrim (rtrim (convert (char,
getdate(),105)))+'Dump.bak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD ,
NAME = @.DBNAME, NOSKIP, STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
END
How to Execute:
This procedure will take @.UNCPATH as the input parameter, Say you have to
backup the database to machine BACKUPSERVER in to share SQLBACKUP then the
execution will be
EXEC BACKUP_SP '\\BACKUPSERVER\SQLBACKUP\'
This will backup all the databases to the SQLBACKUP folder in BACKUPSERVER.
Thanks
Hari
MCDBA
"Jerry Penna" <jerrypenna@.msn.com> wrote in message
news:3ac97809.0408271340.382f4981@.posting.google.c om...
> Hi all ~
> I've been able to use the command line listed below to automate a
> backup. One snag - I'm trying to backup to an alternate server. In
> my case, my servers are grab-02 (database node) and grab-01 (location
> of desired backup).
> I do have shared folders for both folders.
> How can I use this command to backup from grab-02 to grab-01 using the
> following command?
> What would be the fully quanlified path for the shared docs folder on
> grab-01?
> C:\Documents and Settings\GB>osql -U sa -P password -S grab-02 -Q
> "BACKUP
> DATABASE gb_production TO DISK = 'c:\database_backup'"
|||Thanks Hari ~
A couple of questions. Can you explain your Prerequisites
1. & 2. I know little about SQL Server and XP - I'm an Oracle DBA
familiar with UNIX.
Also, when I ran the script you provided, but I received the followng
error:
Server: Msg 156, Level 15, State 1, Procedure BACKUP_SP, Line 5
Incorrect syntax near the keyword 'DECLARE'.
Thanks again.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:<uSLxD5ajEHA.1040@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> Hi,
> Provide UNC path along with the BACKUP DATABASE command.
> Eg:-
> Backup database gb_production TO DISK = '\\grab-01\share_name\dbname.bak'
> Please go thu the below script will take the UNC path as the parameter and
> will Backup Master, MSDB and all the User databases to the remote machine.
> This
> script will create the unique Backup files names, this will ensure that old
> backup sets were not overwritten.
>
> Prerequisites
> 1. SQL server and SQL Server Agent should be configured to start in Domain
> Account
> 2.. This Domain account should have change privileges to add files to the
> Remote machine
> Script
>
> CREATE PROCEDURE BACKUP_SP @.UNCPATH VARCHAR(200) AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.NAME VARCHAR(100),
> DECLARE @.DBNAME VARCHAR(100)
> DECLARE BACKUP_CUR CURSOR FOR
> SELECT name FROM master..Sysdatabases where name not in
> ('model','pubs','tempdb','northwind')
> OPEN BACKUP_CUR
> FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
> WHILE @.@.FETCH_STATUS=0
> BEGIN
> SELECT
> NAME=@.UNCPATH+@.DBNAME+'_'+ltrim (rtrim (convert (char,
> getdate(),105)))+'Dump.bak'
> BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD ,
> NAME = @.DBNAME, NOSKIP, STATS = 10, NOFORMAT
> FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
> END
> CLOSE BACKUP_CUR
> DEALLOCATE BACKUP_CUR
> END
> How to Execute:
> --
> This procedure will take @.UNCPATH as the input parameter, Say you have to
> backup the database to machine BACKUPSERVER in to share SQLBACKUP then the
> execution will be
> EXEC BACKUP_SP '\\BACKUPSERVER\SQLBACKUP\'
> This will backup all the databases to the SQLBACKUP folder in BACKUPSERVER.
>
> Thanks
> Hari
> MCDBA
>
> "Jerry Penna" <jerrypenna@.msn.com> wrote in message
> news:3ac97809.0408271340.382f4981@.posting.google.c om...
|||The comma at the end of line 4 shouldn't be there...
On 30 Aug 2004 13:51:50 -0700, Jerry Penna <jerrypenna@.msn.com> wrote:
[vbcol=seagreen]
> Thanks Hari ~
> A couple of questions. Can you explain your Prerequisites
> 1. & 2. I know little about SQL Server and XP - I'm an Oracle DBA
> familiar with UNIX.
> Also, when I ran the script you provided, but I received the followng
> error:
> Server: Msg 156, Level 15, State 1, Procedure BACKUP_SP, Line 5
> Incorrect syntax near the keyword 'DECLARE'.
> Thanks again.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:<uSLxD5ajEHA.1040@.TK2MSFTNGP09.phx.gbl>...
Glenn Adams
Tiber Creek Consulting
http://www.tibercreek.com
glenn@.tibercreek.com
Please DO NOT respond to me directly but post all responses here in the
newsgroup so that all can share the information
|||I've corrected the comma, thanks, but now there is a syntax error at line 23:
Server: Msg 170, Level 15, State 1, Procedure BACKUP_SP, Line 21
Line 21: Incorrect syntax near '@.DBNAME'.
"Glenn Adams" <glenn@.tibercreek.com.nospam> wrote in message news:<opsdlnonryfcuvf7@.saruman>...[vbcol=seagreen]
> The comma at the end of line 4 shouldn't be there...
>
> On 30 Aug 2004 13:51:50 -0700, Jerry Penna <jerrypenna@.msn.com> wrote:
|||"NAME=@.UNCPATH+@.DBNAME"
should be
"@.NAME=@.UNCPATH+@.DBNAME"
On 31 Aug 2004 13:53:44 -0700, Jerry Penna <jerrypenna@.msn.com> wrote:
[vbcol=seagreen]
> I've corrected the comma, thanks, but now there is a syntax error at
> line 23:
> Server: Msg 170, Level 15, State 1, Procedure BACKUP_SP, Line 21
> Line 21: Incorrect syntax near '@.DBNAME'.
> "Glenn Adams" <glenn@.tibercreek.com.nospam> wrote in message
> news:<opsdlnonryfcuvf7@.saruman>...
Glenn Adams
Tiber Creek Consulting
http://www.tibercreek.com
glenn@.tibercreek.com
Please DO NOT respond to me directly but post all responses here in the
newsgroup so that all can share the information

Auto_increment

Hello my name is Elisa and I have a problem with this line of code:
CREATE TABLE Film
(IdFilm int not null auto_increment PRIMARY KEY,
Titolo VARCHAR2(20) NOT NULL,
Regista VARCHAR2(20) NOT NULL,
Personaggi_Principali VARCHAR2(50) NOT NULL,
Personaggi_Secondari VARCHAR2(50) NOT NULL,
Nazione VARCHAR2(20) NOT NULL,
Durata NUMBER(3) NOT NULL,
Genere VARCHAR2(10) NOT NULL,
Trama VARCHAR2(500) NOT NULL,
Critica VARCHAR2(10) NOT NULL,
Locandina VARCHAR2(20) NOT NULL,
Note VARCHAR2(100) NOT NULL)
when I execute the result is:
Error: ORA-00922: missing or invalid option.
It's very urgent.
Thank you Elisaauto_increment does not exist in Oracle!

You have to create a sequence:

CREATE SEQUENCE FILM_SEQ;

and then you can either use it during the insert as in:

INSERT INTO Film VALUES (FILM_SEQ.NEXTVAL, ...);

or create a trigger:

CREATE OR REPLACE TRIGGER BI_FILM_TRG
BEFORE INSERT ON FILM FOR EACH ROW
BEGIN
SELECT FILM_SEQ.NEXTVAL INTO :NEW.IdFilm FROM DUAL;
END;
/
NOTE: DO NOT USE IT ON BOTH THE INSERT AND TRIGGER!!!

:cool:|||Thank you very much for your help
Elisa

auto new line in same table

Hi

I have Table Like this In SQL server

Code Name 1001 Bank 1002 Cash

I need a TRIGGER
FOR INSERT, UPDATE, DELETE

that ADD a new Row's in the same table like this:

Code Name 8001 Bank 9001 Bank 8002 Cash 8002 Cash

In other mean for any row add to table by user that code start with 1xxx the trigger must inset to other row to that table whit code 8xxx & 9xxx.

with the same name.

thanks for your help.You could use an INSTEAD OF TRIGGER to do this. Code below should give you an idea of how to do this.

create trigger InsNewRows on tbl instead of insert
as
begin
insert into tbl (Code, Name)
select i.Code, i.Name from inserted as i
union all
select i.Code + n.Code, i.Name
from inserted as i
cross join (select 8000 union all select 9000) as n(Code)
where i.Code between 1000 and 1999
end|||Probably would want to use

where i.Code >= 1000 and cmpid <= 1999