"Kevin" <nospam@discussion.com> wrote in message
news:OrAaeECWFHA.3320@TK2MSFTNGP12.phx.gbl...
> Hi,
>
> I am trying to figure out how I can copy a database and all the db user
> objects within it to a new database that has today's date as a part of the
> name. The only way I know is to run below and create/populate and tables
> using SQL statements. Is using the DTS objects from VBS the recommended
> way to accomplish this?
>
> execute('create database ' + @todayDate)
>

"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:uWPVkICWFHA.548@tk2msftngp13.phx.gbl...
> No that is way too much trouble.  Just do a restore with a new name.
> These links should show you all the ways to copy a db.  You just need to
> do a few lines of code to create a new name and issue the restore.
>
> http://vyaskn.tripod.com/moving_sql_server.htm  Moving DBs
> http://www.databasejournal.com/features/mssql/article.php/3379901
> Moving system DB's
> http://www.support.microsoft.com/?id=314546     Moving DB's between
> Servers
> http://www.support.microsoft.com/?id=224071     Moving SQL Server
> Databases to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465             Using WITH MOVE in a
> Restore
> http://www.support.microsoft.com/?id=246133     How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897    Mapping Logins & SIDs after
> a Restore
> http://www.dbmaint.com/SyncSqlLogins.asp         Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001     User Logon and/or
> Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872    How to Resolve Permission
> Issues When a Database Is Moved Between SQL
>
> --
> Andrew J. Kelly  SQL MVP
>
>
> "Kevin" <nospam@discussion.com> wrote in message
> news:OrAaeECWFHA.3320@TK2MSFTNGP12.phx.gbl...
>> Hi,
>>
>> I am trying to figure out how I can copy a database and all the db user
>> objects within it to a new database that has today's date as a part of
>> the name. The only way I know is to run below and create/populate and
>> tables using SQL statements. Is using the DTS objects from VBS the
>> recommended way to accomplish this?
>>
>> execute('create database ' + @todayDate)
>>
>
>

"David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in
message news:u5$9WRCWFHA.2660@TK2MSFTNGP10.phx.gbl...
>
> "Kevin" <nospam@discussion.com> wrote in message
> news:OrAaeECWFHA.3320@TK2MSFTNGP12.phx.gbl...
>> Hi,
>>
>> I am trying to figure out how I can copy a database and all the db user
>> objects within it to a new database that has today's date as a part of
>> the name. The only way I know is to run below and create/populate and
>> tables using SQL statements. Is using the DTS objects from VBS the
>> recommended way to accomplish this?
>>
>> execute('create database ' + @todayDate)
>>
>
> If the database has only one data file, and the files have the default
> names, the following script should work with appropriate modifications.
>
> If your database has multiple files or different logical file names, then
> you will have to code that.
>
> declare
>  @db varchar(255),
>  @newdb varchar(255),
>  @dumpfile varchar(2000),
>  @datafolder varchar(2000),
>  @logfolder varchar(2000),
>  @datafile varchar(50),
>  @logfile varchar(50),
>  @newdatafile varchar(2000),
>  @newlogfile varchar(2000)
>
> begin
>   set @dumpfile = 'c:\temp.bak'
>   set @db = 'test'
>    set @newdb = @db + convert(varchar,GetDate(),112)
>    set @datafolder = 'c:\program files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\'
>    set @logfolder = 'c:\program files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\'
>    set @datafile = @db
>    set @logfile = @db + '_log'
>    set @newdatafile = @datafolder + @newdb + '.mdf'
>    set @newlogfile = @logfolder + @newdb + '_log.ldf'
>    print  @newdatafile
>
>   backup database @db to disk=@dumpfile
>   --restore filelistonly from disk=@dumpfile
>
>   restore database @newdb  from disk=@dumpfile
>    with move @datafile to @newdatafile,
>         move @logfile to @newlogfile
>
> end
>
> --David
>

"Kevin" <no_spam@nospamfordiscussion.com> wrote in message
news:%23mFL5GEWFHA.2196@TK2MSFTNGP09.phx.gbl...
> Unfortunately, the code did not work. I got "Device activation error" ..
>
>
> "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in
> message news:u5$9WRCWFHA.2660@TK2MSFTNGP10.phx.gbl...
>>
>> "Kevin" <nospam@discussion.com> wrote in message
>> news:OrAaeECWFHA.3320@TK2MSFTNGP12.phx.gbl...
>>> Hi,
>>>
>>> I am trying to figure out how I can copy a database and all the db user
>>> objects within it to a new database that has today's date as a part of
>>> the name. The only way I know is to run below and create/populate and
>>> tables using SQL statements. Is using the DTS objects from VBS the
>>> recommended way to accomplish this?
>>>
>>> execute('create database ' + @todayDate)
>>>
>>
>> If the database has only one data file, and the files have the default
>> names, the following script should work with appropriate modifications.
>>
>> If your database has multiple files or different logical file names, then
>> you will have to code that.
>>
>> declare
>>  @db varchar(255),
>>  @newdb varchar(255),
>>  @dumpfile varchar(2000),
>>  @datafolder varchar(2000),
>>  @logfolder varchar(2000),
>>  @datafile varchar(50),
>>  @logfile varchar(50),
>>  @newdatafile varchar(2000),
>>  @newlogfile varchar(2000)
>>
>> begin
>>   set @dumpfile = 'c:\temp.bak'
>>   set @db = 'test'
>>    set @newdb = @db + convert(varchar,GetDate(),112)
>>    set @datafolder = 'c:\program files\Microsoft SQL
>> Server\MSSQL.1\MSSQL\Data\'
>>    set @logfolder = 'c:\program files\Microsoft SQL
>> Server\MSSQL.1\MSSQL\Data\'
>>    set @datafile = @db
>>    set @logfile = @db + '_log'
>>    set @newdatafile = @datafolder + @newdb + '.mdf'
>>    set @newlogfile = @logfolder + @newdb + '_log.ldf'
>>    print  @newdatafile
>>
>>   backup database @db to disk=@dumpfile
>>   --restore filelistonly from disk=@dumpfile
>>
>>   restore database @newdb  from disk=@dumpfile
>>    with move @datafile to @newdatafile,
>>         move @logfile to @newlogfile
>>
>> end
>>
>> --David
>>
>
>