Easy T-SQL, brain lapse on Friday

Is there a view or something that lets you see what recovery mode your
db is set to?
I'm trying to tidy up a stored procedure that does a backup of all the
trans logs, but I get an error when it hits a db set to Simple.
Thx.> Is there a view or something that lets you see what recovery mode your
> db is set to?
Try:
SELECT DATABASEPROPERTYEX('MyDatabase', 'Recovery')
--
Hope this helps.
Dan Guzman
SQL Server MVP
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> Is there a view or something that lets you see what recovery mode your
> db is set to?
> I'm trying to tidy up a stored procedure that does a backup of all the
> trans logs, but I get an error when it hits a db set to Simple.
> Thx.
>|||One way is to use DATABASEPROPERTYEX with the recoverymode property.
--
Andrew J. Kelly SQL MVP
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> Is there a view or something that lets you see what recovery mode your
> db is set to?
> I'm trying to tidy up a stored procedure that does a backup of all the
> trans logs, but I get an error when it hits a db set to Simple.
> Thx.
>|||Thanks guys, worked like a charm.
Happy Friday!
Andrew J. Kelly wrote:
> One way is to use DATABASEPROPERTYEX with the recoverymode property.
> --
> Andrew J. Kelly SQL MVP
> "PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
> news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> > Is there a view or something that lets you see what recovery mode your
> > db is set to?
> >
> > I'm trying to tidy up a stored procedure that does a backup of all the
> > trans logs, but I get an error when it hits a db set to Simple.
> >
> > Thx.
> >

Easy T-SQL, brain lapse on Friday

Is there a view or something that lets you see what recovery mode your
db is set to?
I'm trying to tidy up a stored procedure that does a backup of all the
trans logs, but I get an error when it hits a db set to Simple.
Thx.> Is there a view or something that lets you see what recovery mode your
> db is set to?
Try:
SELECT DATABASEPROPERTYEX('MyDatabase', 'Recovery')
Hope this helps.
Dan Guzman
SQL Server MVP
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> Is there a view or something that lets you see what recovery mode your
> db is set to?
> I'm trying to tidy up a stored procedure that does a backup of all the
> trans logs, but I get an error when it hits a db set to Simple.
> Thx.
>|||One way is to use DATABASEPROPERTYEX with the recoverymode property.
Andrew J. Kelly SQL MVP
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> Is there a view or something that lets you see what recovery mode your
> db is set to?
> I'm trying to tidy up a stored procedure that does a backup of all the
> trans logs, but I get an error when it hits a db set to Simple.
> Thx.
>|||Thanks guys, worked like a charm.
Happy Friday!
Andrew J. Kelly wrote:[vbcol=seagreen]
> One way is to use DATABASEPROPERTYEX with the recoverymode property.
> --
> Andrew J. Kelly SQL MVP
> "PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
> news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...

Easy question

I generally do backups/restores of data and log files
using Enterprise Manager. I am just learning.
What does the following statement do?
BACKUP LOG YourDatabase WITH Truncate_Only
Like if take the backup from Enterprise it creates .bak
files whether the above statement will also create
some .bak files.That command truncates the inactive portion of the transaction log without
taking a transaction log backup. Good from reducing the size of the
transaction log, but since no backup was taken you will not be able to
restore any data that was added, deleted, or updated if those data
modifications where contained in the inactive portion of the transaction
that is truncated.
--
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
<anonymous@.discussions.microsoft.com> wrote in message
news:3fa501c49ffd$1dc02350$a301280a@.phx.gbl...
> I generally do backups/restores of data and log files
> using Enterprise Manager. I am just learning.
> What does the following statement do?
> BACKUP LOG YourDatabase WITH Truncate_Only
> Like if take the backup from Enterprise it creates .bak
> files whether the above statement will also create
> some .bak files.

EASY - backing up SQL Server Databases...

Hello All,
I think this question should be easy... I am trying to setup a temporary
backup solution by just setting SQL Server to automatically backup databases
to a LARGE Drive on another machine.
I am able to do this MANUALLY on one of my SQL Server machines.. but, on
another machine.. it will not let me backup to a NETWORK DRIVE L: ... when I
try to setup a backup procedure.. only local drives are listed.. but, under
MY COMPUTER... L: is mapped... this is still the case after a restart..
etc...
I can not just type in L: .. as a place to backup to... because it says that
LOCATION does NOT Exist?
any help would be greatly appreciated...
thanks...
--
Systems Programmeruse a unc path \\servername\shareName
Greg Jackson
PDX, Oregon|||THANKS
"pdxJaxon" wrote:
> use a unc path \\servername\shareName
>
> Greg Jackson
> PDX, Oregon
>
>

EASY - backing up SQL Server Databases...

Hello All,
I think this question should be easy... I am trying to setup a temporary
backup solution by just setting SQL Server to automatically backup databases
to a LARGE Drive on another machine.
I am able to do this MANUALLY on one of my SQL Server machines.. but, on
another machine.. it will not let me backup to a NETWORK DRIVE L: ... when I
try to setup a backup procedure.. only local drives are listed.. but, under
MY COMPUTER... L: is mapped... this is still the case after a restart..
etc...
I can not just type in L: .. as a place to backup to... because it says that
LOCATION does NOT Exist?
any help would be greatly appreciated...
thanks...
Systems Programmer
use a unc path \\servername\shareName
Greg Jackson
PDX, Oregon
|||THANKS
"pdxJaxon" wrote:

> use a unc path \\servername\shareName
>
> Greg Jackson
> PDX, Oregon
>
>
sql

EASY - backing up SQL Server Databases...

Hello All,
I think this question should be easy... I am trying to setup a temporary
backup solution by just setting SQL Server to automatically backup databases
to a LARGE Drive on another machine.
I am able to do this MANUALLY on one of my SQL Server machines.. but, on
another machine.. it will not let me backup to a NETWORK DRIVE L: ... when
I
try to setup a backup procedure.. only local drives are listed.. but, under
MY COMPUTER... L: is mapped... this is still the case after a restart..
etc...
I can not just type in L: .. as a place to backup to... because it says that
LOCATION does NOT Exist?
any help would be greatly appreciated...
thanks...
--
Systems Programmeruse a unc path \\servername\shareName
Greg Jackson
PDX, Oregon|||THANKS
"pdxJaxon" wrote:

> use a unc path \\servername\shareName
>
> Greg Jackson
> PDX, Oregon
>
>

Easiest way to move database from 1 machine to another ?

I am not a DBA, so pardon me if my question is basic.
What is the easiest way to move SQL Server 2005 database from 1 machine to
another ?
If I backup the database, I can not restore it in another database or in
another machine, can I ?
Is exporting the database the easiest way to move it to another machine (in
the new machine before importing the data I will still need to create the
database and run the database script to create all the tables and stored
procedures and views, right ?) ?
Thank you.
fniles,
Yes, restoring a backup to another server is a very easy way to move a
database from 1 machine to another. If you no longer want the database on
machine 1, detach the database, move the files (mdf & ldf( to the new
server, then attach them.
You will need to deal with any issues raised by logins not existing on both
servers, if that turns out to be the case for you.
RLF
"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>
|||Backups are easy, however, if you are using differently physically
configured machines that make the backup incompatible, you may want to
look at Microsoft SQL Server Management Studio and first scripting the
database and table definitions (in 2005, right click the database name
or table name and select Script As->Create To File) to generate SQL
scripts that recreate the database and table structures (less the
data) and then running the SQL Scripts in SQL Server Management Studio
on the target machine.
After doing this, you can move the data over by creating import/export
packages in SQL Server Management Studio by right clicking the
database the table is in and selecting tasks Export Data on the source
machine, and tasks Import Data on the target machine. Select
Microsoft Excel file format as the way to transport the data between
the machines if they cannot be connected to each other via a network
cable.
|||Two easy ways:
1) Backup and restore. Backup database and restore it on another instance.
2) Detach and attach. Detach database, copy all database files to another
instance and attach database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"fniles" wrote:

> I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine (in
> the new machine before importing the data I will still need to create the
> database and run the database script to create all the tables and stored
> procedures and views, right ?) ?
> Thank you.
>
>
|||For me, the easiest way to move databases from one machine to the other is
have a good documentation to back it up. Some pointers
1) Service pack levels and hotfixes should be the same on both source and
target
2) If you want the backup/restore to be easy and straightforward, make sure
that you have the same disk configurations. You can also do a detach/attach
approach
3) No need to move tempdb nor model databases
"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>
|||"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...

>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?

> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
sure you can; make sure you detach the DB first (after backup of course) ...
you might have to change the location of the physical files in the restore
dialogue options
or you can use the "Copy Database" facility in SQL Server Management Studio
(select DB, right click for menu; Tasks->Copy Database
or ... there are other options still ... but those are likely the easiest
ones

> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>
|||Thank you all for the replies.
I still want the original database on machine A, I just want another copy of
it in another machine.
So I backup the original database (say it's called MYDB) on machine A.
I then copied the MYDB.bak file from machine A to machine B.
In machineB, I then created a database called MYDB. Then I right click on
the database and select "Restore". Under "Specify source and location of
backup sets to restore" I selected "From device" and I pointed it to the
MYDB.bak in machine B.
I then got the error "Restore failed for server "serverB"
System.data.SqlClient.sqlerror: The backup set holds a backup of a database
other than the existing "MYDB" database. (microsoft.sqlserver.smo)
What did I do wrong on the above steps ?
Since I still need the original database and still using it, can I detach it
after backing it up ?
When I tried to copy the database from machine A to machine B, it works when
I selected "Use the detach and attach method" but when I selected "Use the
SQL Management Object method", I got an error with a view about an invalid
table name HistTradesOrig, which exists in the database, and I am able to
run the query fine.
Event Name: OnError
Message: ERROR : errorCode=-1073548784 description=Executing the query "
Create view [dbo].[Commission] AS
SELECT HistTradesOrig.email, Sum([Quantity]) AS Quan, HistTradesOrig.Status
FROM HistTradesOrig
GROUP BY HistTradesOrig.email, HistTradesOrig.Status
HAVING (((HistTradesOrig.Status)='F'))
" failed with the following error: "Invalid object name 'HistTradesOrig'.".
Possible failure reasons: Problems with the query, "ResultSet" property not
set correctly, parameters not set correctly, or connection not established
correctly.
helpFile= helpContext=0
idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
StackTrace: at
Microsoft.SqlServer.Management.Dts.DtsTransferProv ider.ExecuteTransfer()
at Microsoft.SqlServer.Management.Smo.Transfer.Transf erData()
at
Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask. TransferObjectsTask.TransferDatabasesUsingSMOTrans fer()
Operator: PFGBEST\sqladm
Thank you.
"Liz" <liz@.tiredofspam.com> wrote in message
news:eaGlygxMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> "fniles" <fniles@.pfmail.com> wrote in message
> news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>
>
> sure you can; make sure you detach the DB first (after backup of course)
> ... you might have to change the location of the physical files in the
> restore dialogue options
> or you can use the "Copy Database" facility in SQL Server Management
> Studio (select DB, right click for menu; Tasks->Copy Database
> or ... there are other options still ... but those are likely the easiest
> ones
>
>
|||Did you solve this issue? If not, try going to tab 2 of the restore db page,
and clicking Overwrite Existing Database. That should work.
John
"fniles" wrote:

> Thank you all for the replies.
> I still want the original database on machine A, I just want another copy of
> it in another machine.
> So I backup the original database (say it's called MYDB) on machine A.
> I then copied the MYDB.bak file from machine A to machine B.
> In machineB, I then created a database called MYDB. Then I right click on
> the database and select "Restore". Under "Specify source and location of
> backup sets to restore" I selected "From device" and I pointed it to the
> MYDB.bak in machine B.
> I then got the error "Restore failed for server "serverB"
> System.data.SqlClient.sqlerror: The backup set holds a backup of a database
> other than the existing "MYDB" database. (microsoft.sqlserver.smo)
> What did I do wrong on the above steps ?
> Since I still need the original database and still using it, can I detach it
> after backing it up ?
> When I tried to copy the database from machine A to machine B, it works when
> I selected "Use the detach and attach method" but when I selected "Use the
> SQL Management Object method", I got an error with a view about an invalid
> table name HistTradesOrig, which exists in the database, and I am able to
> run the query fine.
> Event Name: OnError
> Message: ERROR : errorCode=-1073548784 description=Executing the query "
> Create view [dbo].[Commission] AS
> SELECT HistTradesOrig.email, Sum([Quantity]) AS Quan, HistTradesOrig.Status
> FROM HistTradesOrig
> GROUP BY HistTradesOrig.email, HistTradesOrig.Status
> HAVING (((HistTradesOrig.Status)='F'))
> " failed with the following error: "Invalid object name 'HistTradesOrig'.".
> Possible failure reasons: Problems with the query, "ResultSet" property not
> set correctly, parameters not set correctly, or connection not established
> correctly.
> helpFile= helpContext=0
> idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
> StackTrace: at
> Microsoft.SqlServer.Management.Dts.DtsTransferProv ider.ExecuteTransfer()
> at Microsoft.SqlServer.Management.Smo.Transfer.Transf erData()
> at
> Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask. TransferObjectsTask.TransferDatabasesUsingSMOTrans fer()
> Operator: PFGBEST\sqladm
> Thank you.
> "Liz" <liz@.tiredofspam.com> wrote in message
> news:eaGlygxMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>
>

Easiest way to move database from 1 machine to another ?

I am not a DBA, so pardon me if my question is basic.
What is the easiest way to move SQL Server 2005 database from 1 machine to
another ?
If I backup the database, I can not restore it in another database or in
another machine, can I ?
Is exporting the database the easiest way to move it to another machine (in
the new machine before importing the data I will still need to create the
database and run the database script to create all the tables and stored
procedures and views, right ?) ?
Thank you.fniles,
Yes, restoring a backup to another server is a very easy way to move a
database from 1 machine to another. If you no longer want the database on
machine 1, detach the database, move the files (mdf & ldf( to the new
server, then attach them.
You will need to deal with any issues raised by logins not existing on both
servers, if that turns out to be the case for you.
RLF
"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>|||Backups are easy, however, if you are using differently physically
configured machines that make the backup incompatible, you may want to
look at Microsoft SQL Server Management Studio and first scripting the
database and table definitions (in 2005, right click the database name
or table name and select Script As->Create To File) to generate SQL
scripts that recreate the database and table structures (less the
data) and then running the SQL Scripts in SQL Server Management Studio
on the target machine.
After doing this, you can move the data over by creating import/export
packages in SQL Server Management Studio by right clicking the
database the table is in and selecting tasks Export Data on the source
machine, and tasks Import Data on the target machine. Select
Microsoft Excel file format as the way to transport the data between
the machines if they cannot be connected to each other via a network
cable.|||Two easy ways:
1) Backup and restore. Backup database and restore it on another instance.
2) Detach and attach. Detach database, copy all database files to another
instance and attach database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"fniles" wrote:

> I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine (i
n
> the new machine before importing the data I will still need to create the
> database and run the database script to create all the tables and stored
> procedures and views, right ?) ?
> Thank you.
>
>|||For me, the easiest way to move databases from one machine to the other is
have a good documentation to back it up. Some pointers
1) Service pack levels and hotfixes should be the same on both source and
target
2) If you want the backup/restore to be easy and straightforward, make sure
that you have the same disk configurations. You can also do a detach/attach
approach
3) No need to move tempdb nor model databases
"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?
> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>|||"fniles" <fniles@.pfmail.com> wrote in message
news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...

>I am not a DBA, so pardon me if my question is basic.
> What is the easiest way to move SQL Server 2005 database from 1 machine to
> another ?

> If I backup the database, I can not restore it in another database or in
> another machine, can I ?
sure you can; make sure you detach the DB first (after backup of course) ...
you might have to change the location of the physical files in the restore
dialogue options
or you can use the "Copy Database" facility in SQL Server Management Studio
(select DB, right click for menu; Tasks->Copy Database
or ... there are other options still ... but those are likely the easiest
ones

> Is exporting the database the easiest way to move it to another machine
> (in the new machine before importing the data I will still need to create
> the database and run the database script to create all the tables and
> stored procedures and views, right ?) ?
> Thank you.
>|||Thank you all for the replies.
I still want the original database on machine A, I just want another copy of
it in another machine.
So I backup the original database (say it's called MYDB) on machine A.
I then copied the MYDB.bak file from machine A to machine B.
In machineB, I then created a database called MYDB. Then I right click on
the database and select "Restore". Under "Specify source and location of
backup sets to restore" I selected "From device" and I pointed it to the
MYDB.bak in machine B.
I then got the error "Restore failed for server "serverB"
System.data.SqlClient.sqlerror: The backup set holds a backup of a database
other than the existing "MYDB" database. (microsoft.sqlserver.smo)
What did I do wrong on the above steps ?
Since I still need the original database and still using it, can I detach it
after backing it up ?
When I tried to copy the database from machine A to machine B, it works when
I selected "Use the detach and attach method" but when I selected "Use the
SQL Management Object method", I got an error with a view about an invalid
table name HistTradesOrig, which exists in the database, and I am able to
run the query fine.
Event Name: OnError
Message: ERROR : errorCode=-1073548784 description=Executing the query "
Create view [dbo].[Commission] AS
SELECT HistTradesOrig.email, Sum([Quantity]) AS Quan, HistTradesOrig.Sta
tus
FROM HistTradesOrig
GROUP BY HistTradesOrig.email, HistTradesOrig.Status
HAVING (((HistTradesOrig.Status)='F'))
" failed with the following error: "Invalid object name 'HistTradesOrig'.".
Possible failure reasons: Problems with the query, "ResultSet" property not
set correctly, parameters not set correctly, or connection not established
correctly.
helpFile= helpContext=0
idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
StackTrace: at
Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
at
Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.Transf
erDatabasesUsingSMOTransfer()
Operator: PFGBEST\sqladm
Thank you.
"Liz" <liz@.tiredofspam.com> wrote in message
news:eaGlygxMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> "fniles" <fniles@.pfmail.com> wrote in message
> news:%230BL$HsMIHA.2000@.TK2MSFTNGP05.phx.gbl...
>
>
> sure you can; make sure you detach the DB first (after backup of course)
> ... you might have to change the location of the physical files in the
> restore dialogue options
> or you can use the "Copy Database" facility in SQL Server Management
> Studio (select DB, right click for menu; Tasks->Copy Database
> or ... there are other options still ... but those are likely the easiest
> ones
>
>

Dynamically changing a backup file name

Hi all
I have read in many posts that DTS is not usually used for doing backups (a
scheduled job is preferred). However if I want to dynamically change the
name of the backup file depending on the date e.g. mydata_september.bak
during the month of september and then mydata_november.bak in november, how
would I go about doing that?
Dale1.Simple way is use the SQL Server Maintenance plan. It
automatically appends the Year,month,Day and time with the
database name.
Example. The backup file name for pubs database will be
pubs_db_200310132200.BAK.
2. If you want to use the datetime with your format, you
have to write a program.
Sample Script:
--
Declare
@.CurrentDateTime varchar(20),
@.dbname varchar(20),
@.dbbackupname varchar(40)
Begin
Select @.dbname=db_name()
Print @.dbname
Select @.CurrentDateTime = substring(DATENAME(month, getdate
()),1,3) +
cast(DATEPART(day, GETDATE()) as
varchar(2))+
cast(DATEPART(hh, GETDATE()) as
varchar(2)) +
cast(DATEPART(mi, GETDATE()) as
varchar(2))
Select @.dbbackupname = @.dbname+@.CurrentDateTime
select @.dbbackupname ='E:\' + @.dbbackupname + '.bak'
backup database @.dbname to disk=@.dbbackupname WITH
NOUNLOAD , RETAINDAYS = 2, DIFFERENTIAL, SKIP , STATS =10, FORMAT
Print @.CurrentDateTime
End
Warning: Test the script before you use. Use at your own
risk.
- SQLVarad(MDCBA-1999,MCSE-1999)
>--Original Message--
>Hi all
>I have read in many posts that DTS is not usually used
for doing backups (a
>scheduled job is preferred). However if I want to
dynamically change the
>name of the backup file depending on the date e.g.
mydata_september.bak
>during the month of september and then
mydata_november.bak in november, how
>would I go about doing that?
>Dale
>
>.
>|||Thanks, will give it a try
Dale
"SQLVarad" <SQLVarad@.hotmail.com> wrote in message
news:01f101c398d9$706888d0$a601280a@.phx.gbl...
> 1.Simple way is use the SQL Server Maintenance plan. It
> automatically appends the Year,month,Day and time with the
> database name.
> Example. The backup file name for pubs database will be
> pubs_db_200310132200.BAK.
> 2. If you want to use the datetime with your format, you
> have to write a program.
> Sample Script:
> --
> Declare
> @.CurrentDateTime varchar(20),
> @.dbname varchar(20),
> @.dbbackupname varchar(40)
> Begin
> Select @.dbname=db_name()
> Print @.dbname
> Select @.CurrentDateTime = substring(DATENAME(month, getdate
> ()),1,3) +
> cast(DATEPART(day, GETDATE()) as
> varchar(2))+
> cast(DATEPART(hh, GETDATE()) as
> varchar(2)) +
> cast(DATEPART(mi, GETDATE()) as
> varchar(2))
> Select @.dbbackupname = @.dbname+@.CurrentDateTime
> select @.dbbackupname ='E:\' + @.dbbackupname + '.bak'
> backup database @.dbname to disk=@.dbbackupname WITH
> NOUNLOAD , RETAINDAYS = 2, DIFFERENTIAL, SKIP , STATS => 10, FORMAT
> Print @.CurrentDateTime
> End
> Warning: Test the script before you use. Use at your own
> risk.
> - SQLVarad(MDCBA-1999,MCSE-1999)
> >--Original Message--
> >Hi all
> >
> >I have read in many posts that DTS is not usually used
> for doing backups (a
> >scheduled job is preferred). However if I want to
> dynamically change the
> >name of the backup file depending on the date e.g.
> mydata_september.bak
> >during the month of september and then
> mydata_november.bak in november, how
> >would I go about doing that?
> >
> >Dale
> >
> >
> >.
> >