Backup and View question

Hi,
I export my DatabaseA into a brand new DatabaseB, using DTS Import/Export
Wizard.
I have some Views in the DatabaseA.
However, those Views in DatabaseA became Tables in DatabaseB, not Views.
But what I want is View, not Table.
Would someone tell me how to fix this problem?
Thanks for help.
JasonThis is how the type of DTS wizard task you selected work. If you want the views to be exported as
views, you need to use "Copy objects and data to SQL Server" task type in the Wizard.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:%23wGsBsASGHA.5156@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I export my DatabaseA into a brand new DatabaseB, using DTS Import/Export Wizard.
> I have some Views in the DatabaseA.
> However, those Views in DatabaseA became Tables in DatabaseB, not Views.
> But what I want is View, not Table.
> Would someone tell me how to fix this problem?
> Thanks for help.
>
> Jason
>|||Also you would avoid select them (within import-export wizard) and then
generate them scripts separately.
--
current location: alicante (es)
"Tibor Karaszi" wrote:
> This is how the type of DTS wizard task you selected work. If you want the views to be exported as
> views, you need to use "Copy objects and data to SQL Server" task type in the Wizard.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:%23wGsBsASGHA.5156@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I export my DatabaseA into a brand new DatabaseB, using DTS Import/Export Wizard.
> > I have some Views in the DatabaseA.
> > However, those Views in DatabaseA became Tables in DatabaseB, not Views.
> > But what I want is View, not Table.
> > Would someone tell me how to fix this problem?
> > Thanks for help.
> >
> >
> > Jason
> >
>
>

Backup and restore - What is the preffered way?

What is the preferred way for Backup and Restore? Maintenance plan wizard
or command line scripts?
Also, to setup email notifications on a sql box, what do we need (outlook
client..) on the server box? Is there a step by step document online to
setup this?
Plus please post some links to backup command scripts pages.
Thanks
BVR"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
> What is the preferred way for Backup and Restore? Maintenance plan wizard
> or command line scripts?
T-SQL scheduled using SQL Server Agent Service
> Also, to setup email notifications on a sql box, what do we need (outlook
> client..) on the server box? Is there a step by step document online to
> setup this?
You need outlook 2000 onwards installed on the box SQL is installed. SQL
Server is going to be a member of a domain and a MAPI mail server has be to
used (e.g Exchange)
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/default.mspx
> Plus please post some links to backup command scripts pages.
The BOOKS Online (get the updated version from www.microsoft.com/sql) is
more then enough as a resource for writing T-SQL for Backup/Restore
> Thanks
> BVR
>
Cheers
Gill|||http://weblogs.sqlteam.com/tarad
Tara has some good backup/restore scripts. She has a standard version and a
SQL LiteSpeed version.
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
> What is the preferred way for Backup and Restore? Maintenance plan wizard
> or command line scripts?
> Also, to setup email notifications on a sql box, what do we need (outlook
> client..) on the server box? Is there a step by step document online to
> setup this?
> Plus please post some links to backup command scripts pages.
> Thanks
> BVR
>|||"Sarbjit Gill" <ssgill.no.spam@.no.spam.hotmail.com> wrote in message
news:%23hl3NHa9EHA.1408@.TK2MSFTNGP10.phx.gbl...
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
> > What is the preferred way for Backup and Restore? Maintenance plan
wizard
> > or command line scripts?
> T-SQL scheduled using SQL Server Agent Service
> >
> > Also, to setup email notifications on a sql box, what do we need
(outlook
> > client..) on the server box? Is there a step by step document online to
> > setup this?
> You need outlook 2000 onwards installed on the box SQL is installed. SQL
> Server is going to be a member of a domain and a MAPI mail server has be
to
> used (e.g Exchange)
>
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/default.mspx
This is not true. Yes, you need a MAPI client, but you can seti it up to
use an SMTP server. In fact that's what I find to be the most stable, set
up an SMTP server on the same box as the SQL Server and use that to forward
outgoing email. This eliminates the problem of the mail client locking up
due to time outs.
> >
> > Plus please post some links to backup command scripts pages.
> The BOOKS Online (get the updated version from www.microsoft.com/sql) is
> more then enough as a resource for writing T-SQL for Backup/Restore
> >
> > Thanks
> > BVR
> >
> >
> Cheers
> Gill
>
>|||Thanks
BVR
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:OCYipwc9EHA.2788@.TK2MSFTNGP15.phx.gbl...
> http://weblogs.sqlteam.com/tarad
> Tara has some good backup/restore scripts. She has a standard version and
a
> SQL LiteSpeed version.
>
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
> > What is the preferred way for Backup and Restore? Maintenance plan
wizard
> > or command line scripts?
> >
> > Also, to setup email notifications on a sql box, what do we need
(outlook
> > client..) on the server box? Is there a step by step document online to
> > setup this?
> >
> > Plus please post some links to backup command scripts pages.
> >
> > Thanks
> > BVR
> >
> >
>

Backup

Hi,
What i would like to do is to use script to do the maintenance plan
instead of using the maintenance plan wizard. I create the following backup
scipts, my question is how am i able to write the result to a log file just
like what maintenance plan does?
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT Catalog_Name
FROM Information_Schema.Schemata
-- WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
WHERE [Catalog_Name] IN ('BusinessViews_Backup')
--('MASTER','MODEL','MSDB')
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'F:\Program Files\Microsoft SQL Server\MSSQL\Backup' +
@.DBName + '_Full_' +
CONVERT(NVARCHAR(16),GETDATE(),112) -- + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
-- PRINT 'Backing up database ' + @.DBName
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
-- RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
-- PRINT '--- '
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
My second question is assume i copy the above code to do the transaction_log
backup, after that, i execute the following code to each database,
CheckPoint
DBCC shrinkdatabase ('databasename', 10)
is that correct to shrink the database and log files?
Thanks so much for your time
ed>my question is how am i able to write the result to a log file just
> like what maintenance plan does?
The error messages are sent to the client application. I presume that the cl
ient application in this
case is Agent. Let Agent write an output file for you (defined in the job st
ep). You can send
additional information to the output file using RAISERROR. And you can take
this a step further by
emailing this (using SMTP if you wish) with info about success etc in email
subject line. See
http://www.karaszi.com/SQLServer/info_no_mapi.asp for some information.

> My second question is assume i copy the above code to do the transaction_l
og
> backup, after that, i execute the following code to each database,
> CheckPoint
> DBCC shrinkdatabase ('databasename', 10)
Don't shrink. See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for
more information.
Note: I'm in a hurry so I didn't have time to check your TSQL code...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:445823E6-7E16-4DB6-AA25-3C36F258AA34@.microsoft.com...
> Hi,
> What i would like to do is to use script to do the maintenance plan
> instead of using the maintenance plan wizard. I create the following back
up
> scipts, my question is how am i able to write the result to a log file jus
t
> like what maintenance plan does?
> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
> DECLARE cur_DBs CURSOR STATIC LOCAL
> FOR SELECT Catalog_Name
> FROM Information_Schema.Schemata
> -- WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB
')
> WHERE [Catalog_Name] IN ('BusinessViews_Backup')
> --('MASTER','MODEL','MSDB')
> OPEN cur_DBs
> FETCH NEXT FROM cur_DBs INTO @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.Device = N'F:\Program Files\Microsoft SQL Server\MSSQL\Backup' +
> @.DBName + '_Full_' +
> CONVERT(NVARCHAR(16),GETDATE(),112) -- + N'.BAK'
> SET @.Name = @.DBName + N' Full Backup'
> -- PRINT 'Backing up database ' + @.DBName
> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
> -- RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
> -- PRINT '--- '
> FETCH NEXT FROM cur_DBs INTO @.DBName
> END
> CLOSE cur_DBs
> DEALLOCATE cur_DBs
> My second question is assume i copy the above code to do the transaction_l
og
> backup, after that, i execute the following code to each database,
> CheckPoint
> DBCC shrinkdatabase ('databasename', 10)
> is that correct to shrink the database and log files?
> Thanks so much for your time
> ed
>