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
Printable View
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
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?Quote:
Originally Posted by Asgorath
Regards
Im Nearly there, can anyone help i have an error near the .* on the Select INTO statement
VB Code:
--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
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.
Code: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?Quote:
Originally Posted by szlamany
Also i get an Error saying 'Funds_data' doesn't exist! What is it?
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.
Another way to know these names is to get into ENTERPRISE MANAGER and look at the DATABASE properties.Code:RESTORE FILELISTONLY
FROM DISK = 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.bak'
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.
Cheers for the reply figure that bit out! For everyones info tho
Code: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
You don't need to use a cursor
Try this
RegardsCode:Declare @sql NVarChar(4000)
Select @sql= isnull(@sql,'') +'Delete From ' + Name + ';' From sysObjects Where xType='U'
Exec sp_executesql @sql
Jorge
LOL i spent 2 hours learning TSQL to do that loop, and you do it in 3 lines LOL Cheers mate :thumb:Quote:
Originally Posted by Asgorath
In general CURSORS are considered not set-based logic and should be avoided.Quote:
Originally Posted by dannywooly
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? :D
Its all good mate, im a learner and like new information, i have learnt loads this morning trying to fumble through these techniques!Quote:
Originally Posted by szlamany
Cheers All
have a good day