IF the server is NOT a PRODUCTION server, then you can backup the databases you need for Data manipulation and restore them with databaseName_test.
Keep in mind that you will still be competing for resources on the server by creating another set of databases.
Why cant you install another instance of sql server ? Even the enterprise edition is free for 30 days (for evaluation purpose ONLY) or just buy a Developer edition which is much cheaper. If you have MSDN subscription, you will be able to get one. Windows Azure is also another option.
Below will give you the correct version and some more info :
SELECT
SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
SERVERPROPERTY ('ServerName') AS SQLServerName,
SERVERPROPERTY ('Edition') AS ServerEdition,
CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) + ' + '
+ CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build'
+ CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion,
RIGHT(@@version, (Len(@@Version)-charindex('Windows',@@version, 1))+1) AS [O.S.],
SERVERPROPERTY ('Collation') AS Collation
One to do the backup and restore (running as an sa) and then creating a separate user that only has access to the PRODCOPY database, that then runs the clean up section of this script. Any thoughts on this?
This is a good idea. You can have 2 jobs
- Perform backup and restore of PROD database to dev environments
- Once above is completed, kick off the scrubbing script to purge old data.
Some things to consider in your script :
If the users are connected to the PRODCOPY
, make sure to disconnect them using
alter database db_name
set single_user with rollback immediate
waitfor delay '00:00:05'
alter database db_name
set multi_user
Backing up PROD database, if you are on sql server 2008R2 and up, then standard edition supports BACKUP compression
. You should use that as well.
-- we need to delete the existing PRODCOPY
Make sure you have Instant File Initialization to cut down the restore time.
do not drop the database, instead restore with replace
. With Replace will NOT require pages with identical data to be rewritten there by cutting down the restore time.
Your subsequent script will fail as you are restoring the database, but still putting it in NORECOVERY. You should change it to RECOVERY
.
-- We just want to remove everything from this table
A good coding practice, avoid using INFORMATION_SCHEMA. Instead use sys.tables
.
Also, always use schema.tableName instead of just tableName.
The references are to Aaron Bertrand's blog that explains in more detail
Change the sequence of shrink and rebuild indexes. The Shrink should be first followed by a rebuild or reorg and update stats.
Use the new syntax of alter index rebuild | reorganize
. To automate the above point, you can use Ola Hallengren's - SQL Server Index and Statistics Maintenance
Best Answer
If this is not needed to perform on each and every day but sometimes, I suggest the following procedure:
Open the source server in Management Studio. Right click the database. Choose "Tasks" and "Generate Scripts". In the wizard choose either "entire database" or "specific objects" (and choose your tables).
Click Next.
Click Advanced.
At the property "Check for existence" choose "TRUE".
At the property "Script Drop and Create" choose "Script Drop and Create" (this (re-)creates the (all|selected) tables with the current structure).
Click OK.
Click OK / OK.
Run the script in your destination server and you are done.