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 DBs With Retrospecct

I have SBS2003 and am using Dantz Retrospect to back up the SQL databases to
a NAS box outside of our building. I have never been sucessful doing this
because Retrospect throws an error saying that it cant read the database due
to "an unknown Windows error". I have tried talking to the folks at Dantz
and they said that they have done all they can and that I should call the SQL
tech support.
Any ideas? TIA
Scott
I don't know what Retrospect is but it is most likely trying to just backup
the sql files directly. That is never a good way to back up a database.
You need to either use the native SQL Server BACKUP command to dump the
backup file to disk (or tape if directly attached) and then you can back
that file up with your backup software. There are some 3rd party backup
tools that have a Plug-In for Sql Server that does the equivalent of a
native backup but I doubt that Retrospect has that.
Andrew J. Kelly SQL MVP
"scottdog129" <scottdog129@.discussions.microsoft.com> wrote in message
news:5D4CDEFB-3390-4AC4-B554-BB3427CE3A21@.microsoft.com...
>I have SBS2003 and am using Dantz Retrospect to back up the SQL databases
>to
> a NAS box outside of our building. I have never been sucessful doing this
> because Retrospect throws an error saying that it cant read the database
> due
> to "an unknown Windows error". I have tried talking to the folks at Dantz
> and they said that they have done all they can and that I should call the
> SQL
> tech support.
> Any ideas? TIA
> Scott
|||Retrospect does (or is supposed to do) the same thing as the Backup Database
option in EM, however, it doesnt work and I have to manually do the backup
every day.
"Andrew J. Kelly" wrote:

> I don't know what Retrospect is but it is most likely trying to just backup
> the sql files directly. That is never a good way to back up a database.
> You need to either use the native SQL Server BACKUP command to dump the
> backup file to disk (or tape if directly attached) and then you can back
> that file up with your backup software. There are some 3rd party backup
> tools that have a Plug-In for Sql Server that does the equivalent of a
> native backup but I doubt that Retrospect has that.
> --
> Andrew J. Kelly SQL MVP
>
> "scottdog129" <scottdog129@.discussions.microsoft.com> wrote in message
> news:5D4CDEFB-3390-4AC4-B554-BB3427CE3A21@.microsoft.com...
>
>
|||Well it's hard to say what this is doing. If you can do a native backup
then it must be their code. It's pretty lame that they can't help you.
Andrew J. Kelly SQL MVP
"scottdog129" <scottdog129@.discussions.microsoft.com> wrote in message
news:0A5D2BBA-12DD-4819-B4E2-6737CC59C5F7@.microsoft.com...[vbcol=seagreen]
> Retrospect does (or is supposed to do) the same thing as the Backup
> Database
> option in EM, however, it doesnt work and I have to manually do the
> backup
> every day.
> "Andrew J. Kelly" wrote:
sql

Back up system DBs

I noticed the transaction logs for master and other system dbs can't be backed up. What are some of the practices of backing up System DBs as far as method, frequency and what type of situation would there be a need to restore from them?
Thanks
You cannot backup logs for the syetm dbs because they are in simple =
recovery model.
Daily full backup should easily suffice unless you are in the habit of =
creating many databases or logins per day.
Mike John
"Niles" <Niles@.discussions.microsoft.com> wrote in message =
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't =
be backed up. What are some of the practices of backing up System DBs =
as far as method, frequency and what type of situation would there be a =
need to restore from them?
> Thanks
|||Hi,
Backup the system databases once a day. I recommend you to take a backup of
Master database inbetween
if you add a new database/new login/ adding roles / changing
configurations/....
Take the backup of MSDB inbetween if you add or modify a Job, add
operator/alert, replications , Logshipping...
Thanks
Hari
MCDBA
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be
backed up. What are some of the practices of backing up System DBs as far
as method, frequency and what type of situation would there be a need to
restore from them?
> Thanks
|||I prefer to do only db backup for master and model. For msdb, I also do log backup (because of
backup history is stored there). As Agent set msdb to simple recovery at startup, I just have a job
scheduled to autostart that set msdb to full recovery.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be backed up. What are some
of the practices of backing up System DBs as far as method, frequency and what type of situation
would there be a need to restore from them?
> Thanks

Back up system DBs

I noticed the transaction logs for master and other system dbs can't be back
ed up. What are some of the practices of backing up System DBs as far as me
thod, frequency and what type of situation would there be a need to restore
from them?
ThanksYou cannot backup logs for the syetm dbs because they are in simple =
recovery model.
Daily full backup should easily suffice unless you are in the habit of =
creating many databases or logins per day.
Mike John
"Niles" <Niles@.discussions.microsoft.com> wrote in message =
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't =
be backed up. What are some of the practices of backing up System DBs =
as far as method, frequency and what type of situation would there be a =
need to restore from them?
> Thanks|||Hi,
Backup the system databases once a day. I recommend you to take a backup of
Master database inbetween
if you add a new database/new login/ adding roles / changing
configurations/....
Take the backup of MSDB inbetween if you add or modify a Job, add
operator/alert, replications , Logshipping...
Thanks
Hari
MCDBA
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be
backed up. What are some of the practices of backing up System DBs as far
as method, frequency and what type of situation would there be a need to
restore from them?
> Thanks|||I prefer to do only db backup for master and model. For msdb, I also do log
backup (because of
backup history is stored there). As Agent set msdb to simple recovery at sta
rtup, I just have a job
scheduled to autostart that set msdb to full recovery.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be backed up.
What are some
of the practices of backing up System DBs as far as method, frequency and wh
at type of situation
would there be a need to restore from them?
> Thanks

Back up system DBs

I noticed the transaction logs for master and other system dbs can't be backed up. What are some of the practices of backing up System DBs as far as method, frequency and what type of situation would there be a need to restore from them?
ThanksYou cannot backup logs for the syetm dbs because they are in simple =recovery model.
Daily full backup should easily suffice unless you are in the habit of =creating many databases or logins per day.
Mike John
"Niles" <Niles@.discussions.microsoft.com> wrote in message =news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't =be backed up. What are some of the practices of backing up System DBs =as far as method, frequency and what type of situation would there be a =need to restore from them?
> Thanks|||Hi,
Backup the system databases once a day. I recommend you to take a backup of
Master database inbetween
if you add a new database/new login/ adding roles / changing
configurations/....
Take the backup of MSDB inbetween if you add or modify a Job, add
operator/alert, replications , Logshipping...
Thanks
Hari
MCDBA
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be
backed up. What are some of the practices of backing up System DBs as far
as method, frequency and what type of situation would there be a need to
restore from them?
> Thanks|||I prefer to do only db backup for master and model. For msdb, I also do log backup (because of
backup history is stored there). As Agent set msdb to simple recovery at startup, I just have a job
scheduled to autostart that set msdb to full recovery.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be backed up. What are some
of the practices of backing up System DBs as far as method, frequency and what type of situation
would there be a need to restore from them?
> Thanks

Auto-restore of database backups

Our DBs are set up to do a full backup once a day (late at night) and then transaction log backups during the day at shorter intervals.

I want to setup a dev database on the same server. I want this database to be an automatically restored copy of the live database. So every night, after the full backup of the live DB, I want to restore the live DB to this dev DB.

Can this be automated? Can the restore automatically stop the dev database in case some open connections exist?

Is there a better way to do this?Ok, I got this far:

declare @.PhysicalDeviceName nvarchar(260)
select top 1
 @.PhysicalDeviceName = M.physical_device_name
 from backupmediafamily M
 join backupset S
 on M.media_set_id = S.media_set_id
 and S.database_name = 'northwind'
 and S.type = 'D'
 order by backup_finish_date desc
restore database NorthwindDev
 from disk = @.PhysicalDeviceName
 with move 'northwind' to 'D:\DB\Microsoft SQL Server\MSSQL\Test\Test.mdf',
 move 'northwind_log' to 'D:\DB\Microsoft SQL Server\MSSQL\Test\Test.ldf'

And this works fine. I'll schedule this in SQL Server Agent.

Now my only issue is that I need get exclusive access to the NorthwindDev database before the restore will work. How do I organise that? Do I have to kill all processes connected to NorthwindDev (horrors!)? Or is there a better way?|||Ok, it's working now. What I do is I kill all processes connected to NorthwindDev using a script, and then I run the restore above. I don't like the senseless killing, but it works... Is there a more elegant way?

declare @.PhysicalDeviceName nvarchar(260),
 @.spid int,
 @.Kill varchar(50)
create table #Locks
 (spid int,
 dbid int,
 ObjId int,
 IndId int,
 Type varchar(5),
 Resource varchar(50),
 Mode varchar(10),
 Status varchar(10))
insert into #Locks
exec sp_lock
declare NextFrag cursor local for
 select spid
 from #Locks L
 join master.dbo.sysdatabases D
 on L.dbid = D.dbid
 and D.[name] = 'NorthwindDev'
open NextFrag
while (1=1)
begin
 fetch next from NextFrag
 into @.spid
 if @.@.fetch_status = -1 break
 set @.Kill = 'kill ' + cast(@.spid as varchar)
 exec(@.Kill)
end
close NextFrag
deallocate NextFrag
drop table #Locks
select top 1
 @.PhysicalDeviceName = M.physical_device_name
 from backupmediafamily M
 join backupset S
 on M.media_set_id = S.media_set_id
 and S.database_name = 'Northwind'
 and S.type = 'D'
 order by backup_finish_date desc
restore database NorthwindDev
 from disk = @.PhysicalDeviceName
 with move 'northwind' to 'D:\DB\Microsoft SQL Server\MSSQL\Test\NorthwindDev.mdf',
 move 'northwind_log' to 'D:\DB\Microsoft SQL Server\MSSQL\Test\NorthwindDev.ldf'

|||I like talking to myself sometimes|||Why are there connections to the NorthwindDev database in the middle of the night? Do you have insomniac developers?

Reading BOL, it says that these processes should not be killed:
AWAITING COMMAND
CHECKPOINT SLEEP
LAZY WRITER
LOCK MONITOR
SELECT
SIGNAL HANDLER

And be aware that it might take a while to kill a SPID, so you might want some sort of loop to check to make sure all relevant processes are truly killed before attempting the restore. (See the KILL topic in BOL for more info.)

And also, I came across this simple series of commands to kill all processes and perform the restore. This might be a better method:

alter database yourdatabasename set single_user with rollback immediate
alter database yourdatabasename set multi_user with rollback immediate
RESTORE DATABASE WebReqs FROM disk = 'c:\inetpub\wwwroot\administration\database\WebReqs.bak'

Terri
PS "Talking to yourself is a sign of impending mental collapse" -- Zork ;-)|||In my shop developers often leave Query Analyser or Enterprise Manager connected to the databases overnight. So even though nothing is executing, the processes are still connected and thus own shared DB locks.

Since it's only a dev DB that's getting overwritten anyway, I'm not going hold off on killing certain processes.

I'll give the single_user thing a go, though. Looks much better than my KILL hack.

autonumbering question - newbie

I'm just getting into SQL server, so I apologize if this is a basic
question. Most of my experience with DBs comes from Oracle, so that's
the reference from which I'm working.

I'm trying to use the identity property in MS SQL Server to autonumber
a unique identifier field, and I'm wondering if it's possible to
manually assign a value for this field in my insert statement, or if
using the identity property ties me to the sequence. I know in
Oracle, since it uses sequences, one can choose whether or not to use
the next number in the sequence to assign a value, or to do it
manually. Since the insert statement for SQL Server doesn't allow a
value (or a reference or any kind of placeholder) of any sort, I'm at
a loss as to how to manually assign a value. Is it possible?

If it's not possible, could somebody lead me in the direction of
another way to address the situation? My main concern is having to
import data from another DB x years from now and that I'll want to
keep the unique contraints and all referential integrity associated
with the importing database.

Any help anybody could give me would be greatly appreciated. Thanks!You can manually assign a value to an identity column by turning on
IDENTITY_INSERT and explicitly specifying a column list. For example:

CREATE TABLE MyTable
(
MyIdentityColumn int NOT NULL IDENTITY(1, 1),
MyData int NOT NULL
)
GO
SET IDENTITY_INSERT MyTable ON
GO
INSERT INTO MyTable (MyIdentityColumn, MyData) VALUES(1, 1)
INSERT INTO MyTable (MyIdentityColumn, MyData) VALUES(2, 1)
INSERT INTO MyTable (MyIdentityColumn, MyData) VALUES(3, 1)
GO
SET IDENTITY_INSERT MyTable OFF
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"systems_newbie" <ccrupper@.hotmail.com> wrote in message
news:5939e7a7.0308201121.4aca153b@.posting.google.c om...
> I'm just getting into SQL server, so I apologize if this is a basic
> question. Most of my experience with DBs comes from Oracle, so that's
> the reference from which I'm working.
> I'm trying to use the identity property in MS SQL Server to autonumber
> a unique identifier field, and I'm wondering if it's possible to
> manually assign a value for this field in my insert statement, or if
> using the identity property ties me to the sequence. I know in
> Oracle, since it uses sequences, one can choose whether or not to use
> the next number in the sequence to assign a value, or to do it
> manually. Since the insert statement for SQL Server doesn't allow a
> value (or a reference or any kind of placeholder) of any sort, I'm at
> a loss as to how to manually assign a value. Is it possible?
> If it's not possible, could somebody lead me in the direction of
> another way to address the situation? My main concern is having to
> import data from another DB x years from now and that I'll want to
> keep the unique contraints and all referential integrity associated
> with the importing database.
> Any help anybody could give me would be greatly appreciated. Thanks!|||It worked perfectly! Thank you very much for the solution and for the
prompt reply.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"Charles Crupper" <ccrupper@.hotmail.com> wrote in message
news:3f43df99$0$200$75868355@.news.frii.net...
> It worked perfectly! Thank you very much for the solution and for the
> prompt reply.

I'll just point out a possible "flaw" in Dan's suggestion.

If two clients try to update the row with the same ID, you'll have to handle
that.

And that could cause issues if a client tries to do an insert and use the
Identity column vs. a client not using it.

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||> I'll just point out a possible "flaw" in Dan's suggestion.
> If two clients try to update the row with the same ID, you'll have to
handle
> that.
> And that could cause issues if a client tries to do an insert and use
the
> Identity column vs. a client not using it.

Good point. I should have mentioned that IDENTITY_INSERT should be
used for infrequent data migration or import and not part of routine
processing.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Greg D. Moore (Strider)" <mooregr@.greenms.com> wrote in message
news:R5S0b.122271$wk4.47486@.twister.nyroc.rr.com.. .
> "Charles Crupper" <ccrupper@.hotmail.com> wrote in message
> news:3f43df99$0$200$75868355@.news.frii.net...
> > It worked perfectly! Thank you very much for the solution and for
the
> > prompt reply.
> I'll just point out a possible "flaw" in Dan's suggestion.
> If two clients try to update the row with the same ID, you'll have to
handle
> that.
> And that could cause issues if a client tries to do an insert and use
the
> Identity column vs. a client not using it.
>
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!|||"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:y4U0b.3512$sV.364@.newsread1.news.atl.earthlin k.net...
> > I'll just point out a possible "flaw" in Dan's suggestion.
> > If two clients try to update the row with the same ID, you'll have to
> handle
> > that.
> > And that could cause issues if a client tries to do an insert and use
> the
> > Identity column vs. a client not using it.
> Good point. I should have mentioned that IDENTITY_INSERT should be
> used for infrequent data migration or import and not part of routine
> processing.

I'll admit I had to test something first and was pleasantly surprised by the
results.

I wasn't sure if the seed was updated if you did IDENTITY_INSERTs, but
apparently it is, which is nice.

(I still have nightmares about SQL 6.5 and how easily it would fubar it's
IDENTITY Seed.)

> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP|||> (I still have nightmares about SQL 6.5 and how easily it would fubar
it's
> IDENTITY Seed.)

Yea, the whole identity assignment method was re-written for SQL 7 and
above. I have no qualms about using IDENTITY is later releases.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------

"Greg D. Moore (Strider)" <mooregr@.greenms.com> wrote in message
news:bXU0b.122319$wk4.13930@.twister.nyroc.rr.com.. .
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:y4U0b.3512$sV.364@.newsread1.news.atl.earthlin k.net...
> > > I'll just point out a possible "flaw" in Dan's suggestion.
> > > > If two clients try to update the row with the same ID, you'll have
to
> > handle
> > > that.
> > > > And that could cause issues if a client tries to do an insert and
use
> > the
> > > Identity column vs. a client not using it.
> > Good point. I should have mentioned that IDENTITY_INSERT should be
> > used for infrequent data migration or import and not part of routine
> > processing.
> I'll admit I had to test something first and was pleasantly surprised
by the
> results.
> I wasn't sure if the seed was updated if you did IDENTITY_INSERTs, but
> apparently it is, which is nice.
> (I still have nightmares about SQL 6.5 and how easily it would fubar
it's
> IDENTITY Seed.)
>
> > --
> > Hope this helps.
> > Dan Guzman
> > SQL Server MVP