Converting problem: basis

Showing posts with label basis. Show all posts

Tuesday, March 20, 2012

Copy Database Wizard

When I choose to have the database copy occur on a daily
basis, I can't find the job or package (whichever it is
creating) anywhere. Does anyone know how this gets
saved/scheduled or where I can view the properties/add
steps?The package that gets created should be on the destination server.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Monday, March 19, 2012

Copy database question

Is there a way to copy a database on a scheduled basis and name the copy
different names every time ?
For example: database copy for today will be named mydb_061207, database
copy for tomorrow will be named mydb_061307, etc.
Thank you.Craft a RESTORE statement using dynamic sql and a date variable formatted as
112 (see Convert in BOL).
--
TheSQLGuru
President
Indicium Resources, Inc.
"fniles" <fniles@.pfmail.com> wrote in message
news:eRmOKcSrHHA.3248@.TK2MSFTNGP03.phx.gbl...
> Is there a way to copy a database on a scheduled basis and name the copy
> different names every time ?
> For example: database copy for today will be named mydb_061207, database
> copy for tomorrow will be named mydb_061307, etc.
> Thank you.
>|||Thank you.
I am sorry, I am new to 2005. What did you mean by crafting a restore
statement using dynamic sql ?
When I copy the database, in the SQL Server Management STudio I right click
on the database - Tasks - Copy database and use the copy wizard. This is
stored as a job. I would like the job to copy to a different file everyday.
How can I do that ?
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:OZa8uITrHHA.4624@.TK2MSFTNGP06.phx.gbl...
> Craft a RESTORE statement using dynamic sql and a date variable formatted
> as 112 (see Convert in BOL).
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "fniles" <fniles@.pfmail.com> wrote in message
> news:eRmOKcSrHHA.3248@.TK2MSFTNGP03.phx.gbl...
>> Is there a way to copy a database on a scheduled basis and name the copy
>> different names every time ?
>> For example: database copy for today will be named mydb_061207, database
>> copy for tomorrow will be named mydb_061307, etc.
>> Thank you.
>|||Hi,
You'll have to write you own BACKUP script that generates the filename
dynamically based on todays date. Then you will have to schedule this to
run every day.
A very simple example of a script to do this, could be something like:
DECLARE @.Filename VARCHAR(300)
DECLARE @.BackupPath VARCHAR(300)
DECLARE @.Device VARCHAR(600)
SET @.BackupPath = 'C:\MSSQL\BACKUP\'
SET @.FileName = 'YourDatabaseName'+CONVERT(NVARCHAR(16),GETDATE(),112) +
'.BAK'
SET @.Device = @.BackupPath+@.FileName
BACKUP DATABASE YourDataBaseName TO DISK = @.Device
This will backup your database to a file named with the databasename and
todays date in the format YYYYMMDD.It will place the backup in the
Backuppath directory you specify. Please remember that the backup folder
has to exist before you run the script.
The script is just an example to get you started. You can then add on
extra "features" as you get the need.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
fniles wrote:
> Thank you.
> I am sorry, I am new to 2005. What did you mean by crafting a restore
> statement using dynamic sql ?
> When I copy the database, in the SQL Server Management STudio I right click
> on the database - Tasks - Copy database and use the copy wizard. This is
> stored as a job. I would like the job to copy to a different file everyday.
> How can I do that ?
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:OZa8uITrHHA.4624@.TK2MSFTNGP06.phx.gbl...
>> Craft a RESTORE statement using dynamic sql and a date variable formatted
>> as 112 (see Convert in BOL).
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:eRmOKcSrHHA.3248@.TK2MSFTNGP03.phx.gbl...
>> Is there a way to copy a database on a scheduled basis and name the copy
>> different names every time ?
>> For example: database copy for today will be named mydb_061207, database
>> copy for tomorrow will be named mydb_061307, etc.
>> Thank you.
>>
>|||Thank you, that's great !
Do you by any chance know the T-SQL to COPY the database, instead of BACKUP
? I need to copy.
Thanks.
""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:OFtrUrYrHHA.484@.TK2MSFTNGP06.phx.gbl...
> Hi,
> You'll have to write you own BACKUP script that generates the filename
> dynamically based on todays date. Then you will have to schedule this to
> run every day.
> A very simple example of a script to do this, could be something like:
>
> DECLARE @.Filename VARCHAR(300)
> DECLARE @.BackupPath VARCHAR(300)
> DECLARE @.Device VARCHAR(600)
> SET @.BackupPath = 'C:\MSSQL\BACKUP\'
> SET @.FileName = 'YourDatabaseName'+CONVERT(NVARCHAR(16),GETDATE(),112) +
> '.BAK'
> SET @.Device = @.BackupPath+@.FileName
> BACKUP DATABASE YourDataBaseName TO DISK = @.Device
>
> This will backup your database to a file named with the databasename and
> todays date in the format YYYYMMDD.It will place the backup in the
> Backuppath directory you specify. Please remember that the backup folder
> has to exist before you run the script.
> The script is just an example to get you started. You can then add on
> extra "features" as you get the need.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>
> fniles wrote:
>> Thank you.
>> I am sorry, I am new to 2005. What did you mean by crafting a restore
>> statement using dynamic sql ?
>> When I copy the database, in the SQL Server Management STudio I right
>> click on the database - Tasks - Copy database and use the copy wizard.
>> This is stored as a job. I would like the job to copy to a different file
>> everyday. How can I do that ?
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:OZa8uITrHHA.4624@.TK2MSFTNGP06.phx.gbl...
>> Craft a RESTORE statement using dynamic sql and a date variable
>> formatted as 112 (see Convert in BOL).
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:eRmOKcSrHHA.3248@.TK2MSFTNGP03.phx.gbl...
>> Is there a way to copy a database on a scheduled basis and name the
>> copy different names every time ?
>> For example: database copy for today will be named mydb_061207,
>> database copy for tomorrow will be named mydb_061307, etc.
>> Thank you.
>>|||There isn't any COPY command - you'll have to use BACKUP/RESTORE.
The closet you can get to a COPY command, is to first detach your
database, copy the files manually and then attach both the original and
the new database. The commands for this is sp_detach_db and CREATE
DATABASE FOR ATTACH which you can look up in Books On Line.
Personally I prefer the BACKUP/RESTORE though. With the BACKUP/RESTORE
task you'll keep your source database on-line and operational all the
time. This means that if the backup file is corrupted or damaged so it
can't be restored, you can just make a new copy. With the detach/attach
method your source database will be off-line for the time it takes to
copy the files and until it is being attached again. Theoretically
you'll also have the risk that something has damaged the files during
(or after) the detach so they can't be attached again. In that case
you've lost both the original and the new database and you'll have to
restore your most recent backup (...which will give you even more
downtime...:-)...).
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
fniles wrote:
> Thank you, that's great !
> Do you by any chance know the T-SQL to COPY the database, instead of BACKUP
> ? I need to copy.
> Thanks.
> ""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
> message news:OFtrUrYrHHA.484@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> You'll have to write you own BACKUP script that generates the filename
>> dynamically based on todays date. Then you will have to schedule this to
>> run every day.
>> A very simple example of a script to do this, could be something like:
>>
>> DECLARE @.Filename VARCHAR(300)
>> DECLARE @.BackupPath VARCHAR(300)
>> DECLARE @.Device VARCHAR(600)
>> SET @.BackupPath = 'C:\MSSQL\BACKUP\'
>> SET @.FileName = 'YourDatabaseName'+CONVERT(NVARCHAR(16),GETDATE(),112) +
>> '.BAK'
>> SET @.Device = @.BackupPath+@.FileName
>> BACKUP DATABASE YourDataBaseName TO DISK = @.Device
>>
>> This will backup your database to a file named with the databasename and
>> todays date in the format YYYYMMDD.It will place the backup in the
>> Backuppath directory you specify. Please remember that the backup folder
>> has to exist before you run the script.
>> The script is just an example to get you started. You can then add on
>> extra "features" as you get the need.
>> --
>> Regards
>> Steen Schlüter Persson
>> Database Administrator / System Administrator
>>
>> fniles wrote:
>> Thank you.
>> I am sorry, I am new to 2005. What did you mean by crafting a restore
>> statement using dynamic sql ?
>> When I copy the database, in the SQL Server Management STudio I right
>> click on the database - Tasks - Copy database and use the copy wizard.
>> This is stored as a job. I would like the job to copy to a different file
>> everyday. How can I do that ?
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:OZa8uITrHHA.4624@.TK2MSFTNGP06.phx.gbl...
>> Craft a RESTORE statement using dynamic sql and a date variable
>> formatted as 112 (see Convert in BOL).
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:eRmOKcSrHHA.3248@.TK2MSFTNGP03.phx.gbl...
>> Is there a way to copy a database on a scheduled basis and name the
>> copy different names every time ?
>> For example: database copy for today will be named mydb_061207,
>> database copy for tomorrow will be named mydb_061307, etc.
>> Thank you.
>|||Thank you very much. That's very helpful.
If I use BACKUP/RESTORE, when restoring, is it possible to restore it to a
different database name so that it does not override the original database ?
""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:udQr4VlrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> There isn't any COPY command - you'll have to use BACKUP/RESTORE.
> The closet you can get to a COPY command, is to first detach your
> database, copy the files manually and then attach both the original and
> the new database. The commands for this is sp_detach_db and CREATE
> DATABASE FOR ATTACH which you can look up in Books On Line.
> Personally I prefer the BACKUP/RESTORE though. With the BACKUP/RESTORE
> task you'll keep your source database on-line and operational all the
> time. This means that if the backup file is corrupted or damaged so it
> can't be restored, you can just make a new copy. With the detach/attach
> method your source database will be off-line for the time it takes to copy
> the files and until it is being attached again. Theoretically you'll also
> have the risk that something has damaged the files during (or after) the
> detach so they can't be attached again. In that case you've lost both the
> original and the new database and you'll have to restore your most recent
> backup (...which will give you even more downtime...:-)...).
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
> fniles wrote:
>> Thank you, that's great !
>> Do you by any chance know the T-SQL to COPY the database, instead of
>> BACKUP ? I need to copy.
>> Thanks.
>> ""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote
>> in message news:OFtrUrYrHHA.484@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> You'll have to write you own BACKUP script that generates the filename
>> dynamically based on todays date. Then you will have to schedule this to
>> run every day.
>> A very simple example of a script to do this, could be something like:
>>
>> DECLARE @.Filename VARCHAR(300)
>> DECLARE @.BackupPath VARCHAR(300)
>> DECLARE @.Device VARCHAR(600)
>> SET @.BackupPath = 'C:\MSSQL\BACKUP\'
>> SET @.FileName = 'YourDatabaseName'+CONVERT(NVARCHAR(16),GETDATE(),112) +
>> '.BAK'
>> SET @.Device = @.BackupPath+@.FileName
>> BACKUP DATABASE YourDataBaseName TO DISK = @.Device
>>
>> This will backup your database to a file named with the databasename and
>> todays date in the format YYYYMMDD.It will place the backup in the
>> Backuppath directory you specify. Please remember that the backup folder
>> has to exist before you run the script.
>> The script is just an example to get you started. You can then add on
>> extra "features" as you get the need.
>> --
>> Regards
>> Steen Schlüter Persson
>> Database Administrator / System Administrator
>>
>> fniles wrote:
>> Thank you.
>> I am sorry, I am new to 2005. What did you mean by crafting a restore
>> statement using dynamic sql ?
>> When I copy the database, in the SQL Server Management STudio I right
>> click on the database - Tasks - Copy database and use the copy wizard.
>> This is stored as a job. I would like the job to copy to a different
>> file everyday. How can I do that ?
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:OZa8uITrHHA.4624@.TK2MSFTNGP06.phx.gbl...
>> Craft a RESTORE statement using dynamic sql and a date variable
>> formatted as 112 (see Convert in BOL).
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:eRmOKcSrHHA.3248@.TK2MSFTNGP03.phx.gbl...
>> Is there a way to copy a database on a scheduled basis and name the
>> copy different names every time ?
>> For example: database copy for today will be named mydb_061207,
>> database copy for tomorrow will be named mydb_061307, etc.
>> Thank you.
>>|||> If I use BACKUP/RESTORE, when restoring, is it possible to restore it to a different database name
> so that it does not override the original database ?
Yes. Make sure you read up on the MOVE option of the RESTORE command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"fniles" <fniles@.pfmail.com> wrote in message news:OH%23xOIprHHA.3896@.TK2MSFTNGP04.phx.gbl...
> Thank you very much. That's very helpful.
> If I use BACKUP/RESTORE, when restoring, is it possible to restore it to a different database name
> so that it does not override the original database ?
> ""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in message
> news:udQr4VlrHHA.3228@.TK2MSFTNGP03.phx.gbl...
>> There isn't any COPY command - you'll have to use BACKUP/RESTORE.
>> The closet you can get to a COPY command, is to first detach your database, copy the files
>> manually and then attach both the original and the new database. The commands for this is
>> sp_detach_db and CREATE DATABASE FOR ATTACH which you can look up in Books On Line.
>> Personally I prefer the BACKUP/RESTORE though. With the BACKUP/RESTORE task you'll keep your
>> source database on-line and operational all the time. This means that if the backup file is
>> corrupted or damaged so it can't be restored, you can just make a new copy. With the
>> detach/attach method your source database will be off-line for the time it takes to copy the
>> files and until it is being attached again. Theoretically you'll also have the risk that
>> something has damaged the files during (or after) the detach so they can't be attached again. In
>> that case you've lost both the original and the new database and you'll have to restore your most
>> recent backup (...which will give you even more downtime...:-)...).
>> --
>> Regards
>> Steen Schlüter Persson
>> Database Administrator / System Administrator
>> fniles wrote:
>> Thank you, that's great !
>> Do you by any chance know the T-SQL to COPY the database, instead of BACKUP ? I need to copy.
>> Thanks.
>> ""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in message
>> news:OFtrUrYrHHA.484@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> You'll have to write you own BACKUP script that generates the filename dynamically based on
>> todays date. Then you will have to schedule this to run every day.
>> A very simple example of a script to do this, could be something like:
>>
>> DECLARE @.Filename VARCHAR(300)
>> DECLARE @.BackupPath VARCHAR(300)
>> DECLARE @.Device VARCHAR(600)
>> SET @.BackupPath = 'C:\MSSQL\BACKUP\'
>> SET @.FileName = 'YourDatabaseName'+CONVERT(NVARCHAR(16),GETDATE(),112) + '.BAK'
>> SET @.Device = @.BackupPath+@.FileName
>> BACKUP DATABASE YourDataBaseName TO DISK = @.Device
>>
>> This will backup your database to a file named with the databasename and todays date in the
>> format YYYYMMDD.It will place the backup in the Backuppath directory you specify. Please
>> remember that the backup folder has to exist before you run the script.
>> The script is just an example to get you started. You can then add on extra "features" as you
>> get the need.
>> --
>> Regards
>> Steen Schlüter Persson
>> Database Administrator / System Administrator
>>
>> fniles wrote:
>> Thank you.
>> I am sorry, I am new to 2005. What did you mean by crafting a restore statement using dynamic
>> sql ?
>> When I copy the database, in the SQL Server Management STudio I right click on the database -
>> Tasks - Copy database and use the copy wizard. This is stored as a job. I would like the job
>> to copy to a different file everyday. How can I do that ?
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:OZa8uITrHHA.4624@.TK2MSFTNGP06.phx.gbl...
>> Craft a RESTORE statement using dynamic sql and a date variable formatted as 112 (see Convert
>> in BOL).
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "fniles" <fniles@.pfmail.com> wrote in message news:eRmOKcSrHHA.3248@.TK2MSFTNGP03.phx.gbl...
>>> Is there a way to copy a database on a scheduled basis and name the copy different names
>>> every time ?
>>> For example: database copy for today will be named mydb_061207, database copy for tomorrow
>>> will be named mydb_061307, etc.
>>>
>>> Thank you.
>>>
>

Wednesday, March 7, 2012

Copy All tables

Hey I'm trying to simply copy all tables from one database to another on a
nightly basis.
If i select the database, choose export, and go through all the steps,
choosing the starting database, the destination database, the values to back
up and scheduling it, it all seems fine. But the job always fails. If i
select only a couple of tables, the job runs fine? anyone have any ideas
why this would be? Or advice on another way to backup the tables nightly?
Thanks
James,
I'm not sure if you're using replication or DTS for this but either way it
sounds like a locking issue. You may need to prevent users accessing the
tables when you do the copy eg setting the database to single user mode.
Alternatively, you could use transactional replication to synchronize the
data. The initial snapshot can be taken without requiring a table lock -
allowing concurrent access. This would perhaps be preferable as you won't be
duplicating data transfer each day.
Regards,
Paul Ibison
|||Are you deleteing all the rows in your destination table or appending? It sounds like you aren't. Go back in your package and delete the existing rows before appending.
Looking for a book on SQL Server replication
http://www.nwsu.com/0974973602.html
|||How do i lock the database?
This job is running at 3am, so there shouldn't be any users working, and the
backup seems to only run for about 5 minutes before failing?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u6twIeqNEHA.1456@.TK2MSFTNGP09.phx.gbl...
> James,
> I'm not sure if you're using replication or DTS for this but either way it
> sounds like a locking issue. You may need to prevent users accessing the
> tables when you do the copy eg setting the database to single user mode.
> Alternatively, you could use transactional replication to synchronize the
> data. The initial snapshot can be taken without requiring a table lock -
> allowing concurrent access. This would perhaps be preferable as you won't
be
> duplicating data transfer each day.
> Regards,
> Paul Ibison
>
|||James,
you can't lock a database, apart from making it single-user. I need to ask
some questions: Is your job runnung a dts package or replication or sps? Is
the structure of the tables changing each day or constant? How much of the
data is changing each day? How much data is there in the tables? What error
messages are returned? I need a bit more info before committing myself to
definite advice, although I suspect log-shipping or transactional
replication are what you need.
Regards,
Paul Ibison
|||Heres the error message:
Executed as user: CDC-EDB1\SYSTEM. ...Start: Copy SQL Server Objects
DTSRun OnProgress: Copy SQL Server Objects; Scripting objects for Transfer;
PercentComplete = 0; ProgressCount = 0 DTSRun OnProgress: Copy SQL Server
Objects; Scripting Logins; PercentComplete = 0; ProgressCount = 0 DTSRun
OnProgress: Copy SQL Server Objects; Scripting Users; PercentComplete = 0;
ProgressCount = 0 DTSRun OnProgress: Copy SQL Server Objects; Enumerating
objects for Transfer; PercentComplete = 0; ProgressCount = 0 DTSRun
OnProgress: Copy SQL Server Objects; Enumerating objects for Transfer : 0
Percent Completed; PercentComplete = 0; ProgressCount = 0 DTSRun
OnProgress: Copy SQL Server Objects; 0 Percent Completed; PercentComplete =
0; ProgressCount = 0 DTSRun OnProgress: Copy SQL Server Objects;
Enumerating objects for Transfer : 10 Percent Completed; PercentComplete =
10; ProgressCount = 0 DTSRun OnProgress: Copy SQL Server Objects; 12
Percent Completed; Pe... Process Exit Code 1. The step failed.
Its a DTS package thats running. The values in the tables are changing
daily, I want them copied to another server where we run reports on the
data. This server is old and slow, so I don't want it slowing down the live
server.
Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OaKmLUsNEHA.4060@.TK2MSFTNGP10.phx.gbl...
> James,
> you can't lock a database, apart from making it single-user. I need to ask
> some questions: Is your job runnung a dts package or replication or sps?
Is
> the structure of the tables changing each day or constant? How much of the
> data is changing each day? How much data is there in the tables? What
error
> messages are returned? I need a bit more info before committing myself to
> definite advice, although I suspect log-shipping or transactional
> replication are what you need.
> Regards,
> Paul Ibison
>
|||James,
the error message is not helpful and there is no way with the copy objects task of getting more info. It uses nolock , so locking is not the issue, and it is difficult to tell the exact cause of the problem. If you have constraints on the table and are us
ing row by row logging you can use the transform data task, which will give you a lot more info and possibly help you identify the cause of the problem. This will be more of a hit on your system though. For less of a hit, you can use log shipping, and the
n move the tables to a separate standby server and then copy them, or possibly overwrite the standby database completely if this suits your needs. Finally, there is transactional replication with a remote distributor which would also transfer the table da
ta.
Regards,
Paul Ibison

Tuesday, March 20, 2012

Copy Database Wizard

Monday, March 19, 2012

Copy database question

Copy database question

Copy database question

Wednesday, March 7, 2012

Copy All tables

Converting problem

Blog Archive

About Me