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.
>>>
>