Batching a cmd

Hi,
I'd like to do a daily backup of my db using windows programmed task and
running the following batch file:
sqlcmd -S server\db_Express -U username -P password
BACKUP DATABASE [dbSQL] TO DISK = N'D:\db05\dbSQL' WITH NOFORMAT, INIT,
NAME = N'dbSQL-Complite backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
declare @.backupSetId as int
select @.backupSetId = position from msdb..backupset where
database_name=N'dbSQL' and backup_set_id=(select max(backup_set_id) from
msdb..backupset where database_name=N'dbSQL' )
if @.backupSetId is null begin raiserror(N'Error de comprobacin. No se
encuentra la informacin de copia de seguridad para la base de datos
''dbSQL''.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'D:\db05\dbSQL' WITH FILE = @.backupSetId,
NOUNLOAD, NOREWIND
GO
exit
but the batch file hangs at exit and doesn't close the session.
What am I doing wrong?
TIA
Ana
SQL2005 Express
> but the batch file hangs at exit and doesn't close the session.
This is because you have not specified the script source for the SQLCMD
utility so it is running in interactive mode. With a long script and/or
multiple batches, you can save the script to a file and specify the script
file path via the -i argument. For example:
sqlcmd -S server\db_Express -U username -P password -i
"C:\Scripts\MyBackupScript.sql"
See the Books online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/e1728707-5215-4c04-8320-e36f161b834a.htm)
for SQLCMD argument details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ana" <ananospam@.yahoo.com> wrote in message
news:O7%23cUkxcHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'd like to do a daily backup of my db using windows programmed task and
> running the following batch file:
> sqlcmd -S server\db_Express -U username -P password
> BACKUP DATABASE [dbSQL] TO DISK = N'D:\db05\dbSQL' WITH NOFORMAT, INIT,
> NAME = N'dbSQL-Complite backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
> GO
> declare @.backupSetId as int
> select @.backupSetId = position from msdb..backupset where
> database_name=N'dbSQL' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset where database_name=N'dbSQL' )
> if @.backupSetId is null begin raiserror(N'Error de comprobacin. No se
> encuentra la informacin de copia de seguridad para la base de datos
> ''dbSQL''.', 16, 1) end
> RESTORE VERIFYONLY FROM DISK = N'D:\db05\dbSQL' WITH FILE =
> @.backupSetId, NOUNLOAD, NOREWIND
> GO
> exit
> but the batch file hangs at exit and doesn't close the session.
> What am I doing wrong?
> TIA
> Ana
> SQL2005 Express

Batching a cmd

Hi,
I'd like to do a daily backup of my db using windows programmed task and
running the following batch file:
sqlcmd -S server\db_Express -U username -P password
BACKUP DATABASE [dbSQL] TO DISK = N'D:\db05\dbSQL' WITH NOFORMAT, INIT,
NAME = N'dbSQL-Complite backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
declare @.backupSetId as int
select @.backupSetId = position from msdb..backupset where
database_name=N'dbSQL' and backup_set_id=(select max(backup_set_id) from
msdb..backupset where database_name=N'dbSQL' )
if @.backupSetId is null begin raiserror(N'Error de comprobación. No se
encuentra la información de copia de seguridad para la base de datos
''dbSQL''.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'D:\db05\dbSQL' WITH FILE = @.backupSetId,
NOUNLOAD, NOREWIND
GO
exit
but the batch file hangs at exit and doesn't close the session.
What am I doing wrong?
TIA
Ana
SQL2005 Express> but the batch file hangs at exit and doesn't close the session.
This is because you have not specified the script source for the SQLCMD
utility so it is running in interactive mode. With a long script and/or
multiple batches, you can save the script to a file and specify the script
file path via the -i argument. For example:
sqlcmd -S server\db_Express -U username -P password -i
"C:\Scripts\MyBackupScript.sql"
See the Books online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/e1728707-5215-4c04-8320-e36f161b834a.htm)
for SQLCMD argument details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ana" <ananospam@.yahoo.com> wrote in message
news:O7%23cUkxcHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'd like to do a daily backup of my db using windows programmed task and
> running the following batch file:
> sqlcmd -S server\db_Express -U username -P password
> BACKUP DATABASE [dbSQL] TO DISK = N'D:\db05\dbSQL' WITH NOFORMAT, INIT,
> NAME = N'dbSQL-Complite backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
> GO
> declare @.backupSetId as int
> select @.backupSetId = position from msdb..backupset where
> database_name=N'dbSQL' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset where database_name=N'dbSQL' )
> if @.backupSetId is null begin raiserror(N'Error de comprobación. No se
> encuentra la información de copia de seguridad para la base de datos
> ''dbSQL''.', 16, 1) end
> RESTORE VERIFYONLY FROM DISK = N'D:\db05\dbSQL' WITH FILE => @.backupSetId, NOUNLOAD, NOREWIND
> GO
> exit
> but the batch file hangs at exit and doesn't close the session.
> What am I doing wrong?
> TIA
> Ana
> SQL2005 Express

Batching a cmd

Hi,
I'd like to do a daily backup of my db using windows programmed task and
running the following batch file:
sqlcmd -S server\db_Express -U username -P password
BACKUP DATABASE [dbSQL] TO DISK = N'D:\db05\dbSQL' WITH NOFORMAT, INIT,
NAME = N'dbSQL-Complite backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
declare @.backupSetId as int
select @.backupSetId = position from msdb..backupset where
database_name=N'dbSQL' and backup_set_id=(select max(backup_set_id) from
msdb..backupset where database_name=N'dbSQL' )
if @.backupSetId is null begin raiserror(N'Error de comprobacin. No se
encuentra la informacin de copia de seguridad para la base de datos
''dbSQL''.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'D:\db05\dbSQL' WITH FILE = @.backupSetId,
NOUNLOAD, NOREWIND
GO
exit
but the batch file hangs at exit and doesn't close the session.
What am I doing wrong?
TIA
Ana
SQL2005 Express> but the batch file hangs at exit and doesn't close the session.
This is because you have not specified the script source for the SQLCMD
utility so it is running in interactive mode. With a long script and/or
multiple batches, you can save the script to a file and specify the script
file path via the -i argument. For example:
sqlcmd -S server\db_Express -U username -P password -i
"C:\Scripts\MyBackupScript.sql"
See the Books online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/e1728707-5215-4c04-8320
-e36f161b834a.htm)
for SQLCMD argument details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ana" <ananospam@.yahoo.com> wrote in message
news:O7%23cUkxcHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'd like to do a daily backup of my db using windows programmed task and
> running the following batch file:
> sqlcmd -S server\db_Express -U username -P password
> BACKUP DATABASE [dbSQL] TO DISK = N'D:\db05\dbSQL' WITH NOFORMAT, INI
T,
> NAME = N'dbSQL-Complite backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
> GO
> declare @.backupSetId as int
> select @.backupSetId = position from msdb..backupset where
> database_name=N'dbSQL' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset where database_name=N'dbSQL' )
> if @.backupSetId is null begin raiserror(N'Error de comprobacin. No se
> encuentra la informacin de copia de seguridad para la base de datos
> ''dbSQL''.', 16, 1) end
> RESTORE VERIFYONLY FROM DISK = N'D:\db05\dbSQL' WITH FILE =
> @.backupSetId, NOUNLOAD, NOREWIND
> GO
> exit
> but the batch file hangs at exit and doesn't close the session.
> What am I doing wrong?
> TIA
> Ana
> SQL2005 Express

batch file to copy file and append date

The Sql Server database can only see the local drive.

I would like to set up a batch file that will copy a SQL Server

backup file from the local drive to the network drive. I would

like to append the file date to the end of the copied file. I

assume a batch file can accomplish this but I am new to batch

file writing. Does anyone have code that they already created

for this sort of task??

Thank you!You can easily do this sort of stuff with a VBScript batch file. See the FileSystemObject. It's a great tool for moving and renaming files.|||How do you plan to do this?

From the context of xp_cmdshell it would run under the sql server service account, which would need write to write to the network drive.

I've heard of people using robocopy...what do you want to use? ftp?

Just a plain copy command?

And from where a sproc, SQL Server agent Job?|||How do you plan to do this?

From the context of xp_cmdshell it would run under the sql server service account, which would need write to write to the network drive.

I've heard of people using robocopy...what do you want to use? ftp?

Just a plain copy command?

And from where a sproc, SQL Server agent Job?

I just want a plain copy within a batch file, but one that can append the file date (ie BackupFile_mm_dd_yyyy). Currently this copy has been set up within windows Task scheduler. I want to do the same but run the batch file in the task. Fyi: Because SQL Server cannot see the network, the task was set up with Windows tasks scheduler.

Let me know your thoughts..

Thank you.|||You can easily do this sort of stuff with a VBScript batch file. See the FileSystemObject. It's a great tool for moving and renaming files.

Thank you for your suggestion. I have not worked with VB Script so was hoping to accomplish this within a batch file. But thank you for your response. I will look into this as another option.|||If you are using XP or Windows 2003, and N:\networkpath\ is where you want the files to go, then you might try a batch file like:copy %1 "n:\networkpath\%~1 %~t1"-PatP|||If you are using XP or Windows 2003, and N:\networkpath\ is where you want the files to go, then you might try a batch file like:copy %1 "n:\networkpath\%~1 %~t1"-PatP

Can you please explain the %1 and %~t1, will this append the file date?
Thanks!|||Go to the Windows XP help, and enter "Using batch parameters" (please include the quotation marks). It has a full explaination.

-PatP|||If you are using XP or Windows 2003, and N:\networkpath\ is where you want the files to go, then you might try a batch file like:copy %1 "n:\networkpath\%~1 %~t1"-PatPIf the destination file doesn't exist your trick will not work. If it does exist the copy will result in "The syntax of the command is incorrect" error. The best place to generate the new filename would be in T-SQL.|||I have a job which save databases structure into files. It create directory 'year-month-day' and dump structure there. This is code:
----------
DECLARE @.command varchar(1000);
-- create local directory for dump
SET @.command='mkdir C:\DBStruct\'+CAST(YEAR(GETDATE()) as varchar)+'-'+CAST(MONTH(GETDATE()) as varchar)+'-'+CAST(DAY(GETDATE()) as varchar);
exec xp_cmdshell @.command;

--create dump
SET @.command='xp_cmdshell ''"C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s TESTERS /d ? /P 1 /f C:\DBStruct\'+CAST(YEAR(GETDATE()) as varchar)+'-'+CAST(MONTH(GETDATE()) as varchar)+'-'+CAST(DAY(GETDATE()) as varchar)+'\?.sql''';
exec sp_MSforeachdb
@.command1 = @.command,
@.replacechar = '?'

--create remote dir
SET @.command='mkdir \\saturn\DBStruct\'+CAST(YEAR(GETDATE()) as varchar)+'-'+CAST(MONTH(GETDATE()) as varchar)+'-'+CAST(DAY(GETDATE()) as varchar);
exec xp_cmdshell @.command;

--copy databases structure to remote computer
SET @.command='copy '+'C:\DBStruct\'+CAST(YEAR(GETDATE()) as varchar)+'-'+CAST(MONTH(GETDATE()) as varchar)+'-'+CAST(DAY(GETDATE()) as varchar)+' \\saturn\DBStruct\'+CAST(YEAR(GETDATE()) as varchar)+'-'+CAST(MONTH(GETDATE()) as varchar)+'-'+CAST(DAY(GETDATE()) as varchar);
exec xp_cmdshell @.command;
----------
I hope this help you|||If the destination file doesn't exist your trick will not work. If it does exist the copy will result in "The syntax of the command is incorrect" error. The best place to generate the new filename would be in T-SQL.
Can you suggest how to append mmddyyyy to end of filename in a backup command within a SQL SERVER job?|||If you are using XP or Windows 2003, and N:\networkpath\ is where you want the files to go, then you might try a batch file like:copy %1 "n:\networkpath\%~1 %~t1"-PatP
The results is close but beacuse of the slashes in the date, the output is going into subdirectories not a single filename. Any suggestions?|||I figured out how to copy a file and append a date to the copy. Here is the code for the batch file:

:: COPY FILE AND DATE
::
@.ECHO OFF

FOR /F "tokens=2,3,4 delims=/ " %%a IN ('DATE /t') DO SET mydate=_%%a_%%b_%%c

::ECHO The value is "%mydate%"
copy "c:\readme" "L:\DATABASES\PROJECT REVIEW\readme%mydate%" /Y|||I am sure you did, and so did I, after reading this (http://www.computerhope.com/batch.htm#5)and similar pages ;)

Batch file call to SQL Agent to make Bakcup

Hello.
I've a Backup system that copies my files and I want to copy also my
SQL Server DDBB. I intend to execute the batch file before de File
Backup starts to call de SQL Agent so it makes those Backups and then
copy them to the Backup folder so the Backup system copies everything.
The problem is that I don't know if it is possible to call to the SQL
Agent through a Batch file to make these Backups. If so, how could I
do it?
Thank you.
One suggestion ... instead of having the batch file calling the SQL Agent,
you can use the OSQL utility (it is a command line utility for SQL). You can
backup the database using OSQL.
Thank you
Lucas
"Gurk" <gurkgamer@.gmail.com> wrote in message
news:1181666228.803256.267530@.d30g2000prg.googlegr oups.com...
> Hello.
> I've a Backup system that copies my files and I want to copy also my
> SQL Server DDBB. I intend to execute the batch file before de File
> Backup starts to call de SQL Agent so it makes those Backups and then
> copy them to the Backup folder so the Backup system copies everything.
> The problem is that I don't know if it is possible to call to the SQL
> Agent through a Batch file to make these Backups. If so, how could I
> do it?
> Thank you.
>
|||If the backup script is in a job, you can execute that job
through OSQL, SQLCMD, etc by executing sp_start_job. You can
find more information on sp_start_job in books online
-Sue
On Tue, 12 Jun 2007 09:37:08 -0700, Gurk
<gurkgamer@.gmail.com> wrote:

>Hello.
>I've a Backup system that copies my files and I want to copy also my
>SQL Server DDBB. I intend to execute the batch file before de File
>Backup starts to call de SQL Agent so it makes those Backups and then
>copy them to the Backup folder so the Backup system copies everything.
>The problem is that I don't know if it is possible to call to the SQL
>Agent through a Batch file to make these Backups. If so, how could I
>do it?
>Thank you.

Batch file call to SQL Agent to make Bakcup

Hello.
I've a Backup system that copies my files and I want to copy also my
SQL Server DDBB. I intend to execute the batch file before de File
Backup starts to call de SQL Agent so it makes those Backups and then
copy them to the Backup folder so the Backup system copies everything.
The problem is that I don't know if it is possible to call to the SQL
Agent through a Batch file to make these Backups. If so, how could I
do it?
Thank you.One suggestion ... instead of having the batch file calling the SQL Agent,
you can use the OSQL utility (it is a command line utility for SQL). You can
backup the database using OSQL.
Thank you
Lucas
"Gurk" <gurkgamer@.gmail.com> wrote in message
news:1181666228.803256.267530@.d30g2000prg.googlegroups.com...
> Hello.
> I've a Backup system that copies my files and I want to copy also my
> SQL Server DDBB. I intend to execute the batch file before de File
> Backup starts to call de SQL Agent so it makes those Backups and then
> copy them to the Backup folder so the Backup system copies everything.
> The problem is that I don't know if it is possible to call to the SQL
> Agent through a Batch file to make these Backups. If so, how could I
> do it?
> Thank you.
>|||If the backup script is in a job, you can execute that job
through OSQL, SQLCMD, etc by executing sp_start_job. You can
find more information on sp_start_job in books online
-Sue
On Tue, 12 Jun 2007 09:37:08 -0700, Gurk
<gurkgamer@.gmail.com> wrote:
>Hello.
>I've a Backup system that copies my files and I want to copy also my
>SQL Server DDBB. I intend to execute the batch file before de File
>Backup starts to call de SQL Agent so it makes those Backups and then
>copy them to the Backup folder so the Backup system copies everything.
>The problem is that I don't know if it is possible to call to the SQL
>Agent through a Batch file to make these Backups. If so, how could I
>do it?
>Thank you.

Batch file call to SQL Agent to make Bakcup

Hello.
I've a Backup system that copies my files and I want to copy also my
SQL Server DDBB. I intend to execute the batch file before de File
Backup starts to call de SQL Agent so it makes those Backups and then
copy them to the Backup folder so the Backup system copies everything.
The problem is that I don't know if it is possible to call to the SQL
Agent through a Batch file to make these Backups. If so, how could I
do it?
Thank you.One suggestion ... instead of having the batch file calling the SQL Agent,
you can use the OSQL utility (it is a command line utility for SQL). You can
backup the database using OSQL.
Thank you
Lucas
"Gurk" <gurkgamer@.gmail.com> wrote in message
news:1181666228.803256.267530@.d30g2000prg.googlegroups.com...
> Hello.
> I've a Backup system that copies my files and I want to copy also my
> SQL Server DDBB. I intend to execute the batch file before de File
> Backup starts to call de SQL Agent so it makes those Backups and then
> copy them to the Backup folder so the Backup system copies everything.
> The problem is that I don't know if it is possible to call to the SQL
> Agent through a Batch file to make these Backups. If so, how could I
> do it?
> Thank you.
>|||If the backup script is in a job, you can execute that job
through OSQL, SQLCMD, etc by executing sp_start_job. You can
find more information on sp_start_job in books online
-Sue
On Tue, 12 Jun 2007 09:37:08 -0700, Gurk
<gurkgamer@.gmail.com> wrote:

>Hello.
>I've a Backup system that copies my files and I want to copy also my
>SQL Server DDBB. I intend to execute the batch file before de File
>Backup starts to call de SQL Agent so it makes those Backups and then
>copy them to the Backup folder so the Backup system copies everything.
>The problem is that I don't know if it is possible to call to the SQL
>Agent through a Batch file to make these Backups. If so, how could I
>do it?
>Thank you.

Batch database backup and restore

Does anyone know how I could setup a backup or restore executed from a .bat
file?
TonyTony (tonyng2@.spacecommand.net) writes:
> Does anyone know how I could setup a backup or restore executed from a
> .bat file?
Put the command you want to run in a file called BACKUP.SQL, and then
say:
osql -E -n -i BACKUP.LOG
for more details, please look up OSQL in Books Online.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

basic restore question

Is it possible in SS2K5 to restore from a backup between databases with
different MDF/LDF file locations? I don't have the option to recreate the
original directory structure on the target.
PaulCheck Books Online for the MOVE option to RESTORE DATABASE command.
"PJ6" <noone@.nowhere.net> wrote in message
news:ujFvDp1BIHA.536@.TK2MSFTNGP06.phx.gbl...
> Is it possible in SS2K5 to restore from a backup between databases with
> different MDF/LDF file locations? I don't have the option to recreate the
> original directory structure on the target.
> Paul
>|||Never mind, I got it. I finally found an example of RESTORE with MOVE and
discovered that the original file names must be specified without a path or
extention.
Paul
"PJ6" <noone@.nowhere.net> wrote in message
news:ujFvDp1BIHA.536@.TK2MSFTNGP06.phx.gbl...
> Is it possible in SS2K5 to restore from a backup between databases with
> different MDF/LDF file locations? I don't have the option to recreate the
> original directory structure on the target.
> Paul
>

Basic questions (Part 2)

1) How can I schedule a daily full backup of the AS Database ? I know in SQL
server, therese a SQL Agent that i can run a job .. But on my AS Server, all
I have is AS.. Also what the command to use ? Can one send me an example ?
2) I looked at the data folder and I see so many different files of
different file types.. Can one point me to some reference about what each
kind of file is ?
> 1) How can I schedule a daily full backup of the AS Database ? I know in
SQL
> server, therese a SQL Agent that i can run a job .. But on my AS Server,
all
> I have is AS.. Also what the command to use ? Can one send me an example ?
Look in BOL in the section which describes the msmdarch utility. You can use
SQL Agent to spawn a command line instance of msmdarch to create the
archive.

> 2) I looked at the data folder and I see so many different files of
> different file types.. Can one point me to some reference about what each
> kind of file is ?
This is talked about a bit in the Analysis Services Operations Guide at:
http://www.microsoft.com/technet/pro.../anservog.mspx
See appendix K.
Hope this helps.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uP%23rIdiMFHA.2736@.TK2MSFTNGP09.phx.gbl...
> 1) How can I schedule a daily full backup of the AS Database ? I know in
SQL
> server, therese a SQL Agent that i can run a job .. But on my AS Server,
all
> I have is AS.. Also what the command to use ? Can one send me an example ?
> 2) I looked at the data folder and I see so many different files of
> different file types.. Can one point me to some reference about what each
> kind of file is ?
>

Basic questions (Part 2)

1) How can I schedule a daily full backup of the AS Database ? I know in SQL
server, therese a SQL Agent that i can run a job .. But on my AS Server, all
I have is AS.. Also what the command to use ? Can one send me an example ?
2) I looked at the data folder and I see so many different files of
different file types.. Can one point me to some reference about what each
kind of file is ?> 1) How can I schedule a daily full backup of the AS Database ? I know in
SQL
> server, therese a SQL Agent that i can run a job .. But on my AS Server,
all
> I have is AS.. Also what the command to use ? Can one send me an example ?
Look in BOL in the section which describes the msmdarch utility. You can use
SQL Agent to spawn a command line instance of msmdarch to create the
archive.

> 2) I looked at the data folder and I see so many different files of
> different file types.. Can one point me to some reference about what each
> kind of file is ?
This is talked about a bit in the Analysis Services Operations Guide at:
http://www.microsoft.com/technet/pr...n/anservog.mspx
See appendix K.
Hope this helps.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uP%23rIdiMFHA.2736@.TK2MSFTNGP09.phx.gbl...
> 1) How can I schedule a daily full backup of the AS Database ? I know in
SQL
> server, therese a SQL Agent that i can run a job .. But on my AS Server,
all
> I have is AS.. Also what the command to use ? Can one send me an example ?
> 2) I looked at the data folder and I see so many different files of
> different file types.. Can one point me to some reference about what each
> kind of file is ?
>