Copy DB to another DB

[dannywooly]

Hi All

After a trawl around the net i just need some pointers. I have Local DB that i want to make an empty copy of on the same machine in Vb.NET. I am wondering if there are some SQL commands to achieve this?

Regards

Danny

[Asgorath]

Hi
Just generate all sql scripts you need (tables,stored procedures,etc...) and run it on destination sql sever.

Regards
Jorge

[dannywooly]

Hi
Just generate all sql scripts you need (tables,stored procedures,etc...) and run it on destination sql sever.

Regards
Jorge

In a SP how do i loop the SysObjects for the table names?

Regards

[dannywooly]

Im Nearly there, can anyone help i have an error near the .* on the Select INTO statement

--Create New DB
CREATE DATABASE BufferDB
ON
(NAME = BufferDB, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\data\BufferDB.mdf', 
SIZE = 4, MAXSIZE = 10, FILEGROWTH = 1)
 
--Loop the Tables
DECLARE Source_Tables CURSOR FOR
SELECT * FROM SysObjects WHERE [xType] = 'u' AND [NAME] <> 'dtProperties'
 
OPEN Source_Tables
 
BEGIN
DECLARE @tablename VarChar
  FETCH NEXT FROM Source_Tables INTO @tablename
    WHILE (@@FETCH_STATUS = 0)
 
      --Copy table across to new buffer
      SELECT @tablename.* INTO BufferDB.@tablename FROM @tablename
 
      --Delete all records
      DELETE * FROM BufferDB.@tablename 
 
    FETCH NEXT FROM Source_Tables INTO @tablename
 
  CLOSE Source_Tables
  
  DEALLOCATE Source_Tables
END

[szlamany]

Simply do a BACKUP on the server and RESTORE on another server - scripting does not get everything (permissions for example).

Here's an example of making BACKUP and restoring to the SAME SERVER with a different DATABASE name.

BACKUP DATABASE Funds 
   TO DISK = 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.bak'
RESTORE FILELISTONLY 
   FROM DISK = 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.bak'
RESTORE DATABASE Funds_Copy
   FROM DISK = 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.bak'
   WITH MOVE 'Funds_data' TO 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.mdf',
   MOVE 'Funds_log' TO 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.ldf'
GO

[dannywooly]

Simply do a BACKUP on the server and RESTORE on another server - scripting does not get everything (permissions for example).

Here's an example of making BACKUP and restoring to the SAME SERVER with a different DATABASE name.

BACKUP DATABASE Funds 
   TO DISK = 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.bak'
RESTORE FILELISTONLY 
   FROM DISK = 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.bak'
RESTORE DATABASE Funds_Copy
   FROM DISK = 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.bak'
   WITH MOVE 'Funds_data' TO 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.mdf',
   MOVE 'Funds_log' TO 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.ldf'
GO

Excellent, what about clearing out the Data?

Also i get an Error saying 'Funds_data' doesn't exist! What is it?

[szlamany]

FUNDS is the name of my database...put your own name in the first BACKUP statement...

FUNDS_COPY is the new name I chose for the database for when it gets restored...

If you don't know the names of the DATA and LOG objects of your database, then this command will list them - but only after the BACKUP is done.

RESTORE FILELISTONLY 
   FROM DISK = 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.bak'

Another way to know these names is to get into ENTERPRISE MANAGER and look at the DATABASE properties.

Deleting the data is up to you - probably using a method like you have shown - looping through sysobjects cursor works - I've done that before to remove sensitive data from a DB we were giving to someone else.

[dannywooly]

Cheers for the reply figure that bit out! For everyones info tho

BACKUP DATABASE YourDBName
   TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\CopiedDB.bak'
RESTORE FILELISTONLY 
   FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\CopiedDB.bak'
RESTORE DATABASE CopiedDB
   FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\CopiedDB.bak'
   WITH MOVE YourDBName_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\CopiedDB.mdf',
   MOVE YourDBName_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\CopiedDB.ldf'

--Loop the Tables
DECLARE @tablename SYSNAME
DECLARE Source_Tables CURSOR FOR
SELECT [Name] FROM SysObjects WHERE [xType] = 'u' AND [NAME] <> 'dtProperties'

OPEN Source_Tables

BEGIN
  FETCH NEXT FROM Source_Tables INTO @tablename
    WHILE (@@FETCH_STATUS = 0)
      --Delete all records
      EXECUTE ('DELETE * FROM ' + @tablename)  
    FETCH NEXT FROM Source_Tables INTO @tablename
  CLOSE Source_Tables
  
  DEALLOCATE Source_Tables
END

[Asgorath]

You don't need to use a cursor
Try this

Declare @sql NVarChar(4000)
Select @sql= isnull(@sql,'') +'Delete From ' + Name + ';' From sysObjects Where xType='U'
Exec sp_executesql @sql

Regards
Jorge

[dannywooly]

You don't need to use a cursor
Try this

Declare @sql NVarChar(4000)
Select @sql= isnull(@sql,'') +'Delete From ' + Name + ';' From sysObjects Where xType='U'
Exec sp_executesql @sql

Regards
Jorge

LOL i spent 2 hours learning TSQL to do that loop, and you do it in 3 lines LOL Cheers mate

[szlamany]

LOL i spent 2 hours learning TSQL to do that loop, and you do it in 3 lines LOL Cheers mate

In general CURSORS are considered not set-based logic and should be avoided.

Then again, executing a dynamic SQL string is considered bad practice also.

But in your case - needing to do "system" cleanup/DBA work means you can ignore those two rules - you could use the CURSOR or use the dynamic SQL - both fit the bill in this case.

It's good to know both techniques - the SELECT @SQL=... "loop" that Asgorath posted is a very nice trick that can be used for all kinds of purposes.

As far as I'm concerned, if I can take a mainframe BASIC program calculation and get it into T-SQL and it requires a cursor to pull it off, I've still made a huge leap towards getting all my business logic into the database.

So, in the end, cursors are not so bad - just make sure they are required to pull of the end goal, and not that it's simply easier to think in the sequential processing model as opposed to the SQL set-based processing model.

Have I talked too much yet?

[dannywooly]

In general CURSORS are considered not set-based logic and should be avoided.

Then again, executing a dynamic SQL string is considered bad practice also.

But in your case - needing to do "system" cleanup/DBA work means you can ignore those two rules - you could use the CURSOR or use the dynamic SQL - both fit the bill in this case.

It's good to know both techniques - the SELECT @SQL=... "loop" that Asgorath posted is a very nice trick that can be used for all kinds of purposes.

As far as I'm concerned, if I can take a mainframe BASIC program calculation and get it into T-SQL and it requires a cursor to pull it off, I've still made a huge leap towards getting all my business logic into the database.

So, in the end, cursors are not so bad - just make sure they are required to pull of the end goal, and not that it's simply easier to think in the sequential processing model as opposed to the SQL set-based processing model.

Have I talked too much yet?

Its all good mate, im a learner and like new information, i have learnt loads this morning trying to fumble through these techniques!

Cheers All

have a good day