Backing up SQL with Batch - and Backup Name Prompt

Hello. I update our SQL QA server about four times a day and back it up
before every update. There are three DBs that I have to backup. Each
backup is given a specific name accoring to the 'Update Name and Number".
This take a lot of time to go through Enterprise Manager every time.
For one backup session the the backup names would be:
DDMMYY_QA_UpdateNumber_DB1.bak
DDMMYY_QA_UpdateNumber_DB2.bak
DDMMYY_QA_UpdateNember_DB3.bak
I know how to backup the DBs with a dos batch, but I have to then change all
the names which is still a pain.
If I could have a batch file that I click on and it prompts me for:
DDMMYY_QA_UpdateNumber and I could plug that in say: 092404_QA_Update283 and
hit enter, and it would produce:
092404_QA_Update283_DB1.bak
092404_QA_Update283_DB2.bak
092404_QA_Update283_DB3.bak
My life would change!
Is this possible? How could it be done?
Thanks in advance for any help!!!
Hi,
No need to write a batch program, Go with a small procedure which generates
the backup file names based on MMDDYYHHMinSS
See the below site for script and details:-
http://www.microsoft.com/india/msdn/articles/190.aspx
Note:
Change the script slightly to backup to local drives...
Thanks
Hari
MCDBA
"Tom" <none@.none.com> wrote in message
news:ewQtjdmoEHA.1800@.TK2MSFTNGP15.phx.gbl...
> Hello. I update our SQL QA server about four times a day and back it up
> before every update. There are three DBs that I have to backup. Each
> backup is given a specific name accoring to the 'Update Name and Number".
> This take a lot of time to go through Enterprise Manager every time.
> For one backup session the the backup names would be:
> DDMMYY_QA_UpdateNumber_DB1.bak
> DDMMYY_QA_UpdateNumber_DB2.bak
> DDMMYY_QA_UpdateNember_DB3.bak
> I know how to backup the DBs with a dos batch, but I have to then change
> all
> the names which is still a pain.
> If I could have a batch file that I click on and it prompts me for:
> DDMMYY_QA_UpdateNumber and I could plug that in say: 092404_QA_Update283
> and
> hit enter, and it would produce:
> 092404_QA_Update283_DB1.bak
> 092404_QA_Update283_DB2.bak
> 092404_QA_Update283_DB3.bak
> My life would change!
> Is this possible? How could it be done?
> Thanks in advance for any help!!!
>

backing up SQL server

Hi, I'm new to SQL server and hope someone can help me. I have SQL 7 and am
wanting to back it up remotely. I'm currently using a batch script that sh
uts down the service and then uses an xcopy command to copy the SQL director
y to a temporary location.
The script then uses a net start command to restart the services on a schedu
le. I then backup the temporary files that the xcopy command created. My q
uestion is, in the event of a System Crash, can I reinstall and configure SQ
L server and then restore
the temporary files and have them work? If not, is there another way to suc
cesfully back up SQL Server? Any help is very much appreciated. Thanks in
advance.Hi,
Please go thu the 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
"Mike" <mhebner@.getnet.net> wrote in message
news:9CB58890-10EF-477E-84E1-C953D7EE6534@.microsoft.com...
> Hi, I'm new to SQL server and hope someone can help me. I have SQL 7 and
am wanting to back it up remotely. I'm currently using a batch script that
shuts down the service and then uses an xcopy command to copy the SQL
directory to a temporary location. The script then uses a net start command
to restart the services on a schedule. I then backup the temporary files
that the xcopy command created. My question is, in the event of a System
Crash, can I reinstall and configure SQL server and then restore the
temporary files and have them work? If not, is there another way to
succesfully back up SQL Server? Any help is very much appreciated. Thanks
in advance.|||"Mike" <mhebner@.getnet.net> wrote in message
news:9CB58890-10EF-477E-84E1-C953D7EE6534@.microsoft.com...
My question is, in the event of a System Crash, can I reinstall and
configure SQL server and then restore the temporary files and have them
work?
You should try out restoring the files to a different server and see if it
works. There is no substitute to this. It is always better if you have
carried out some test restores to confirm that your process will work, even
if somebody else says that it will, as it gives you some experience for when
you might have to do it for real.

Backing up SP's and Functions...

Hello,

I want to write a batch file which gets the backup of the SP's,Functions,Views and Tables which belongs to me.Is there a utility to do this?

The best option may be using one of the excellent third party products. Or, try these scripts and see if you can convert one of them for your usage.

DDL -Script Database to File
http://www.wardyit.com/blog/blog/archive/2006/07/21/133.aspx
http://www.sqlteam.com/publish/scriptio
http://www.aspfaq.com/etiquette.asp?id=5006
http://www.codeproject.com/dotnet/ScriptDatabase.asp
http://www.nigelrivett.net/DMO/DMOScriptAllDatabases.html
http://rac4sql.net/qalite_main.asp

I'd recommend one of the third party products, such as SQL Script from www.ApexSQL.com.

sql

Back Up

Hi
Is there any way I can run a back up routine from a batch file
regards
Jonathan
===============
Jonathan Crawford
01273 440018
07799 068570
fax 01273 380221
jc@.jcrawford.co.uk
===============You mean you want to perform for instance a database backup from a .BAT file
? If so, yes:
Create the bat file. In there, you call OSQL.EXE using the /Q parameter, whe
re after the /Q
parameter you have your BACKUP command. Here's a simple example with a PRINT
instead of BACKUP
command:
OSQL /E /Q "Print 'Hello'
The command line switches for OSQL as well as the BACKUP command are documen
ted in Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jonathan Crawford" <jc@.jcrawford.co.uk> wrote in message
news:u3nc$KoEFHA.3992@.tk2msftngp13.phx.gbl...
> Hi
> Is there any way I can run a back up routine from a batch file
> regards
> Jonathan
> --
> ===============
> Jonathan Crawford
> 01273 440018
> 07799 068570
> fax 01273 380221
> jc@.jcrawford.co.uk
> ===============
>|||You can call the SQL Server command line utility (called osql.exe) from a
batch file, and you can run all SQL statements in osql, including BACKUP.
You can find the complete syntax for osql in Books Online.
Jacco Schalkwijk
SQL Server MVP
"Jonathan Crawford" <jc@.jcrawford.co.uk> wrote in message
news:u3nc$KoEFHA.3992@.tk2msftngp13.phx.gbl...
> Hi
> Is there any way I can run a back up routine from a batch file
> regards
> Jonathan
> --
> ===============
> Jonathan Crawford
> 01273 440018
> 07799 068570
> fax 01273 380221
> jc@.jcrawford.co.uk
> ===============
>|||You can invoke the osql commad from the batch file. Which in turn does the
actual backup using an SP / backup command.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Jonathan Crawford" <jc@.jcrawford.co.uk> wrote in message
news:u3nc$KoEFHA.3992@.tk2msftngp13.phx.gbl...
> Hi
> Is there any way I can run a back up routine from a batch file
> regards
> Jonathan
> --
> ===============
> Jonathan Crawford
> 01273 440018
> 07799 068570
> fax 01273 380221
> jc@.jcrawford.co.uk
> ===============
>

Avoid interrupting of BEGIN-END?

Hello,
I am searching for a way to get data moved from table to table. Meanwhile, data is being added to the table.
The batch query I thought of was the following:

BEGIN
SELECT (Column1, Column2) INTO Table2 (Column1, Column2)
FROM Table1;
TRUNCATE Table1;
(process data from Table2)
TRUNCATE Table2;
END

Problem is, it musn't be interrupted.
But data is being inserted continuously by another process, so the data from that process need to be copied to a cache table of sql server, or something like that.
Another thought of mine was letting that process insert it's data via a synonym (in this example called "Synonym1"). The application making use of Table 2 can either make use of Table1, that doesn't matter.
The batch query would be like the following:

BEGIN
ALTER Synonym1=Table1_Temp;
(process data from Table1);
TRUNCATE Table1;
ALTER Synonym1=Table1;
SELECT (Column1, Column2) INTO Table1 (Column1, Column2)
FROM Table1_Temp;
TRUNCATE Table1_Temp;
END

But the ALTER function does not exist for the SYNONYM function in SQL Server.
It needs to be done with drop and create.
Problem is, in that split second the other program wich is adding data to the database, will make an error because it cannot find that synonym.
Does anybody know how to make such processes uninterruptible?
Thanks in advance

Can you modify the structure of the tables in question or are they set?

If you can change them then I would add a processing field to table1 which defaults to Null (or possibly 0 if bit). The first step is to set that flag for all the records in table1. Then all your other operations (copying and deleting) use that in a where clause to only affect those records. Any records added after you start will have that value at the default and therefore not be affected (until the process runs the next time.

The only problem I can see is if this process runs twice with overlap - and the old version would have that problem as well. In that case what you could do is use a unique ID for each run and place that rather than simply a flag value in to the processing field. The most obvious is a GUID but as those are 16-bytes there is probably a better solution (particularly if this is as active a table as you imply).

A problem with transferring between tables as you show in your sample is if you try to use automatic identity columns as these will probably not be unique between the tables.