SQL
How to properly do an export and import of a database
Last year, I came across a database that had 2 corrupted tables. Fortunately, these 2 tables were actually not used so it can safely get ignored. However with those 2 corrupted tables still in the database, I could not get a passing result when doing DBCC CheckDB against the database. And I have tried exporting the database into an “empty” database created by the same application without success.
Here is how I fixed my problem:
Step 1 – Export Database
- Launch SSMS as an admin and login as SA if possible or with a user with sysadmin rights.
- Right-click on the database and select Tasks > Export Data. Click Next.
- Select SQL Server Native Client 11.x under Data Source, specify the Server Name, use SA (preferred). And then select the source database. Click Next.
- Under Destination, select SQL Server Native Client 11.x. And specify the other values.
- Click New under Database to create a new DB where the data will be exported to. Click Next.
- In the next screen, select Copy data from one or more tables or views. Click Next.
- In the next screen, select all tables you would like to export and select Next. I omitted the Views at this stage as I do not need them. And leave all the rest as default and click on Finish to start the export process.
Step 2 – Working on the Empty Database
- In my case, the application my database was using has the ability to create an empty database with all views, schemas, keys, etc. My only problem is that it then also populates some of the tables with default data. I tried to truncate these tables got FK restrictions would not allow me. So I had to run a delete from on all tables.
use <empty_db>
EXEC sp_msforeachtable @command1=”delete from ?”
GO
- This command can also be used to make sure your database is really empty. I have seen other posts where they suggest to delete all FKs and then delete the data and then reinstall FKs. For me, that is too much work.
Step 3 – Importing Data into the Empty Database
- Right-click on the Empty database, select Task > Import Data
- Fill out the data source referencing to the exported database from the previous step. Click Next.
- In the Choose a Destination window, set it to the empty database. Click Next.
- Again select Copy data from one or more tables or views on the next screen.
- In the next screen, select all tables. When they are all selected, click Edit Mappings and select Enable Identity Insert. Click Next. And hit Finish to start the import.
You should not get any errors at this point. But if you do, review the errors and make appropriate adjustments and try again.
——————————- Additional Info ———————————–
If you are having problems with constraints when deleting existing data on the table or when trying to import data. Use the command below:
EXEC sp_msforeachtable @command1=”ALTER TABLE ? NOCHECK CONSTRAINT ALL”
However, you may need to know what constraints are currently set so you know what to enable later. I use this command to show me what they are.
SELECT name, tbl=object_name(parent_obj)
FROM sysobjects
WHERE objectproperty(id, ‘CnstIsNotTrusted’) = 1
GO
SELECT *, name, is_not_trusted
FROM sys.foreign_keys
WHERE is_not_trusted = 1
GO
Disclaimer: Scripts are provided AS IS, and author is not responsible with any issues resulting to usage of this script.
SQL SSRS: creating multiple parameters and allowing search on single or multiple entries
I built an SSRS report for one of my colleagues and it took me a little bit to figure how to properly setup the Parameters. I am hoping this would help somebody out there that may run into same issue.
Here is the scenario:
I created 4 parameters named ‘Asset Barcode, Asset Serial, Purchase Order and Former Tag’. And if all these 4 parameters are set to blank, then all data should be displayed. And if any of the parameters are blank, then it will not be included in the query.
For the past couple of days, I have been testing and searching online on how to get this working, but it seems that a lot of people are suggesting to create a stored procedure which is very cumbersome. Without the correct script, the blank parameters are still being included in the query and thereby not giving an accurate result.
Here is how my script looks like that worked:
NOTE: DO NOT copy and paste the code below, the commas and quotes are not formatted correctly in WordPress.
SELECT
Assets.CreatedBy
,Assets.ModifiedBy
,Assets.[General/Asset Name]
,Assets.[General/Site_id]
,Assets.[General/Loc_id]
,Assets.[Assets/Classification]
,Assets.[Assets/Usage]
,Assets.[Assets/Condition]
,Assets.[Assets/Inventory Date]
,Assets.[Assets/Data Source]
,Assets.[Assets/Serial Number]
,Assets.[Assets/Former Tag No]
,Assets.[Assets/Equipment/Model]
,Assets.DateCreated
,Assets.DateModified
,Assets.[General/Purchase Order]
,Assets.[General/Barcode]
,Assets.[Employee Details/Employee Notes]
,Assets.[Assets/Mfg]
,Assets.[Assets/Category]
FROM
Assets
WHERE
(@Asset_Barcode = ” or Assets.[General/Barcode] LIKE ‘%’ + @Asset_Barcode + ‘%’) and
(@Asset_Serial = ” or Assets.[Assets/Serial Number] LIKE ‘%’ + @Asset_Serial + ‘%’) and
(@Purchase_Order = ” or Assets.[General/Purchase Order] LIKE ‘%’ + @Purchase_Order + ‘%’) and
(@Former_Tag = ” or Assets.[Assets/Former Tag No] LIKE ‘%’ + @Former_Tag + ‘%’)
Merging two tables in a non-conventional way
I found this an easy way to merge two tables together without using UNION, UNION ALL. Here is a summary that one of our admins are having that led me into creating this script:
We are using a hardware asset manager software, and the vendor did all the data migration from the old version to the new version which also included a migration of data to a new database with different table schemas. The migration went fine, but our admin noticed some values that were not migrated correctly. So on the new database, there are some missing data.
Instead of using the UNION or UNION ALL to merge the old and new database. I decided to create my own script. Which also made more sense for us, since we only wanted to update the data that are on the new database and not add junk to it. The old DB has 10,000 records and the new DB has only 7,000. The old DB included the retired assets that we no longer want.
So here is how I did it in a script: (I will leave the table and database name as is)
— You may have to fix the single and double quotes after pasting it to SMC as wordpress formatted the characters differently
use vam_master
DECLARE @T TABLE (SQL varchar(1000))
INSERT INTO @T
SELECT ‘UPDATE dbo.assets SET AssetSerialNo=”’ + [assets/serial number] + ”’ WHERE Barcode=”’ + CAST([general/barcode] as varchar(10)) + ””
FROM dbo.assets
where [general/barcode] is not NULL
INSERT INTO @T
SELECT ‘UPDATE dbo.assets SET PurchaseOrderNo=”’ + [general/purchase order] + ”’ WHERE Barcode=”’ + CAST([general/barcode] as varchar(10)) + ””
FROM dbo.assets
where [general/barcode] is not NULL
INSERT INTO @T
SELECT ‘UPDATE dbo.assets SET Model=”’ + [assets/equipment/model] + ”’ WHERE Barcode=”’+ CAST([general/barcode] as varchar(10)) + ””
FROM dbo.assets
where [general/barcode] is not NULL
INSERT INTO @T
SELECT ‘UPDATE dbo.assets SET mfg=”’ + [assets/mfg] + ”’ WHERE Barcode=”’+ CAST([general/barcode] as varchar(10)) + ””
FROM dbo.assets
where [general/barcode] is not NULL
select * from @T
Running this script will output results like ‘UPDATE dbo.assets SET AssetSerialNo=’blah’ WHERE Barcode=’blah’. So you can then run this against the new DB and it will update the table with missing values for what you have selected and just ignore those entries that do not exist on the new DB.
I hope this helps, if you are on the same situation as me.
Workaround for syspolicy_purge_history step 3 error on SQL 2012
I have started setting up a couple of servers with SQL 2012 SP2. And I noticed that the syspolicy_purge_history job is failing on Step 3 with the following error message:
A job step received an error at line 1 in a Powershell script. The corresponding line is ‘set-executionpolicy RemoteSigned -scope process -Force’. Correct the script and reschedule the job.
So far I have not seen any valid resolution online, so as a workaround, I have setup the Execution policy to UNDEFINED on the following registry entry:
Computer\HKLM\Software\PowerShell\1\Shelllds\Microsoft.SqlServer.Management.PowerShell.sqlps110Execution Policy, REG_SZ,Undefined
And as a warning, changing the execution policy to UNDEFINED may open security holes on your server. If you found a fix for this issues, please feel free to leave a comment so I can update this post.
Update: I found a Microsoft KB article that talks about adding a GPO to correct the issue. http://support.microsoft.com/kb/2995870
Thank you
How to create a list of servers to be managed in SSMS
Most of the time when you try to connect to SQL servers, you often have to connect and enter in the name of the server/instance or you have to click on the drop down to show the previous server you have connected to. That works for me most of the time, but I am going to show you how to add those servers in such a way that you can easily connect to them by just clicking on the server name. And you can even setup different types of authentication used and connection properties.
First, you have to open SSMS and select View, then Registered Servers. And then you can add connections to the servers and instances via the roles assigned on your SQL server.
Select the ‘Database Engine’ icon if you would like to connect to the main database, right-click and select ‘New Server Registration’. And setup the server connection properties.
Finding out most recent queries run on a specific database
I found this useful script online to find out the queries run on a specific database. This script is useful when you are trying to go back and find out what was ran on the database.
SELECT deqs.last_execution_time AS [Time], dest.text AS [Query], dest.*
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.dbid = DB_ID(‘dbname’)
ORDER BY deqs.last_execution_time DESC
Reference: http://blog.sqlauthority.com/2008/01/03/sql-server-2005-last-ran-query-recently-ran-query/
How to move a user database to a different location
Here are a couple of ways to move the database files to a different location other than the default.
Using SSMS:
1. Using the SQL Server Management Console, right-click on the database, go to Task and Detach. You may also have to restrict access to the database to SINGLE_USER before you can perform the Detach if there is a process currently using it. To set it to SINGLE_USER mode, right-click on the database, properties, Options and scroll down to Restrict Access.
2. Once the database is detached, copy/move the data file (.MDF) and log file (.LDF) to the new location, and then Attach the database back. Right-click on Databases, select Attach. Hit Add on the Attach Databases window and then browse to the new data file (.MDF) location and select the database. If the log file (.LDF) is located on a separate disk, then you will also have to specify the path for the log file under the Database details window. Then hit OK.
Using T-SQL scripts with Detach:
1. You can run the following scripts to Detach, copy/move the data files and then perform the Attach command. I will use the TEST database with an example path.
USE MASTER;
GO
ALTER DATABASE TEST
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
EXEC MASTER.DBO.SP_DETACH_DB @dbname = N’TEST‘
CREATE DATABASE [TEST] ON
( FILENAME = N’Y:\DATABASE\TEST.mdf‘ ),
( FILENAME = N’Z:\LOG\TEST_log.ldf‘ )
FOR ATTACH
GO
Using T-SQL Scripts with Alter Command:
ALTER DATABASE [TEST] SET OFFLINE
WITH ROLLBACK IMMEDIATE
GO
— Please note that the EXEC XP_CMDSHELL command might not work if it is not enabled in your SQL server. You will need to enable it first or you can substitute this command with manually copying/moving the data files.
EXEC XP_CMDSHELL ‘COPY “C:\OLD_LOCATION\TEST.mdf” “Y:\DATABASE\TEST.mdf“‘
GO
EXEC XP_CMDSHELL ‘COPY “C:\OLD_LOCATION\TEST_LOG.ldf” “Z:\LOG\TEST_log.ldf“‘
GO
ALTER DATABASE [TEST] MODIFY FILE (NAME = ‘TEST‘, FILENAME = ‘Y:\DATABASE\TEST.mdf“)
GO
ALTER DATABASE [TEST] MODIFY FILE (NAME = ‘TEST_log‘, FILENAME = ‘Z:\LOG\TEST_log.ldf‘)
GO
How to move System database locations
–Move Model Database
USE master;
GO
ALTER DATABASE model
MODIFY FILE (NAME = modeldev,FILENAME = ‘E:\DATA\model.mdf’);
GO
ALTER DATABASE model
MODIFY FILE (NAME = Modellog,FILENAME = ‘E:\DATA\modelLog.ldf’);
GO
–Move MSDB Database
USE master;
GO
ALTER DATABASE msdb
MODIFY FILE (NAME = MSDBData,FILENAME = ‘E:\DATA\MSDBData.mdf’);
GO
ALTER DATABASE msdb
MODIFY FILE (NAME = MSDBLog, FILENAME = ‘E:\DATA\MSDBLog.ldf’);
GO
–Move Tempdb Database
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘E:\DATA\tempdb.mdf’);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘E:\DATA\tempLog.ldf’);
GO
To move the master database:
In SQL Server configuration manager, edit the advanced properties for the SQL Server Service.
Change the startup parameters to the new location of the files, –l flag refers to log destination and –d flag refers to data file destination:
How to sort data pulled from sp_who2
— using a temporary table
CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
Login VARCHAR(255),HostName VARCHAR(255),
BlkBy VARCHAR(255),DBName VARCHAR(255),
Command VARCHAR(255),CPUTime INT,
DiskIO INT,LastBatch VARCHAR(255),
ProgramName VARCHAR(255),SPID2 INT,
REQUESTID INT)
INSERT INTO #sp_who2 EXEC sp_who2
SELECT *
FROM #sp_who2
— Add any filtering of the results here :
WHERE DBName = ‘<database name’
— Add any sorting of the results here :
ORDER BY DBName ASC
DROP TABLE #sp_who2
— using a table variable
declare @tempTable table (SPID INT,Status VARCHAR(255),
Login VARCHAR(255),HostName VARCHAR(255),
BlkBy VARCHAR(255),DBName VARCHAR(255),
Command VARCHAR(255),CPUTime INT,
DiskIO INT,LastBatch VARCHAR(255),
ProgramName VARCHAR(255),SPID2 INT,
REQUESTID INT);
INSERT INTO @tempTable
EXEC sp_who2
select *
from @tempTable
where DBName = ‘<database name’
How to find path and file name of a specific document in Velocity
This is a procedure for finding a specific document path from Fiserv Velocity application:
use prod_docs
select * from prod_docs.dbo.imageallocation
where (AllocationID in
(Select DocumentKey from prod_app.dbo.documents where
documentid=XXXXX))
or use an inner join
select a.allocationid, a.folderid, a.imagepath, b.documentid
from prod_docs.dbo.imageallocation a
inner join prod_app.dbo.documents b
on a.allocationid = b.documentkey
where b.documentid=XXXXX
WIKIPinoy 